Excel Graphs, Infographics & Data Visualization

1. Introduction: David visualizations is a powerful tool for communicating information. Excel charts are an efficient means to visualize the data to convey the results. In addition to the chart types that are available in Excel, some application charts are popular and widely used. In this course, you will learn about these advanced charts and how you can create them in Excel. Hello, and welcome to the Microsoft Excel charts, infographics, and data visualizations masterclass course. My name is Andreas and I will be your instructor through this trip of knowledge. I'm a full-time teacher and have my computer learning school for over 20 years. This course will guide you through Excels Data Visualization futures from the shapes to conditional formatting to charts. I include several realistic case studies. You can see how business question is turned into a chart or a dashboard will start by why and how we visualize data. Then I'll walk you through the charting techniques to create and modify a chart quickly and easily. After that, we will chop up the defaults of Excel charts with some ninja skills to use what It's still does have and create powerhouse visualizations. Once we've mastered the basics, I'll walk you through all the tools to improve your graphics with conditional formatting, custom number formatting, and shapes. The next section is about infographics with itself. Infographics are studying graphic visual representations of information which are intended to present data quickly and clearly. You will be shocked at how easy you can add great looking infographics to an Excel worksheet. Then we'll dive into the real meat of the course, this section with custom build advanced charts. In this section, I'll make you a data visualization expert. Finally, I'll walk you through the next level, dynamic interpreted charts with help from a form control and formulas, and a ton of real-life examples. Make your reports look amazing. Thank you, and I'll see you inside. 2. Why Do We Visualize Data: Why do we visualize data? Why do we visualize data? Let's see why it is vital to visualize numbers by beginning with this table. There are four groups of numbers, each with 11 pairs. In a moment, we will create a chart from them. But before we do, let's take a look at the numbers. What can you see? Are there any discernible differences in the patterns or trends among them? Let me guess. You don't see anything clearly. It's too hard. Before we put the numbers into chart, we might consider their statistical properties. Where are we to do that? We'd find the statistical properties of each group of numbers are very similar. If the table doesn't show anything in statistics, don't reveal much what happens when we plot the numbers. Now, do you see the differences? Seeing the numbers in a chart shows you something that tables in some statistical measures. Cannot. We visualize data to harness the incredible power of our visual system to spot relationships and trends. Another reason to visualize numbers is to help our memory. Consider the following table which shows sales numbers for three categories by quarter over four years. What trends can you see? To read the table? We need to look up every value one at a time. Unfortunately, our short-term memories aren't designed to store many pieces of information. By the time we've reached the fourth or fifth number, we've forgotten the first one we looked at. Let's try a trend line. Now, we have much better insight into the trends. Office supplies have been the lowest selling product category and all the two quarters, furniture trends have been dropping slowly over the time period, except for a bump in sales in 2015 quarter four and arise in the last two quarters. Technology sales have been mostly the highest, but we're particularly volatile to start at the time period. The table and the line chart each visualize the same 48 data points, but only the line chart. Let us see the trends. The line chart turned 48 data points into three chunks of data, each containing 16 data points. Visualizing the data hacks or short-term memory. It allows us to interpret large volumes of data instantly. I hope you understood the power of visualizing data. In the next lesson, we're going to learn how to visualize data. Thanks for watching. 3. How Do We Visualize Data: How do we visualize data? We've just looked at some examples of the power of visualizing data. Now we need to move on to how we build the visualizations. To do that, we first need to look at two things. Pre-attentive attributes and types of data. Pre-attentive attributes. Visualizing data requires us to turn data into marks on a canvas. What kind of marks make the most sense? One answer lies in what is called pre-attentive attributes. These are things that our brain process is in milliseconds before we pay attention to everything else, there are many different types. Let's look at an example. Look at the numbers in this image. How many nines are there? How did you do? It's easy to answer the question. You just look at the values and count the nine's. But it takes a long time. We can make one change to the grid and make it very easy for you. Have a look at this image now. Now it's easy to count the nines. Why? Because we changed the color. Nines are red, all the other numbers are light gray. Color differences pop out. It is easy to find one red nine on a table full of hundreds of numbers as it is on a ten by ten grid. Think about that for a moment. Your brain registers the red nines before you consciously address the grid to count them color. In this case, q is one of several pre-attentive attributes. When we look at this scene in front of us or chart, we process these attributes and under 250 milliseconds. Let's try out a couple more pre-attentive features with our table of nines. In this image, we've made the 9th of different size from the rest of the figures. Differences in size or easy to see to size and Hugh, aren't they amazing? That's all very well when counting the nines, what if our task is to count the frequency of each digit? That's a slightly more realistic task, but we can't just use a different color or size for each digit that would defeat the pre-attentive nature of each color. Look at the mess of this image right here. Coloring every digit is nearly as bad as having no color. It's not a complete disaster. If you're looking for this sixes, you just need to work out that they are read and scan for those quickly. Using one color on visualization is highly effective to make one category stand out. Using a few colors as we previously did to distinguish a small number of categories is fine too. Once you're up to around eight to ten categories, however, there are too many colors to easily distinguish one from the other. To count each digit we need to aggregate. Visualization is at its core about encoding aggregations such as frequency. In order to gain insight, we need to move away from the table entirely and encode the frequency of each digit. The most effective ways to use length, which we can do in a bar chart. This chart shows the frequency of each digit. We've also colored the bar showing the number nine. Since the task is to count the nines and the data source, the bar chart is one of the best ways to see the results. This is because length and position are the best for quantitative comparisons. This series of examples with nines re-emphasizes the importance of visualizing data. We went from a difficult to read table of numbers to an easy to read bar chart. The series of examples we just presented used color, size, and length to highlight the nines. These are three of many pre-attentive attributes. This image shows 12 that are commonly used in data visualization. To recap, we've seen how powerful the visual system is and looked at some visual features we can use to display the data effectively. Lastly, we need to take a look at the different types of data in order to choose the best visual encoding for each type. There are three types of data. Categorical, ordinal and quantitative. Categorical data. Categorical or nominal data represents things. These things are mutually exclusive labels without any numerical value. Ordinal data. Ordinal data is similar to categorical data, except it has a clear order. Quantitative data. Quantitative data is the numbers. Quantitative or numerical data is data that can be measured and aggregate it. In the next lesson, we're going to learn how to make the right chart. Thanks for watching. 4. Making the Right Chart with the 4-step Process: Hello everyone and welcome to the first charting lesson in the course. In this lecture, we'll talk about how to make the right chart. Making the right chart is crucial if you want to make effective dashboards. So let's dive in. Bad graphs are everywhere and you can put some in Excel and create a graph. There is a story in your data, but your tools don't know what the story is. That's where you take it to bring the story visually and contextually to life. The whole purpose of making a chart is to communicate some information to an audience. Whether that's your boss, end-user, customers, someone else. That's why it's important to select the right chart and also understand how to make the right chart. Let's see an example before and after, to give you a visual sense of why you need to make the chart correct. In this table we have the sales of accompany. These are the sales for the year 2017 and these are the sales for the year 2018 per month. And here's a simple default column chart that we created to communicate the data to our audience. Now look at this chart. It's the same data represented much better with this graph. You should only select chart that's easy to interpret in terms of intended message. There is a four-step process that will help us to make the right chart. These are the four steps. Step one, understand the context step to arrange your data. Step three, choose an effective visual and stamp for removed the clutter. Let's explain these stamps one by one, starting from step one. Before you start creating the chart, there are a couple of questions you should be able to concisely answer. Who is your audience? What do you need them to know or do what data is available that will help them make my point. So you should always ask yourself with the message is usually we have six different types of messages. Comparison, distribution, parts to whole trend over time, deviation relationship. Here's a chance selection diagram created by Dr. Andrew bella that should help you pick the right chart for your datatype. You can find the PDF file for the resources if you want to download it. The chart selection diagram has four different messages. If you read some of Stephen views books, you might find seven or eight different types of messages that the result is the same. So our first topic is comparison. Examples include comparing sales of one branch with another. The second topic is distribution. A distribution message is very useful because you want to show how the values are distributed. And then based on the distribution message that your boss probably wants to make a decision on when to hire temporary employees or went to worry about increasing expenses, etc. The third part is parts to hold. For example, if you have various products and you want to show which product is contributing how much to your overall revenues. The next topic is trend over time. In examples, if you want to compare last year's figures with this years, the last two topics or deviation in relationship. These are the most common messages, but you can mix and match these messages to convey certain ideas. Now, the second step is to arrange the data. If the data that is already available, then we need to rearrange it or cleaning up the data. For example, you need to calculate the percentages of the numbers. You can also use formulas for that. Also, you can use tables and pivot tables to arrange your data. The third step is to choose an effective visual or in simple words, prepare the chart. To make an a common chart in Excel 2019 version, all you have to do is click on Insert tab from the Excel ribbon and choose a default chart from the selections. For example, column charts, line or area charts. Pi or donut charts. Treemaps are sunburst, story grammar box and whisker charts. Scatter or bubble charts. Waterfall, funnel, stalk, surface or radar charts. Finally, combo charts. There are two more special charts in Excel 2019, chart maps and pivot charts. Now for each message I have shown some of the appropriate charts. Starting from comparison, you can select any of these six types of charts. The next type of message that we have is a distribution message. For example, we want to show how the values are distributed. For that, you can use the vertical bar chart or the column chart if you want to show spatial distributions or how various values are distributed over the x and y-axis, you can create a scatter plot. And if you just want to show the distribution curves, just shows a line chart. Finally, the boxplot is an excellent example of the distribution of values and outliers for the parts of the whole. I usually prefer making a bar or column chart, but you can also use a pie chart and of course a table for the next message, the trend over time, usually a column or line chart is good. I prefer using a column or line chart and not a bar chart because time is usually perceived left to right in the horizontal direction. Also a table can be used for trends over time. For deviation, we use a column or bar chart or even a line chart, or even tables for conditional formatting. Finally, relationship messages are best expressed when we use a scatter plot or a line chart. And of course a table. Tables are always an option for almost all kinds of messages. These are the six different kinds of messages in the charts that we can use. The last step is removing the clutter, remember clitoris or enemy. And when we make a chart, we have to make sure that there are no distracting elements on it. There are some rules for when formatting a chart, like remove gridlines or make subtle grid lines. Also remove the chart border and use simple colors. You can also select colors from Page Layout tab to get some really good color ideas based on themes, Excel uses AD descriptive and bold titles and labels for important points. Of course, the x and y axis lines and labels to the background, making them gray. They should not compete visually with the data. In the next lecture, we're going to talk more about chart formatting. That's all for this lesson. Thank you all. And I hope that this lesson has helped you make better charts and tell the story in a better way. And don't forget to download the chart selection diagram created by Dr. Andrew Bella, which is great. If you want to take it to the next level, There is an excellent chart chooser tool by Jews analytics that will help you make better charts. Thank you, and I'll see you in the next lesson. Bye. 5. Introducing Excel Charts: Hello students and welcome to the second lesson on charting techniques. In this lecture, we'll start with an introduction to Excel charts go through various chart types and explains them formatting options. So let's get started to create a chart. The first step is to select some data. To add. Go to new chart, go to Excel ribbon, insert tab, click this arrow down at the bottom right corner of the chart selection. The Insert Chart dialog box appears. There are two tabs, recommended charts and all charts. If you're exploring charts in Excel and having a hard time to gray out which one is right for you, then you can try the Recommended Charts command. Excel will analyze your data and make suggestions for you. Click the All Charts tab. Excel users who have been using previous versions of Excel, like Excel 2013, can see a lot of charts available that are part of the default charts. The newer versions of Excel, like Excel 2019 in Excel O365, have several new chart types, including most notably a histogram, frequently used in statistics. A waterfall that is effective at showing running financial totals. A hierarchical tree map that allows you to find patterns in data, funnel chart, box and whisker and the sunburst chart. Later all teach you how you can create these beautiful charts step-by-step. I'll show you only the useful charts that we frequently use in reports and dashboards. You can also define your own charts and save them as templates and reuse them. They will be listed in the templates folder and we'll show you how to save a chart as a template in the next lesson. Unfortunately, I see too many charts that are discussing information rather than conveying it clearly and succinctly. The most common problems or the wrong type of chart and the overuse of formatting. There are a lot of useless chance that I do not recommend using. Here are some examples. Radar charts, donut charts, 3D line charts, pie charts, 3D column and bar charts, 3D unstack area charts. I do not recommend that you use these charts because they will probably distort your information rather than providing information. If you click in science and data and then press Alt plus F1 on your desktop keyboard, Excel will provide a chart and use the default chart type. Usually this is a clustered column chart, but you can change the default if you want to change the default chart type, all you have to do is right-click on the chart icon that's highlighted in gray and click Set as Default chart. Let's close the Insert tab dialogue box. You can also insert a new chart quickly by selecting the cells and pressing the F11 key. I have already selected some cells from A1 to F6, press F11 and the chart will be created and it will be inserted as a separate sheet here. To delete the sheet with the chart, right-click the same sheet name and press the Delete command. Click Delete again in this confirmation box. Now let's create a column chart from the answer tab. And this button, I have many columns and bar chart types to choose from, but I'll select the first one for you. Now, let's move it inside the table and increase the size a bit. The chart is ready. Notice the horizontal axis, it has the categories, cosmetics, alcohol, bakery, fruit, and totals. But the totals are not really a category of products. If you want to exclude the totals, right-click anywhere inside the chart and click Select Data from this Select Data Source dialog box search for totals. It's here inside horizontal axis labels. Uncheck the box and okay, the totals have gone from horizontal axis. This is a basic column chart created in Excel 2019. We have horizontal axis, a vertical axis with Values, labels, five series with five different colors, one for each year, and grid lines. To quickly identify a data series in a chart, you can add data labels to your data points of the chart. You can right-click one of these columns, for example, this light blue column, and choose to add data labels. The data labels were only added to these columns for the year 2014. Great. To make a chart easier to understand, you can add a chart title and axis titles to any type of chart. This chart has the title right here. To remove a chart title or to change the location of it. You can select the chart, go to the Design tab, Add Chart Elements, Chart Title, and you can choose the title type that you want. I'll select centered overlie to lay the title over the chart. Now let's modify this column chart to a line chart. Right-click on it, select Change Chart Type. Select the chart type that you want to switch to. I'll select the default line chart. Press Okay, now we have a line chart. Let's delete the labels by selecting them and pressing the Delete key. You can also change the chart type for our particular series alone. I'll make an example for this orange line, which is the year 2011. Right-click on it and select Change series chart type. Now changed the year 2011 to a clustered column chart. These kinds of charts are called combination charts because you have combined two different charts column in line in this example, for instance, press OK and see the result. Let's go ahead and understand how bar charts and pie charts work. I'm going to delete this chart. Will make a new chart from this data. Highlight the cells from A9 to B21. Go to the Insert tab, click in the Column button Vince like a two-dimension bar chart, move it to beside the table and resize it. One of the annoyances with Excel bar charts is that even though the order is in data from January to February, etc, the order of the churners reversed with January at the bottom, whereas December is at the top. This might be annoying if you want to change it, select the axis by clicking anywhere on it. Right-click and select Format Axis. The format access paint appears on the right side of the screen. Another way to fully open the pain is to click on it and press Control plus one. Control plus one is a very powerful shortcut. Whenever you press it, the selected object will be formatted. The Axis Options dialogue will open. Now you'll see down there an option called categories in reverse order. Click on it. Look at the chart. Excel will change the order of categories. Look at the axis moved to the top. And you can adjust that by selecting the option called horizontal axis crosses at maximum category. By this trick, we sorted the months, January at the top and December at the bottom, will now talk about pie charts. A pie chart is suitable when you want to show the distribution of values or parts to the whole relationship. So this table is suitable for a pie chart. Let's create it. Delete the chart, highlight again the cells from the Insert tab. Find the pie chart button. Select the first type of pie charts. Great. I'll move it there and resize it so that we can see the details. Usually pie charts are suitable if you have three to four values or even less than that. In this example, we may not be able to make out which slice is the largest because both of the large slices look fairly similar to me. That's why generally do not recommend using a pie chart for your reports. You can move this entire chart from this sheet to a separate worksheet from the Design tab. Click on this button, move the chart. From the dialog box. You can choose to move the chart to a new sheet. If you select the first bullet. Cancel that. Remember that these two tabs, design and format are the most important and contain all the chart tools that you want to change your chart by chart titles, access information, legend, data labels, data table, access grid lines, plot areas, trend lines, airlines, et cetera. Delete the pie chart. The next chart that we're going to talk about, his area charts, select the data from A1 to eat six. From this button, insert a two-dimension area chart. This is how an area chart works and it gives a visual representation of how much area of each portion is taken. In this example, some areas are overlapped, so I will change the area typed a stacked area chart. Press Okay. Look at that. All the years are visible in the chart. Delete the chart now. If you have data comprising of the product group, product and sales, like this table, you can select the entire data from cell A24 to S3 and make a column chart. Excel automatically groups the values for you like this on the axis. This is a very useful feature. Excel has automatically understood that these three categories are cosmetics and these three are alcohol categories, because the groups are part of the data. If I change the chart to a line chart, for example, the axis remains. But of course I do not recommend this type of chart to group values data undo. If you have a worksheet with data in columns that you want to rotate to rearrange it in rows, use the transpose feature. With it. You can quickly switch data from columns two rows or vice versa. Select the chart and click on the switch rows to columns button. When you do that, excel will switch everything. Now let us talk about scatter plots and bubble charts. Scatter plots or how values are distributed across two ranges. For example, the x-axis and the y-axis. When we see a table with daily ice cream sales, we measure how many ice creams were sold during the day. Select values and then from this button, select the scatter plot option. The x and y values are neatly shown as a scatter plot like this. This is the line chart without the line and the markers that we have seen just now. The horizontal axis shows the time and the vertical axis shows the sales. Let's delete the chart and move on to bubble charts. Bubble chart is the same as a scatter chart, but it can show the value at the point of intersection, whereas the scatter chart will just show the intersection point. A bubble chart can also show the volume at that portion. They are useful to show three-dimensions. Dimension one is the product, dimension, two is the market share, and dimension three is the actual value of sales. Highlight the data and insert a bubble chart. These are a bubble charts are each bubble represents a product. The bigger the bubble, the bigger the sales. Finally, we're going to talk about donut charts. A doughnut chart is like a pie chart, but it looks like a doughnut as it has a hole in the middle. We need data with two columns like this, one, month and sales. Now we're ready to insert the chart. Here is a doughnut chart. I do not recommend making a doughnut or pie chart unless you know what you were doing, will lie on the basic chart types or make a table, because tables are much easier to understand and they go well with the flow. Another nice tip if you want to duplicate a chart, is to select it and press control plus D, it'll instantly duplicate the chart with the same formatting, same data and same size. When you are moving the charts, if you hold down the Alt key, excel will ensure that the chart is aligned to the cell. If you align it to the cell than it looks neatly structured in the table. I hope you've learned the most useful charts in Excel and some formatting tips. In the next lesson, we'll dive into charting and formatting features. Thank you, and I'll see you in the next lesson. Bye. 6. Introducing Charting Techniques: In this video tutorial, we're going to look at some of the fundamental techniques for working with charts. In this example, Chris would like to create a column that would show the CO two emissions by different fuel types. You have prepared the data by showing snapshots of every ten years from 1937 to the present. The first thing that you'll need to do is select the data that you want to be included in your chart. It's also very important that you include the labels. We're going to select from here all the way across to other and not include our totals. We're then going to come inside of our Insert tab in the ribbon, and in the middle of the insert is our chart group, from what you can choose a great selection of charts. If you have the recommend charts option, which is available in newer versions of Excel, that's really worth having a look at as it will often give you the chart type that you want. If you're working with an older version of Excel, however, you may not have it. We're going to do it the manual way. Now, to create a column chart, you can come to the column chart icon, click the drop down, and then choose the column or the bar chart that you want. In this case, a two D clustered column. There is, however, a keyboard shortcut that you can use as well, and that is Alt F one, and that gives you a clustered column, which is great, where there is a cluster of columns for each row. Now, probably the first thing that you want to do is move and resize your chart, and when moving, do be careful to not select a piece of the chart because then you'll just move that piece, which case immediately undo. Stay near the edges of the chart and then just click and drag. To resize, you can use any of the resize handles and you can just click and drag away from the middle to make it bigger or towards the middle to make it smaller, and there is our chart. There is one immediate problem. Our years have not appeared as our x axis. If you look closely, they actually appeared as a series, and that's because they are numbers, and as far as Excel is concerned, if it sees a number, it tries to chart it. One of the first things that we're going to do is we're going to have to address this issue. Now we need to remove as a series, and there's a really quick way you can do that. You'll notice that the data is surrounded by a big blue box. If I come over to the corner and I click and drag, I can actually change what data is included. I've removed it as a series, but now I need to add it as labels. Now, with your chart selected, you'll notice that you have two new ribbons. The names vary across different versions of Excel. In this version, it's called Chart Design. In older versions, you may see chart tools design, but either way, please click on this ribbon and then come to select data. The select data dialog allows you to manage exactly what data is or isn't included in your chart. You could see, I can go and add and remove series over here. But more importantly, you can change our horizontal axis labels. I'm going to click Edit. And then you can literally just select the labels that you want to see included and it's fixed already. I'm just going to say, and okay. Now, it's quite possible that having created your chart, you look at it and decide that the different chart type might be a better thing. And certainly, with this many datasets, this graphic is looking a little bit busy. Let's have a look at another alternative. Okay. We're going to come up to our chart design ribbon and we're going to click Change Chart Type. Then we can swap this for any chart type that will support this type of data, and a stacked column in this case, may be a better option. Do be careful, though. There's also a 100% stacked column, and that shows the stacked out of 100%, and that's not what we want. Make sure that you've got a stacked column and then say, Okay. What this has done is it's taken all of your little clustered columns and piled them on top of each other so you can still see the breakdown. But you can also more easily compare the total emissions for each year represented. Now, let's talk about making our chart look a little bit nicer. To begin with on your chart design tab, you'll see a gallery of styles, and as you hover over these different styles, it gives you a preview. If you want even more options, you can click the drop down and you'll see even more options in there. These are really good quick options, but we're going to do them manually so you can see all of the alternatives. First of all, my legend. It looks fine, but I'd like to change the position. I'm going to come to this little green plus to the right of my chart, and this shows me all the different components. From here, I can switch them off altogether, which I don't want to switch my legend off, so I'm going to turn it back on, and if I click the little black arrow to the right of it, you'll see that I can change the position. If I were to move it to the left, for example, you'll see that it just adjust the whole chart accordingly, but I'd like to see it at the top. Now, I should probably also change my chart title. Your chart title would normally show by default. But again, if it doesn't, you can always just turn it on in the chart elements dialogue. Clicking into my chart title, I'm actually going to need to replace this text. If you highlight and press delete, you may delete your title by mistake. So I just suggest press Control A to select everything and just over type. I'm just going to type in emissions by fuel, and then you can click away. Another thing I'd like to address is my vertical axis. Because the numbers are so large, these numbers are actually quite hard to understand. So I'm going to click on my vertical axis, and if you want even more options than available from the plus, you can either right click format or just double click, and it will open up a panel on the right. And this gives you a full range of options, changing the components within your chart. Now, first of all, please select axis options, which is the little bar icon, and then we go up axis options, and the one we want to change is the display units. These are such big numbers that I'm actually going to set to billions. Not only is it much more readable now, but it's also put in a label for me. That was just one of many options. I encourage you to explore and see what else you can do in here. Let's make one final aesthetic tweak. I would like to manage each of these bars a wider. I'm going to select one series. It doesn't matter which, and that's which is the gray panel over to show me the formatting options for my data series. The gap width between these columns is really wide. I'm going to change it to 50%, which reduces the gap width and thus increases the width of each column. And I think that looks much nicer now. Well more Chris wants to do, he liked to include his percentage increase in this chart. Now, previously, we saw how we could just adjust this area, but that only works with data that's right next to each other. Obviously, this is not sitting with that data. We can come to select data and edit from there. But there's a quick and neat trick that is not as obvious, which I'd like to show you. We're actually going to select the data from H three, including the heading and write down to 16%. I'm going to copy Control C, then I'm just going to click on the edge of the chart so it's selected and I'm going to paste Control V. That is actually added my percentage increase. But I can't see it. And the reason for that is that if these numbers are going up in billions, whereas my percentage increase are all less than one, so the values are so tiny that I can't actually see them. In order to compare data of different units, what you need to do is put this in a second axis. And the easiest way to do that is to come to the chart design. Come to chart design type, and at the bottom, choose combo. Now let's switch everything to a clustered column for a moment, but don't worry about that, okay? We're just going to scroll down, and we actually like our other to go back to being a stacked column. And you'll see that when you do that, it switches everything else back to stacked. We do want our percentage increase to be aligned, but the important thing is we want that to be a secondary axis. I'm going to tick and say, Okay. Now it has created a secondary axis showing our percentages, and we can see our percentage increase along with our billion tons of emissions. There's our finished chart, which is now looking very great. 7. Identify different chart elements: Welcome back, everybody. In this lesson, we will learn and identify different chart elements. Let's dive in. In this chart elements worksheet, we have a number of different charts on it. Notice I'm using the word chart officially and formally in Excel, these various images that we're seeing here are referred to as charts, not graphs. Outside of Excel, the word graph is perhaps more commonly used. If you go into the help system and type the word graph, you will be directed to a description of what a chart is in Excel. As we work with different kinds of charts on this worksheet, we will see column charts. So pie charts, some line charts, and some others as well. Certain terms will pop up repeatedly, and we want to be able to identify what are called chart elements, the pieces of a chart. In the chart below the data over on the left side, the chart with the turquoise background is a column chart. As I slide them into the upper portion of it, we see the term chart area, the perimeter of any chart if referred to as the chart area. The inner area that holds the columns of the lines is referred to as the plot area. If you point to one of the lines we see across the chart here, you'll see the term grid lines. Now, it says vertical here, but that refers to the axis over the left hand side. You might have a title down the left hand side, and you'll see the term vertical value axis title. There could be a title on top and there often is. That's a chart title. And I'm sure some of these terms are not surprising neither. And off to the right hand side and the same chart where we see the months, that's referred to as the legend. The chart to the right, the one with the green background is the line chart. I just pointed into the lower left corner and that's the chart area. Remember, that's the perimeter area. The inner area, if we're not pointing to a line is the plot area. If you happen to point to a line, then you'll see the term series emerge. Same thing will happen on the chart column off to the left point of one of those columns, we will see the term series. Now these are not exactly definitions, but we start to become familiar with the terminology. The three D column chart, the one with the yellow background is certainly a bit unusual compared with others. We don't see these as often, but here too, there will be some terms we'll be familiar with if we use these kind of charts. The left area will be referred to as the back wall off to the right side wall. And down below, you might have guessed it already, that's the floor. And below that, there's a legend. And off to the right, that pink background is a pie chart. The inner area that holds the pie chart is called the plot area. And we've seen that before, and sure enough, the outer area chart area. And slitting every one of these sectors, we see the term series. So these are the terms, the so called chart elements we become familiar with. And all these different chart types are using those same terms in slightly different ways depending upon the chart type. When you select a chart, in other words, you click on it, that activates two new terms in the menu. Sometimes this is referred to as a new ribbon called Chart Tools. There is a design tab and a format tab. And if you happen to click on the format AM off to the left, you'll see in this group called current selection up above term that's currently selected that says plot area. Let's click the drop arrow to the right and you'll see some of the other terms. If we do this for another chart, I'm going to click on the blue chart below the data off to the left. There's that drop down arrow again over on the left hand side, and we see some of the terms that we've been talking about. So depending upon the charts that you've become familiar with, then it becomes easier to work with these various chart types as you establish familiarity with what we call the chart elements. If you happen to double click, for example, a column, a line, both of those are part of the word series. This will activate the dialog box off to the right. We're not going to be making any changes here, but the keyword here is the word series, format data series. Let's close that for now. What would happen if we double click on a legion? I'm about to double click on the legend in this line chart. Double click, that activates the dialog box format legend. We see the terms popping out that way as well. So helpful to understand the elements, the various chart elements as we work with charts in Excel. 8. Deal with empty and hidden cells: On this worksheet, we're seeing a line chart, but it's quite a bit different on a typical line chart. In it, it has gaps, and it reflects on the fact that the data over in columns B through H has some empty cells. But let's show some options here on how we can deal with empty cells. If you click on a chart and select data, or if you go to the chart design tab on the top ribbon, you'll see a choice called select data. They both do the same to this dialog box, lower left corner hidden and empty cells. The default setting in Excel to show the empty cells as gaps. Now, if you can imagine a column chart here, we just wouldn't see columns for these missing points here. But here's a line chart that stands up very obviously. Could we make this a zero, or if it made sense to put a zero in the data? We could either do that or in this case, simply choose zero. And all those points would be filled in on the chart with a zero value. And so if we'll look at Arizona, that's the big blue line you see near the bottom of the chart there, we're going to see that jumping to zero for February 3. Click Okay. Okay. And now we see those values. Every time we see a yellow cell over there on the data, we're seeing the zero value being a representative. Again, depending upon the data that could be appropriate. Let's revisit this again. Let's go back to select data, hidden and empty cells. Instead, we could choose connect data points with line. And the idea here could be, well, maybe we just didn't report that day. What if these were meter reading, something along those lines, and we just missed the day here, on there, and let's just interpolate the value. So if we're looking there at Arizona, no data for February 3, that's between the value. The value of seven is as if we're putting in the value of six here, now that won't really change the data, but that's the way that will appear on the chart. Connect data points with line. Let's not show any gaps. Click Okay. Okay. Now there are no points there, and that's a slight difference, but you see what's happened. For example, this dark green line right here, this represents Colorado, that February 5 entry, which is blank, we see the February 4 and the February 6 entries connected by a line. Maybe for a moment here on this charter for a while, we don't want to see the data from the Midwestern states. Or altogether here, rows five, six, seven, eight, and I'm going to hide those rows. And as I do, watch the chart. We don't see the data. When you think about it, if you've got a chart and is an appropriate source of data nearby, you want that one to one visual relationships. When we want to hide the data, you have to see the data in the chart. I doubt it. But if we did, we could right click on the chart, Chart Design and select data, This time, hidden and empty cells show data in hidden rows and columns. Click Okay. OK. So we're seeing the data even though it's hidden. Let's go back to select data, hidden and empty cells and turn off that feature. Let's say that nearly always, we would not want to show the hidden data when it's part of a chart source data. Eventually, at some point, we probably click on the upper left corner, right click any row number, and bring back the data by unhiding those rows and then seeing these in our charts. Different techniques here for using features available from that hidden on a chart design tab called select data hidden and empty cells. 9. Camera Tool: Excel's camera tool is really useful tool that enables you to take a live picture of a chart or a range of cells that updates dynamically wealth of data updates. Although Microsoft has chosen not to include this tool on the mainstream ribbon, it's actually quite useful if you're building dashboards and reports. If you search ribbon, you cannot find this tool and you have to add it. How can we do that? Go to the File tab and select options. From the list on your left, choose the Quick Access Toolbar, and there are two columns from the left column and drop-down list select commands not in the ribbon. Find the camera, and press the Add button. Now the tool is added here. As you can see, how does it work. To use the camera tool, you simply highlight a range of cells, then capture everything in that range in a live picture. The cool thing about the camera tool is that you are not limited to showing a single sales value as you are with a linked text box. Because the picture is live, any updates made to the source range automatically changed the picture. Highlight the range from A1 to age 27, which includes the data table on the chart. The goal here is to create a live picture of the range that holds both the numbers in the chart. Select the camera tool icon on the quick access toolbar. Click the worksheet in the location where you want to place the picture and click the mouse. Excel creates a live picture in the entire range. Let's move somewhere there and rotate it a bit. The cool thing is that every change will appear in that picture. For example, go to sales data type 500 and B3 and enter and see the result, it updated immediately. Of course, we can use the cool effects and tools from Format tab. For example, I'll choose this picture style. By default, the picture that's created has a border around it. To remove the border, right-click the picture and select format picture from the menu that appears. This opens the Format Picture dialog box on the colors in Lines tab, you can see the line color drop-down list. There. You can select no color, thereby removing the border. On a similar note, you can get a picture without grid lines. Simply remove the grid lines from the source range. The camera tool is very useful when we create dashboards. Here are a few ways to go beyond the basics and Use Camera tool to enhance or dashboards and reports, consolidate disparate ranges into one print area. Sometimes a data model gets so complex that it's difficult to keep one final data in the printable area. This often forces you to print multiple pages that are inconsistent and layout and size. You can use the camera tool in these situations to create live pictures of various ranges that you can place on a single page. Rotate objects to save time. Again, because the camera tool outputs pictures, you can rotate the picture in situations in which placing the copied range on its side can help save time, creates small charts. When you create pictures with the camera tool, you can resize and move the pictures around freely. This gives you the freedom to test different layouts and chart sizes without the need to walk around column widths, hidden rows or other nonsense. That's how you can use the camera tool. Thanks for watching. 10. Charts with Tables: Tables are special areas in a spreadsheet that hold data. They're meant to be like database tables, but they only share some of the same properties. The two main advantages of using tables are that sill formulas and formulas are consistent for all the rows and references to the table grow and shrink automatically as the table grows and shrinks. Here I have some data in regions and the sales for each region in each year. The first step is to convert a normal Excel range into a table. To do that, select Range A1, C6. Then from insert tab, choose table and from the ribbon to show the Create Table dialogue. This dialogue confirms the range that you want to convert and lets you specify in the first row of your range contains headers. Click Okay to finish creating the table. Next, select the table. Choose Insert Recommended Charts from ribbon. Choose the clustered column option to insert the chart. You can change the chart title, but it's not necessary for this example. Now, imagine a scenario where you want to add the next year into this chart. And you want to split Texas out of the Southwest region and make it its own region. With the normal accelerate, you would add a new series for New Year and adjust the existing series to a point to the larger range. But because you're using a table, you can simply add the data in the chart will adjust to add the new year enter 2023 in cell D1, Excel has increased the table columns for a few range and is already added a new series to the chart. Next, enter Texas into cell A7. Excel will increase the table rows and you can enter values for the new year and split the southwest region. You can now see a new table size and the resulting chart. Excel has automatically added a new clustered column to the chart for a new region. And 2023 series is populated with the data entered in the table. Let's add some data. The new data entered for Texas for non-existing years is automatically formatted with the same number format as the other data in those columns. Nothing on the chart was changed. You only had to add new data to the table to achieve these results. The chart in the previous example adjust that as the data in the table was changed, when you refer to tables and formulas using structured table referencing, your formulas will automatically account for changes in the table two. For this example, I will change the name of the table to table revenue from Excel's default table name. To change the name of the table, select any part of the table and choose the Table Design tab, then table name from the ribbon and type the tables new name. To create a formula using structured table referencing selected unused cell in the worksheet, for example, B9, and enter the start of a formula like this. Equal sum open parenthesis, and point to the range B2 to B7. If you enter dated in a cell that adjacent to the table, excel will assume that you want to expand the table. So choose a cell away from the table. By default, Excel convert your formula to structured table referencing the formula below, equal some parenthesis, table revenue, the year 2021 in brackets. Instead of referring to the range B2 to B7, Excel converted the argument to table revenue 2021, referring to the column labeled 2021 in the table named table revenue. If you're staging data for a dashboard, you can use a structured table referencing to make sure your formulas always covered the data. You don't have to point to the data to get structured table references. You can enter that same formula manually. Start entering a formula by cell B11 by typing equal some table revenue into a cell. See the drop-down box showing all your choices that contain those letters. If you have more than one table in your workbook, you choose to name them all starting with the same letters, like table. This method gives you a handy list of tables to choose from. In this example, there is only one table and it is selected because it is the first one on the list. From here you can press the Tab key to complete the table name without having to type at all. After the table name, open an open bracket to see a list of options related to that table. Notice the list of options for table revenue. The first option is the at symbol. When you are using formulas within a table, the add symbol tells Excel to return the value from the same row as the formula. The reference at 2021 returns the value from the column name 2021, that's on the same row. Contrast that to the reference 2021, which returns the entire column. The next four options are the column names in the table. Excel is aware of the structure of that table and auto-generate names ranges for the columns. The less we're options are references to special areas in the table. The hash all reference refers to the entire table including headers and totals. The hash data reference is the same as hash, all excepted excludes headers in totals. The hash headers in hash totals, references refer only to their respective areas. You can narrow it down to the option you want and press Tab to complete the entry. For example, if you arrow down to the hash data reference, press tab and enter a closing bracket and closing parentheses. You will get the following formula that sums all the data in the table, equal SUM, parentheses, table revenue, hash data in brackets. As you can see, tables at many advantages. If you have any questions, please let me know. Thanks for watching. 11. Sharing Charts: Excel is an excellent tool for creating visually appealing charts. Sometimes, however, we wish to see those charts in our presentations or our reports. The good news is that those charts that we create in Excel can be easily shared with other applications by simply copy pasting. There are a variety of paste options that we should be aware of. So let's take a look at them now. We're going to start by copying our regional sales chart into a word report. We follow the same copy paste process as before. Select the chart by clicking on it. Come to your Home tab on your ribbon and click Copy. You can also right click and use the keyboard shortcut. Now, go to the application you wish to paste onto. We're pasting into Word document, but you could just as easily be a PowerPoint presentation or many other software applications that will also let you paste charts into them. Now, click on the document where you want the chart to go and either click Paste or your shortcut key Control V or Command V on a Mac. The chart is immediately pasted in, but you will notice the fonts and the colors have changed. This is because if you work in a Microsoft application or default, it will use the destination theme, which is a set of colors and fonts. This is good because now it means that the chart matches the other items in this report. If that wasn't what you wanted, however, you do have other options that you can select. If you click the drop arrow, there are, in fact, five options that we can choose from. By default, when you paste, Excel uses the destination theme and links the data. If I'd want to keep my original colors, I could choose to keep source formatting and link data. Now, what does that mean to link data? Well, when we actually pasted it in this chart, it kept the link back to the original Excel workbook, which means the values in that workbook change, this chart will update. Let's see how this works. So, coming back to my Excel, I'm going to change my Sydney figures to 150,000. And when I press enter, you'll see that this chart has, of course, changed. But what's more impressive is that when I come back to my Word report, it has changed here as well. So really powerful options for keeping your charts linked back to the original source. In certain circumstances, however, you might not want that. So you have two other options that you can consider. You can embed the workbook using destination theme or embed it using the source theme. So again, you get that color choice. If I embedded the workbook, what that means is that it is no longer linked back to the original. It has taken a copy of that original worksheet and embedded it in this word document. So I can still edit the chart and change the figures, but I'm no longer linked back to the original. There is one more option if you wish to avoid the complexity of either linking or embedding, and that is the option to just paste the picture. Now, here you don't get a color option because it literally takes a snapshot of the chart and treats it like a picture rather than a chart and paste it into your document. This cannot be embedded. But again, you don't have the overheads that either linking or embedding might introduce. So five options to choose from. If you know at the point where you come to paste, which option you're going to use, you can go directly to that option by using the right click option. Let's see how that works. So I'm going to come back to Excel, and this time, I'm going to select my pie chart, right click and copy. Okay. Back into my word report, I'm going to scroll down and I'm going to right click and there are my five options. I have to choose one of them. Let's go for a keep source formatting and embedded workbook. And there I have done my paste option all in one without having to go and revise my options. Very easy to share your charts just by using copy paste. Don't forget to select the option that you want. 12. Mastering Basic Charts - INTRODUCTION: A common question among Excel users is, how do I know which chart type to use for my data? Unfortunately, this question has no cut and dry and answer. Perhaps the best answer is a vague one. Use the chart type that gets your message across in the simplest way. A good starting point is Excel's recommended charts. Select your data and choose from the Insert tab, recommended charts to see the chart types that Excel suggest. Remember that these suggestions are not always the best choices. This worksheet shows the same set of data plotted by using six different chart types. Although all six charts represent the same information monthly website visitors, they look quite different from one another. The remainder of this section contains more information about the various Excel chart types. The examples and discussions may give you a better handle on determining the most appropriate chart type for your data. So with that, let's go ahead and get started. 13. Line Chart: Multiple Lines: The line graph is one of the simplest graphs that you can make an Excel, the most effective visuals are often the simplest, and line graphs are some of the easiest to understand. Line graphs, also referred to as line charts, are used for plotting the data series are several against category values to display trends. These graphs are commonly confused with x, y, or scatter charts, which unlike line graphs, have no categorical values. If you have data with labels, numbers, or dates and are looking to display trends in that data, create a line chart. A few significant advantages of line graphs are they show things well over time. The make it easy to see multiple sets of data. It's easy to read and additional information is easy to see like trend lines and other statistical nominations. So let's dive in. Once your data is properly formatted, you can create your chart. It only takes a few clicks. First, highlight the data that you want in the graph. Then open the tab in the ribbon, the chart group. Click inside line and area chart button. You can make your line chart in Excel with six different line types. Select a line chart with markers. Delete the title. We have created a multiple line chart. Excel's default colors and design for line charts are nice, but there's a lot of customization you can do to make it look better. The easiest way to do this is with chart styles. From this button, select this style in this palette. You can also add individual elements in the Add Chart Element menu or from this plus icon. Let's add a trend line. A trend line is also referred to as a line of best fit, is straight or curved line in a chart that shows the general pattern or an overall direction of the data. Check the trend line box to insert the default linear trend line for the 2018 sales. Now, click again the plus icon trend lines. Click this little arrow and select more options. This will open the format trendline pain where you can switch to the Trendline Options tab to see all the trend lines available in Excel and choose which one you want. The default linear trend line will be pre-selected automatically. Optionally, you can also display the trend line equation in the chart. Click on the moving average. By default, the line graph in Excel is drawn with angles, which works fine most of the time. However, if the standard line chart is not beautiful enough for your presentation or printed materials, there is an easy way to smooth out the angles of the line. Here's what you do. Double-click the line you want to smooth the Format Data Series pain switch from the fill in line tab and select the smooth line checkbox. To make it simpler, remove the grid lines of the chart and the worksheet. These two legends also boarded the chart from the Format tab and shape outline button. Done. A line graph might not seem like much. It's only a line after all, but it can be very powerful as a tool for presenting data. I hope you enjoyed this lesson and I want to thank you for watching. 14. Column Chart with Upper and Lower Limits: Hi, everyone. Welcome to this video tutorial. Bar and column charts are very useful when it comes to comparison. In this worksheet, I have the sales of three products, soft drinks, cosmetics, and food products. Also, the table has two more columns, lower limit and upper limit sales, a target that's been set by the company. We want to compare the sales of a product with lower and upper boundary defined by specific criteria. Let's get started. Select the data and insert a clustered column chart. Move the chart below the table. Click the switch row to column button. So we have this chart with three columns. The blue column is sales. The orange is lower limit, and the columns with the gray color is the upper limit. We want the sales to remain as columns and the lower and upper bounds to be shown as markers and not columns. To do that, right click on either one of them and choose change series chart type. From this dialog box, we can easily change the chart type. Select lines with markers for the lower and upper limits and press k. We don't want any connecting line for the upper and lower boundaries. Let's select this upper limit line. Right click Format Data Series. Okay. Fill and line bucket and click No line. Do the same for the lower limit line. So we have lower and upper boundary indicated by dots. It's better to change the marker style from the format data series pane, marker options. There are some built in markers, but I would prefer to use custom shapes. The first step is to insert a down and up arrow from the insert tap, illustrations and shapes. Select the upper arrow. And place it somewhere here. Now, insert the down arrow. Change the color to green and red respectively. Let's resize them to be reasonably small. Now, copy the up arrow and paste it to the upper limit shape. Then copy the down arrow and paste it to the lower limit shape. With this trick, you can instantly change the marker symbol to anything you want. Our chart is ready. I hope you have enjoyed this lesson. Thank you, and I'll see you in the next lecture. Bye. 15. (NEW 2024) Pies and Doughnuts: Hey, what's going on, everybody? It's Andreas. We hope you're doing well. In this video, we're going to take a look at how we can create some very creative visualizations using pie charts and doughnut charts. Now, we're not going to create basic pie and dout charts. As you'll see, by the end of this video, we will make some really impressive pies and doughnuts that you can use on reports and dashboards and wow your boss. In the first example, I would like to show how in 2023, half of the world's emissions came from just three countries. Okay? And we're going to start by selecting our data. So let's highlight the cells from A three to B seven. I don't want the world total for now. We're going to come up to the insert tab. We're going to choose Pie chart. I'm just going to go for a two D pie chart. The first thing we're going to do is move a little bit and resize slightly, so it fits nicely under the data. Right? Now, while we can take a pretty good guess at these segments, it's useful to have the percentages. And a quick way of adding them is to come to your chart design ribbon. Come to Quick layout, and these give you some instant presets. I'd like to choose the first one, layout one, which gives me these series labels and percentages. Now, if you want to format these, just click on any one of the nice labels. They're all highlighted, and then you can come and make them white. Nice. Let's change the size to eight and you can make them bold if you'd like. And then we're going to come to make one final little tweak here. Click on the plus sign chart element. We're going to turn off our chart title, and we're going to replace it with an image. So make sure that your chart is still selected. Come to insert pictures, play over cells, and navigate to the folder that you have the CO two image. I have it here in this folder. I have included the image. You can find it in the resources files. We'll resize that slightly and just pop it into the middle of our chart. Very cool. That's a very nice way of presenting our data. Now, one thing to consider is that all of these countries actually have extremely large populations, but one limitation of pie chart is that you can only show a single series. So you can't show the population data on this chart. Fortunately, we have a new type of chart called the doughnut chart, which is very much like a Pi, but it will let us show multiple series. So we're going to copy our Pi. So Control plus C, and we're going to click into the cell next door and control plus V to paste. We'll get rid of our image for this one, and then we're going to come to our chart design. Come to change the chart type, and we're going to switch that over to a doughnut chart. So, similar concept, but we have a hole in the middle, and we can add multiple series. Coming up to our data, we're going to grab that blue square bottom right corner and just drag across. And now we can see our population as well.'s a little messy, though. So let's do a little bit of a cleanup. First thing we're going to do is to click on the labels for the outer ring. All right. Now we're just going to switch those off. How? Click the chart elements plus icon and uncheck the data labels. Nice. And then we're going to click on the labels in the inner ring. Right click format data labels, and we're going to come to our label options. Let's switch off the percentages and we don't need the leer lines either. And then if we just select one of those rings, it doesn't matter which one and come to the format data series, you'll see that you can actually change the whole size. We can make that 25%, and that gives us a little bit more breathing space. Very cool. Looking pretty good, but I'd like to make the outer ring look slightly different. One way we can do this is by adjusting the color slightly. So I'm going to click again into one of the segments, such as that one segment selected and come to my format data point label. And I'm going to set that to solid fill. Now, that may change the color. So we're going to set that back to our dark teal, and then we're going to change our transparency to 35%. So it's still the same shade, but it's just a little bit lighter. So we're going to do that for the orange as well. I'll do the same thing with the dark grain. And then the same turquoise color. Now, the only sort of downside is that it's not easy to label the actual rings themselves. You can use the category labels, but an easier way is to just use text boxes. So I've gotten pre prepared over here, population and emission. For example, I'm going to copy population, click into my chart, and paste them. And then just move that somewhere into the outer ring. Then I'll do the same with the emissions. Copy, clicking my chart, control plus V to paste, and then pop that into the inner ring. And the reason we copy pasted rather than just dragging them is that when we move the chart, you'll see the text box move with it. So that's one way that we could represent two sets of data, but in a Pi format. I'll leave you to decide what is the most effective option. But the reason I wanted to introduce you to this multiple ring option is that it does open up a lot of creative options for creating really effective visualizations. The next one we're going to look at is called a tracker. Here is a tracker. So this tracker is showing us that there's been a 17% improvement in energy efficiency for the USA 2012-2022. To prepare this data, I have calculated the improvement, and I have also calculated the offset. How much has it improved by by just taking one minus the improvement. And these are the values that we're going to use inside our tracker. We can now build a tracker for China. Now, the first thing we're going to do is build a series that forms this little gray backdrop feeler. If you scroll across to column L, you'll see that we actually have 60 ones that I have prepared earlier. That's all there in the column, and we're going to select them. So click L two, Control plus Shift plus down arrow, and we're going to come to our Insert tab. Come to our charts, choose Pi, and then select the donut chart. Now, we're going to need to move this up a little bit. Okay? Now, the first thing we're going to need to do is to turn off our title, our legend from the chart elements plus icon. So let's get rid of both of those. And then we can resize that, so it fits nicely in the space beside the other charts. Now, clicking into our series, we're going to change our whole size again, right click Format Data Series. This time, I'm going to set it to 50%. Then we're going to come to our fill color. So this little bucket icon, okay? And we're going to say solid fill, and we're going to set the whole thing to pale gray. So that's created the backdrop. Now, to add the improvement, we're going to add another series. So we're going to come to select data from the chart Design tab, and we're going to click Add. Our series name is going to be our selected region, so click on the China L. And our series values are going to be improvement and our offset. And we're going to say and okay again. The problem is it's showing as concentric rings. But I'll show you how to solve this problem. Clicking on one of the rings, we're going to come to the chart type. From combo charts, we're going to put that on a secondary axis. Then we're going to say, Okay. And that overlays the first series with the second series. Let's do a couple of little tweaks. Click the ring once and then twice on your percentage improvement and turn on your data labels. I think this will make those white as well. And then click on your offset once and twice to make sure that it's selected. Come to your fill options and set that to no fill. Finally, to get our chart title, we're going to use the text box again. So I'm just going to say Insert. Come to shapes, choose the text box, and a nice text box to go into the center here. Then we're going to come into our formula bar, type equals and click on our region name. And then press Enter. Probably a good idea just to set that vertically and horizontally. And maybe just adjust the size slightly down to a ten. And now we can straightaway see that the energy improvement for China was 45%. Oh, let's remove the border also from that text box. I will also remove the outline from the chart. Very cool. Not only that, but these trackers are completely dynamic. So if I come and change my improvement percentage, for example, to 60%, you see it adjust. And if I change my location to Greece, it also adjusts. It's a really effective visualization and nice, creative way of presenting your data. Okay. 16. (NEW 2024) Area charts: Area charts are very similar to line charts in that they show change over time. The key difference being that the area between the line and the x axis is shaded in. In this video, we're going to work with area charts. For example, taking our long term CO two. I'm going to call up to chart sign. Come to change chart type. Come down to area and choose area chart. And when I say, very similar, but arguably a little bit more eye catching. There are, however, situations where the area chart may prove to be a little more effective for visualization. Let's look at an example. We're going to come to emissions by region. All right. I've already created a line chart tracking the increase in emissions by the major world regions. Unfortunately, this chart is really messy and not so easy to read. What I would ideally like to show is the proportional increase by region and emissions as well as the total increase. And I can do this by using a stacked area chart, okay? So we're going to click on emissions by region chart, and we're going to come to change chart type, and we're going to come down to area. If we were to choose just the standard area chart, you can see that it's not really going to solve the problem. If anything, it's making it worse because I can't see all the areas that are hidden behind the others. I'm not going to choose that option, right? I'm going to choose the stacked area, and we're going to say, Okay, And there is a very attractive readable chart. Great. Let's copy that. Just click on the edge Control C, and come back to our area worksheet and we'll pop that into B 25. If you see some security notice from Microsoft, just ignore it and click paste everything. Now we've got one final example that we're going to create from scratch. We're going to come to our last lab, which are emissions by sector. But here I want to do something quite different. I actually want to see how different sectors proportional contributions to CO two have changed over time. We're not looking for the actual amount, but the proportion, a little bit more like a pie chart. For this, we're going to use a 100% stacked chart. Let's start by clicking into C three, transport. Hold control and shift key together and press your right arrow and your down arrow. Then we'll lease control and shift, and we're going to come to our insert tab. We're going to come across to the line chart, but we're going to choose the 100% stacked area. This will show us the percentage contribution of each of our sectors. With the line chart still selected, we're still going to come to format, and we're going to make it five inch high by nine inch wide. All right. And now I'm going to move it up so you can see it more easily. We're going to need to fix our horizontal axis. Let's click to Chart Design. Select data. Then click Edit Under horizontal Category Axis. Click on to 1960, and Control Shift down arrow. And then press and click Okay. All right. Here you go. Of course, we should give this chart a proper title. Clicking into Chart title, I'm going to press Control A and type emissions by sector. Then one final little formatting tweak we'd like to make. Instead of showing the labels on the legend, we would actually like to see the labels on the segments. Again, more like a pie chart. So we're going to come to our chart elements, and we're going to turn off our legend, and instead, we're going to turn our data labels on. But Oh, dear, what a mess. Instead of putting the series labels, it put on the value labels. But we can easily change this. Click on to one set of data labels, and then another way to get to that panel on the right open, we're going to right click and format data labels. Now, you see at this moment, it's showing the value. We're going to unclick value and turn the series name on. Okay? Now, unfortunately, we have to do these one at a time, but it's pretty quick, which is going to unclick value, turn on series, and then repeat that for each of the series. Now, this will probably look a little bit better in white. So I'm going to come to my home tab, make them white, maybe increase the size. And then to apply that to the others, I'm going to use my format painter. I'm going to double click the format painter and then just single click onto each of my labels. We should apply the same format. Then single click on your format painter again to turn it off. Now, let's select our whole chart, Control C. Come back to our line and area chart. Pop it into K 25 to complete your emissions overview reports. One last thing we're going to talk about in this video, and that is colors. When we're using visualizations, obviously, colors are very important, it may be that the colors you see here will not represent your organization or the type of data that you're trying to represent. Now, there are two ways you can approach this, for example, coming into emissions by region. All these series are not really standing out. I'm going to click on the last series, and I'm going to come to my format tab, and then shape fill. Here, you've got some theme colors. Generally, I've recommended use theme colors, but if you don't want to, you can come to more fill colors custom, and there's so much more you can choose from. I'm going to come back to my theme colors, and I'm going to go for all blue colors from light to dark. Do the same until change the color of the last series. That's the one way that you could approach this. But we've already created quite a lot of charts, and to go manually and change each of the colors in each of those charts is going to be very time consuming. And we also run the risk of losing consistency. So a better approach is to come with your page layo tab, come to the themes button, which controls the colors used within your workbook. And as you'll see, as I hover over the different themes, it completely changes the color scheme. And sometimes the font scheme within your workbook. When you find one where you like the look of it, you can click to apply the changes. Now, you'll notice that it has also changed my font. And if I don't want that to happen, you can mix and match. If I come back to Font, you can just set it back to Calibre if you want, or you can go to change your colors. You even have the option to come down to customize colors and you can go and set up your own color scheme together. We're not going to do that now, though. You'll notice having changed our colors. All the colors in the workbook, not just on this sheet, but on the pies and doughnuts and the techniques have changed except for those two blues that I added before. And that's because they weren't the colors. The benefit of using theme colors is when you change your theme, the colors all change. Now I'm going to go back. I'm going to undo and set it back to the colors that I had before because I really like those colors. But if you have a play, find a color scheme that works for you. 17. (NEW 2024) Floating Bar Chart: Making a floating bar chart in Microsoftic cell is a great way to visually represent distribution between entities. Vewing the distribution of related values from one entity to another is a frequent request, and that's where Microsoftic cell floating bar charts can help. Instead of starting from the x axis, the low and high values seem to float above the x axis. They're easy to create an Excel, but the root isn't intuitive. In fact, the floating bar option is practically hidden. Well, do you find it, the placement makes sense. You create a line chart and then the up down bars element. In this video, I'll show you first, generate a low and high value for each entity. Then we'll represent those values in a floating bar chart. Along the way, you'll learn about the min Is and mats Is functions. A floating bar chart is a visual comparison of distribution. In other words, not all the charted value begin at the same place on the x axis, which typically represents zero or some other shared lowest measurement. For instance, the simple dataset displays a minimum and maximum sales value for a few employees. A typical bar chart would start at zero for each person, but we want to chart both values. By starting each bar at the minimum point on the y axis, you create what appears to be floating bars. The benefit is the ability to compare high and low values for each series. In this case, each person. Now that we have an idea of what a floating bar chart is, let's look at the values we plan to chart. In this scenario, we won't be charting the table data. Instead, you'll be charting the minimum and maximum values for each person. In our case, the minimum and maximum values represent the bottom and top of each person's bar respectively. We're going to calculate the values in H three to L four using the Mn IFS and Max IFS functions respectively. These two functions use the following syntax where men or Max range identifies the values are checking. Criteria range one is the conditional range. In our case, the criteria range is the personnel column, and criteria one is the condition. In our case, the condition is the person the values belong to. All right. Let's enter the following function into H three, equal MinFs parentheses. The minimum range are the values from C three to C 12 in absolute reference. The criteria range are one of the names of the personnel column from D three to D 12, again, in absolute reference. And we close the formula with the person the values belong to. So it's Andreas and that's cell H two. Press Enter. Good stuff. Let's copy the formula to the right, so we have the minimum values. We will follow exactly the same steps to calculate the maximum values for personnel using the Max. Let's do it quickly. Very cool. Copy also this formula to the remaining columns. Both functions allow a condition. In this case, the condition is the person. The function returns a value from the value column when the corresponding personnel value matches the value in the header row about the functions. Absolute references are necessary. If they don't work properly after copying, check for these absolute references before any further troubleshooting. Once you have the low and high values for each personnel, you are ready to chart them. Excel doesn't offer a floating bar chart of its own, and finding the option isn't intuitive. First, we'll create a line chart, and that chart type offers floating bars. Okay? Let's start creating a floating bar chart from the minimum and maximum values. The first step is to select cells h2l4. These are the low and high values that we want to compare across employees. Click the Insert tab from ribbon. Now click the line chart button. I will choose the first two dimension line chart. Nice. Let's move it and resize it a bit. I think it's okay now. So once you're inserting the chart into the sheet. Click the chart elements icon in the top right corner. At this point, the chart has two elements, lines and bars. Do you see this option up and down bars? Let's click on it. Here are the floating bars. Let's click one with the floating bars, for example, the first one. Choose format bars, which opens the format up bars pane. In the fill options, click gradient fill and change the direction to linear down. As you can see in the gradient stops, control the light blue is on the bottom and switches you to almost white. I want the red to be at the bottom and green to be at the top. So let's click the first stop and change the color to a light green. Cool. And let's click the last stop and choose a red color. Finally, for the other stops, I will choose yellow and orange. The green and red gradient in the bars is also subjective. In fact, they are tad ugly, but I wanted you to see how easy it is to have two colors in a gradient fill. You could use any color gradient or texture. I also choose a thin blue outline from border options. I could leave the chart as is as a combo chart, but I want to remove the line, so it's strictly a floating bar chart, right? Okay. Now, right click this line and choose format data series. This will open the corresponding pane. Click Phil and line at the top. Then choose no line in the line section. Let's repeat the same steps for the other line. The chart is nearly done. Click the series legend at the bottom center of the chart window and press the lete. I will add some title text which isn't strictly necessary for this example. Let's type sales comparison. And I will remove the outer line of the chart. Go to format tab, shape outline. No outline. Good stuff. The relationship between the people represented is obvious in this chart. And that's why a floating bar chart is a good choice for this type of comparison. With a glance, you can see the highest and lowest sales of personnel. 18. (NEW 2024) Geospatial Charts (Office 365): All right. Let's now look at how we can use Excels map charts to compare values across different geospatial regions. This is an interesting topic, and I suggest paying attention to it, okay? Also, maps are very useful as a dashboard component. As you can see, I have biocapacity and Eco footprint table. I took the data from the footprint network website, and here is the link. To create a map chart, you must have some geographical data. Countries work really well. States, provinces, regions, can also work depending on the country that you're working with. The scenario here is the following. Jim adds a dataset showing the varying biocapacities for different countries. You'd like to be able to compare these on a world map. He's going to do this using a chart map, okay? Now let's start by selecting our data. So I'm going to select B three and C three and then press Control Shift down arrow and then scroll up again, or our chart will be created halfway down the sheet. Now we're going to come to our Insert tab and in the Charts group, we're going to look for maps. A big disclaimer here, you'll need Office 2019 or Microsoft 3605 to get this feature. When we click maps, you'll see a filled map that we're going to click on to apply. If there's a problem and it doesn't recognize your geographical data, you will get an error bar at the top, explaining that, but our own data is absolutely fine, and that's looking great. Let's make a few important changes, though. In a moment, we're going to actually filter the data to allow us to zoom in on specific areas in the chart. But if I were to hide some rows, I'll just quickly demonstrate, I'm going to select some rows and I'm going to right click and hide the chart immediately and it minimizes. We need to be a little careful if we don't want our chart to resize when we change or hide ourselves. We need to specify that. Let's stop there. I'm going to show you a pro tip that you can use to your map charts or other charts. This is also extremely useful for a dashboard when you want to lock the size of the charts. I'm going to click on my chart and Control one or just double click to open the panel on the right. Now we're going to click on our chart options and just expand properties if you need to. You have a choice to move and size with cells move but don't size with cells or don't move or size. It's that third option that we want in this case. We can also change the way the actual map projection is shown. I'm going to click somewhere into the data itself, for example, Africa, and we're going to start by looking at our series options. This is our automatic map projection, but there are different styles of maps to choose from. You can have a look through. I particularly like the Miller one. You can also choose what map area you see. By default, it will show you all the regions covered in your data. If I only had regions in the US, it will show me regions in the US. You'll notice where there is no data for the region. It is grayed out. There are two other options, however, I can show only regions with data, and what that will do is hide all the regions that don't have data. You can see those grade out areas have disappeared as a little piece of Africa and a little piece of South America because we don't have data for those areas. The last option is world, and that will always show the whole world map, regardless of which areas have data. Now, for ours, we actually want to force it to zoom in on a particular areas we filter, and for that, we're going to need to use only regions with data. You can also specify what you want. Map labels. If I click Show Wall, it tries to squeeze them all in, but a lot of them are not properly fitting. So it would actually recommend going for best fit only. And then we can also change our series color. This is biocapacity. So I think shades of green might be better. It does it on a sliding scale or a gradient, if you like. So we need to specify our lowest value. I'm going to go for quite a pale green. Then my highest value, I'm going to go for a more vivid green. It just makes the map look a little more exciting. That's everything I wanted to do from the formatting panel. I'm going to close that up. All right. A couple of more tweaks, though. Let's move our legend to the bottom of the chart. Okay. Let's click the plus icon here. I'm just going to come to the legend and specify bottom. And let's give it a proper chart title. I'm going to triple check in there and type biocapacity. And now the map chart is looking great. But now we get to the exciting bit, being able to zoom in on those particular areas of the data by filtering our chart. We're going to actually create something called a slicer, which is a very intuitive way of filtering data. But in order to use the slicer, I have to have a table or a pivot table. Now, we had a look at tables in the previous lectures, but here's a quick reminder. I'm going to click on my data. I'm going to come to Insert and I'm going to choose table. It automatically identifies the table data provided in your first row containing your headings. You can just say, Okay. Now it has added some funky formatting. If you don't like that, you'll see it on the table ribbon, you can switch it off altogether, or you can go over something a little more to your taste. But more importantly, having added to our table on the table design ribbon, you'll see an option to insert a slicer, and that's what we really want. I'm going to click Insert Slicer. You can have as many slices as you have columns or fields. We want to filter by region, though. I'm going to take region and say, Okay. Now it's popped my slicer onto my sheet. I can move that across just by dragging it, and you can resize it using the re sizer handles. From slicer tab, I like to change the columns of the slicer, so to fit it below the map chart, Very cool. And you can format it as well. With your slices selected, you get a slicer ribbon and a gallery of slicer style similar to our table. I'm going to go for the green. Nice. That's creating the slicer. Let's see how easy it is to work now. I'm going to click onto the Americas. It has filtered the data to only show countries in the Americas and filtering the data, I filtered my chart. There's Asia, there's Europe. If I want to just get back to my whole world, I come to the top right hand corner and clear the filter. Three really simple steps to create a totally interactive geo spatial visualization. Start by inserting a map chart, convert your table to a data and then insert a slicer. 19. DEMO: Emphasizing & Mark Significant Events: Some trending components may contain certain periods in which a special event occurred causing an anomaly in the training pattern. For instance, you may have an unusual large spike or dip in the trend caused by single currency in your organization. Or maybe you need to mix the actual data with forecasts in your charting component. In such cases, it could be helpful to emphasize specific periods are specific events in your trending with this special formatting. In this lesson, we will learn how to emphasize in Mark significant events, making good use of a bar pie and doughnut chart. A simple bar graph that can be focused to help the audience remember in a single number is way more effective. This data table shows us the countries where the coronavirus is spread except China. I made this grant easier for my audience by ordering the bars from the greatest to least. To do the same look to the right of the Home tab. And you'll see a button called sort and filter. Click the drop-down arrow next to it and select Custom Sort. This opens a dialog box where you can adjust each option to select the column you want to be sorted, and the order of the sort largest to smallest in our case. Now you highlight your data along its corresponding labels. And let's insert a basic bar chart from the Insert tab. Choose the first option in the bar chart drop-down menu. Move it below the data and adjust the size. To adjust the width of the bars, right-click on any of them. From the sub menu. Select Format Data Series. The Format Data Series pain from a series option decrease the gap width to 50% or something around the size that you like. Now from Phil and line I will change the fill color for all the vars to a light gray. Then click twice on the bar for Japan so that it will be the only one highlighted. Then it'll select the blue action color as my fill. Choose dialog box. Delete the title of the chart. I want people to remember a single number here. So right-click on the Japan bar and select add data label from that menu. You can see that Excel added the number of cases outside of the end of the bar. And only for Japan, I prefer my number on the inside of the borrower whenever there is room because it keeps the visual focus squarely on the data. So click on the labelled twice and then from the format data labeled pain that opens, look for labeled position and change the radio button from outside to inside end. The last step, you'll want to change the text color for that label to white. It is visible against the action color of the bar. These strategies give laser focus to a single number that you want to boost. If you want, you can delete the grid lines and the border of the graph from the Format tab and the Shape Outline button. Great. To add a text box to the chart. Click Insert tab on the ribbon and select text block icon. Then left-click inside the charge to create an empty text box, which you can feel with your words. Now let's move to the PI or donut worksheet doughnut chart and display more than one series of data and it has a hole in the middle. It is okay to use a doughnut graph on occasion. For example, when you were showing just one chunk to support a single number and give it a bit of an eye candy emphasis. Here we have a little David, a table with the percentages of land area and water area on Earth. Let's make a doughnut chart. Highlight all of the data with your mouse and click on the Insert tab. In the charts family, click on the drop-down arrow next to the tiny pie chart icon and select the last option there, the donut. The doughnut chart type is hidden in the drop-down menu options for a pie chart. Now, right-click on one of the pieces of the doughnut. In that menu, select Format Data Series. This opens a dialog box where you can adjust the size of the donut hole. I will reduce mine to about 3%. Decreasing the size of the doughnut hole creates a stronger visual aspect. Now, there's room for me to type a label. I want to highlight the water land on Earth. And I also want the largest wedge to start at noon. So I will adjust the angle of the first slice in that same dialog box to 220 degrees. The next step is to use an action color to highlight a key piece of the data visualization. So we will change the color of the unimportant doughnut apiece to light gray so that it holds very little visual emphasis. To do this, click on the land area piece twice and then open the drop-down arrow by your paint bucket located in the Home tab and select a light gray from that palette. I will also change the fill color of my important piece of data representing the water area to blue. Finally, I will delete the legend, just click on it and hit the Delete key. I will add a text box to the graph with my main message. Also delete the title. The border around the chart. The same process worked for a pie chart. Let's do it quickly. Now, how cool is that? Generally, pies and donuts aren't ideal graph choices. However, in these examples, we're not asking our audience to do much mental work because we've muted all but one piece of the graph. It's always better to highlight just one wedged of the pie to support your point. I hope you enjoyed this lesson and you managed to understand how to make an effective bar, doughnut or pie chart. 20. TreeMap or Hierarchy Chart (Office 365 / Excel 2016+): How to create and modify an Excel TreeMap. Excel 2016's new treemap chart offers interesting ways to visualize a hierarchy of data. A treemap chart is a type of data visualization that is especially useful for displaying hierarchical data. On a TreeMap, each item is represented by a rectangular shape, where smaller rectangles represent the subgroups. The color and size of rectangles are typically correlated with the tree structure, which in return makes it easier to see the groups and sizes. Treatment of charts are great for highlighting the contribution of each item to the hole within the hierarchy. In this lesson, I'll show you how to create and modify a TreeMap. The data we will use as shown below. We have countries broken down by segments, each with a value. Four segments for each country. Highlight the data from A1 to S3, and click the Insert tab. Click the Insert Hierarchy button. Then TreeMap Excel will create an insert a treemap chart into your spreadsheet. You're treemap chart will be automatically created. You will notice that each block represents a different color for each item country, and a different size for each segment. The size of the block is determined by the value. A treemap chart mainly consists of three sections. Plot, area, chart, title, legend. Click the plus sign the chart shortcut to edit the Chart Elements. Chart Title, data labels or legend. Which shortcuts you can see the effects of options on the fly before applying them. Let's modify the data labels. Click this little arrow and click on More Data Label option. Click on the value checkbox. And you will notice that the values now appear next to the segments as a separator, I liked the new line. You can also add a border to these data labels from the fill bucket. I'll select no fill. Close to format data labels, pain. You can now further customize the look and feel of your treemap chart by clicking the paintbrush to change the charts design such as style and color. Select this style. Also delete the legend. Delete the chart title, and remove the grid lines from the View tab. Great. Now right-click any of the rectangles on the chart and select Format Data Series. In the Format Data Series pane, click Series option, the chart icon. Click the Series Options down arrow and browse through the menu choices, Chart Area, Chart Title, Data Labels, legend, plot area. In series, I prefer to add the Banner option from the label options. Also, note that the banner is gray when it is initially installed. You need to select a major category and then update the fill color to ensure the banner is the same color as the category. Close the pain. If you need a more advanced modification of the TreeMap, you can use the two tabs, chart design and format. Preset layouts are always a good place to start for detailing your chart. For example, from this button you can apply a quick layout. From this, you can change the colors. Now you have your beautiful looking treemap chart. I hope you enjoyed this video tutorial and I want to thank you for watching it. 21. Sunburst or Hierarchy Chart (Office 365 / Excel 2016+): Sunburst charts. Sunburst charts are one of the many new charts available in Excel 2016. In later versions, the sunburst chart is ideal for displaying hierarchical data. Each level of hierarchy is represented by one ring or circle, the innermost circle at the top of the hierarchy. Sunburst chart without any hierarchical data, one level of categories looks similar to the doughnut chart. However, a sunburst chart with multiple levels of categories shows how the outer rings relate to the inner rings. A sunburst chart is more advanced than the doughnut chart as it is not only showing the sizes, but also shows the relationships in the hierarchy. The sunburst chart is most effective at showing how one ring is broken into its contributing pieces. While another type of hierarchical chart, the treemap chart is ideal for comparing relative sizes. Let's dive in. Highlight your table from A1 to D23. From the Insert tab. Insert Hierarchy Chart button. Select sunburst. Move it somewhere there and make it bigger. Let's take a look at the chart. The innermost ring we marked with blue colour all the sales from January. From the chart containing only the raw data. We could not have seen right away the fact that January sales take up almost half of all the income. Amongst other things, this is one of the advantages of data visualization. The second ring is the subcategory level. Within the category of January, week two was the most profitable. From the plus icon, you can edit the Chart Elements, Chart Title, data labels, or legend. You can further customize the look and feel of your sunburst chart by clicking the paintbrush to change the charts design, such as style and color. Select this style. Also delete the legend, the chart title, and remove the grid lines. If you need more advanced modifications of the sunburst, you can use the two tabs Chart Design in format. For example, from this button, you can apply a quick layout. And from this you can change the colors. I hope you found this tutorial useful and I want to thank you for watching it. 22. Histogram Chart (Office 365 / Excel 2016+): On this word she called histogram. We've got nearly 600 rows of data here. Alphabetized list of people's names in column A and their salaries in column B. And we'd like to get a rough picture of how many people follow the different categories. Like, for example, how many people are 50-60 thousand in terms of salary or 60 70,000, Something like that. We will do this by way of a chart called a histogram. It is surrounded by empty cells, so we don't have to highlight the data. We just click anywhere inside the data. Then we'll simply go to the Insert tab and in the groups chart here, the middle button here, Insert Statistic chart, there's histogram. We see a preview already. Let's click it and we can get started here. I think what most of us would want to do with this, perhaps, though, is to just adjust the numbers, but it gives us an idea. We're not seeing any numbers just yet other than the salary numbers at the bottom, like how many does this represent? Well, let's look at the x axis. This is over nine actually 94. We're not seeing 94 in the actual data, but there are 94 salaries 66990-80990. You might want to change these breakouts down here. I think that's what a lot of us would do. How about the number of columns. A couple of approaches here. Let's double click the horizontal axis. That brings up the format axis dialogue box off to the right. First thing you might notice is bin width. We could change that. It's currently 14,000. That's an unusual number. How about 20,000? Would that make sense? It's changed and that might or might not be your approach or 10,000. How about more columns. So certainly experiment with that a little bit. So another approach could be, I want a certain number of bins. I was thinking of ten all along. Well, that's another approach. Change the number of bins to be ten. But as we do this, you'll see that the interval is somewhat unusual. What is that? 13,000 now? Or is it 12,860? We see that in the background. I think for a lot of people, that might be unacceptable. But nevertheless, I think you do have valid answers here that would make some sense. Another approach here could be to adjust what's called the underflow bit. Now, up in row one, we see the lowest salary here is 24,990. What if we change this to be $25,000? Now, that's slightly above the lowest entry, but we'll see what happens here if we change the underflow bin to be $25,000. Now we see what's happening. Maybe it would make sense to now change the bin width, maybe make it to be $20,000 exactly. I think you'd get the idea here about experimenting with the bin width, the number of bins, the underflow, the overflow as well. So we could quickly get a wreath though on salary levels here. Do note that if you're experimenting with these, you can get a different feel for the data depending upon the number of bins and the bin width. It's definitely something to explore. I think when you initially see these, you just might not be satisfied with the first, but by adjusting these, eventually you will. And what other approach here too is once we've closed this dialogue box, let's add some data labels too. That would be helpful. We're seeing the numbers on top here, and we could certainly add others as well. So a valuable chart is giving us some kind of read on the large amount of data that we have in this list, almost 600 rows. 23. Box and Whisker Chart (Office 365 / Excel 2016+): We're looking at a worksheet called the Box and Whisker. That's an unusual name, but it's a valuable chart. And on this worksheet, we see data in columns A, B, and C. We've got salespersons in four major states here and they're sorted by ID number. The statistics we see in columns through are valuable. We don't truly need these here, but it's going to help us explain how to read a box and whisker chart. We'll click within the data over on the left hand side, and it is surrounded by empty cells. So we don't have to highlight it. We just click anywhere inside the data, and we'll simply on the Insert tab, go to the histogram icon. This is a statistical type chart, and there's a box and whisker. We see the preview below. Use this chart type to display variation within a set of data. Let's click on the icon. And because I do have the data up there, I want to line up these images with the data above. And again, I remind us, and again, I'll say we don't really need to use those statistics there. The upper part of this line is for California only, and for the moment, represents the maximum salary. It's the maximum amount of sales, $101,000. We actually see that in cell F two, by the way, it's reflected up here. And these are simply a bunch of formulas showing us what those results are. So as you look at the bottom line here and we see the horizontal there that represents the maximum value. And on the bottom here, as I slide it over, we see another value. This is the lowest value for California, $24,105. And the same is true for the other states. The box represents a half of all entries. And if we were to point to the top box, we'll see the highest entry among the middle half. You might say 80,856. And then down below and we can do this, of course, for all the other boxes. The lowest among that middle group there, 47. The x C is in the middle, pertains to the average $63,713. And you see that displayed up above and sell F four. That's true of the other boxes as well. The line represents the median, 65,803. Once again, we see that above and sell F three. So as we look at these at a glance, think of the size of the box as representing half of all the sales entries that we see in column C for each of the states here. Data labels would be helpful here, but it might be overkill. So selecting the chart off to the right, that chart element button the plus. Let's click it at data labels. I think you can see that it's kind of crowded, and we could make the numbers bigger. We can make them bold, but maybe making the chart taller will help a little bit. Those numbers are kind of clustered. Now, if you want to get rid of some of these, you can't do a bunch of them at once, unfortunately. Maybe I don't want to see the average. That's right next to the x and each of these. I'll click on the number next to the X for California. Notice how all of these are selected. Click again there. Press the lead. I'm only going to get rid of the average on the display here for California. Then I'll have to do the same thing for the others. I'll do just one more here. It's Texas. Click here. They're all selected. Click again, just this entry and delete. So in different ways, you might want to clean up that appearance. We can also come back, click on any of these, make it bold, use a bigger font if that seems appropriate. It could be one of these charts where you have to do perhaps more explaining than you want to. That's why maybe the numbers up above are helpful too. But we're getting a quick read here on the data that we are seeing over in columns A B and C. It's a box and whisker chart, but a valuable chart. I think you can see how it gives us some good statistical analysis of the data over in columns A B and C, some 600 rows of data there. 24. Pareto Chart (Office 365 / Excel 2016+): Another statistical chart is called a preto chart. It was introduced in Excel 2018. I've heard it pronounced a number of different ways. I think that's the most common way. We've got data in columns A, B, and C that actually calculate the cumulative percentage based on sales. These numbers in column B here are in descending order. Prior to the introduction of the Pereto chart, you would have come up with this calculation. It's not terribly difficult, but let's just say what we're showing here is the counting just these two states. That's 57% of our states counting these three, that's 73%, and so on. Now, we've got the same kind of data to the right without that calculation. With a Pereto chart, we can come up with that information and show it in a combination of chart that shows both column and line. Notice that the data here is not sorted by sales. We don't need to write any formulas. We'll simply click within it, and on the insert tap off to the right in the charts, the statistical charts, we see them right here. Is easily overlooked. We don't see the word Pereto unless we slide it to the right. There it is. We see that the description showed the relative portion for each factor, we're also seeing the chart down below two, and it in effect, has the background sort of the data in descending order by sales to give us these cubulative totals that we're looking for here. It's relatively easy to create. Now, getting back to this idea, if we had highlighted the data like this and pressed Alt plus F one to create a chart quickly, If we're trying to convert a chart into Pereto as we go to change chart type, we do not see Peretto here. But if we click histogram, we'll see it here. We don't want that one, but the other one, there it is. So in a certain sense, you've got to go looking for it or fight to find it. And there's that preview that we would want if we were switching from a column chart to a Pereto chart here. Click Okay. It's a valuable chart for showing us what's happened here we can see it by looking at the percentages over on the right side that counting just California taxes in Florida, that's what's 75% of our sales. Now, that is confirmed by what we're seeing over in Column C, but we didn't need to have that data there present at all. I think you can see it has a valuable role to play in analyzing sales type data as we're seeing in the example here. 25. Waterfall Chart (Office 365 / Excel 2016+): A Welcome back, everybody. We're looking at a worksheet called Waterfall. When we go to the Insert tab when we're in the charts group, the upper right corner. We do see an icon here with quite a few different chart types. Let's click inside the sales table. Waterfall chart is ideal for showing what's happening from month to month, particularly when we're changing or doing positive and negative data flows. That's when we've got the data turning over in columns A and B. We'll simply use waterfall here and show the cumulative effect of a series of positive and negative values, and we're seeing this here. The data in column B doesn't really have to be there. Get to that in a moment. But as we look at the data here, we can see pretty clearly, particularly if you're looking at the numbers over in column B, what's happening. The blue columns have represented positive entries. The orange ones represent negatives. We see the negatives in April, June and July, and that's reflective of the numbers over in column B. Now, we're going to make this a little bit bigger and taller. Notice in the legend blue for increase, orange for decrease, and then green for total. That's a little bit obscure. That actually refers to the lines that connect the columns in each case. Now, what you could do is double click here. They are a little bit more prominent now. Possibly the gap width that appears in the dialog box to the right. I want to change that a little bit. I'll just hold down that lower arrow there, and we'll see what's happening. That probably isn't where you want to go down to zero, but on the other hand, at different times, you get different needs. As you're explaining how to read this and looking at it, consider changing the gap width. That's certainly one option. Also, if we cut over to the paint bucket here and solid fill, you see what's happening that way. We could be changing the color there too, or at the border. That's the line on the outside. How about a solid line? First thing looks too strong and down below that we can change the width of it. So lots of fine tuning options here to consider how to make this look a little bit more prominent. But it is a little bit deceptive. At first, the way that the legend is working. Right now, it seems to make little to no sense of the color change. So I'm going to press Control C a few times to undo recent actions here. I'm pressing repeatedly, so we go back to some of those previous color layouts, and sometimes when you're pressing controls like I'm doing now, it takes a while to get back to where we are. Well, there we are. So I'm going to move the chart slightly to the right, although it's not necessary to have this data present. This could be the good accompanying set of data to have along with it. These, by the way, are simply formulas that are tabulating cumulative data. And I've applied conditional formatting to indicate when these are negative. So every time we see the orange sales corresponds with the sales here that are negative. I hope you understood that a waterfall chart is this and when it's useful to use it. If you have any questions, please let me know. Thanks for watching. Okay. 26. Funnel Chart (Office 365 / Excel 2016+): Funnel chart is similar to a bar chart. The bars are centered to create a funnel shape. The funnel chart can help give a reader a visual picture of the stages in the process. In the case of the sales pipeline that we started with a lot of customers in stage one, initial contact, but only a small percentage of those customers make it to stage five, which is close with a purchase is made, the values decrease gradually allowing the bars to resemble a funnel. In this lesson, we're going to learn the top five ways to create a funnel chart. So let's get started. In Excel 2019, you already have a built-in sales funnel chart. The first step is to set up your data in a sorted manner where the largest values are at the top. Use one column for the stages in the process and one for the values. You should not have more than one column with values in a funnel chart. Also the sales values are sorted from highest to lowest. Select the data from A1 to B6 and from Insert tab, select the funnel chart. Move it somewhere there and increase the size of the chart. The funnel chart is pretty much already done, so we don't really need to do anything except maybe changed the style on the color of the chart from the chart styles icon. Also, if I want to have a different color for each of these, I will have to double-click on these series and from the Home tab, fill color icon. Select the color that I want. From Chart Elements plus the eye icon, you can make some changes like remove the chart title and a legend, etc. The second funnel chart fits nicely within the crowded print reports. For example, a page might have a large amount of columnar data from the regional marketing and sales data. The Intel funnel chart can fit right alongside the numeric data in the adjacent cells. This in cell funnel chart draws itself using the roughed function. The rubbed function can be used to dry in cell bar charts using the vertical pipe symbol. The rubbed function repeats texts as many times as specified. If you use the right character and font and you can draw bar charts this way. For example, click on the PF2 cell and type equal wrapped parenthesis. Vertical pipe symbol in double-quotes, comma 15. With this technique, it's easy to use the number of contexts in the funnel as the number of characters repeats, and then use the center of the resulting bar chart to create a funnel. However, you need to make two modifications to make it look right. Both marketing and sales numbers are in the hundreds or thousands, so you need to scale your numbers down. That's why you see a cell called scale with number 53rd column called scaled sales value. Also, we use different fonts instead of the default Calibri font to remove the spaces around the pipe characters. First of all, let's make the scaled numbers go to C3 cell and type equal B30 divided to d1. This absolute reference. Copy this formula down. Now go to E3 and type equal wrapped vertical pipe comma C. Copy the formula all the way down. Highlight cells from E3 to E7 and change the font to play belt. As a default Windows font. You center formatting to center align the pipe characters. Try to change the scale number 225 and change the color. The cell funnel chart is ready. The third Excel funnel chart is compatible with any version of Excel. It is also easy to give it a custom modifications. Before Excel 2013 or 2016, the best way to create a funnel chart was to use a stacked bar chart with two sets of data. The leftmost bars, the spacer that pushes the rightmost bars into a funnel in the center. Filling the leftmost bars with no color makes them invisible. When you finish the funnel chart, an additional couple of clicks closes the gaps between the bars and turns the chart upside down so that a funnel is in the direction that people expect the stack bar chart needs. In sets of data to create an Excel funnel chart. The data under the sales value C2 to C6 on the right is the value of sales at each stage of the tunnel. The data under invisible B2 to B6 is calculated value that I'll sets the value bars so that they appear centered as a funnel. When the chart is first created, the funnel bars appear inverted, but we will fix that later. The formula in the topmost cell is the invisible column is equal max C2 to C6 with absolute reference minus s2. And all divided by two. Copy this formula down through B2 to B6. So there is a space, or for each count cell, select the data from A1 to C6 and insert a stacked bar chart. Delete the title. Legend and grid lines. Make the funnel fit the full width of the chart area. Right-click the category axis, select Format grids and from the axis option tab and maximum to 3500. Close the window. Now we can delete the horizontal value axis. The next step is to invert the funnel so that the next stage is at the top. Right-click the vertical axis, select Format Axis. Select Axis, Options tab, and check the categories in the reverse order. Checkbox. Close the gap between bars so that the funnel is solid. Right-click a bar. Select Format Data Series and set the gap width to 0. To make spacer bars invisible. Right-click the bars on the left. Select Format Data Series. Select, Fill Tab, select no fill. The funnel chart is almost ready. Add data labels so it appears in each stage of the funnel, Right-click any bar and select add data labels. From the data labeled by changing the color to white, make them bold and change the size to 12. To create a unique and impressive funnel chart, you can click on any bar and from the Format tab shape effects, selected glow variation. Remove the border and the Excel grid lines and the chart is ready. The fourth Excel funnel chart is also compatible with any version of Excel. It displays sideways and it's smooth and elegant. This method is very similar to creating a funnel chart with stacked bars, but in this case, you use a stacked area chart. Create your table of data using the same layout as the stacked bar chart, the same columns, and the same formula as you can see. Now, selected data from A1 to C6 and insert a stacked area chart. When you first create this chart, you will see the lower area as a color blue in this example. You will make this invisible later. Remove the extra space at the top of the vertical axis by setting the maximum height as the vertical axis as the same as the maximum count. Right-click the vertical axis, select Format Axis and select Axis, Options tab, and maximum to 3500. Close the format axis pain. Delete the chart title the legend. The horizontal grid lines and the vertical axis. Select the invisible area, the blue color and right-click on it. Click Format Data Series and then fill in line. Change the color of the lower area to no fill so it's invisible and closer pane. Select the sales value area and then from the plus icon, add data labels to show the numbers for each stage. Move them, and change their font size or effect. Remove the border in the Excel grid lines and now we're ready. The final funnel chart is a bit different as a chart then it looks like a real funnel with the help of smart art, we already made a built-in Excel funnel chart. Begin by selecting Insert tab and from the illustration selection, inserts mark tab, select the pyramid category and then select inverted pyramid from the list. Move it here and increase the size at two more shapes to the hierarchy to match the number of stages in the data. From the Design tab, click the Add Shape twice. Now we will convert the SmartArt graphic to traditional shape object. Right-click on the inverted pyramid and select Convert to shapes. Also select a different color for each level. Although we can add text to SmartArt objects, the object is not dynamic with the converted shape object. So at the top level than the pyramid, click and click, click copy or Control plus C to copy the top-level shape to the clipboard. Now select the topmost level in the funnel chart. Right-click with a level and select format data point. The format datapoint panel. Click the Fill Options and expand the field category, select picture or texture fill, and then click Clipboard. We've pasted the new shape to the topmost level. If the shape has a border, remove the border by selecting the new level and from the Format tab set the shape outline to know outline. Now repeat the process for each additional level, that the gap width of the funnel chart to 0%. This will reduce the whitespace between each level. Now change the values of the sales. Observe how the chart has been updated to reflect the new data. These are the top five ways to make a funnel chart using Excel 2019. I hope that you enjoyed this lesson and I want to thank you for watching it. 27. Map Charts based on Geographic Data (Office 365 / Excel 2016+): On this worksheet called geographic map, we see different clusters of data that include either states or cities or countries. From time to time, you might want to include this kind of data with an actual geographic map. So selecting the data over in columns A and B for each of the states and the relative populations and millions, let's go to the Insert tab. And although found in the charts area, we see maps just off to the right of those other icons. Maps. There's the icon. Immediately, we see the states here. Now, the numbers are relative. Now I might be making an adjustment over here. Maybe Pennsylvania is really 30 million, so I double click, and I put in 30. And as I press enter, keep an eye on Pennsylvania on the map. It'll be a darker blue and you saw what happened. So the magnitude of the numbers is being reflected in the shadowing that we see to the right. I'll undo that with control plus. Now, watch the Pennsylvania portion of the map change again as I undo that. Three states here to the right. I'll highlight these Insert geographic maps. There we go. And we're seeing those three states, but we're also seeing the entire United States there. If you actually wanted to show less of the United States, what you have to do here is actually turn this into a picture. So if you were to right click and copy this or Control C to copy it, we can paste it somewhere else. If I go to the paste button off the home tab and this here is a picture. And at that point, although it's no longer dynamic, in other words, if the data changes, this will not change, but we could now because it's not a picture. Go up to picture format and off to the right crop. And then we could do this if we wish. So it's not completely flexible feature, but on the other hand, I think you can sense with certain kinds of data, this will work. Let's try these two, New York and Philadelphia. Insert maps. And a little indicator in the upper right corner and you might see this from time to time. If you click this, we plotted 50% of the locations from your data with high confidence. Apparently, they're just not confident about showing us Philadelphia here. So if you will encounter this from time to time. Also with countries here, let me delete these charts. I've got some countries here, France, Spain, Portugal, and Greece. These are highlighted. Insert Geographic maps again. There we are, and we're getting a European map. Here too, we might want to consider copying this, pasting this, and then cropping it. So at different times, when you're working with geographic type data, it's worth considering using a chart of the kind we're seeing here, the geographic map. 28. Sparklines and Win/Loss Charts: A nice feature of Excel is sparklines. These magically named visualizations are essentially meaning word size charts placed in and among the textual data in tables. Sparklines enable you to see at a glance trends and patterns within your data using minimal real estate on your dashboard. This lesson introduces you to the concept of sparklines and then shows you how to customize and add them to tables. Much of the reporting done in Excel is table based, which in premise numbers are much important than pretty charts. However, in a table based reporting, you often lose the ability to show important aspects of data such as trends. The number of columns needed to show adequate trend data in tables makes it practical to do so. Any attempt to add trend data to a table usually does nothing more than render your report unreadable. With the sparklines added, you can see the more broad story behind each metric. Understanding sparklines. Although spark lines look like miniature charts and can sometimes take the place of a chart. This feature is completely separate from the Excel chart feature. For example, charts are placed on a worksheets drawing layer and then a single chart can display several series of data. In contrast, the sparkline is displayed in singable works through cell and then displays only one series of data. Excel supports three types of sparklines, line, column and win-loss line. Similar to a line chart, the line type of sparkline can appear with or without a marker for each data point column, similar to a column chart. The second group shows the same data with the column sparklines. Win-loss. A win-loss sparkline is a non-binary type of display that displays each data point as a high block or low block. The third type shows the win-loss sparklines. Notice that the data is different. Each cell displays the change from the previous month. In the sparkline, each data point is depicted as a high block when or a low block loss. Creating sparklines. In this workbook, we have a table with sales per month for Microsoft Office products. You can see for products in total, Word Excel, PowerPoint and access. Also, there are 12 columns, one for each month. And of course, the values per product and month. We want to make a chart for each row independently. First select the data range that you want to summarize. These cells are from B3, M3. But be careful only the numbers. If you were creating multiple sparklines, select all data. With the data selected. Click the Insert tab on the ribbon and find the sparklines group. Here are the three different kinds of sparklines, line column and win-loss. Select the line option. Excel displays a create sparklines dialogue box. We already specified the data range of sparkline, makes sure that they are correct in the data range box, the location rain shows the cell that we want sparkline cell to appear. Press this button with the red arrows like the N3 cell. And again the button and OK. Excel creates a sparklines graphic of the type that you specify. The sparklines are linked up to the data. So if you want to change any of the values in the data range, the sparkline graphic will update. Now, drag and drop all the way down. We see our lines charted one for each cell. Customizing sparklines. When you activate a cell that contains a sparkline, EXL displays an outline around all the sparklines in the group. You can then use the commands on the sparkline tools design tab, and customize the group of spark lines. After you create a sparkline, changing the color is easy. Simply click the sparkline, click to open the sparkline Tools tab on the ribbon and select design, then style. There you'll find various options to change the color and the style of the sparkline. Remember the colors used in sparkline graphics are tied to the document theme. If you change the theme, the sparkline colors, and then change to the new theme colors. Use the commands under Show to customize the sparklines to emphasize the key aspects of the data. The options that are in the show group are the following. High Point, apply a different color to the highest point in the sparkline. Low point, applying different color to the lowest data point in the sparkline. Negative points applying a different color to the negative values in the sparkline. First, apply a different color to the first in the data of the sparkline. Last point, apply a different color to the last data point in the sparkline. Markers show data markers in the sparkline. This option is available only for line sparklines. When you create one or more sparklines, they all use by default, automatic axis scaling. In other words, excel determines the minimum and maximum vertical axis values for h sparkline in the group based on the numeric range of the sparkline data. The sparkline axis commands let you override this automatic behavior and control the minimum and maximum values for each sparkline or group of sparklines. Three, the more control you can use the custom value option and specify the minimum and maximum for each sparkline group acts as scaling can make a big difference in sparklines, the vertical axis, minimum and maximum value options are set to automatic. Let's change them to the same for all sparklines. Look at sparklines. There is a huge difference in how they look now and before, essentially N7 cells compared to other cells. Now we'll try to insert a different kind of sparkline. Click on the O3 cell and go to Insert tab and from the sparkline section select Column. Click on data range and highlight the same cells. Click the Okay button. Drag and drop again all the way down. If we have positive and negative numbers like these data than an ideal choices when law sparkline, but this time I'll create multiple sparklines. Highlight the cells from P3 to P7, and click on win-loss sparkline in data range. Now type B3 to M7, again, only the numbers. Press the OK button. But what about gas? We see in some sparklines because of the empty cells, the best solution is to fill empty gaps somehow. How do we do that? Select, for example, N3 to N7. Go to the Design tab, and at the left-hand side, you can see the edit data button. Press and see the hidden and empty cells Option. Click on it. We see two or three options. It depends on the sparkline that we selected. For example, line sparklines have three options. I think the best choice is third, connect data with a line. Choose it and press Okay. Now look at the result. Pretty straightforward, right? Sparklines or content. But if you want to remove them from the worksheet, you can't do without the delete key. And easy way to remove them is by deleting the whole column like this. Let's undo. However, the best way to remove a sparkline is by using the clear button from the Design tab. The clear button has two options. As you can see, either clear selected sparklines or clear selected sparkline groups. Click on N3 and select this option. See the results includes all the lines, sparklines, they are in the same group. Of course, sparklines are not about adding flash represents your tables. It's about building the most effective message in the limited space you have. Sparklines or another tool that you can use to add another dimension to your table based reports. Thanks for watching. 29. Identifying the Difference between a Formula and a Function: In Excel functions and formulas both help you calculate and answer. You may hear the two words used interchangeably, but they're not technically the same. So what's the difference? A formula is an expression that uses cell references or hard coded numbers to calculate the value of a cell. For example, A one plus A two and equal 35 multiply by ten are formulas. Sometimes a simple formula is all you need to get the right answer, but you can do so much more using functions. A function is a predefined formula already available in Excel. Functions streamline the process of creating a calculation. To date, Excel has more than 400 functions. More functions are added every time Microsoft releases a new version of Excel. Functions can do complicated calculations that would be time consuming to build manually. For example, if you wanted to add up a range of cells without using a function, you'd need to write something like this. Equal B one plus B two. Plus B three plus B four. Instead of the equal sum of B one to B four, now for four cells, writing it manually isn't such a big deal. But when you're adding a range of hundreds of cells or thousands, functions make calculations a lot easier and much, much more efficient. So, here's a quick summary here. Equal A one plus A two is just a formula, but equal average A one to B ten is a formula containing a function. But remember, there's much more to being an excellent function modeler than knowing lots and lots of Excel functions. And the more functions you know, the more likely you are to choose the one that's the most appropriate for the job at hand. As with many things in life, there are several ways to achieve the same result. But the best option is the one that's the most transparent and accessible for others to understand and the simplest to audit. If you're trying to perform a calculation in Excel and you aren't sure what the function you want is called, don't worry. Step one is to click the Formulas tab. Step two is to click the Insert Function button, and the Insert function dialog box appears. Two quick tips here. You can access the insert function dialog box by clicking the FX button to the left of the formula bar or by using the shortcut Shift plus F three. Step three is to type a brief description of what you want to do in the search for a function text box, and then click Go. A list of functions appears in the select a function box. Now, if you're still not finding the function that you're looking for, select a category of functions from the selected category dropdown list to narrow down the list. Step four is when you found the function that you think might be right. Select it from the selective function box and click Okay. And you're done. Thanks for watching. 30. Relative vs Absolute Cell Reference: In this lecture, we will learn how to use relative versus absolute cell references. So let's dive in. The cell reference is your way of saying, use this cell in this formula. If you are writing a formula, for example, equal sum A one, A two, A three. You're telling Excel to get the values from cells A one, A two, and a three and apply them in the sum formula to give the total of the three cells. Do you know that you can use these cell references in such a way that when you copy the formula from one cell to another, Excel will automatically adjust the cell references. Or you can tell Excel to just adjust only certain portions, but not the other portions. Let me give you two simple examples by which you will be able to better understand the power of these cell references. Let us calculate the square of the numbers in a column. To calculate the square, we are writing a simple formula in cell C four. Equals B four times B four. Then we just copy the formulas down from C four to C seven. You'll get 100 400, 916 hundred, and obviously something has gone wrong here. This isn't correct math at all. To diagnose what might have gone wrong, we can select any cell and hit the F two or just notice the formula bar. Excel has adjusted the values to be B five times B five. B six times B six and B seven times B seven. And even though the initial formula was B four times B four, this is because when we copy the formula from one place to another, C four to C six, Excel then realizes that the formula is copied two cells further down, and so the reference should be moved two cells further down. This is intelligent but may not always be useful. For example, if you have a scenario where you have to calculate a fixed percentage of values, this could be very useful when you're designing tables with markups or et cetera. In cell F four, we would have taken the value in E four and multiplied it by the fixed percentage in F three. However, when you move the cell down to F seven, it refers to E seven, which is correct, but moves the reference cell F three to cell F six instead of continuing to be applied in cell F three. This is not right, but Excel thinks that even this has to move with the formula. In such scenarios, you can use dollar symbol to tell Excel that no matter where you move or copy the formula, this particular reference should remain F three only. Don't move it around. See the reference remain fixed at F three. When you feel the formula down now, you'll see the reference remain fixed it of three. We are locking the cell. Using the dollar symbol is your way of telling Excel to keep this cell reference absolutely where it is in this particular cell reference. Don't make it relative. Even if you move the cell, don't change the values. This is a very powerful use of cell references and can help you a lot when you're designing tables or writing conditional formats or giving data for charts and things like that. Another simple trick that you can learn is that you don't always need to go to the cell and type the dollar signs. You can select the cell or put the cursor anywhere inside the cell and press the F four key. When you press the F four key once, Excel inserts dollars before E and four. When you press F four once more, Excel removes $1 from E. Now it is making the particular row static while the column is remaining dynamic. When you move the formula across the spreadsheet, E will become F and F will become G and so on. When you press F four again, this time the dollar sign will move from four to E. This will ensure that when you move the value across the spreadsheet, E will remain E all the time, but when you move the formula down, four will become five and five will become six and so on. When you press the F four key once again, all the dollars are gone. As I've already said, formulas are crucial when it comes to making dashboards and reports. Understanding the difference between relative and absolute reference is the first rule to continue with formulas. 31. Commonly Used Functions - SUM, MAX, MIN and AVERAGE: In this section, I will fill you in on all the functions you'll rely on most often and give you examples of how and why we use them. The sum function adds the series of numbers. Usually, sum is used to add a contiguous range of cells, but it can also be used to add cells in a non contiguous range. In other words, cells that aren't adjacent to each other. To try this out, summon a column of numbers to yourself, so like the cell E 14 and click the Autosum button on the home tab or the formulas tab. When you click the Autosum button, the sum function tries to determine the figures that you want to add up additionally, and it usually gets it right. If it hasn't selected quite the proper range, you'll need to fix it manually by editing the range, by retyping the references in the formula bar. Let's do the same with the other subtotals, E 31 and E 40. Okay. That looks good. Now, I'll show you an example of summoning up a specific set of cells in a non contiguous range. Rows 14, 31, and 40 already contain subtotals. So click on cell E 43 and start typing equal sum, E 14 E 31, E 40. Note that you need to enter a comma to separate each cell or a range of cells from the others. Enter and you're done. One final note. Instead of pressing autosum, you can use the shortcut plus equal. The sum function is inserted precisely as though you had pressed the Autosum button. Learning and using shortcuts like this can save a lot of time when building financial models. The max function helps identify the maximum value. The syntax for this function is the same as the sum function, as you can enter a range of cells, individual cells, separated by commas or a combination of both. The max function returns an error if the cells you need to analyze have some texts that can't be converted to numerical values. For example, if you wanted to determine the max sales from the year 2019, you could use the max function to do so. Select cell E 15 and type equal max and select the range with the mouse or the two arrol keys. You could use the Autosum button to do the same thing. The MIN function is basically the opposite of the MAX function. It calculates the lowest value in the column. So very opposite concept here. The MIN function can also be used in any combination of range series and individual cells. Let's determine the minimum sales for the year 2019. There is so much more you can do with these two functions. Maybe you can use these formulas to calculate the inventory level spread or variance between the minimum and maximum values. Maybe your investors want to know how volatile your stock levels are. What if you have several hundred cells in a single spreadsheet column, each with a numerical value, and you want to find the average or mean value? Using ordinary formulas, you would have to sum all the numbers up, count the number of rows and then divide the sum of the number of rows. Well, fortunately, Excel has an average function, making this calculation much easier. So I will quickly and easily calculate the average value of sales for the year 2019 with the average function. A quick warning here. The average function only uses the cells with values. Make sure that this is what you intend with your calculation. In most situations, there isn't much difference between a blank cell and a cell containing a zero value, and most functions treat a blank cell as though it contains a zero. When using the average function, however, this is not the case. The average function counts the cell with a value of zero, but it ignores empty cells and doesn't include them in the calculation. If you want those cells to be counted, you need to enter a value of zero in those cells. 32. IF and NESTED IF Formulas: Alright. In this lesson, we're going to learn about the I formula in Excel and understand how to write nested if formulas. Nested formulas are lengthy if formulas where you can mix one I formula with another to test a complex condition and return an output. Before getting started, let's understand what the I formula is all about. If formula is your way of telling Excel to test a simple condition. In plain English, the way it works is that if we use I as a clause or a condition, even in our thinking or day to day English usage, when we say I today is Monday, I'll wear a black shirt. Otherwise, I'll wear a brown shirt. The first thing we are testing is what day is today. If today is Monday, then we'll select a black shirt. Otherwise, we'll select a brown shirt. Similar thinking can be applied to Excel formulas also. The I formula syntax has three components. It tests for logical condition and returns one value if the test is true and another if the value is false. Let's go ahead and try to experiment with a few simple I formulas as with the sample data that is shown here. For example, if you want to check if a doctors from the United States, write this formula, equal if parenthesis, C two, equal to United States. American citizen. No American closed parenthesis, press Enter. We don't need to give an extra case sensitive value when we're using equal two. Let's copy the formula and type it to another location. For example, F ten. If you remember our lesson on relative and absolute references, you would know that when we copy the formula from one location to another, Excel will automatically adjust the cell reference. So the formula has been adjusted as follows. Since C ten is United States, the value that Excel displayed as the result is an American citizen. This is how a basic I formula works. I'm sure this is very elementary for some of you because you have been working with Excel formulas. But the important thing to understand here is that the I formula remains one of the most highly used formulas in Excel and knowing a bit more about it and knowing how to nest if formulas can lead to great improvement in your Excel productivity. Let's also test a numerical formula. Click on cell G two and type equal if E two greater than 20 then type profit maker. Ls type loss makeer. One more thing that you may want to remember is that you can just leave the I formula without mentioning it in any of the true or false parameters. You can leave one of the parameters out, but not both. Now let's move on to the interesting topic of how you can nest if formulas. For those of you who are not familiar with this terminology called nested ifs, I'll provide a bit more detail. Suppose we want to test if today is Monday and choose a shirt based on the weekday. If it's Monday, I'll wear a black shirt. If it's Tuesday, I'll wear a brown shirt. If it's Wednesday, I'll wear a red shirt. If it's Thursday or Friday, I'll wear jeans. This is my weekly outfit schedule, so let's write the conditions for this. Equal if open parenthesis, today is Monday, black shirt. If open parenthesis, today is Tuesday, brown shirt. If open parenthesis, today is Wednesday, red shirt. I open parenthesis or open parenthesis, today is Thursday, today is Friday, closed parenthesis, jeans, stay home, closed parenthesis. As you can see, this is not a simple conditional logical test. Even though the input for all the testing conditions is the same, IE, what is today's weekday, you need to test it against four conditions before you can eventually determine what shirt you are going to wear today. One of the mistakes that we commonly make when we do this is closing the right brackets. We might want to close them in the wrong order or close them earlier on. That's what the nested if formulas actually mean. This is a very powerful and useful technique. It's also very good for thinking tool for this, because whenever we have a problem like the one shown here in the real world, you can model it as a nested if formula very simply. Of course, there may be some more complications. So it's important to learn about some of the logical operators. For example, in our last condition, we are saying that if it is either Thursday or Friday, then the shirt would be jeans. This is where we are using the logical operator called O. This is the same as our mental operator or whenever you say, do you want coffee or tea? You are introducing the or operator. You can also have an and operator where you are asking for a specific set of criteria to be met. For example, it could be like this. If it is Monday and the date is the second, I'll wear my brown shirt. This is a good example of the end operator, where we are saying that the weekday should be Monday and the date itself should be the second of the month. One of the disadvantages of nesting formulas is that they tend to grow quite big. A solution that I suggest people try instead of a nested if formula is to use a choose formula. The I function is very commonly used in financial models because it allows you to test certain conditions in your model and change outcomes or results depending on what the user inserts into the model. It's beneficial when building scenarios because you can build the model so that the user can turn certain conditions on and off. I hope you've benefited from this lecture. I'll see you in the next lesson until then have a good time. Bye. 33. Advanced Chart Formatting Options: In this lecture, we're going to learn advanced chart formatting options like dynamic titles, reverse categories, axis options, error bars and chart templates. We'll start with dynamic titles. You already know every time you add a chart, Excel adds a chart title. You can also add a chart title if you go to Design tab, Add Chart Elements. Chart title. To make a tidal dynamic, you have to point this chart title to a value like a cell reference. For example, I've written to chart titles here. The first is a simple text, revenue and net profit per year. If you want to make the chart title dynamic, just click on the title, click on the formula bar, type equal, and assign it to the cell with a written title. In our case are the revenue and net profit per year. Press Enter and Excel will automatically show the title. The second is a more complex title that I use a formula to create. Click on the formula bar again, delete them all except the equals sign, and assign it to the cell with the cell E12. Press Enter, click on A12 cell, and let's explain the formula. A7 is this year 2018. The ampersand is a concatenation operator which can be used to combine two text strings are values. After the year 2018, I've added a space. Then I have added the text revenue went up by. Then I use the INT formula which rounds a number down to the nearest integer. After that, I made a calculation to find the percentage of increase. And finally, I've added the A16 value, which is the year 2017. That way I compare two years, 20182017. This is a very powerful dynamic title, and these are two examples of how dynamic title charts can be used. Dynamic titles are used very often in dashboards. Next, we're going to talk about the reversing chart categories. Here we have created a bar chart based on these datas. However, the values in the table are in reverse order. The first year, here's 2015, but in the chart is 2018, we are going to reverse the order of the values in the chart. Click on the vertical axis and press Control plus one. The Format Axis pain appears from Axis Options select categories in reverse order. Now look at the chart, excel reverse the values, but also moved the x-axis from the bottom to the top. Choose horizontal axis crosses vertical axis at the maximum category and close. We have corrected the category axis. Now we're going to learn to format axis and axis bounds. I have a lot of values and vertical axis starting from 0 and increases by 50 until the 500 value. Select the vertical axis and press Control plus one. The Format Axis pain opens. Look at the bounds from access options. You can set the minimum and maximum values, but make sure you consider your data. It's wrong to set a maximum bound to 300 and the max venue at 450, then the bar will appear to be cutting through the chart. Let's adjust the major unit to a high value, for example, 200. The Display Units option is useful when we have big numbers. I don't think tick marks are very useful. The number settings will allow us to format the axis values on a custom number. For example, changing the category number two currency and look at the chart. Undo to general category. A clever features the format code. Let's suppose, for example, that we want to highlight the values above 300. Type the following. Green in brackets, greater than 300 in brackets again, and three zeros separated by semi-colons. Now look at the axis. We wanted to green color the values that are greater than 300. Great. Delete this specific format and press Add button to return to the default format. When we have dates, there are some special options that I'm going to explain now. This chart has a very noisy horizontal axis. We can't even see the dates. One of the beautiful features of Excel is access date formatting is that you can actually group the data by month or year. Right-click on the horizontal axis and select the Format Axis. From the Format Axis, pain changed the major unit two months. Excel group the values by month. Pretty straightforward, right? You can play with this option and group, for example, the data by ten days, like this. I think it's better now, as you can see, the grouping feature can be applied straightaway from access formatting. The next topic is about combining two chart types. In this table, we have some sales and some expenses per year. Both sales and expenses are in this column chart. I want to change the expenses as a line chart. This is done by selecting a series right-clicking and choosing the Change Series Chart Type option. Selecting a line chart. Excel will nicely overlay the line chart over the columns. I don't recommend adding a secondary axis because it's a bit confusing. Press Okay. This is how combo charts work. I also recommend removing the chart elements and clean up the chart. For example, you could remove the grid lines or reduce the vertical axis frequency. Or delete legend if necessary by pressing the Delete key. Sometimes the labels are not readable, especially vertical labels. So you might want to show the labeled horizontally. I will show you a trick so that you can create an artificial tool on effect. You can create these kind of effect very easily using some simple formulas. Look at these labels. They're actually a mirror lookup. The first cell is referring to be E911, but the second cell is referring to cell B19, T2, but it also has a formula called char ten. The Excel char function returns a character. When given a valid character code. Chart can be used to specify characters so that we are hard to enter into a formula. For example, char ten returns a line break on Windows. So we'll use this formula to insert a new line into a value. The third sells referring to B19, A3, right? The first two formulas and then drag them down and it creates a range of values. After that, right-click on the horizontal axis. Select data from this dialog box, press the edit button from horizontal axis labels and highlight the new labels. Press OK and OK. Again, Excel created this two line effect. The distracting elements of the chart are now in the background and the actual message is popping out of the chart. Let's continue to the topic which is the error bars. Error bars in Excel are graphical representations of data variability. They show the precision of measurement. The bars usually represents standard deviation and standard error. They indicate how far from the determined value is the true value. Error bars begin on a chart and they provide a more comprehensive view of the dataset and allow users to see the margins of error of the data. Microsoft Excel allows to add error bars to certain types of charts, including line chart, bar, bar chart, and scatter chart. In our example, we have the sales of console's from the year 2016 to 2018. And this is a line chart with markers. Select the chart and click on the plus symbol on the right of the chart. Check error bars, and click on the arrow to see the different options. You can add the following error bars to your chart. Standard error indicates the standard error for all values in the dataset. Percentage determines a percentage error range and error amount for each value. Standard deviation displays a standard deviation for all values. If you click on More Options, you can add error bars based on these series. Nice, we've touched on a useful feature in Excel like error bars, and now we're ready to move on. Formatting a chart is also chosen the right colors for our chart. There are four principles wound designing a chart, contrast, repetition, alignment, and proximity. The first-principle contrast means that in a chart you want to contrast one thing against others. For example, in this chart I want to contrast the highest amount of revenue with everything else, which is revenue for the year 2018. It has to be completely different than others. For example, a red color. So that everybody looking at the design will know that this is different from that. To do this, I will highlight the specific column, choose a red color for the fill color dashboard. The contrast principle has great importance when creating a dashboard. Undo now. The second principle is repetition, which means that you have to repeat the same ideas throughout your design. For example, the same colors, fonts, gridlines, labels, etc. In this chart we repeat similar elements throughout our design, like colors and labels so that everything looks consistent. The third principle is alignment. It means that all the edges of the elements should be aligned to each other. This aspect can be achieved whenever we were making multiple charts or we were making dashboards and the size is similar. The charges are aligned with the table, et cetera. The last principle is proximity. Proximity means that there are similar elements that should be grouped together. In this chart, the years are grouped here. And here. These are the four basic design principles that you should keep in mind when creating a chart or dashboard. Also, the color palettes in Excel 2019 are nicely contrasted. Don't choose confusing colors just like the default pallets. You should be able to keep everything simple and work with it. If you liked the design of this chart, for example, you can save it as a template and reuse it for future purposes. Just select it, right-click and choose to save as a template. Excel will prompt you to select a location to save it. This is a huge time-saver. Once you save it as a template, you can reuse them whenever you are making a new chart. That concludes our lesson on chart formatting. I hope you found this lesson useful. In the next lesson, we're going to learn more advanced charting and formatting tips. Thank you, and I'll see you in the next lesson. Bye. 34. Advanced Charting Techniques: Hello everyone, Welcome back to the course. In this lecture, we're going to learn advanced charting and formatting techniques. These are the topics that we're going to cover. Overlapped and bar charts in series gaps. Negative bars, highlighting the maximum or minimum item automatically. Adding an average line to a chart. Let's get started. Here I have some data, revenue and net profit for a company from 2015 to 2018. By default, if I make a column chart out of this data, it will look like this. Now we're going to create a visual effect or profits or slightly overlapping the revenue figures so that it is easy to compare. How can we do that? Right-click on the net profit series, the orange columns then Format Data Series. By default, series overlap percentage is set to the middle, sometimes 0, sometimes negative. Let's set it to 50%, which this kind of visual effect, gap width was the width between the two different points in the series. I recommend setting this from 50 to 150%. The final result is this profits or slightly overlapping the revenue figures. So let's move on. The next topic we're going to talk about as negative bars. Here I have some similar data with some losses in revenue. Also, I have removed the net profit column. Here's the default column chart that Excel created where everything is the same color as you already know when the values are negative, we usually highlight them and in different color like red for example. Okay, let's see the steps to achieve negative bars. First right-click on any bar and select Format Data Series. The Format Data Series pain appears to the right of the screen. Click on the fill in line tab. It's the bucket icon. From the fill section. Check the option called invert if negative. The negative series is looking like this. No fill color inside, only the outline appears. The next step is to fill them with the red color. If I click Solid Color, I will notice that I have to fill two different colored buckets, one for positive bars and one for negative bars. Let's change the inverted field color. The same technique can be applied to any other type of chart. This is how you can show negative bars. Next, we're going to learn how to highlight the maximum or minimum value. Here is a default column chart. Of course we do the max value in the year 2018, but I want to highlight the values dynamically. I'll use the IF function to find the max or minimum value. And I've already added an extra column called max. Now start typing equal IF parenthesis B34 equals to the max, parenthesis B34 to be 37 with absolute reference, close parenthesis, comma. Comma not applicable. Close parenthesis. It checks if the value is the maximum value in the series. And if it is, then we want that value. Otherwise we want to display not applicable and slash, Hey, if you don't understand something in this formula, don't worry, I have a whole section with all the functions you will need, including the if and max functions. Copy this cell all the way down. The max value is 240, of course. Now, right-click on the chart, select data from the Select Data Source dialog box. I'll add a secondary series. Press the Add button from the Legend Entries. The series name is this cell C33 in the series values are C34. See 37 cell. Click OK Twice. Notice the second orange columns beside the blue one. Next we're going to overlap these columns completely. Right-click on the orange column, format data series and change the series overlap percentage to 100%. Close the pain. If you want, you can change the color of this max column. I'll change the value of the year 2015 to 300 and enter. Look at the chart. It changed dynamically. The steps are exactly the same. If we want to highlight the minimum value. Let's move on and talk about how to add an average line so that it's easy to find out what the values are above average or below average, will use the average formula in the first cell, calculating the average of the sales column. Labels C4d eight as an average and type in equal average from before E19 to B1502 in absolute reference and enter. Copy this cell down. Now let's select cells from C14, E19 to CD52. Copy them. Select the chart and paste. Excel adds the new values as an additional series to the chart. Right-click the series to the orange columns, change the series chart type and from this dialog box, replace series to from a clustered column chart to align with markers. Click the OK button. This is a technique that adds an average line. Easily. Go ahead and download the project file and experiment with these four techniques. If you have questions, please let me know. In the next lesson, I'll show you how the camera tool works. Very useful Excel tool for dashboards. Thanks for watching, and I'll see you in the next lesson. 35. Advanced Custom Number Formatting: You can apply number formatting to cells in several ways. Most people simply use convenient number commands found by the home tap. By using these commands, you can quickly apply some default formatting, number, percent, currency, and so on, and just be done with it. But a better way is to use Format Sells dialog box in which you have the ability to create your own custom number formatting. In this worksheet, I have two columns, date and sales. I'll highlight the first ten cells from the sales column to apply basic number formatting. Right click a range of cells and then select format cells from the menu that appears. The format cells dialog box appears. Open the numbers tab and choose a starting format that makes the most sense for your scenario. The values are sales, so we could use 1,000 separator with zero decimal places and enclose negative numbers in parenthesis with red color. Click the button. You can clearly see the changes for these ten cells. Now I'm going to highlight the cells from B 11 to the end. Right click again, the range of cells and select format cells from the menu that appears. Click the custom option. Excel takes you to a screen that exposes the syntax that makes up the format you selected. Here you can edit the syntax in the type input box to customize the number format. The number format syntax tells Excel how a number should look in various scenarios. Number formatting syntax consists of different individual number formats by separated semicolons. I'll click on this format. Here you can see two different formats, the format to the left of the semicolon and the format to the right of the semicolon. By default, any formatting to the left of the first semicolon is applied to positive numbers and any formatting to the right of the first semicolon is applied to negative numbers. So with this choice, positive numbers will be formatted as a simple number, whereas negative numbers will be formatted with parentheses. Note that the syntax for the positive formatting in the previous example ends with an underscore into closing parenthesis. This tells Excel to leave a space with the width of a parenthesis character at the end of the positive numbers, which ensures that a positive and negative numbers align nicely with negative numbers and they are wrapped in parentheses. You can end at the syntax in the type input box so that the numbers are formatted differently. For example, try changing the syntax to the following. When this syntax is applied, positive numbers will start with the plus symbol and negative numbers will start with the minus symbol. Now, I'll show you how to format numbers in thousands and millions. Highlight all the cells of the sales column. Right click and select format cells from the menu that appears. After the format sales dialog box opens, click the custom option. Select this type. In your type input box, at a comma after the format syntax. After confirming your changes, your numbers will automatically appear in thousands of places. The beautiful thing here is that this technique doesn't change the integrity or truncate the numeric values in any way. Excel is simply applying a cosmetic effect to the number. Look in the formula bar to see the real unformatted number. Custom number formatting has obvious advantages over using other techniques to format numbers to thousands. For instance, many beginning analysts would convert numbers to thousands by dividing them by 1,000 in a formula. But that changes the integrity of the number dramatically. When you perform a mathematical operation into a cell, you are literally changing the value represented in that cell. This forces you to carefully keep track and maintain the formulas you introduce to simply achieve a cosmetic effect. Using custom number formatting avoids that by changing only how the number looks, keeping the actual number intact. If needed, you can even indicate that the number is in the thousands by adding K to the number syntax. Let's do it. Highlight the number of cells, click on one of them and select format cells. Besides the comma, add a K letter in double quotes. Click the OK button and look at the numbers now. Now, how cool is that? Need to show numbers in millions. Easy. Simply add two zeros and two commas like the number format syntax and the type input box. Then add an M letter like this. Press Okay. Note that the use of extra decimal places or 0.00 when converting numbers to millions is often useful to show additional precision points. The numbers we have are relevantly small to convert them into million, but you get the point. Custom number formatting isn't just for numbers. You can also format dates and times. Highlight the cells 2-32. Open the format cells dialog box and click on the custom category. Date and time formatting involve little more than stringing together date specific or time specific syntax. The syntax used is fairly intuitive. For example, add DDD is the syntax for the three letter day. MMM is the syntax for the three letter month and why, why, why, why is the syntax for the four digit year. I have included a PDF file with some common date and time format codes that you can use as a starter syntax for your reports and dashboards. I hope you learn how you can apply number formatting cells in several ways. I want to thank you for watching this lesson, and I will see you in the next one. 36. Introduction (Conditional Formatting): Excel makes creating a basic chart easy. Select your data, choose a char type. You're finished. You may want to take a few extra seconds and select one of the pre-built chart styles and maybe even select one of your chart layouts. But if your goal is to create the most effective chart possible, you probably want to take advantage of the additional customization techniques available in Excel. Customizing a chart involves changing its appearance as well as possibly adding new elements to it. These changes can be purely cosmetic, such as changing colors, modifying line widths are adding a shadow. Or quite substantial, say, changing the axis scales are adding a second value axis. Chart elements that you might add includes such features as a DataTable, a trend line or error bars. The proceedings section introduce charting in Excel and described how to create basic charts. This section takes the topic to the next level. You learn how to customize your charts to the maximum so they look exactly like what you want. You also pick up some of the slick charting tricks that make your charts even more impressive, like conditional formatting and shapes. Microsoft Office, including Excel, provides easy access to a variety of customizable graphic images known as shapes. You might want to insert shapes to create simple diagrams, display texts, or just add some visual appeal to a worksheet. Keep in mind that shapes can add unnecessary clutter to a worksheet. Perhaps the best advice is to use shapes sparingly. Ideally, shapes can help draw attention to some aspect of your worksheet. They shouldn't be the main attraction. You can add a shape to a worksheet by choosing the Insert tab and then shapes from Illustrations group. The shapes gallery opens to show you the choices. Shapes are organized into categories. In the category of the top displays the shape that you've used most recently. Every shape has a name. Some have generic names like shape one, shape two, but others are given more descriptive names. For example, rectangle one, to change the name of a shape selected and type in a new name and the name box and press Enter. Excel 2019 includes a new icon library that offers free scalable vector graphics or SVG icons. Svg graphics can be sized and formatted without losing image quality. These icon graphics are essentially a modern set of graphic files that can be used to add visual elements to your Excel dashboards in photographs and reporting solutions. To add an icon to your worksheet, select Insert tab, and then icons from the Illustrations group. This activates the Insert Icons dialog box here, you can browse by category and then double-click the graphic you want to use Excel inserted into your workbook. So with that, let's go ahead and get started. 37. (NEW 2024) Trend-based Conditional Formats: In this lesson, we're going to look at how we can make our data more self documenting by applying trend based conditional formats. A conditional format is where Excel will make a cell look different based on some criteria. With the trend based conditional formats, it compares a dataset against itself and basically ranks all the values in that data and applies formatting accordingly. In this case scenario, Bill has been given data on waste management in Australian states and has been asked to make it easy for people to quickly spot trends and anomalies. For example, we're going to scroll down to our resource in recovery and recycling rates. Bill would like to make it very obvious where we're producing large amounts of landfill. We're going to select the data from E 29 down to E 36, and then we're going to come up to our Home tab in our ribbon and click conditional formatting. The first two options are value based conditional formats, which we'll look at in the next lesson. Well, we're going to be focusing on all these three, which are trend based. We'll start with our color scale. Now, Bill wants where we have a high level of landfill to show in red, going through orange, yellow and green for lower values. To achieve that, we're going to go for the red, yellow and green color scale. Then as you hover over the different options, you'll see that you can choose whichever one suits your data. I'm just going to click to apply. Really easy. Now, you're not limited to just a column or just a row. You can apply to an entire block of data. So let's do the same for resource recovery rates by region. I'm going to click on C 17 and drag down to age 24. Again, come to conditional formats. Resources recovery rates, and we want the high results recovery rates this time to be green. So we're going to come down to the color scales, and I'm going to choose the first option this time. And there's our data nicely formatted. The potential downsides with the color scales is that they're quite busy, and if you don't provide a key, it might not be totally intuitive. Let's look now at how we can remove a conditional format and then we'll apply one that might be a little bit better for this data. I'm going to come back to conditional formatting, and this time, I'm going to come down to clear rules. You'll see we have an option to clear these selected cells or from the entire sheet. We want to clear from selected cells. Easy, come, easy go. Still, with your data selected, we're going to come back to conditional formatting, and this time we'll have a look at the data bars. Hover over the different options. I'm going to go to the light blue, but you can choose any color that you like. You can see it's almost like applying a chart. The larger values, the bar is almost the whole way across the cell, whereas the smaller values, you get a much smaller bar. Now, the way it works with all the trend based formats is by default. It starts with the lowest value as the bottom of the set and the highest value at the top of the set. In a moment, we will look at actually how you can modify that. But for this one, it's looking great. We're just going to left a line the text and maybe make it a size nine. One more example we're going to look at, which is icon sets. We can apply the icon sets to the data in the cell, but in this particular example, we'd actually like to put it in a separate cell. Let's click into J 17 and let's type equals and click into your cumulative growth right in I 17 and press Enter well, then just click back and double click the fill handle to copy that down. Then with your data still selected, we're going to come up to the conditional formatting and come to icon sets. The icon sets allow you to categorize your data into three, four or five categories. And the category we'd like to use is three triangles. So we're going to click on that and to apply it. Once again, you can see that it's taken the top third made of those green triangles. The middle third has got the yellow lines, and the bottom third, because that value is quite a lot lower than the others, has got the red triangle. But in this case, it's not what we want at all. We would like all of our positive growth rates to have a green triangle and all of our negative ones to have a red triangle. And if there's been no change to see the yellow line. We're now going to look at how to actually customize our conditional format. Again, make sure that you've got them selected. Come back to conditional formatting. We're now going to choose manage rules right at the bottom. By default, you will see the rules applying to the current selection. But if you come up to where it says current selection, you can actually switch this worksheet to see all the rules in your worksheet. And from here, you can delete rules you don't want or edit rules, and we're going to edit the icon set rules. So we're going to click on it and then click Edit Rule. We could switch it out for a completely different trend based format if we want it, and you can mix and match the icons, but you have to select the icons provided. You can also change the boundaries. You can see at the moment that it's making the top 33% green. We actually want all the positive numbers to be green. We're going to switch percent to number, and then we're going to say greater than zero, so it doesn't include zero. Then the second group, we're going to make that number as well. I'm going to say greater than or equal to zero. Now, that seems a bit odd, but the ones that are greater than zero are already getting a green triangle. This will only pick up equal to zero, and everything else will get a red triangle. One more thing. We want to show icons only. I'm going to tick that box, and we're going to say, Okay, at this point, you don't have to press Apply unless you want to do something else in this dialogue. You can just click Okay, and there are icons clearly showing that there has been an increase or a decrease in our cumulative growth rate. We've looked at three types of train based conditional formats. In the next lecture, we're going to have a look at value based conditional formatting. 38. (NEW 2024) Value-based Conditional Formats: In this video tutorial, we're going to look at how we can use value based conditional formatting to highlight specific criteria in our worksheets. For example, Bill would like to identify the five lowest incidents of coal waste production. We're going to select the data from C five to age 12. Come to conditional formatting and come to top slash bottom Rules. These basically allow us to select a top or bottom band within our data. We want the bottom ten items, but we're not locked into ten. We're actually going to make that five. By default, it shows us a red fill with a dark red text, but there are other presets that you can choose from. If you don't like any of them, you can come to the custom format and define your own. We're just going to go through a green fill and say, Okay, it's very easy to apply. Okay if our data changes, I'll make the zero. So you'll see the conditional formatting immediately responds. Let's undo that change. Control Z, and we're back. Now, one problem that can creep into our data that can be a real issue is duplicate values. Conditional formatting can help us identify any duplicates. For example, I'm going to select my states or territories. So highlight cells from B five to B 12. And I've got to come to conditional formatting, come to highlight cell rules, and here you can get a really rich set of options. We're going to click on duplicate values near the bottom. Oh, dear, we do have duplicates. That could have been very embarrassing. I'm going to select from B ten all the way to J ten because that's my duplicate, and I'm just going to press delete. It's an easy fix. Now let's look at something even more specific. I'm going to scroll down. Bill wants to identify all the recycling rates that are below 48%. So we're going to select our recycling data. Then come to conditional formatting and come to highlight cell rules. We're going to choose less than and type 48%. And click Enter to apply. We have quite a lot of options, and they're very quick and easy to apply. Thanks for watching. 39. Small Multiples: Trying to compare to any numbers in ones can quickly become overwhelming and meaningless. Look at this chart. The values in that dataset are so close together that we barely see anything. Small multiples allow you to create clear and easy to read charts. In this video tutorial, we're going to reduce clutter and busy line charts by plotting your data in small multiple charts. Small multiples are several graphs all on the same scale arranged together. In this data table, we have the average rainfall and five countries per year. To construct a small multiples, we will create five separate line graphs, one for each country. Let's create the first line chart. Highlight the data from A1 to A2. From the Insert tab, click the line chart. Make it smaller and move it somewhere here. This is the first line chart for Italy. In small multiple series, the axis needs to be the same. So we need to revise the scale of the y-axis to cover the Min and max average rainfall of all the countries. Right-click on the y-axis and click Format Axis. Now right-click on the status bar and enable minimum and maximum value. Highlight all the values and see the Min and max numbers. The minimum is 715 and the maximum is 1860. Change the Min bound to 400 and the max bound to 2 thousand. The next step is to copy the chart and paste it somewhere here. The char percent, the first one. Right-click on the second graph and choose Select Data. Click the Edit button, and then use the cell picker icons to point the graph onto the next set of data for the United States. Click Okay, and Okay, again, delete that axis and repeat the same steps for each of the countries. Make sure that all the graphs have the same sized plot area, including the first one on the left which houses the axis. Let's select all grasp by holding down the Shift key and clicking on them all. Then look in the drawing tools tab and under Align button, select Align Top. You can also remove the outline of each chart. Finally removed the Excel grid lines from the View tab. Small multiples of the line chart make it easier to compare values. I want to thank you for watching this lesson, and I will see you in the next lesson. 40. Mini In-Cell Charts (using REPT Function): An insult char provides visualization of the number shown. To create an Excel chart, we have to use the rect function. The function repeats a character x times an x is the number we decided to repeat the given character. For example, if you go into the D1 cell and enter equal parenthesis, double quotation marks, G, double quotation marks, 20 and close parenthesis. Make the column wider. And look at the return value. Now let's do something else, trying fancy fonts instead of numbers or letters. Highlight this cell and change the font from the Home tab. We want to use fonts that contain icons like Wingdings or weddings. Let's try weddings and see the result. We wanted to create in cell charts in a new column beside the table using the rep function. Type in C1 cell, in cell sales chart. Column B has big numbers repeating a character too many times. Isn't that useful? When you think you have large values, you can cut the number down to size by dividing it by a 100 or 10 thousand glucagon cell C2 and type equal wrapped G and double-quotes b2 divided by a 100 thousand. Now, drag it down. We also want to convert it the letter to an icon I have attached to useful PDF file that shows all the conversions from letters and numbers, the icons. You can use it in many situations. We see that the letter G is a square icon. If we put the web dings font, Let's try it. Now. Drag down again. If you want, you can change the color of the cells from the Home tab and make the column wider. Thanks for watching. 41. Representing trends with Icon Sets, Data Bars and Symbols: Hello students. Dashboard environment may not always have enough space available to add a chart that shows trending. These cases, phi concepts, symbols, and data bars are ideal replacements enabling you to visually represent the overall trending without taking up a lot of space. In this worksheet, we have a table with 50 companies, their countries, and a number of employees. For each one, we will create a combination of data bars, icon sets, and special fonts to emphasize key metrics visually, I want to compare the ranks with these two columns. Let's get started with data bars. Data bars fill each cell you are formatting with mini bars in varying lengths indicating the value in each cell relative to the other formatted cells. Excel essentially takes the largest and smallest values in the selected range in calculates the length for each bar. Highlight the first sounds from D2 to D15. The easiest way is to click Conditional Formatting button. Then select from the data bars a gradient fill that you prefer. That's it. Are in cell chart is ready. If you want more detail, for example, to choose the lowest and highest value or borrow appearance. Then you can select the Manage Rules and edit the rules. Now let's create in this column some indicators. Click on the F12 cell. I will insert an IF function that puts one of the rankings of this year has improved. If it's not changed at all, I'll put a 0. And if it's a negative function, then I'll put minus one. After that, I'll take the results of the IF function and work with conditional formatting. Let's see the steps one-by-one. Go to the F2 cell and start typing. Equal IF parenthesis a2 equals to E2, then put 0. Otherwise, if a2 greater than E2, then put minus one. Otherwise, put one, close parentheses and Enter. Copy the formula all the way down and look at the results. Cell F3, for example, is 0 because both ranks are the same. But cell F4 is one because the rankings of this year are better than the ranking of last year. Now highlight all of the cells from F2 to FEV1. Click the Conditional Formatting button, new rule. Select this rule type. Format all cells based on their values. From format style, choose icon sets. Icon sets are sets of symbols that are inserted in each cell that you are formatting. Excel determines which symbols to use based on the value in each cell relative to other formatted cells. The icon style I prefer is this. When the value is greater than 0, it gives a green icon. When it is less than or equal to 0 and greater than or equal to 0. In other words, when it's 0, it puts in a yellow icon. When it's less than 0, it puts a negative sign. Also check the show icon only. I don't want both icons and numbers press. Okay, and we're ready. You can imagine why icon sets will be better on a dashboard and simply color variances. Icons and shapes do much better jobs and conveying your message, especially when the dashboard is printed in black and white. The last step is to use special fonts are symbols in column G. It's an alternative method of conditional formatting. I will use three characters from Wingdings, three fonts in Up arrow over positive change, a down arrow for negative change in a double-headed arrow for no change at all. I will use the results from Column F to determine which character I will use an IF function with to help us achieve it. But how can I decide which character I will use? Go to an empty cell, select Insert tab. Symbol. Check that the font is Wingdings three and insert this, this, this symbol. If you want to see the actual symbols, change the font to Tahoma. Notice how the hash symbol is now the up arrow. The dollar symbol is the down arrow, and then number one is the double-headed arrow. Now I'm ready to type by formula. Go to Cell G21, start typing equal if parenthesis F2 equals to one. Then puts in the hash. Otherwise, if F2 equals to minus one, input the dollar sign. Otherwise if it's equal to 0, put one. Copy the formula. Select from the Home tab, the Wingdings, three fonts. Make them bold and our visual indications are ready. This is just one of the ways to use symbols in your reporting. With this basic technique, you can insert symbols to add visual appeals to Tables, PivotTables, formulas, or any other object that you can think of. Using icon sets, data bars, and symbols provides a nice visual element, allowing for an at-a-glance view of which markets are up, down, or flat over the previous month. Thanks for watching. 42. Pictograph: The advice to eat more fruits and vegetables will likely always be a staple of nutrition recommendations. But how much do you really need? As it turns out, there may be a specific breakdown of how many servings of each you need to increase your longevity. According to a recent study in the journal Circulation, researchers found that five servings per day, it seems to be the magic number when it comes to reducing your risk for early death. Split up into two servings of fruit and three servings of vegetables. In this example, we are plotting the number of servings of fruits and vegetables consumed per day by some families in our simulated program, as well as the national average, could just be a bar chart or even a lollipop or dotplot. But maybe we need to be a little more precise since the counts are so small. So we will replace the bar with an icon of a fruit, one icon per serving consumed. Notice that the servings consumed or in the column labeled ideal. The goal is five servings per day, so the remainder is listed in the next column called gray. You'll also need to procure a fruit icon and a greening reversion saved somewhere on your computer. I have the icons ready for you. If you check out the resource file, will look in the icons menu in the newest version of PowerPoint to download them. Now grab the data in Excel. Insert a stacked bar chart. Right-click on the bars that represent your ideal data and select Format Data Series. In the field section, this is inside the icon that looks like the paint bucket. Select the radio button by picture or texture film. Then select the Insert button and go locate the green serving fruit icon you saved earlier. Then click the stack and scale with button. We want those grapefruit icons to look like the remainder follow the same procedure as above to fill the other bars with a picture of the grapefruit. Now, the graph is probably looking a bit distorted with some seriously squished in an advertising fruits. We will adjust a few things here to make this look right. First of all, Excel likes to give you more than you need. So the x-axis is currently running to six, but it needs to stop at five. To fix this, right-click on one of the numbers in your x-axis. Choose Format Axis, change the maximum bound to five instead of six. Well, this probably added to the distortion, but it'll be easier to deal with the overall chart area now, in fact, let's delete that axis. Just click on it and hit the Delete key on your keyboard and delete any grid lines. If you click on one of the fruits, you'll see that it is sitting within your original stacked bar area. And that area is skinny, thin, stretching your fruits. Let's make it thicker. Right-click on any bar and select Format Data Series. You'll see a menu called gap width. This is referring to the width of the gap between your stacked bars slash fruits, set it to 0 and the fruits will become more proportionate. The fruits are still a bit stretched. So re-size the overall chart area until the icons look proportionate. You could be done at this point, but you may want reordered the categories on your axis so that the average is at the top and family for is at the bottom. Right-click on the y-axis and select Format Axis. Check the box that says categories in reverse order. You'll notice that there is still one line left, even though we deleted the grid lines. That's actually the y-axis line. While in that same menu messing with the order of the categories, go into the paint bucket icon and in the align menu, select no line. Now delete the legend and add a title. Click the chart and from Format Chart Area from Phil options select no border. Whether you call it a pictograph, pictogram, isotype chart or icon array. It's an efficient way of communicating small datasets. It might take a few minutes of your time to construct something like this, but the investment is worth it. These graph types are easy to read. 43. Comparisons and Data Bars (with Conditional Formatting): Visualization is the presentation of abstract concepts or data in visual terms through a sort of graphical imagery. A traffic light, for example, is a visualization of the abstract concepts of stop and go. Conditional Formatting is determined given to Excel's capability to dynamically change the formatting of a value cell arrange itself is based on a set of conditions you to find. Conditional formatting adds a level of visualization that allows you to look at your Excel reports and make split-second determinations on which values are good and which ones are bad simply on formatting. Conditional formatting is flexible, applying specified formatting only when certain conditions are met. By applying conditional formatting to your data, you can quickly identify variances in a range of values with a quick glance. It allows you to automatically apply formatting such as colors, icons, and data bars to one or more cells based on the cell value. Thanks to the many predefined scenarios that Excel offers, you can apply some basic Conditional Formatting with a few clicks of a mouse. First of all, you'll need to create a conditional formatting rule. In our example, we have a worksheet containing two tables, one with monthly sales data and one with Yearly Sales. Suppose that the sales goal is 110 per month. So we'll create a conditional formatting rule for any cells containing a value higher than a 110. Here are the steps. Select the cells where the conditional formatting rule. Go to the Home tab, click on conditional formatting, a drop-down menu will appear. Highlighted cells, rules, top bottom rules, data bars, color scales, and icon sets. Hover the mouse over the first selection, which is the highlight cells rules. The thing to remember about these scenarios is that they work much like an if then else statement. That is to say if the condition is met, then the cell is formatted and if the condition is not met, the cell remains untouched. Select the desired rule from the menu that appears in our example. We want to highlight cells that are greater than a 110. Choose the greater than scenario. A dialog box will appear in this dialogue box, the idea is to define a value that will trigger the conditional formatting. Either type the value or references cell that contains the trigger value, and then use the boxes drop-down menu to specify the format that you want to be applied, will enter a 110 is our value. Select a formatting style from the drop-down menu. In our example, we'll choose green fill with dark green text and then click the OK button. It conditional formatting will be applying to the selected cells. Now it's easy to see which salespeople reach the 110 sales goal for each month. The benefit of a conditional formatting rule is that Excel automatically re-evaluates the rule every time a cell is changed. To avoid overlapping different conditional formatting scenarios, you may want to clear any conditional formatting that you have previously applied before applying a new scenario. Declaring conditional formatting for a given range of cells. Select the cells and select conditional formatting from Home tab of the ribbon. There you can find the clear rules selection. Click clear rules, and select whether you want to clear conditional formatting for the entire sheet or only the selected workbook. Now we'll use conditional formatting to create data bars for the second table, data bars or horizontal bars added to each cell, much like a bar graph. Using the data bars conditional formatting option can sometimes serve as a quick alternative to creating a chart. The length of the bar is based on the value of the cell relative to the other values in the range. When you adjust the column width, the bar length adjust accordingly. When the bar is wider than the differences among the bar lengths, they are more prominent, so it's better. First of all, select the range and from the Home tab, click the Conditional Formatting button. Choose Data Bars and select one of the fill options. We've created a chart directly in a range. You don't have to use one of the predefined scenarios offered by Excel. Excel gives you the flexibility to create your own formatting rules manually. Creating your own formatting rules helps you better control how cells are formatted and allow you to do things that you wouldn't be able to do with predefined scenarios. For additional choices, click the More Rules option which displays the New Formatting Rule dialog bonds. From this dialog, you can show only the bar without the numbers specify minimum and maximum values for the scaling change the appearance of the bars and many more, it's worth taking some time to understand how this conditional formatting rule works. In the next lesson, we'll learn more conditional formatting options and tricks. Thanks for watching. 44. Heat Map with Color Scales: In this lecture, we're going to learn how to create a heatmap and Excel. Basically a heatmap is a presentation of data in colors according to the values. We can visualize trends or relationships using color scales. Colors, skills fill in each cell you are formatting with the color varying in scale based on the value in each cell relative to the other format itself. Excel essentially takes the largest and smallest values in the selected range and determines the color for each cell. For example, I can easily spot which are the months for accompany when sales were low as compared with the other months. Or I can spot the low and high temperatures by a city per month. Keep in mind that in order to have a nice heatmap, you need to have the intuitive colors, scales like red to green for earnings or blue to red for temperatures. First, we will create a simple heatmap using conditional formatting to highlight cells based on the value. This way in case you change the values in the cells, the color or format of the cell would automatically update the heatmap based on the pre-specified rules in conditional formatting. In this table, you can see the earnings for of accompany by month from January to December and from the year 2 thousand to the year 2017. The steps are very simple. The first step is the highlight cells from B2 to S 13. From the home tab, choose colors, scales from the conditional formatting menu. Here I can choose various color combinations that are used to highlight the data. The most common color scale is the first one, where the cells have high values. They are highlighted in green and low in red. Note that as you have for the mouse over these color scales, you can see the live preview in the dataset. I will choose the third one that serves my purpose, more green, more earnings, more red, fewer earnings. Now if you only show the colors and not the values in the cells, a great tip is to use custom formatting rule from Format Cells properties. To the bottom of this category list, select 0, deleted, and just type three consecutive semi-colons. I know K. The numbers are invisible. The conditional formatting is still based on the values, but the user doesn't see them in that way. You just visualize the trends. Another option is to change the colors skill by managing the rules from this window. The rule and edit the color scale. For example, I'll change the color of the lowest value. Ok? Ok again and see the different shades of green. The heatmap is a wonderful tool to tell a story. Thank you, and I'll see you in the next lesson. Bye. 45. Chart Up and Down Arrow Symbols: Hi everyone. In this lecture we're going to learn how to use symbols and formulas and insert them into our chart. Symbols are essentially tiny graphics, not unlike those who use when you've seen Wingdings or web dings and other fancy fonts. However, symbols are not really fonts. They are Unicode characters. Unicode characters are acidic industry standard text elements designed to provide reliable character set that remains viable on any platform regardless of international font differences. In terms of Excel presentations, Unicode characters were symbols can be used in places where conditional formatting cannot. Specifically we are going to use up and down arrows. Where can we find these symbols? Click on Insert tab and then the symbol button. New window appears. From the drop-down menu, choose Arial and from the subsets use geometric shapes. Now you can see the arrows that we want, I like to use triangles is to give me an indication of the change in my data. I already put it up the op triangle and cell G2 and the down triangle and cell G3. I've got my Unicode characters here. Now let's look at the table. Here we have the sales of two years, 20142015 for 12 months. We also have a simple formula that calculates the percentages of change between 20142015 in cells G2 and G3, I have the up and down arrow in column E, we will calculate the arrows using the if statement. Let's get started. Go to the E2 cell and start typing equal. If C2 is greater than b2, then apply G2, which is the up arrow. Otherwise apply G3. Don't forget that the absolute reference in the cells of J2 and J3 close parenthesis and press Enter. Copy the formula all the way down. Great. The next step is to create the chart cells. Select my data from cells A1 to either the Insert tab and recommend charts icon. I'm going to choose clustered column chart and move it below the table. We have to make some changes in the chart, starting with a horizontal axis. Right-click on it. Select data from the changed data source dialog box. Click on the Edit button from the far-right horizontal axis labels section. The axis label range right now is for the months from A2 to A31. That's why the months appear there. We need to change the range horizontal axis labels to highlight the range from D2 to y3. Press the red arrow. And then okay. Also to simplify the graph, I will delete the year 2014, the percentage change and change. Press Okay, from the quick little button, select Layer five, which adds the table with sales below to the chart. Get rid of the grid lines. I don't actually need my vertical axis, so delete it, delete the title also, the chart is ready. The symbols in my chart gives me more visual information about the data and how they behave. You could also use symbols inside the pivot chart with similar steps. If you want to change the font of the axis, the color of the columns to a nice blue color. If you want change also the gap width. The symbols and text also give you more information. And these are the types of charts that are used in dashboards very frequently. Because we were working with a table data in the chart. It's a very good idea to catch any formula error. Mike, the DIV error, the NA error or the RAF error just to name a few, the way that your error will not appear in the chart. And to do this, we should simply wrap up a formula in the IFERROR function. Let's get started with the E2 cell edit type IF error at the front. Go to the end, parenthesis comma, and two sets of double-quotes. The arrow would not show up in the chart using the IF error formula. Copy and paste the formula to the other cells. Let's edit these other cells as well, adding the IF error formula. Let me show you what will happen if I put 0 here. This formula would result in a DIV error in other circumstances. Now it simply shows nothing, only a blank cell and down in our chart just shows no result. That's the beauty of an if error function and it's a good idea to use it in your reports or dashboards to avoid Excel errors. Thanks for watching. 46. Introduction (Infographics): Most of us think of Excel shapes is mildly useful objects that can be added to a worksheet if you need to show a square, some arrows, a circle, and so forth. But if you use your imagination, you can leverage Excel shapes to create stylized interfaces that can really enhance your dashboards. Here are a few examples of how Excel shapes can spice up your dashboards and reports. Peekaboo tablets, you tag a section of your dashboard with a label that looks like it's wrapping around your dashboard components. In the example illustrated here, a peekaboo tab is used to label this group of components as well, longing to the one-hour tour. As you can see in the deconstructed worksheet, there is no real magic here. It's just the set of shapes and text boxes that are cleverly arranged to give the impression that a label is wrapping around to show the tour name, we want to draw attention to a handful of key metrics. Try wrapping your key metrics with a peekaboo banner. The banner is shown here. It goes beyond boring text labels, allowing you to create a feeling that a banner is wrapping around your numbers. Again, this effect is achieved by layering a few its cell shape so that they fall nicely on top of each other, creating a cohesive effect. Here's an idea to get the most out of your dashboard tour. You can lay your pie charts with column charts to create a unique set of views. As you can see in these four charts. Each pie chart represents the percent of a total customer for the specific tour. And a column chart showing the level of detail for the tour, like the number of customers for each year. Simply layer your pie chart on top of a circle shape and a column chart. You can see from the deconstructed worksheet. Excel offers a way to alter shapes by editing their anchor points. This opens up the possibility of creating your own infographics widget. Right-click on a shape and select Edit Points. This place is little points all around your shape. Then you can drag the points to reconfigure the shape. Constructed shapes can combine with other shapes to create interesting infographic elements that can be used in your Excel dashboards and charts. I made a newly constructed shape that I combined with a standard oval and textbox to create nifty infographic widgets. Dynamic labeling is less a function in Excel then it is a concept. Dynamic labels are labels to change to correspond to the data that you're viewing. Here's one example for this concept. The selected text box shape is linked to cell C3. Note the formula in the formula bar. As the value in cell C3 changes, the text box displays the updated value. A linked picture is a special kind of shape that displays a live picture of everything in a given range. Think of rolling picture as a camera that monitors a range of cells. Click on Link pictures worksheet to take a picture of a rage. Follow these steps. One, select a range to press Control plus C to copy the range. Three, activating another cell. For Choose From Home tab paste. Then Linked Picture. The result is a live picture of the range is selected in step one. Linked pictures gives you the freedom to test different layouts and charts sizes without the need to work around column widths, hidden rows or other such nonsense. In addition, linked pictures have access to the Picture Tools formatting options. When you click on a link picture, you can go to the Picture Tools contextual tab, and play around with a picture styles there. Here I made two linked pictures displaying the contents of the ranges on the left. As those ranges change, the linked pictures on the right will update. These can be moved, resized, and even placed on a completely different sheet. The Excel Camera tool provides an easier way to create a linked picture. Unfortunately, it's not on the Ribbon interface, so it's not that easy to find. In the next lesson, you will find all of the details for the useful Excel Camera Tool. Thanks for watching. 47. People Graph: Excel has many tools that you can use to present data understandably and effectively so you can deliver your message strongly. I have always been fascinated with infographics and story-based visuals. A pictograph is a great example of this. But apart from this, there is a chart in Excel which most of us are not aware of. That's called a people graph. If you haven't used Excel's people graph adding yet, you'll like it if you need to show a number of people or a handful of other objects for any reason, people graph instead of a column, bar or line, we have icons to present the data and it looks nice and professional. It's part of an update of Excel 2016 for both Windows and Mac. In this lesson, we'll learn how to use Excel's people graph to create an interactive, quick, easy infographic using Form Controls and VLOOKUP function. I'll use Excel 2019 to make it. Let's get started. In this worksheet, I have several employees from various age groups and accompany some of them worked part-time and the rest worked full time. We'll use this data to create a people graph. The first step is to enable this feature. Go to the Insert tab and from the admin section, click on this green button. It will insert a people graph with dummy data. Now your next step is to connect data with the chart. Click on the Data icon in the upper right corner of the graph. Change the title of the graph. Then click on the select your data button. Highlight the employees that worked part-time from B2 to B6 cells. Nice. Some customization options can do after that. There are three predefined type charts that we can use. Click on the Settings button and select a type that you want. I'll choose the third one. We can also use different themes for our charts. There are seven predefined themes that we can use. Click on the Settings button and select the type that you want. We can't use a single shape for all types of data. So that's why there are 12 different shapes from which we can Select. Click on the Settings button and select a shape that you want. Of course, when you update any value in the source data, the people graph is automatically updated. If you want to use it further in PowerPoint, send it in an e-mail or upload it to the web, et cetera. You can convert it as an image and save it. Simply click on it. Click the Copy button from the Home tab. Go to an empty sheet, for example. From the paste options, select this one, paste as a picture. Now here's the neutralizing part of our video tutorial. I want to convert this people graph into a chart that we can dynamically use data. First of all, we want to use the insert to option buttons from the Developer tab and form controls. Here's the first option button. Here is the second. After them. We want to connect each of these options buttons to a cell. In my case, I'll connect them with cell A8. How do we do that? Right-click on the first button. Format Control. Control tab. Click the upper arrow from the cell link and select a shade cell. Okay, Well let's do the same with the second button. If I check the first button, the cell changes the value to one. If I check the second, the value changes to number two. Great. The next step is to insert a VLookups formula in cell B9, which will change when I press Option button one or two. Let's start typing. Equal VLOOKUP parenthesis, the lookup value of the age group A9. The table array is from A2 to C6. The column index number is eight in an absolute reference plus one. The value of eight is one. It will appear in the second column, which is be part-time. And if the value of eight is two, it will appear in the third column, which is C full time. We want the exact match, so I'll type 0, enter. In its simplest form, the VLOOKUP function says equal VLOOKUP. What you want to look up, where you want to look for it. The column number in the range consisting to the value of return. Return an approximate or exact match indicated as one true or false. We could use the lookup function and improved version of VLOOKUP that works in any direction and returns exact matches by default, making it easier and more convenient to use in its predecessor. Copy the formula all the way down. And at this point, we have a dynamic table where we can get data by using option buttons. The last step is to connect with people graph with the new data. So highlight it. Click the Data icon, changed the title. Click on the green button. Select your data, highlight the data from A9 to be 13, and click on Create, Test it in our dynamic people graph is ready. If you want, you can do some formatting like removing the grid lines, renamed the option buttons and group them. Infographics are superb and vividly presenting data. And I believe that creating a people graph in Excel is really fun. You can also use it in your dashboards and templates to give them an awesome book for an effective way of making others understand data. I hope you enjoyed this video and I want to thank you for watching it. If you have questions or some ideas on how to create a different graph, please let me know. 48. Progress Doughnut Chart: In this lesson, we're going to learn how to create a progress doughnut chart. Here is a progress doughnut chart that displays the percentage completion towards a goal. Then we're going to apply some conditional formatting to the chart. The bar color changes as the percentage changes. Before I jump into how to create the chart, I wanted to explain why and when we would use these charts, we use these charts. We want to display the progress or the percentage completion of a single metric. So their grade for measuring goals and targets. The first step is to set up the source data. And in this case, it's very simple. It just requires two cells within them. The first set was going to be the actual amount or the amount of progress that's already been completed. This is a number that you can either type into a cell or it can be calculated based on some other results for moral report. The second signal is going to be the remainder amount to 100%. So it will be a simple formula. Equals to 100% minus, and we're going to subtract this actual value right here, and it will return a remainder amount. So both of those cells need to add up to a 100%. But what will happen if I put on B2 of value greater than 100%, for example, a 110%. The remainder amount is minus 10%, which is false, of course. Now in this case here, I will create a more complex formula that is going to return a 0 if the amount is actually over 100%. If you are calculating this number and this number could possibly go over a 100%, we still want our remainder amount to be 0. And that way the doughnut chart will display the visual properly. Type equal max parenthesis a 100% comma B2, close parenthesis minus b2. This formula here just picks the max of 100% or this actual amount if it's over 100% and then subtracts the actual amount. Once we have those two numbers in place, we're going to select both cells. Then we're going to the Insert tab on our ribbon from the Pie Chart dropdown. We're going to choose the doughnut chart. We just now need some formatting. The first thing I do is remove the legend here by clicking left key and hitting the delete key. Now let's change the colors here by left clicking on this twice to select the series. And then we go to the Format tab on the ribbon and choose a shape fill color. Let's make it a green color. Would do the same for the remainder amount here. Let's change it to a light gray. Finally, we might want to add a label here that also displays the percentage completion. I like to use a textbox for that. We're going to select the chart and then go into the Insert tab on the ribbon. And from the Illustrations group, we're going to choose shapes and they will insert a text box. Draw the textbox so we're in the middle of the doughnut. Then for this text box, we're going to link all of this cell that contains the actual values. So left-click here on the formula bar and type equal, then select the cell that contains that amount. So I have cell B2 reference there in the formula. And then I'll hit Enter. Now we have the textbox linked to the value of this cell light here. If I were to change to 50%, enter, our textblock has now changed to reflect that same value as well. These two values are linked and now we can format our text box, put the value in the middle. Then also changed the size and the color of the font and match our bar. The reason we put the text box inside of the chart is that this textbox will now move with the chart. So now we have a fairly nice looking doughnut chart. If you want to make the bar or the doughnut bigger or thicker, you can do that by right-clicking format data series. Then over here on the Format Data Series pain, we can just change the doughnut hole size to make it smaller, load 60%, and that would make the bar bigger. You have a nice progress doughnut chart. Certain things to remember is that both of these values add up to 100%. And if this goes over 100%, we want the remainder amount to be 0. The next step is to apply conditional formatting so that we want when the progress number changes, the color of the bars in the chart changes as well. Let's set up the source data here. Down here in these rows we have our data values. These are controlled with formulas for the IF function in the cell here I have this simple if statement That's just saying that if the actual value is less than or equal to 50%, which is the first level here. And we're going to return the actual value, which is cell B8. If it's not that, then we're going to return a blank. And in this case, we have a space here inside double-quotes. In cell C11122. There's the same basic thing, but the if statement gets a little more complex. We're going to use the AND function here to evaluate multiple criteria. And those criteria are if the actual value is greater than level one, which is 50%, and the actual value is less than or equal to level two, which is 75%. So basically this value is between these two values. Then we're going to return this value here, the actual value to the cell. If not, then we're going to return that blank again. Then the final if statement here is the same thing. It's just saying if the actual value is greater than the level two value, then return the same value here. If not, return a blank. And that's what it's doing right here. We can see if we change this value now it changes to 45%, will get 45% displayed right here because it's less than the level one amount. If we want to change it to a value between those two, for example, 70%, we could see 70% is displayed here because it's between these two levels, those amounts there. And the final thing we're going to have is the remainder amount. This remainder here is going again to be that 100%, which is 100% minus our actual value. And I have that max function there, again named k's are actual value goes over 100%. Once we have all that setup work done, we can create our chart, put 50% in the actual amount. I'm going to select the cell and its data series. And then the first value in the column. From the Insert tab on the ribbon, we're going to insert a doughnut chart from the Pie Menu. Let me just resize it. Right now it's just displaying 50% because it only has one value. So we want to include all of the values we have in the list here. Now, here is a pro tip. We can easily include all the values just by hovering the mouse over the bottom-right corner of this cell until it turns those arrows. And then left-click and drag it down to include all of those cells. So that's added all four of these values to the data series in the doughnut chart. We can see here in the legend. So any blank cells will not be displayed on a doughnut chart. Now, really, the next big step is just applying our colors. So we can see here that level one to 50%, it's currently this blue color. We might have to left-click, left-click twice, go to the Format tab. Then we'll change this fill to an orange color. The next step is to change our actual value here to something in level two, for example, 70%. So I'll just change it to 70%. And we'll see that also colored orange right now, I'm going to double left-click again, go to the format where you might want to change this and will do so to change it to blue. Then we'll do the same thing, change this to 90%, so we're a level three. And level three is currently gray. So double left-click. We'll change this one, will make it green. We also want to change our remainder value. So double left-click on that one. It'll change to a light gray. All of our setup work is now done. Again, if we change this back to, let's say 45% bar turns orange. 70% for level two, it turns blue. And then as we saw, anything over 90%, and it will turn green. So the last step is to just clean up the formatting of the chart and also apply the data label. I'm going to remove the legend, just left-click and delete it. Delete also the title. Now we want to add those data labels again to the center of the chart using that text box. So again, make sure the chart is selected first and then go into the Insert tab on the ribbon. We're going to choose the text box shape and we'll just draw it right in here. Then we're going to link this, go to the formula bar type equals. And in this case we wanted to equal the first-level here, the label for the first level where that cell can be displayed so that it's equal to cell C21. We're not currently seeing anything there right now because that's a blank cell. If I change the 40%, will now see that 40% is in this text box right here. Let's just quickly change the color of it like that. We have some color in that cell. Will just repeat that process. And you can do that by either inserting another textbox or we can duplicate this text box. The keyboard shortcut for that is control plus D. The first we do the shape formatting before duplicating the shapes to save time. So hold down the Control key and press D and that will duplicate the shape. We have a duplicate copy of that shape. And now all we want is to change the link instead of C21, that it's going to be C2V2. Then we can change our value here, change it to 70%, and you'll see our text box on our label. For the 70%. Again, we might want to change the color there and we'll do the same thing. Just Control D to duplicate that shape as well. We'll change that one to C23. Then we'll go here to change our actual value. Now we have our label here for level three. The last step is to make the bar or the doughnut bigger or thicker. You could do that by right-clicking format data series. And then over here in the Format Data Series pain, we can just change the doughnut hole size to make it smaller, about 60%. And that would make the barb dagger. To convert it to a beautiful 3D doughnut chart. You can do it from Format, Shape and then affects. Great. We can obviously duplicate these and create multiple doughnut or progress donut charts and then display those on a dashboard. 49. Tube Chart: In this video tutorial, we're going to design this beautiful tube chart. Let's explain the first day that we need and how the chart works. I have three different YouTube metrics, impressions, watch time and click-through rate. Here are the percentage values. And here are the three individual tubes represented data for each of these metrics. Let's get started to create this beautiful informatics chart. First of all, I will need some supporting data for my graph. All I need is the remaining value of percentage value. It's very simple on d2l and type equal 100% minus C2 cell. Enter. Drag this down. These are remaining values. Highlight the data for impressions from B2 to D2. From Insert tab, select the stacked column chart. Now right-click on it and select Data. Click the button, switch row to column, and okay, it changed to one column, right-click on the vertical axis and select Format Axis. From Axis Options, I'm going to change the bounds. The minimum should be 0 and the maximum should be 100% or one. It's the same. Clothes, the Format Axis pain. I don't want the grid lines. I'm going to remove them. I don't want the vertical axis. I'm going to delete it. Let's reduce the size of the graph and move it somewhere here. The blue color is a percentage of the impressions and the orange colors, the remaining percentage. Click on the orange bar. Right-click on it. Click this option, fill bucket, select no fill. Now I need to remove the background of my chart area. Right-click on it, fill and select no fill. Click on Outline and select no outline. My bar graph is ready. I need two more bar graphs, one for watch time and one for click-through rate. For that, I'm going to select this bar, press Control plus C for copy and pasted here with control plus phi. I will press control plus V one more time. Now, this is my graph or impressions. This is for watch time and this is for click-through rate. Let's select the second chart. I need to adjust the data for each bar graph. I quick tip here to change the data is to drag the data from here to here. We'll do the same trick for the trip chart. Drag the data from impressions, CTR. Now it's time for the customization of these two graphs, like the first graph. Select this portion, right-click, no fill and Noel line. Let's do the same with the portion of the third graph. Nice. The graphs have the same color, blue, so I'm going to change the colors of the graphs. We'll start with the first one. Go to Format tab. From the shape styles. I'm going to select the green color for this second graph. I'm going with this blue effect. For the third, I'm going to select yellow. The next step is to give a tube shaped or graphs. For that, I will use PowerPoint. Open a blank PowerPoint presentation. The lead, the titles. I want a blank slide. From the Insert tab. Click on the shapes, drop-down button. Click on rectangle. Let's draw a nice rectangle here. Now I will insert a second shape from flowchart category. Let's select the store data shape. Draw it somewhere there. I will rotate it left 90 degrees and I will place it over rectangle. Somewhere in the middle. Press the Shift key to select both shapes. From Shape Format tab. I will merge them by clicking and combined shapes. Select the shape again, and select a white color for it. The image is ready. The last step is to change the color from blue to green because I want to use it for the first bar graph. From Shape, Outline, selected green color. Copy the shape with control plus C and paste it inside the Excel sheet is a picture from paste options. I need to crop it a bit. So from picture format, click the Crop button. We're done with the cropping. We need to change a percentage of all the metrics to 100%. It's an important step to align the shapes of the graphs properly. Dry the image and align it in such a way that when the value is 100%, the entire tube is full. Change the value to 40%. For example. You can see the tube is almost empty. We will follow exactly the same steps for the remaining graphs. Return to PowerPoint and change the shape outline to blue. Copy the shape and paste it as an image inside Excel. Crop the shape again. Drag the image and align it in such a way that when the value is 100%, the entire tube is full. The same now for the third graph. Now how cool is that? If you do not want the supporting data to be displayed, you can change the font color to white. The next step is to add the labels to our tubes. Click the Insert tab from shapes, select the oval, drawn it here and adjusted accordingly. Copy the shape twice. Let's change the colors of the shapes. If you want, you can insert a shadow effect the oval shapes. I will use the shapes to add labels to them. This is pretty easy. Simply click on the first shape, go to the formula bar, type equal to, and click on the impressions value. Select the textbox and from Home tab, center and middle line the text. Change the font to pop into black. The color to white. Do the same with the other oval shapes. Let's remove the legends and adjust the shapes. The final step is to give titles to the tubes from insert tab WordArt. Select this time only move it here. I will link the title with the first metric. Click on title, go to Formula bar equal and click on impressions. Rotate it and place it beside the first tube chart. Copy the title and paste it two times. Then change the length cell. Finally, group the tube charts. I hope you enjoyed this infographics lesson. I want to thank you for watching. Bye. 50. 3D Donut Chart: We will learn how to create a 3D doughnut chart in this lesson. Unfortunately, Excel doesn't have a future to create a 3D doughnut chart. If you go to Charts from the Insert tab, you will see an option to create a 3D pie chart. So let's dive in. In his worksheet. I have a small table with a monthly percentage of three methods for YouTube channel, impressions, watch time and click-through rate or CTR if you prefer. For each of these metrics, different 3D donut charts represent them. I will create a doughnut chart for the first metric, which is impressions. Then I will copy and paste the doughnut chart and modify it to represent the other metric and so on. The first step is to prepare our data. I want some supporting data which is the remaining percentage from 100%. This is simple subtraction. Go to d2l and type equal 100%, which is the total percentage minus the cell C2. And this is the remainder. Drag and drop it down to calculate the remainder of the other two metrics. The next step is to design a 3D doughnut chart from Excel shapes. Click on the answer tap and from the Shapes button. Basic shapes. Select the hollow circle. I want to draw a perfect symmetrical circle. I will press the Shift key and then draw the shape. Now click on the yellow point over there and decrease the size a little bit. It's time to change the color of the doughnut. Right-click on it and select the Format Shape. From the Format Shape pane. Click on the bucket icon. From Phil options, select the gradient fill. There are four Gradients stops. Delete these two in the middle and leave the extreme points. Click on the first stump and select this light gray color. Click on the second and select this dark gray color. You can also play with the type, direction, and angle of these options of the gradient. Now, I will click on the Effect button. I'm interested in the 3D format. From the top level, select the round. From the bottom bevel, select also the round. Increase the size of the depth, the ten points. The contours select the white color. From the material, select the middle. Wanted to format the doughnut in such a way that the percentage changes, the filling of the shape also changes. To achieve this, I will use to supporting data. Highlight the cells from B2 to D2 from the Insert tab and charts section, click the two-dimensional pie chart. Let's remove the legend and the chart title. I don't also want the background of the chart. So right-click on the chart and from the Fill Options button, select no fill with the same steps. Remove the outline of the chart. Great. Now we will move the graph so that it covers the 3D doughnut shape like this. Select the blue section of the pie chart, right-click and select no fill. This orange section of the remainder of the impressions metric. Right-click on it and select the format datapoint option. Click on the fill bucket and select the white color. Change also the percentage of the transparency to twenty-five percent. Close the format datapoint pain. If I change the value of the impression to 60%, the chart will adjust accordingly. The final step is to add a data label and the rule of the hole of the doughnut chart. This data label will show the percentage of the impressions metric. For that. I need a text box from the Insert tab. Draw it somewhere there. I need to learn the text box with this value. So select the textbox, go to the formula bar type equal, and then click on cell C2. Right-click on the text box, click on Fill and select no fill. Click on the outline and select no outline. Drag the text and center it over there. Format the text with Shape Format and quick styles. Select the style you prefer. You can format it as you wish. I usually like to add the title of the chart from Word Art. The group, the text with the chart. Let's test to know once more. Now how cool is that? As an exercise, I want to create two more 3D donut charts for the remaining metrics. I want to thank you for watching it and I will see you in the next video tutorial. 51. Dynamic Overlay Custom Image Chart: In this lesson, I'll show you how to make a really unique dynamic overlay custom image chart, beautiful and creative. Here is the data. The first value is the Tesla stock price on the first of January 2022. The second value is the analysts stock price prediction of the first of January 2023. The third value is the percentage difference between these two values. The 100% is supporting data. We need to create a column chart that reflects this percentage and then copy it as an image or as a picture and replace it underneath the thermometer here and underneath the image here. So let's get started. First, we need to create a bar chart. Select these two percentage amounts. From the answer tab, go to column charts and select the first column chart. Move it somewhere there. Now right-click on it to change chart type and use this one with the different colors. The next step is to change the series overlap percentage. So one column to cover the other. Right-click on the orange column and select Format Data Series. From this series option button changed the series overlap to 100%. Close the pain. The total percentage overlap the difference percentage. Now right-click again on the column, Select Data and from this series data source dialog box, move down the percentage difference to change the overlay. Click. Okay. Let's change the color of the percentage total. Right-click Format Data Series from fill in line, solid color. Let's change it to light-blue. Also change this one to a dark blue. The dark blue color as a border. Now if we change, the stock price is 700. This will change to 61%. Undo. Notice the y-axis, the maximum bound is 120%. I need to fix this. Right-click on the vertical axis, format axis, and change the maximum bound to one. Close the format axis pain. The lead, the title of the chart and the grid lines. Delete also the legit. Delete the vertical axis. Also remove the fill and the outline of the chart. Finally reduced the gap width from the Format Data Series. Said the percentage to 0. I don't want any gap at all. Now I want you to take a copy of this chart and pasted here exact same duplication. What I really want to do here is to tune the color of the bar itself, the bottom of the chart itself. So let's make this, for example, light green and dark green. The screen in the borderline to green as well. It's time to answer it. Our thermometer and thumbs up images. You can use anything you choose. Just keep in mind that no matter what type of graphic you're using, make sure that it has some sort of solid opaque background and some transparent area where you want the chart does shine through from behind. Move the chart behind the thermometer image. It's going to be important to drag it over into the line, the 0, 100% marks with the thermometer icon. I'll do it manually. Grade. Make sure to group these two objects, the chart and the thermometer. The last thing I need to do is to just fill in this little bulb here at the bottom, which is actually below 0%. From the Insert tab shapes select the oval. Change the color in the line, too dark blue. Group again, these two shapes. Let's test it. Changed the stock price and see the thermometer. Now how cool is that? I'll quickly follow the same steps for the thumbs up image. There you go. Thank you very much for watching and I'll see you in the next video tutorial. 52. Waffle Charts for KPIs (Better than Pie Charts): After you have a few tricks under your belt, you can have a bit of fun and use conditional formatting and a camera tool together to make a waffle chart. A waffle chart is an interesting visualization that helps display progress towards a goal. A waffle chart is basically a square divided into a ten by ten grid. Each grid box represents 1% towards a goal of 100%. The number of grid boxes that are colored are shaded is determined by the associated metric. Is kind of chart is relatively effective as an option when you want to add an interesting visualization to the dashboard without distorting the data or taking up too much dashboard real estate waffle charts are relatively easy to build using a little conditional formatting know-how. To create your first waffle chart on a new worksheet, you have to dedicate the first of a cell of your actual metric. This is the a2 cell in my worksheet. Then create a ten by ten grid of percentages that range from 1% to 100%, starting from the lower 1% at the bottom left and ending to 100% at the top right corner of the grid. This is the initial setup that you'll need for the waffle chart. Now highlight the ten by ten grid. From the Home tab. The conditional formatting sub menu, click the new rule option. We want to create a rule that all colors in each cell in the ten by ten grid, if the cell value is less than or equal to the value shown in the metric cell A2 in this example, has a rule type selects the second format only cells that contain. Let's edit the rule descriptions. We want to format only cells with cell values less than or equal to a2 cells. From the Format button. Choose a fill color. Click on the Font tab, and choose the same color for the font. Be sure to apply the same color format to both the fill and the font. This ensures that the percentage values in the ten by ten grid are hidden. Click the Okay button to confirm the conditional format. Now makes sure the grid has a clean background color when the boxes are not lit up by your Conditional Formatting. Highlight all cells in the ten by ten grid and apply a default gray color to the cells and font. Also apply a white border to all cells. When you change the metric or target percentages, the grid should automatically adjust colors to reflect the data change. It's time to use the camera tool to shape and position your waffle chart. Let's create a new blank worksheet. It will be the location of all the waffle charts. Return to the sheet. One, highlight the lawful account, and then select the camera tool icon on the quick access toolbar. Remember that you added the camera tool to the Quick Access Toolbar in the Camera Tool lesson. Click the worksheet in the location where you want to place the picture. Excel immediately create the length picture that can be resize and position where you need it. To add a little title to the waffle bonds. Click on the Insert tab in the ribbon and then click the worksheet to create an empty text box. I'll click right above the first waffle chart. While the textblock is selected, place your cursor in the format bar. Type the equal sign. Then click the cell that contains the metric cell. This is cell A1 from Sheet one. Let's change the color of the font to a red one and increase the font size. I'll move it somewhere there at the center of the chart. I'll remove the grid lines also. I don't need the border of the linked picture, so delete it from the picture format. Picture border. No outline. Great. This is the title of our first waffle chart. With similar steps, I can add a label to the waffle box that shows the percentage of this metric. Insert tab textbox. Place your cursor in the format bar, type the equal sign, and then click a2 cell from Sheet1, which contains the percentage. Format the text box and move it somewhere there. You can repeat these stamps to create separate waffle drugs or each of your metrics. For example, if I had two more metrics, metric to metric three, I would follow similar steps. Let's copy and paste the waterfall chart two times. The same with metrics. The crucial step here is to edit the conditional formatting rule from the managed rule and connect each chart with the right metric cell. Change it the fill and font colors also. Now it's easy to use the camera tool to create two more waffle charts link with the original ones. After you have created each waffle drug, you can line them up to create an attractive graphic. It helps your audience visualize performance against the goals for each metric. I hope that you enjoyed this lesson on how to create multiple waffle charts for a nice at-a-glance view for your audience. Thanks for watching. 53. (NEW 2024) Use Excel Add-ins to enhance data visualization: Today, we'll learn how to use Excel add ins to enhance data visualization and engage the audience. These content add ins are part of Microsoft Excel and can enhance what you're already looking at. So let's go ahead and take a look at that. I have already opened this Excel file called data. Here I am in the data worksheet. The first thing I want to do is to make sure that your cursor is anywhere inside the dataset. Go up to Insert tab and we're going to insert a new pivot table on the new tab. Now, once we've created a new tab here, I'm going to go and call this add ins. This is just to keep organized, and I recommend that you do the same. So I'm going to grab mine, drag it all the way over to the right just to make sure I know that it's the most recent step I'm taking. So one thing that we haven't really looked at is the performance of each salesperson and trying to understand how they contribute to each activity. So what I'm going to do is I'm going to grab the salesperson ID here. I'm going to drop it into the rows. And then I'm going to grab Order ID here and drop it into the values. You can see right now it says sum of Order ID. This is not right. Order ID is a unique identifier. So instead, we want to go to the value field settings, which you can get to by clicking on the down arrow right here. We're going to click Value Field Settings, and then I'm going to select count. And when I'm ready, I'll go ahead and click Okay. Now the next thing I want to do is understand what is the total due related to those orders. So I can grab the total due from over here and drop that into the values column like that. That's going to give us the sum. And the next thing that I really want to do is because this is really just a list of numbers, it's always a good idea to sort one of these columns just so you can understand the distribution of the data. So in cell B four, I'm going to right click. I'll go to SRT over here and we're going to do a largest to smallest sort. Now, let's visualize this data with a Microsoft Excel add in. There are one of two places that you can find AddIs and this really all depends on the version of Excel that you have. So on my version, I can go to the Home tab, drag your mouse all the way over to the right and you will see the Addins button over here. Now, on your version, if you don't see any Add ins button on the Home tab, what you're going to need to do is to click on the Insert tab and you will see an addIs button somewhere in the middle here. So it's just going to be in one of those two places. So on mine, I'll click Get AddIs and then the add in that I want to insert is called People graph. So I'll type in People here and I'll hit Enter. And you can see People graph comes up. I'm going to click on this green Add button. And you can see that this content add in has now been added. So this add in starts with some sample data. And to change that data, what we'll want to do is click anywhere into the add in, and sometimes you need to click it again. But I'm going to click up here kind of in this white space, and you can see that it was very quick. But you can see there's a data button here. So I'll go ahead and click that. And that's going to allow me to change the data. So I'll click Select data here. And it says, Please select two columns in your Excel spreadsheet. It gives you an example right here. And what I'm going to do is I'm going to select A four and take it all the way down to B 53, and I'll hit Create. Now, this is giving us some data for all of our activities. So I'm going to grab status here and drop it into filters. And if I'm going to click Down arrow here, we can see that it's showing us everything. What I'm going to do instead is click Select multiple items. And what I am most interested in right now are the due paid and past due because that reflects money that could still come in. On the other hand, canceled and returned reflect transactions that are unlikely to be collected upon. So I'm going to remove those having selected select multiple items, and then I'm going to click Okay. And now I understand that salesperson 121, the person with that ID, has sold the most amount of items that are collectible. However, they might not have sold the most amount of total due, which is to say, from $1 standpoint, they might not have the most. Now, before we go any further, let's see if we can go ahead and change the name of this title. So I'm going to click over here on this dataset button again, and it says numbers about the app. I'm going to write collectible activities because this refers to transactions we can still collect on. And then I'll just click this button here. So let's go and take another look at data visualization to see if that provides a little bit more insight. Go ahead on your machine and click on that Get Addins button again. In this one, we're going to look up called bubbles. So I type in bubbles. Here, I hit Enter. You can see it shows up immediately. Let's click Add and then Okay. All right. I'm going to move some of this around a little bit. To get started, what you'll want to do is to move your mouse over to the select table bubble. You'll click on it. And now it's going to ask you for the data to visualize. In this case, we're going to look at everything all at once. So we'll select A four through C 53, like that. Go ahead and click Okay, and now we see a visualization that uses a mix of color and size. So on the one hand, the size is dictated by the count of order ID, whereas the color of each bubble is dictated by the total do. So as we look at this, we actually see that 144 is probably one of the bigger sizes, and it's more red as compared to a few others. Now, the Bubbles chart is very interesting and it's very fun. But as I said, in going through this, not every data visualization you'll look at is actually going to make the cut to the end. I present this to you because I think there's a lot of different ways to look at your data, and I think this one's very fun. Thank you for watching all the way to the end, and I'm going to catch you in the next video. 54. Introduction (Advanced Charts): In this next section, we're going to dive into some of the advanced next level data visualizations and graphs. But before we do, let's take one minute to set some expectations. Number one, advanced charts and dative is often require the use of advanced formulas and functions. I know this isn't a formulas and functions course. You came here to learn graphs. But the fact of the matter is formulas and functions are critical components to enable some really, really cool functionality with advanced charts. During the following lessons, I'll talk about the most important formulas that we'll face when you're going to design our graphs. I'll give you a really good explanation of the formulas, but by no means will be a comprehensive review of all of the formulas and functions of Excel. Some of these lessons are pretty easy. You might find some really hard. Some might make you want to curl, has been crying. Now. That's okay. All I ask is that you proceed with an open-mind and trust me, it's a really, really comprehensive review of basically all of the formulas and functions that you might encounter in this chart, its course. With that, let's go ahead and get started. 55. Dynamic Line Chart with MAX and MIN Dots: In this lecture, I'm going to show you how to make a beautiful dynamic line chart. The main difference between a simple line chart and this one is that I want to make the highest value marked in green and the lowest value marked in red. As you can see here, the maximum value, which is around 150 thousand, is the salary of a business manager. If I change this, reduced this to 70 thousand, the maximum value will now be the salary of the MIS analyst. And the circle moves dynamically as well. So let's dive in. The first step is to highlight the data from B2 to C9. From the Insert tab, select a simple line chart. When you insert a line chart in Excel, generally the line chart is with angles that may be not beautiful and smooth enough. I prefer to smooth the angles of a line chart. In our case, right-click anywhere on the line and select Format Data Series. From the Format Data Series pane, click on the bucket icon, fill in line. At the bottom you'll see an option called smooth line. Check the box and you can see the line chart becomes smooth. Now we want a creative way to add another type of chart that shows the maximum and minimum points in the line. And that would be a scatter chart. In this case, I will need to do some supporting data that calculates the minimum and maximum salary for this column. So click on d2l and type max. Click on E2 cell and type Min. Format the cells accordingly. The calculate the max and Min values. I will use the IF function, click on d3 and start typing equal IF C3 cell, which is the salary of the first profession, equals the maximum value of this range. Return the salary of the first profession. Otherwise, return 0. Enter. It returns 0. So the sales manager is not the maximum salary. Drag the result in, drop it to copy the formula of the other cells. Something is wrong. Do you know what is wrong with this formula? If you've figured it out, it means you're focused on your learning. So one I would mistake here is that we did not fix this range when we created this formula, which is correct by the way. And we drag it down, all the selections were dragged down to this formula is comparing the value with this strange, it totally ignored the first value. Here is doing the same. Totally ignore the first two values. Same here. Same here. What we need to do is fix this amount by highlighting them in press F4 and then dragging them down. This is called an absolute reference. That way we lock the cells, then drag them down. This will give us the correct value, right? Now I will copy this formula from cell D3 and pasted the cell E3, change the maximum to minimum and d3, c3, drag it down. And this is the minimum salary, which is correct, of course. The next step is to plot these two values right here in the chart. Right-click on the chart and select the Data option. Click the Add button from the legend injuries. As a series name, click on the max header. Series values highlight the range from D3 to D9. Click Okay. Now we have this line. Let's do the same steps for the minimum value. Click Add button as series name, select the Min label. A series values highlight the range from E3 to E9. Click Okay. Now look at the horizontal category axis labels. Instead of the names of the professions, we see numbers, we have to fix these. Click Edit and select a professions from B3, B9. Okay, again, now we have all the lines ready. Now, as we said before, we want to change the chart type for the maximum and minimum scatter chart. Right-click on the chart. And chart Change Type. Go to combo charts and then change the salary to a line chart. Also change both the max and Min to a scatter chart. We don't want to secondary axis, we want to use the same axis. Right? Click OK. Now we have our dots ready. This is the maximum and this is the minimum. Let's test it. Changed the salary for the accountant of 50 thousand. Now at the minimum is moving. But we have a problem. The zeros are drawn here, and I don't want that to be shown in our table. We have to think out of the box here. We know that the zeros are numbers, obviously. The charts or drawing them. Excel is plotting them in this chart. What kind of values excel doesn't plot them in a chart? The answer is the NA or not applicable or no value available. Let's edit the formula and replace 0 with an a. The same for Min values. Now everything's disappeared. The last step here is to add the symbol that we want. First go to the Insert tab and from shapes, select the oval. With a shift press draw a perfect circle somewhere here. Copy the circle. Change the color of the first one degree. Removed the outline. Change also this one to read and remove the outline. Select again the green circle and press control C to copy it. Click on the maximum dot on the scatter chart and press control plus V. Select now the red circle and press control C to copy it, click on the minimum donor of the scatter chart and press Control V. Delete those shapes. Let's smoother line, right-click Format Data Series. Fill in line, and check the smooth line box. Remove the grid lines and the outline of the chart. Done. I want to thank you for watching this video tutorial. 56. Speedometer-Gauge Chart: When it comes to understandably presenting data in Excel, charts stand out there. Few charts are specific and can be used to present a specific kind of data. Speedometer gauge is one of those charts. In Excel. Speedometer chart is just like a speedometer with a needle that tells you a number by pointing it out on the gauge and that needle moves when there is a change in the data. It is a single-point chart that helps you track a single data point against its target. Here are the steps to create a speedometer gauge in Excel, which you need to follow. As I said, we need to insert two donut charts in a pie chart. But before you start to create a speedometer, you need to arrange data for it. In the below worksheet, we have three different data tables too for donut charts and one for a pie chart. The first DataTable is to create a category range for the final speedometer, which will help you understand the performance level. The second data tables for creating labels ranging from 0 to 100. You can change it if you want to have a different range. In the third data table, we have three values that we will use to create a pie chart for the needle. The pointer value is the real value that you want to track. The rest value is the sum of these two totals minus the pointer value and the thickness of the pointer. First go to the Excel ribbon, click on Insert, and go to the charts election, and under the drop-down, click on the doughnut chart. With this, you'll get a blank chart. Now, right-click on the chart and then click on Select Data. The Select Data Source Window from the legend entries, click on the Add button. Enter category in the name of the input bar. After that, select the value column from the first data table. That is cell B3 to B7. Click OK, OK again. Once this is done, you'll get the following doughnut chart. But you'll notice that this is not a semicircle and it's facing the wrong way. The next thing is to change the angle of the chart. For this right-click on the chart and then click on Format Data Series. In Format Data Series pain enter 270 degrees and the angle of the first slice and hit Enter. This rotates the chart the right way. Next, we'll hide the data series in the lower half by removing its color, adding data labels and formatting the chart appropriately. So you need to hide below half of the chart. For this, click on only the part of the chart. Right-click on the bottom half of the chart and select format data point from the bucket icon, select no fill. Next, change the colors of the other data series if you feel it's necessary. For the rest of the 4s data points, I've used four different colors, red, yellow, blue, and green. Once you're done, you should have something that looks like this. Now let's add data labels. Right-click on the chart and select Format Data Labels. Select values from cell. Select the labels in the first table. We're finally done with the first chart. Now for the second doughnut chart, right-click on the chart and choose Select Data. Click on Add button. Series name, type, Legend Entries, series values, select values column from the second DataTable. Press OK. Ok again, once you do that, you will repeat the same steps as the first chart we created. Again, you need to hide below half of the chart using no fill for color. Then the format in the chart colors you feel are most appropriate. Now add the labels. Once you're done, your church would look like the one below. It looks like the labels at the top all show the number ten. This is wrong. You need to change the labels to display the labels from the amount labels. Second table, right-click on any of the numbers. Check the box value from cells. As Range, select the cells from d3. D3. Also uncheck the value box. We're in the homestretch. Just one more thing needs to get done. And that is building the pointer and linking it to a datapoint so that it moves dynamically along with the tables data. Right-click the chart again and choose Select Data. Click on the ad just like this one before. A series name type pointer. Select the values from the last table, that is cell H3 to H5. Once you click OK Twice, your chart will look like this. That looks nothing like a pointer. We'll fix it though. Excel default to a doughnut chart. We must change it to a pie chart. While you click on the chart and select Change series chart type. Now change the drop-down on the third chart to pi. The angle is not correct. There is a chance make sure to change it to 270. The chart has three sections, the gray area, the blue area, and the orange sliver. You'll need to remove the color from sections 12 of the pie. Select both large data parts of the chart and applying no fill color to hide them. After this, you'll only have the small part left in the pie chart, which will be our needle for the speedometer. Next, you need to make this Neil bid out of the chart so that it can be identified easily. For this, select the needle and right-click on it. Then click on format datapoint. In format datapoint go to Series Options and then add 5% in point explosion. At this point, you have already to use speedometer. All done or ROA. There was one more thing I like to do when I create a speedometer chart. I add a text box that automatically updates with the numerical value represented by the speedometer. This minor addition makes the chart much more intuitive and easier to read. Go to the Insert tab in the ribbon and select text box. Draw the text box in the center of the chart like this. Select the textbox and in the formula bar, enter an equal symbol. Now reference the cell with the pointer data in my sheet, it's cell H2. Next format, the text box to whoever you feel is appropriate. When the data in the tables gets updated, the text blocks is value will as well delete the legend of the chart. In the end, you will need to move all data labels to end corners. Your first gauge predominant chart is ready to rock. Thanks for watching. 57. Bullet Graph: A bullet graph is a type of calm slash bar graph developed by visualization experts Stephen Few to serve as a replacement for dashboard gauges in meters. He developed both graphs to allow you to display multiple layers of information without occupying a lot of space on a dashboard. A bullet graph contains a single performance measure, such as year-to-date revenue, compares that measure with a target and displays it in the context of qualitative ranges such as poor, fair, good, and very good. Look at this bullet graph that we're going to create. I've broken down the three main parts of a bullet graph. The single bar represents the performance measure, the horizontal marker represents the comparative measure. The background color branding represents the qualitative ranges. Creating a bullet graph in Excel involves quite a few steps, but the process isn't necessarily difficult. We will start with a data table that gives you all the data points that you need to create the three main parts of a bullet graph. In this worksheet, we have a table with product items that have been sold. We can see the value, the target revenue, and the range with four metrics, poor, fair, good, and excellent. The first four values in the dataset, poor, fair, good, and excellent make-up the qualitative range. You don't have four values. You can have as many or as few as you need. In this scenario, you want the quantitative range to span from 0 to 100%. Therefore, the percentages must add up to 100%. Again, this can be adjusted to suit your needs. The fifth value, value creates a performance barn. The sixth value, target makes the target marker. Let's get started. Highlight your data from B to E to F eight and go to the Insert tab. We want to insert a stacked column chart. The chart that's created is initially plotted in the wrong direction. To fix the direction, click the chart and from the chart design tab. Select the switch row, column button on the ribbon. The next step is to change the color for each qualitative range to incrementally later to use. Let's start with the poor range. It's blue color, Right-click on it and choose Format Data Series. The dialog box appears and we're looking for the fill. Click the bucket and select Solid fill. We're going to select the dark red color. I'll do exactly the same steps through the other three ranges. Read for fair, orange for good, light green for excellent range. Great. The ranges are ready. Now we need to move the value range for the secondary axis when the Series Options select a secondary axis. Because it's covering up all the other columns. We need to have to change the gap width. Let's move the width to 500%. Now we can see the other columns. The next step is to change the target revenue. Right-click on it and change the series chart type. From combo charts, we want to change the target to a line chart with markers. Click Okay. We want to isolate the markers and delete the lines. So right-click on the line and from the Format Data Series, click on fill in line options, choose no line. Now from the marker options, we will change the type and size from feels like solid fill and change to the purple color. Also select no line from the border options. For Marker Options, choose built-in and change the type of marker to a line. And then the size to 20. Close the dialog box. We don't want the secondary axis, so highlight it and press the Delete key. To make my bullet graph simpler, I will delete the title and the legend. If you like, you can change the chart style. From the Format tab. You can apply a visual effect to the charts such as bevel. Finally, let's test the chart changing values from the data table. All works fine. At this point, your bullet graph is essentially done. You can apply whatever minor formatting adjustment for the size and shape of the chart to make it look the way you want. Thanks for watching. 58. TOP and BOTTOM Ranking Chart: When you look at the list of Fortune 500 companies, you will often look for the top 20 companies. Then perhaps you look at who eat out a spawn in the bottom 20 slots. It's unlikely that you would check to see which company came in at number 350. It's not necessarily because you don't care about the number 350. It's just that you can't spend the time or energy to process all 500 companies sell you process the top and the bottom of the list. This concept is the same one behind creating top and bottom displays. Your audience only has a certain amount of time and resources to dedicate to solving any issues you can emphasize on your dashboard. Showing them top and bottom values in your data can help them pinpoint where and how they have the most impact with the time and resources they possess. These table has two columns, the first with the team names and the second column with the winds of each team. First, we will create two more columns wanted to calculate the top five teams and winds. And one to calculate not the top five teams. We will need two functions to complete these calculations of these two columns, the large and if function after that, we will create a ranking chart which depends on these columns. So let's dive in. I assume that you've studied the lesson of how to use the large and small functions that I have in the dashboard function toolbox section. If not, we'll do a quick repetition. Highlight cell AAA teen, and start typing the large function here, the syntax is large and then the array, and then k. The k indicates the first largest if it's number one, the second largest if it's number two and so on. Comma and one, because I want the largest value, Enter and the result is 37. Indeed, if you look closely at the column with winds, 37 is the largest number. Now for the second largest, all type equal large cells B2 to B6. Comma to press Enter. Let's take the third largest. The result is 27. Now the fourth largest the result is 26. Look at that. If you check the table, we have three teams with 26 winds, so they're all in the top five. It's not in the scope of this video tutorial to learn how do we ignore duplicates and find the unique fifth value. So I've left it as is for right now. The smaller function works in almost the same way. Let's do an example in cell B8. Click on this cell and start typing equal small cells B2 to B6 comma one. Notice the similar syntax of large and small functions. Press Enter and the result is nine. Now to find the top five teams that won the most games will combine the if and large functions. Click on cell C2 and we'll start with the first team, the Atlanta Hawks with nine winds will compare the fifth largest number with a B12 cell. And if this cell is smaller than, we'll type the result with an empty cell. Otherwise the result will be the value of that cell, which is nine. Let's start typing. Equal IF open parenthesis cell B2 smaller than a large parenthesis, B2 to B6, comma five, closed parenthesis and comma double-quotes twice. Comma B12. I forgot to want it the cells B2 to B6 and enter, drag it and drop it down. You can see that there are six teams in total that have the most winds. Similarly, we'll type the formula to find the top five teams. Click on cell D2 will start again with the first team, the Atlanta Hawks, with nine wins. We will compare the fifth largest number would be to sell. And if this cell is smaller than the result will be the value of that cell. Otherwise the result will be an empty cell. So let's start typing. Enter, drag and drop it down. You can see that these teams in total have the least winds. To incorporate top and bottom displays into dashboards. You'll need the right chart to create the chart. I like the first two columns in data from A1 to B1 16. And then from the answer tab, click the column charts button and select this two-dimensional bar chart. Move the chart over here beside the data and resize it. So far I have a simple bar chart with all the teams and the vertical axis and the number of winds on the horizontal axis. Right-click the chart, select data. And I'm going to add the top five column as a legend entry. Click the Add button, select the series name, which is C1. Then from the series values, we need to delete that. Now let's select data from C2 to C6. Okay? Click the Add button again, the series name is d1. Then from series values, leave only the equals sign. Select the data from D2 to D16. And press Okay. I don't need the winds columns anymore because it's duplicating the data. So remove it by clicking this button. Press. Okay. What do we see in our graph now? We see the orange bars that represent the teams with the most wins. Also we see with light gray the bars that with the least winds. Can you identify the problem with the graph? Yes, that's right. The problem is the vertical axis. No team names, only a bunch of numbers. The next step is to fix the vertical axis, right-click on it and select data. From this Select Data Source dialog box. Click the Edit button. The range is A2, A6. Click Okay. The names are upside down. So to fix that right-click Format Axis and check the box categories in reverse order. Now I'm going to fix the gap width of the bars. Right-click any of the orange bars Format Data Series change the gap width from 21%, 100%. The series overlap. Let's change the color of the top five bars to green from the bucket icon. To add a legend to the bottom, go to the Design tab, and click on the Chart Element button. And from the legend of sub menu, choose bottom. Delete the not top five legends also delete the major grid lines. Last step is to add labeled inside the top five bars from the Design tab and Chart Element, Data Labels and outside end. We don't want the zeros here. So the trick is to format those numbers in a particular font-style. Select them, right-click Format Data Labels. And then in the numbers section, putting the following custom format code. Hush, hush, hush, 0. Semicolon twice, add an zeros are gone. The same for the zeros here. You could delete the labels from the non top five bars simply by unchecking the value box, close the pain. I will do some formatting at the labels like this. Top and bottom ranking chart are finally ready. Thanks for watching. 59. Dynamic Chart with Drop Down Lists and Filters: Hi everyone. In this lesson we're going to learn about dynamic and interactive charts. Dynamic or interactive charts are a special type of chart where chart is presented, but the user can also interact with the chart. There was no clear distinction between a dynamic and interactive. A dynamic churn will only use Excel's native tricks and tips like Data Filters, camera tools, etc. Whereas an interactive chart we'll use some of the developer toolbar features. We'll talk about developer tools in a later lesson. Dynamic charts are often used in dashboards. Also, you will learn two different methods of creating dynamic charts. Let's start by creating a dynamic chart using the first option. In this worksheet, we have financial data of accompany called Melon training. This data is from 20152019. So you can consider all the figures to be in the thousands of dollars. We have values for sales profits and new customers. We want to make a chart from which a user can select whether they want to see sales profits or new customers alone. This will be a dynamic chart where all the charts are shown and the user can make a selection from a little drop-down. And based on what the user wants, the chart should be shown. Let me show you an easy way to make a simple dynamic chart. The first thing is to create a three column chart. One for each value. Highlight the cells from V2 to C7. From the Insert tab. Click on the recommended charts. Like this. Let's create our second chart. Highlight the cells from B2 to B7. And we've control highlight also D2 to D7. Again from the Insert tab, recommended charts. Select discharge. Follow the same steps to make a chart for new customers. Now we move these three charts to a separate new sheet. Create the new sheet and rename it to dynamic charts. One, go back to the datasheet. Select all three charts with the Control key. Cut and paste here. The next step is to adjust three cells to make them big enough to contain the charts. Let's adjust the row heights and column widths for cells B3, b5, the column width will be 50. The row height will be 150. Also adjust the size of the charges from Format tab. Make the height of each of these charts two inches and the width, three inches. Nice. B3 cell is sales. Before profit. B5 is new customers. Also, B2 will be the chart type. Align all of them in the middle and center. Now place the charts neatly inside the cells. You need to make sure that the chart goes nicely inside the cell. To make sure that happens, select the first chart, drag it down and hold down the Alt key so that excel snaps the chart into a cell. When I hold then Alt key and move the chart toward the corner, Excel automatically snaps into the corner. Now the chart is finally safe inside B3. The chart must be inside the cell, otherwise it won't work. Now go to the B12 cell and enabled data filters from the Home tab and sort and filter button. When I select sales from this filter, this chart can be seen. But when I select profits to charge vanishes. This is because the chart is inside the cell. A quick keyboard shortcut is to turn on or off data filters is control plus shift plus L. Data Filter internally hides the row, adjust the cells are bits of the charts will fit exactly in the cells. This is how the dynamic chart works. It is straightforward and very simple. The only disadvantage of this method is that it also hides other objects and corresponding columns. That's why we will learn a second method more advanced that involves formulas and other tricks. Now, I will learn you how to make an excel Dynamic chart with a drop-down list. First, create a new worksheet with the name dynamic chart to click on the B1 cell and from the Data tab select Validation. We want to create a list of three charts, sales profit and new customers. Choose a list and the source will be these three cells from the data worksheet. The list has been created. The next step is to create a data preparation table. This data preparation table will retrieve the data from the raw table. Then I will create the chart based on the chart preparation table. These are the basic steps that we're going to follow. Let's put years here, copy and paste it into the cell. Then I will click on the E1 sell and make a direct cell reference to the B1 cell. Now, I need to extract the database on years and make this cell from the raw data table. I will use index and match formulas. The first argument in the index is the array, which is anywhere between C3 and E7. I click F4 for absolute reference. Then the row number which is years, I will use the match formula. So type match, open parenthesis, 2015 year. The lookup array or the years from the raw data which are absolute reference. I'm going to look for an exact match. So type 0 and close parenthesis. Now I need to look up for the sales or whatever value is in this cell. Match again. Match for this E one cell with an absolute comma lookup arrays these cells, c2, e2 from datasheet, an absolute reference, an exact match. Press Enter. Copy this cell all the way down. I am sure this is an exhaustive and tricky procedure for most of you. But don't worry, I've specific lessons for index and match functions than I explain everything in detail. Let us make sure these numbers are correct. 60 to 76, etc. Click on data sheet and the numbers are correct. Return them to this worksheet and test the data validation list. Great. Now it's easy to make a chart, highlight these. The answer tab. Recommended charts. Select this, put it here. Increase the size a bit and check if it works. It works. Fantastic. If you have any questions, please make sure that you ask them in the comments area. Thanks a lot for watching this tutorial and I will see you in the next lesson. Bye. 60. Side‐by‐Side Time Comparisons (Panel Chart): Although the name comparative trending is fancy, it is a simple concept. You charge two or more data series on the same chart. So the trends from those series can be visually compared. Assume you work in a supermarket and you have been given the task to create a chart that compares sales revenue figures of three product categories for the past four years. You'd create a data table like this. Just highlight the data from A2 to E15 and click on the Insert tab. Then line chart. You'll end up with this. As you can see, it's not the best charts, so we need to create less cluttered charts that make it easier to understand data from several categories. In this video tutorial, I'll show you how to build a line parallel chart that shows two or more similar sets of data side-by-side. With this technique, you can show periods in different colors without breaking the continuity of the overall trending. So let's dive in. A panel chart is actually one chart with the clever use of formatting and overlaying scanner charts, creating the dividing lines of each dataset. The first step is to lay the data in a standard format, having two sets of columns for each product category and get your data organized like this in the sales worksheet. So it's very crucial to stagger in the data into retrospective years. I prefer to change the format of my months to only one letter so that you can save me more space for the chart. Highlight months for B3 to be 50. From Format Cells. Dade category select m is tight. Look at the sample here. Click Okay. And months are presented with only one letter. The horizontal axis will be more simplified. Now, let's highlight all the data from A2 to age 50. And answered a simple line chart. Move up the science, the data, and increase the size. You can immediately see the panel effect. I have six series, two different lines for each product category. So I have six different colors. For example, we must fix the formatting so that the colors of each category will be the same. Right-click on the first beer, wine and spirits line. And select Format Data Series. The Format Data Series pain appears, click on Fill and line bucket and change the color to blue. Do the same with the second line of beer, wine, and spirit category. The color has changed for this category. Select the next line, bread and bakery and make it red. Finally to the cleaners category and make it green. Close the format data series. Delete the title of the chart. Now we can get rid of the additional legends, click on them and press the delete key. The next step is to add the vertical lines, the dividers to separate and distinguish each of these years. We'll do that by overlaying an XY scatter chart and use error lines is our dividers. First of all, we need to put markers here, so we will know exactly where we want our vertical line to be. Once we have the markers, we will then activate the error bars. From this table here I will control my dividers. The base number is 12, because a year has 12 months and we want our divine or to be after the 12th month. So the first should be 12.5 and the next one should be 24.5 and so on. So click on the K14 cell and I'm going to use this formula. Equal cell K1 with absolute reference, which is the months diviners multiplied by cell J4 plus 0.5. Press Enter. The result is 12.5. Increase the decimals to one to see the exact results. If you need copy the cell down to cells K5 and K6. These three cells are going to be the position of our marker on the x-axis. The y-axis is going to be 0. So put 0 in cells L4, L5, and L6. Now we'll start by copying these cells from K3 to L6. We're going to select the chart and paste special. From the paste special dialog box, we want to add a new series. The y-values would be the column, and we will check the categories or x labels in the first column box. Click the OK button. Looks like nothing happened. If you notice the legend, there is a series YN drink from the Format tab. Click the drop-down arrow from the current selection and find the series y. Now from Design tab, click on the Change Chart Type button. The Change Chart Type dialog box open. From the combo category I can see the Y series name. The chart type is aligned, I will change it to a scatter chart. Also check the Secondary Axis box and click. Okay. Now I've gone to more axes and additional horizontal on the upper side of the chart and an additional vertical on the right side. We don't want the upper horizontal axis highlighting it and deleting it. Now I get the position properly. What I need is a vertical line that goes all the way up and stays there for whatever the values are here. Now we need to add the error bars. Selected chart. Click the Format tab. Select the series y. Then from the Design tab, Add Chart Element. Error bars. Highlight the more error bars option, Select Plus and as the direction of the vertical error bar. No cap as n style. Here from the error amount I'm going to put as a fixed value, the number one. And this is where we activate the error bars. The bars do not go right to the top. Double-click the right vertical axis and find the maximum bounds to one. From tick marks changed the major type to none. And no labeled position. Close the dialog box. Delete these x error bars from the bottom and the line legend. Also delete the grid lines from the Format tab, add a shape style. Remove the border of the chart. I hope you enjoyed the lesson and that you manage to understand how to create panel charts to easily compare values. 61. Lollipop, Dot Plot or Dumbbell Chart: Excel job plants, dumbbells, and lollipop charts are good for comparing 12 or three points of data. Dot plots are flexible and easy to read. We are more accurate and or interpreting dots on a line and we are judging length, for example, in a bar chart. The dotplot does allow for some better comparisons between two points as opposed with side-by-side bar. In this video tutorial, we're going to cover all the details on how to create a dot plot chart and how a dumbbell chart words. So let's dive in my day-to-day blue shown here. I have five categories of a supermarket. Two columns, one with values for 2018 sales targets, and one with the values for the 2019 sales target in thousands. Also, we have a column with dots piercing values. The spacing simply assigns each category to a row in your chart, so they're nicely vertically distributed. You can change the spacing to suit your needs. Make a graphic in Excel, I will select the 2018 sales target and its values and hold down the Control key. Then dot spacing and its values. And insert a scatter chart from answer tab. The John spacing values push the 2018 sales target values. It does separate equally distributed lines. Right-click on the graph and choose, Select Data. Dialog box edit the name of the series so that it points to the cells containing the word 2018 sales target. Then click Add to make a new series for the 2019 sales target. A series name highlights C1 sell. Series X-values highlight the cells from C2 to C6. A series y-values highlight the cells from D2, D6. Press Okay. The lead, the vertical grid lines and also delete the vertical axis. If you want to emphasize the difference between the dots, you can set the horizontal axis minimum to something closer to the lowest value in your dataset. Right-click on the horizontal axis, format axis and type of value, and the minimum bounds box. I've got mindset to five and the maximum to 16. Since the dots will be labeled with their values, we really don't need the x-axis at all, but it may help credibility to have an x-axis in there as an anchor. However, we don't need to have all the intervals along the x-axis. So change the major units from two to four. Close the format axis pane. Next, let's format each set of scores so that they look like big dots. Right-click on one set and select Format Data Series. From fill in line, look for Marker Options. You will want to check the radio button next to built-in. Then choose the circle shape from the dropdown menu, change the size to 20, the fill to white, and make the border thicker. Do the same with the other set. Great. While the markers are highlighted, add data labels, right-click on the markers and select add data labels. Excel labels the dots with DOD spacing values. Right-click again on these values and select Format Data Labels. Align them Center and format them to display x value. Uncheck y-value. Make them bold and size ten. Do the same with the other set. Finally, there are a few ways to get the subject area labels in the graph. We can insert link text boxes right inside the chart like this. Insert a text box link with the a2 cell. Makes them formatting. And it's ready. With control. You can copy this text box to another dot plot and change the cell link. Another way to add a label is from the Format Data Labels pain. Check the value from the cells box and select the data range from cells A2, A6. You can align them left, right, or even below, and you'll do the same with the other set. Do whatever works best for you. Now let's move on to the dumbbell charts. Dumbbell dotplot is also known as connected dot plots have an advantage over the regular dot plot. The line that connects each pair of dots emphasizes their distance. Dumbbell charts require the same steps as a dotplot, but you delete the horizontal grid lines and replace them with error bars. But to make the error bars, you will need two more columns, positive error and negative error. So let's create them. Type positive error and sell one and negative error in cell F1. Positive areas. This subtraction between 2019 minus 2018 sales target values go to cell E2 and type equal c2 minus b2. Copy all the way down. Negative error is the opposite. Go to cell F2 and type equal V2 minus s2. Copy the formula to the other cells. Now replace all the negative numbers with zeros. To add error bars selected 2018 dots, click on the plus icon. It's the chart elements, the little arrow beside the error bars and more options. This will open the error bar formatting dialog box or pain. First you have to delete the vertical position of the error bars. Select percentage. Choosing percentage ensures a horizontal error bar is inserted. Now from the error of our options, select a horizontal error bar. As n style select no cap, and as aromatic select Custom. Click on the Specify Value button and select positive error values, E2, E6, and negative error values from F2 to F6 from the table. Press Okay. The lethal horizontal grid lines of the chart. And move the category labels near the dots. From View tab remove the grid lines of the chart. Let's also remove the border of the chart. The dumbbell chart is ready. I want to thank you for watching this lesson and I'll see you in the next video. 62. Timeline Milestone Chart: Timeline milestone chart. A milestone chart allows you to plot milestones on a timeline. This chart type can be useful when you're planning a new project and wanted visually show the plant milestones during a certain period or chart at the milestones that have been archived in the past. A milestone chart visually shows you the milestones and distance between each milestone. When I was in my day job, we used to create a milestone chart when we were planning a new project and had to report interim updates and deliverables. We showed the dates when we plan the check-in colon in terms slash final deliverables. When you have this data in a boring table, plotting it as a milestone chart helps visually see the progress as well as time between milestones. In this video tutorial, I will show you a simple technique to quickly generate a milestone chart in Excel. So let's dive in. The first step is to get the data in place. This is the most important step to creating the chart. You can see I have two columns of data, date in B3 and B11, and activity and C3 and C2. I need three helper columns. Date activity in text placement in his helper date column. It will display the date only if there is a date in column B. In this helper activity column, it will display activity only if there's a dating column B and activity in column C. Else it will display not applicable or NA. In this text placement column, it will display a text placement value only if there's an activity in column F. So I will use the IF function to complete these three helper columns. Let's start from column E and cell E3. Type equal IF open parenthesis. First of all, check if there is a date in B3, it would be three is not equal to null. Then type B3. Otherwise return nothing. Copy the cell down to cell E11. The first helper column is ready. Now let's move on to column F and cell F3. Type equal. If C3 is not equal to null and E3 is not equal to null, then type s3. Otherwise return nothing. Copy the cell down to cell F21. Great. Now let's move on to column G and cell G3. Type equal. If f three is not equal to null, then type ten. Otherwise return nothing. You can type whatever number you want here. Copy this cell down to cell G11. The three helper columns are ready. Now that I have the data in place, I will start designing the timeline milestone chart. Click on an empty cell and go to the Insert tab from the charts section. Select a line chart with markers. Of course there is no chart in front of me, only a border. Now from the chart design tab, click the Select Data button. From the source data source dialog box. We will start adding data. Click the Add button. The edit serifs dialog box type date as a series name is series values. Leave the equal sign, click the arrow button, and highlight the activity cells from F3 to FTN. Press Okay. This inserts a line chart with x-axis values as 123 and y-axis values is 0. In the Select Data Source dialog box, click on Edit in horizontal, category axis labels, and select dates in column E. Press Okay, this changes x-axis values two dates. Let's add another series. Click the Add button in the edit series dialog box type activity is a series name. As series values leave the equal sign, click the arrow button, and highlight the text placement from cells G3 to G tan. Press Okay. This inserts a half hazard line chart. Press OK again. Move the chart below the data and adjust the size. Click on any of the activity datapoints, right-click and select Change size series chart type. In the Change Chart Type dialogue box from the combo category from the activity series select stack column chart will change the half hazard line chart into a column chart. Now right-click on data bars and select Format Data Series option. In series option pane, select the secondary axis. This would introduce a secondary vertical axis on the right of the chart. Click on it and delete it. From the Design tab. Click on the Select Data button. From the Select Data Source dialog box, select activity series and click on Edit in horizontal category axis labels box. The axis labels dialog box, select the activity cells from Column F, from F3 to FTN. Click okay. Right-click on any of the bars and select add data labels. Now, right-click on any of the data labels and select Format Data Label from the format data labeled pane, select category name, and uncheck any other. This adds activity names as data labels. Adjust the position to get the activity name at the top of the bar by selecting the inside n as labeled position. Select any bar. Go to the Design tab and from Add Chart Elements. Select error bars. Then more error bar options. From the format error bars pain make the following selections. Select minus as vertical error bar. Select no cap as n style, select 100 percentage as error amount. Right-click on any bar and select Format Data Series. From Format Data Series pane, click the Fill in line bucket icon, select no fill and from border Noel line, click the horizontal line. Right-click and select Format Data Series, select no line. Also for Marker Options. Building types. Select the diamond icon. Change the size to ten and the color to a green one. That's it. Your milestone chart is ready. You can further format your chart like add a title, add the font of the horizontal axis months, or change the position of the activity labels. 63. Actual vs Target Chart: If you're working bulge reporting the actual and target data, you may find it useful to present the actual values versus the target values in a chart in Excel. For example, you can show the actual sales values versus the target sales values or the satisfaction rating achieved versus the target rating. There can be multiple ways to create a chart in Excel that shows the data with actual value and the target value. Here are the two representations that I prefer. The first chart, the target values are shown as the wide light blue bar and the achieved actual values are shown as the narrow blue bar. This chart uses contrast and the actual and target bars to show whether the target has been met or not. It is better to have the actual values in the dark shade as it instantly draws attention. On the second chart, the actual values are shown as blue bars and the target values are shown as red markers. This chart uses marker lines to show the target value. The actual values are shown as column bars. In this tutorial, I will show you how to create these to actual versus target charts. Let's get started. Here are the steps to create the actual verse target chart. Select sales from A2 to C6. It's the entire dataset for targets and actual values. Go to the Insert tab in the charts group. Click on the stack columns chart icon. In the chart that is inserted in the worksheet. Click on any of the bars for actual value. Right-click and select Format Data Series. The format series pane, select secondary axis in the plot Series Options. Now select any of the target value bars. Simply click on the blue color bar. In the Format Data Series pain lower the gap width value, I changed it to 80%. The idea is to increase the width of the bars to make them wider than normal. Select any of the actual value bars. Again from the Format Data Series, select secondary axis in a plot Series option, make the gap width value 200%. This will reduce the width of the actual value bars. Click on the secondary axis value on the right of the chart and hit the Delete key. Now it's done to format the chart, shade the target values bar and a light blue color to get a contrast. Change the color of the actual value bars to blue. Note that it's actually better to have a color, shade, contrast and Target and actual values. For example, in the chart above there is a slight shade of blue and a dark shade of blue. Move the chart below the table. Now it's time to move on to the second chart. Here are the steps to create this actual verse target chart in Excel. Select cells from A2 to C6. It's the entire dataset for Target and actual values. Go to the Insert tab in the charts group. Click on the stacked column chart icon in the chart that is inserted in the worksheet, click on any of the bars for target value with the target bars selected, right-click and select Change series chart type. In the Change Chart Type dialogue box from the combo box, select a line chart with markers. This will change the target value bars into a line with markers. Click Okay, select the target line, right-click and select Format Data Series. In the format series pane select Fill and line icon in the line options select no line. This will remove the line in the chart and only the markers would remain. Select the marker icon. In Marker Options, select built-in, and select the marker that looks like a dash. Change the size to 20. You can check what size looks best on your chart and adjust accordingly. From Phil, change the color to red, click any of the actual bars to change the color to blue. Move this chart below the first chart. That's it. Your chart is ready. Make sure you format the chart so that the marker and the bars are visible when there's an overlap. 64. Gantt Chart: A Gantt chart is a timeline of your project. Management requires knowledge and skills on a project activities to meet the project requirements. When it comes to managing projects, you need tools to make the manageable professional use Gantt charts to visualize how highly complex tasks can be broken down into smaller processes. Although Excel itself doesn't support Gantt charts, creating a simple Gantt chart is fairly easy. You just have to get your data ready and set up properly. So let's get started. The first step in your Gantt chart in Excel is the data. Enter your Project table and list each task in a separate row. Structure your project plan by including the project start, date, duration, and date and days in total to complete the tasks. Look at our data. It's a table that shows the making of a software application at various stages. It has to start date, each task begins, and the duration in days. Look at the first row gathered requirements. It starts on January eighth and last 11 days because we want to calculate workdays, not weekends, we use the useful function called Workday. Double-click on cell D3. This function will calculate the end date of the specific project automatically. Finally, we want another column called days in total to calculate the total days. It's a simple subtraction between end and start date. Let's check it. To create a Gantt chart, you need three columns, the project, the start date, and the days in total. Let's highlight them with the help of the control key. Switch to the Insert tab. Then under the charts group click the stacked bar chart. Not exactly what we need, but it's the closest to a Gantt chart. To make a stacked column chart looked like a Gantt chart, we have to make one of these two series disappear. In the end, one series will be looking like the floating tasks that make the typical look of a Gantt Chart. The goal is to hide the blue bars so that only the orange part is visible. Technically, we need to keep the blue bars as the 0 baseline. Let's make them transparent. Double-click any of these Format Data Series paint open from the fill in-line tab. Choose no film from the border, no line here. Close the dialog box. The next step is to adjust the dates. You would notice that the x-axis range does not start on the first day of the first task. We can change the axis range easily. Maybe the trickiest part is to know how Microsoft Excel handles date values. Excel stores dates is sequential serial numbers so that they can be used in calculations. By default, January first 1900 is surreal number one in January eighth, 2022 is serial number 44,569, because it is 44,569 days after January first 1900. You can see that when you select a cell and change the format from date two example, general. We will use the 44,569 as the minimum of the axis range. Double-click on them. Type in the first box, the start date eight January 2022. The maximum box type, the final end date, June first 2022. Of course, Excel recalculates them in values, but we don't mind. The interval between dates is now 20. Let's change it to 30, the days of a month. Also, it would be useful to decrease the gap between bars. Click on any bar and change the gap width to 0%. We don't want a gap at all. Let's change the color of the bars also. Click on the bucket and change the color from their green is okay. Data labels would be a good idea. Go to the chart design tab, click, Add Chart Elements button and centered data labels. Or Gantt chart is ready. A final note. If you want the first project to start on the upper left corner and go downward, double-click on the vertical axis. The last tab called Axis Options has what we need. Find the checkbox categories in reverse order and tick. Let's put on those last touches. Delete the title. Also delete the legend. Finally, delete the border of the chart. We made a beautiful Gantt chart. Thanks for watching. 65. Burn Down Chart: Hi everyone. In this lesson we're going to build a burndown chart in Excel. A Burndown Chart is a great project management tool that shows if a set of tasks are completed ahead of or behind schedule. Bruno charts wanted the most intuitive ways of measuring your product's progress against targets in deadlines and tracking them in microsoft Excel is the go-to option for many teams. If you were ready to excel at burndown charts, Let's get started to make a burndown chart first, all you need to do is have two pieces of data. The schedule of actual and planned remaining hours of a task. The plant hours or aid for each day. The actual is the hours we spend each day. Also in the first column, we have the total days that the task has to be completed. In our example, the total days is 15, starting from day 0. The three additional columns that I have or for the calculations to make the burndown chart. If we add the plant hours, it'll give us a total of 120 hours. Basically, it's the sum of all of these days. We're going to assume that the actual total hours or 120. Now, let's see the calculations here. Go to E6 cell. The total remaining plan hours after day one is a 112 hours. That is 120 minus eight. And here is the formula Equal E5 cell with absolute reference, which is the total plant hours minus the plant hours for this specific day. Don't forget the absolute reference of the cells. It's very important. Let's go to F6 cell to learn how we calculate the remaining total actual hours. Here we use the IF function. So if D6 cell is blank, then type not available or NA, which means we didn't work at all during that specific day. For example, look at the D9 cell, it's empty. Look at the NA error here. Otherwise, calculate the total remaining actual hours. The total actual hours are in cell F6, the actual hours for the first day or in cell D6. The last column is how many hours we actually weren't. Each cell has the same value with this column. Great. Let's get started on creating the chart. The first step is to create the remaining plant and actual series and create a line chart. Use the first column days in the above table for the x-axis labels. Go to the Insert tab and select the line chart. Let me go ahead and move the chart here. The vertical axis or the total hours of the task, 120. The orange line is the plan hours, eight hours for each day. It's a straight line, as you can see. The gray line is the actual hours we've worked. This area means that the project is behind schedule. There is more work left and originally predicted in this area means that the project is ahead of schedule, so there's less work left than originally predicted. The horizontal axis starts from day one. It must start from day 0. So let's change it. Right-click and select Data option. From the Select Data Source dialog box. Click the Edit button from the horizontal axis labels a new dialog box appears. Highlight the axis labeled range, which is from V4 to be 20. Click the arrow down, then click OK. Again. We don't need this line, so delete it. Delete also the title. The next step is to add the daily completed values to burn down chart. Right-click on the chart, select Data option, and click the Add button from the Legend Entries. Type done today as a series name. Now as a series value, highlight the data from G14 to G20. Click OK Twice. What's added. We have to change the chart type from this series to column chart. How do we do that? Right-click here on the blue done today line and select the Change series datatype option. From this drop-down menu, I will change the chart type of the done today's series. Select Clustered Column Chart. Click Okay. Remove the grid lines, adjust colors and add legend if needed. Burndown charts are a great tool for predicting when all of the work will be completed. Practice yourself trying to create the chart. If you have any questions, please post them in the discussion board. Thanks for watching. 66. Tornado Chart: In Excel to her natal chart is useful for those who want to analyze their data for better decision-making. The best use of it is for sensitivity analysis, but you can use it for comparison purposes. The Excel Tornado chart is like a two-sided bar chart, looks like a tornado, where you have two data bars that are opposite to each other and make it easy to compare both of them. In Excel, there is no default option to create a tornado chart, but you can use the default bar chart and customize it. So let's dive in. In this data table, I have ten products with a quantity that they sold for these two stores. First of all, you need to convert data of store one into the negative value. This will help you show data bars in different directions. For this, simply multiply with minus one with this smart paste special trick that I will show you go to A1 cell and type minus one. It can't be this cell. Now select the values of store one from B3 to B12. From the drop-down arrow, select a special paste option. Select values, go to operation. Select Multiply option, click. Okay. This is a great Excel hack that I use when I need to perform some real quick calculations. The next step is to insert a bar chart. Select all the data. Go to Insert tab. Select a bar chart. You'll get a bar chart like the one below where you have two sides. One side is for positive values and another is for negative. From here, select the axis label, right-click and open formatting axis options. From the Format Axis pane, go to access options, labels. Then labeled position, change labeled position too high. Now you need to change the series gap in gap width. This will help to streamline data bars with each other. Select any of the bars. From Format Data Series pane go to Series Options. Change series overlap to 100% and gap width to 10%. In the end, you need to change the format for data labels and store one so that it doesn't show the negative signs. For this. Go to the Format Axis option number. And select custom. This is the correct format code. Click Add, Delete the chart title, and change the font color of the products too wide. Congratulations, you've made your first Tornado chart. Thanks for watching. 67. Frequency Distribution Chart (Histogram): In this lecture, we're going to design this frequency distribution chart. Histogram, also called a frequency distribution chart, shows how many times is specific value occurs within a certain range. It is super useful if you want to know how variable is statistically distributed. Let's get started. Let us consider an educational scenario consisting of 100 students and the marks scored by them in a recent examination. The first step would be to get all of the data in place it on the Excel sheet in a tabular format is shown here. Once all the predetermined data is present on the worksheet, the first step would be to find out the maximum and minimum values of all of the scores in cell E2 type, max value in cell F2 type equal max S3 to see 102. Enter. Similarly, we'll find the minimum value of all the scores from the Min function from the same cells. The next step would be to work out the class limits, ie the intervals with regards to all the scores to establish the frequencies, class limits are calculated and used to facilitate an accurate distribution at an arrangement of data in order to generate the frequency values. The scores range in this instance from six to 97. Let us consider the class limit in the multiple of ten. Now we're going to use pivot tables to easily create a frequency distribution. Click inside the table and insert a pivot table from the Insert tab. Check if the table range is correct. Also place the pivot table to the existing worksheet starting from cell E5. Click OK. Next, Drag the following fields to the different areas. Drag scores into the Row area. Drag again scores into value areas. Click any cell inside the sum of scores column, right-click and click on Value Field Settings. Choose count and click OK. Next click any cell inside the column with row labels. Right-click and click on Group. One for starting at 100, for ending at ten for by click. Okay, our frequency distribution table is ready and the data has been stored in bins. Now, let us go ahead and format this table. Right-click on any of the cells of the pivot table. Then Pivot Table Options from totals and fillers tab remove the grand totals. Press Okay. I need the cumulative frequency of this column. So I'm going to format this data, right-click any of these cells and click on Value Field Settings. From this dialog box, I'm going to click on the Show Values As tab from this drop-down and select a percentage running total in. As base field can make sure that score is selected. I will name it as cumulative percentage. Click, Okay. As you can see, my bins are created and at the same time I have the cumulative frequency against each size. Let's format this table. Select the cells and from the Design tab, select these particular size. It's very crucial to prepare the data correctly in order to make the chart. Now it's time to create the graph. Select the entire data, Insert tab, click the arrow to see all the charts. Then select area chart. Click OK. Move it beside the table. The next step is to format this chart. The first step is to remove all these buttons, scores and cumulative percentage from Analyze tab, click on field buttons and hide all. Delete also the legend. The group lines, the chart title. At the same time, I don't need the vertical axis, so I'm going to select it and click Delete. The next step is to add the vertical drop lines selected chart. From Design tab click the Add Chart Elements drop-down arrow. From the line option. Click on the drop lines. Here are the drop lines, right-click on them. Then format drop lines. From the Format drop lines pane, select the fill and lines option. I'm going to select the solid line and select the black color. Increase the width to one. From dash type. Select the dash. Now right-click on the chart and select Format Data Series. I'll select the light-blue and remove the border. It's time to add the data labels. For that. I'm going to click on this plus sign and check the data labels box. The last step is to format the data labels. Right-click on them, then change the data label shapes. I prefer the rectangle with the rounded corners, but you are free to select any shape you want. I'll change the color to blue and the font color to white. Make the font of the horizontal axis to bold. The frequency distribution chart is ready. I hope you enjoyed the lesson. If you have any questions, please let me know. Thanks for watching. 68. (NEW 2024) Interactive Visualizations & Maps using Macros: In this video tutorial, we're going to introduce and create macros for creating visualizations both interactive and automated. Here we have a map of Australia with the territories and the red square shapes on each of these states. As I hover over the red shapes, I get that little hand icon. And when I click on it, it works fantastically. It shows me the core waste ton for each state. So by the end of this video, you're going to build some fantastic interactive features for our visualizations and our worksheets ready to use for dashboards and graphs. So let's dive in. So what is a macro? A macro is a small program that can automate repetitive or difficult tasks. They can be coded from scratch, but Excel comes with a built in macro recorder, where you can record yourself going through certain steps and then just play it back. That's the approach we're going to take. In this workbook, we have a waste management report with the core waste ton PC per state. The last column right there is the compounded annual growth rate over the ten year period 2013-2023. Also over here in Cell K one, we have a list of the territories or states, if you prefer, okay? This list is very easy to create. From the data tab, you click on this validation button, and from validation criteria, you choose list. Then you can highlight the state sales from B five to B 12. Okay. And the list is ready. Great. Also, we have a map of Australia with the territories and red square shapes on each of these states. I found the map from the Internet, so it's easy to find the same or similar with the territories. These are very simple square shapes that I took from the answer tab, illustrations and shapes. Very, very easy. Now, what Bill wants to do is to add a conditional format to the spreadsheet. He wants to be able to select state or territory by clicking on the map and then have the core waste data for that territory highlighted. So let's start by doing the conditional format. This is a bit different because we were adding a conditional format to an entire row. So we're going to select our data from B five to I 12. And come up to conditional formatting over here. And we're going to need to come up with a new rule because we need a formula for this, right? Well, then we're going to click back into the bar where we enter our formula, and we're going to type about equals. We're going to click on the first state, and we want to see that it's equal to K one. But you'll notice it's put those dollars on again. All right, what do we want to do? As it goes down evaluating the states, we do want it to change, but as it goes across, we need to keep referring back to column B. So we're going to press our F four function key twice, so we still have the dollar in front of the B. That's what you need to fully understand the differences between the relative and absolute references. I don't forget that I have a whole lesson on this topic. Let's continue. We're then going to type equals and check if it's equal to K one. Now we need to add a format. Let's press this format button. So let's make it light blue from the fill tab. And from font tab, we will change our font color to white. We're then going to say, Okay, and okay. ACT's data is highlighted because ACT is in K one. If I come to K one and change that to northern territories, that's working. Very cool. Now, how do we link up our map to the dropdown list? What we're going to do is we're going to record a simple macro where we record ourselves typing the value ACT into k one. We then have the option to link any graphical object to a macro. So we're going to take the square shape covering the ACT region, and we're going to hook that up to our ACT macro. Before we can record the macro, though, there are two important things that we need to do. The first one is saving our workbook as a macro enabled workbook because normal Excel spreadsheets do not support macros. Very important. So we're going to come to File. We're going to click Save As. We're going to change this from an Excel workbook to an Excel Macro Enabled workbook, and we're going to click Save. The second thing we have to do is show developer tab in the ribbon. Here is mine, but it doesn't show by default. So if you haven't used it before, come to your File tab. Come down to Options, come to the customized ribbon and put a tick next to developer. Then just say, Okay, the developer tab should now appear, and we're going to click on that. The first group in the developer tab is called the code group. This gives us all the tools we need for recording, editing, and running macros. Before we hit the record button, though, we're just going to make sure that we click away from K one, because the first thing we want to record ourselves doing is clicking on K one. So do that and then click Record Macro. Now we have to give our macro a name that's unique within the workbook. I'm going to call it ACT, and then you have the option of giving it a keyboard shortcut. But control plus A, in fact, control A through Z are already accounted for. So if you hold your shift key down, you can use the keyboard shortcut Control Shift plus A. We're then going to say, Okay, now, anything we do is going to be recorded. We're going to start by clicking on K one. Don't use the drop down. Then click wherever you want the active cell to be when the macro finishes running. So I'm going to click on L one. Now, really important, you must stop the recording, okay? Let's do another one. So I'm going to click Record Macro, and this time, I'm just going to call it NSW. I'm going to make my keyboard shortcut Control Shift W, hold the shift, not the control and say, Okay. Again, click away into Cell K three, for example, and press Stop recording. Let's test if these macros are working. So we're going to press Control Shift A. Yeah, that worked beautifully. And now Control Shift W. Fantastic. So really easy to create a recorded macro. All right. Be quite good to see what these macros look like. So we're going to come up with our code group and click on the macros button. This dialog will allow you to run the macro if you don't have a keyboard shortcut. You can delete it if you've made a mistake and just start again or you can edit the macro. We're going to click Edit. Welcome to the VBA Editor. The code that we're looking at is visual basics for applications. This is code that our macros are written in Excel. At first glance, it might look a little bit foreign, but we're just going to take a moment to break it down. All recorded macros begin with the word sub and end with the word sub. Make sure any code that you want to run appears between those two and that you don't delete them. The stuff in green is what we call a comment, but it's going to be ignored by the compliers, so it's not actually going to run, but it's useful for giving information about the macro, like who wrote it, or when it was changed. If I wanted to remove a line of code, I could just put an apostrophe, and you'll see that comment, sit out. And if I remove the apostrophe, it's back. Getting into the code itself, range K one dot, select Well, we can work out what that's doing. It's selecting the cell K one. We're then changing the value in the active cell to be ACT. Then we're selecting the L one cell. Not easy to write from scratch, but very easy to understand and very, very easy to change, okay? So rather than going and recording my other six macros, I can just copy and paste this and edit it. I'm going to select the data, the macro for New South Wales, Control plus C. Click underneath the sub and paste. Now all I need to do is replace all the NSW with another state. So I'm going to go to Western Australia. Don't have the keyboard shortcuts, so I'll remove that. Then, very important, make sure the active cell is changed to WA. Now, to get back to our workbook to check this, you could close the window or the great keyboard shortcut Alt plus F 11, which toggles you between the spreadsheet your code. We're going to test the WA macro, but we want to test it by clicking on the map. So we're going to come up to the red square, which is a shape just over the WA territory, and the shapes are brilliant. You can hook up your macro to any geographical object, but shapes are a very versatile option. So I'm going to right click on that shape. I'm going to come to assign macro. I'm going to choose WA and then say, Okay. Now, let's repeat the other two we've set up. So I'm going to come to New South Wales and assign Macro. The same with the Australian capital territory. Now let's go and test them. As I hover over the WA shape, I get that little hand icon, and when I click on it, it works fantastically. Same for New South Wales and the same for the ACT. So we see now using a simple recorded macro, we can add some fantastic interactive features to our visualizations and our worksheets. For your optional homework, try finishing off the rest of the macros and hooking them up to the map so that it works nicely. 69. (ADVANCED) Animated Chart using VBA: Today's lesson is about animated charts in Excel and how a simple VBA code can help us achieve this kind of chart. If this sounds complicated to you, don't worry, we will go step-by-step. We use VBA to control the animation. Here we've got our source data. Here we've got our dynamic source data. And you'll notice that this is formula based, which I'll explain later. These dynamic source data feeds the chart that we will create an a while. Also we have a controlled cell that dictates what is displayed in these columns, I, j, and k. These columns, we've got an IF formula. And it's deciding which data is displaying based on this value in controlled cell. Let's look at the formula. It uses the row function. The row function simply returns the row number that is inside the brackets. If that is blank, it returns the row number that the formula is n. We're in I3 cell, so we will return the number three as a row. If you highlight only the function and press F9, you'll see the result which is three. This is a nice trick to see part of the result of a large formula. The formula says, if the value in E3 is greater or equal to the current row, then display the value of a3, and if not, then return hush and a. Remember, hush NA doesn't display in charts. The control cell has a value of three, so it returns the value in A3, which is 52. If I change the value to ten, then it will display the values from I3 to IE ten. As the control cell counts up, more and more of these data here are slowly revealed. And that is how we create the animation of the chart. To complete the animated chart, we need two steps. The first step is to create the chart, and the second step is to create a VBA code that changes the value of the control cell from number three to number 38. Finally, with the help of a button, we will execute the VBA and the chart will start to animate. Let's start from the first step to create a clustered column chart based on these datas and presented them in a particular format for each time period in a different color. Here we have a column for each year, 201720182019. January's in the second year is repeated from the previous column and the same in the third year. This gives us a continuation of the chart without any gaps. So highlight the data table from G2 to K13. Click on Insert tab. Select the clustered column charts. Move it somewhere there. Change the value to 38, so it will appear in all the columns. I will use formatting to give the illusion of one chart instead of three and give a continuous lug. Right-click any of the series, format data series, and increase the overlap percentage to 100%. Also decrease the percentage of the gap width to 50%. Delete the legend, the grid lines, and the title. The chart is ready. Now it's time for the VBA. First Developer tab macros. Name the new macro animate. Make sure that the macros in this workbook press Create and it opens the Visual Basic Editor. Now I will paste the code and explain it. Save it, makes sure that you save it as an XLS M type, which means Macro Enabled Workbook. Let's take a look at the VBA. We strongly recommend using Option Explicit at the start of your Excel VBA code. Using Option Explicit forces you to declare all your variables. Sleep is a Windows function and not a VBA function, but you still use this function in VBA code by calling the windows sleep API. Actually, sleep is a function present inside Windows DLL files. So before using them, you have to declare the name of API above the code in your module. The syntax of the sleep statement is as follows. Here, delay specifies the time in milliseconds to which you will have to pause the execution. The advantage of sleep statement overweight is that it is quite flexible as you can give the time delay in milliseconds. While in wave function, you can only delay the application by whole seconds. Here's the name of the macro. If you change the name and the VBA editor, you need to reassign your macro to the button you're using to run the macro. Use the Dim statement at the module or procedure level to declare the datatype as a variable. For example, the following statement declares a variable as an integer. This is the number range you want the macro to scroll through. These are the numbers in your control cell. This is the worksheet name you want to control, which is Sheet1. This is the cell you want your control entered in. Finally, this controls the speed. The lower the number, the faster the chart scrolls. Okay, That's how the VBA works and how to update specific parts like worksheet names and so on. I want to assign my macro to a button. Go to Insert tab online pictures and find a play button. Like this. Put it somewhere here and with the right-click, assign the macro. Now every time I hit that button, the macro runs and the animation of the chart is starting. That's how you can do an animated chart. I hope you enjoyed this lesson. I want to thank you for watching and I'll see you in the next video tutorial. 70. (ADVANCED) Rollover Dynamic On-Demand Chart: Hi everyone. In this lesson I'm going to talk about the rollover method and how we can use it to show us dynamically on-demand chart details. Specifically, we will create a table of these products in the dynamic charts showing us the rating of each product. To do that, I will use formulas with the help of index match, average If countifs, IF error, and hyperlink functions. Also, I will use the rollover method, user-defined functions or UDF macros, VBA code, advanced filter and Conditional Formatting. You'll learn a lot of new stuff in this video tutorial. So pay attention. It's a crafty tutorial. Let's dive in. In this workbook, I have two worksheets in the source dataset. I have a data table with products, the customer's code that purchased the product and the customers rating for each item. This worksheet is almost empty and I've typed only the header. Here. We will put the filtered source data with the star rating system, the dynamic data, and the on-demand dynamic chart using the rollover method. Let's get back to the source data. It's better to convert it to a table, so it will be much easier to do a formula calculations later. Glucagon any cell inside the table and from the Home tab select format as a table. Choose any style you want. Change the range if it is correct and if the option my table has headers is checked. Now we have a table with table one is a name. The first step is to create a new table with products, but we'll have to remove the duplicates. We want the unique records out in this data table. There is a tool that is built into Excel and is located on the data tab of the ribbon. It's called an advanced filter. Click on it. In this dialogue box, I'll selected copy to another location radio button. Now I'll select listed range that contains duplicate values, and it's from A1 to a 156. Select a copy to the cell where the new list of unique values will be output, should be a blank column. Let's choose the one. We want, unique records only. So check this box and press Okay. The advanced filter will paste the values of the unique items starting in the cell specified in the copy to the range. Let me move this little table to the worksheet and paste it here at B5 cell. Increase the column width. The next step is to calculate the average of that particular products rating from the raw data. And we're going to use the average if function to do that. Click on C16 cell. Type equal average if open parenthesis product column from table one as range. Comma B16 is my criteria. Comma rating from the table is the average range I need to find. Enter. Copy the formula down. If you need to decrease the decimal numbers to two, you can do it from the Home tab and the number section. As a header put rating format the cell. This is the average rating of each product, but how many are there for one star, two star, three stars, and so on. Also, how can I show the five-star rating system visually? Let's start by creating the five-star rating system. Type on D five-star rating. Change the format. Highlight D5 to H5 range. Click on the merge and center button. To make visual star rating system, I will use conditional formatting and use the ratings from the icon sets and the three-star icons from rating. I have a full star, half star and nothing to 0.83, for example, is too full stars, a half star, etcetera? I will need to put a formula for each of these cells. The formula is going to look at the rating. If it's higher than 1.1, if it's higher than to put one, if it's greater than 3.1. Otherwise put the model for the cell. Let's dive these numbers from one to five, from D14 to age four. They are just visible on the screen because I'll use them in my IF formulas, but you can hide them. Now I'm going to start writing the IF function and then I will explain it in more detail. Equal. If parenthesis C6 with absolute on C column, when I write on, on the formula down, it moves on rows seven, etc. But if I copy the formula across, it does not move from B. Now if C6 is greater than or equal to d four with absolute for put it in number one in that cell so that it means full star comma if it's not tested again with the IF formula. If the integer portion of the dollar C16 value is equal to d four minus one. Then I'm going to bring the MOD function. The modulus of dollars C6 value divided by one MOD formula is going to calculate the half star rating. After the modulus function, it's going to be 0, which means no star rating. I have a lesson for INT and MOD function. So if you don't remember how they work, please check that lesson. Enter. Copy the formula across and copy down so you can see what it's doing here. The 2.62 is greater than one, greater than two, but not greater than three. So we extract 0.62. Let's fix the decimals. And let's decrease the width of the column. Now we've got the values that we need. I'm going to highlight the cells from D16 to H9. Go into my conditional formatting and click on the new rule. Format style. I will choose icon sets and then the three stars change the type to number. If it's a full star than the value must be 1.5 star, the value must be 0.5. And if it's less than 0.5, it's no star. Don't forget to check the show icon only box press. Okay, and that is my rating system. Now we have to find a way to analyze each rating of how many one star has, how many two stars. Instead, to find that the total distribution of values, I will use the COUNTIF function. Type the numbers wanted to find from J4 to N4. Type equal countifs. Parenthesis, product from table one, comma dollar B6. Condition one, rating from table one, comma j dollar for its condition. Two, close parenthesis and enter Degree in absolute reference with structured references, you need to add and duplicate column references. Repeating the column reference will anchor the reference when dragging across columns. The result is six copy across and down. For example, fuel mix has only one four-stars and five five-stars. Let's go to source data to check the values are correct. This is the one four-star, the 12345 five-stars. It looks fine. Go back to this worksheet. Decrease the width of the column. Add a total reviews column, and we will need it later. Autosum and Enter. Copy the formula down. Let's make some formatting to the table. Great. To create the rollover method and the on-demand dynamic chart that we will change when we hover our mouse over the products, we will need to create a dynamic data table to make it easier. I will first create a data validation list of these products. Click on the b22 cell. Data tab. Data Validation. My validation criteria is a list. The source is products. We made a nice list. Give it a name, for example, product selection. To find the position of the specific product, I will use the match function to return to the position. Click on a 22 equal match. Parenthesis. The lookup value is the product selection. The lookup array is products from B6, B9. I want the exact match. Enter. Chai has positioned six. I will need that value to find one-star, two-star, and so on using the index function starting from D22 cell type, equal index parenthesis J6 to N9 in an absolute reference, which is the array comma A22, and absolute reference, which is the position. We find the column number type columns J1 in absolute reference to J1 using this dynamics formula here, I have absolute the first part here, because as I copy across the values, dynamically update, enter, copy, and see it in action. Choose any product from the list and see that the reviews are correct. Highlight the range from B22 to H E122 from the Insert tab select bar chart. Put it somewhere here. The charts values are changing when we choose a different product, but we need to make some formatting for the chart. One annoying thing is that the one stars at the bottom and five-star is at the top. Select the vertical axis, right-click Format Axis from Axis Options check the categories in reverse order. Finally add the word star beside the writing. From number, type, general star. The vertical axis is ready, but the horizontal axis also needs some formatting. Right-click. Format Axis. Change the labeled position too high. The maximum balance to six. Right-click on any of the bars, Format Data Series and change the gap width to 20%. The next step is to fix the title of the chart. I want to say, for example, 16 total reviews from China. I will create a formula and link it with the chart title. Go to B23 and type equal index O6 to 019. Absolute comma 22 cell with absolute comma L1 column. Close parenthesis. Ampersand symbol. Double-quotes, total reviews for close double-quotes, ampersand symbol B22 cell, and press the Enter key. Link the cell with the title. So like the label B203. And we are ready. The rollover method allows you to execute a VBA code when the mouse hovers over sell. To achieve that, we need to create a new function or a UDF. Udf is a user-defined function provided by the user that can use the same way you would use some. For example, we will start by opening the Visual Basic Editor and from the Developer tab, Visual Basic, you can use the shortcut all plus F11. It opens up Visual Basic Editor in a separate window. On your left you can see the workbook has two worksheets. Right-click, Insert and choose the module. It opens a blank window. I will paste the code, which is very simple and straightforward. We're not going to learn VBA code in this lesson and it's out of the scope of the course. If you are interested, I have a course on macros and VBA. The code is ready. Let's explain some parts of the code. This chart selection is the function's name. This is the syntax for our function. This product selection is the name of the B22 range. Self save the code but be careful, it must be saved as a Macro Enabled Excel file. The UDF is ready. Let's see how we use it to create the rollover method. This is where the hyperlink function helps us to achieve our goal. The hyperlink function syntax is linked location and friendly name of this Link Location, click on the B26 cell and start typing. Equal IF error or parenthesis. The Excel IF error or function returns a customer resolve with the formula generating an error and a standard result when no error is detected. With IF error or you can trap and manage errors without using more complicated nested if statements. Type, hyperlink, parenthesis, chart selection, parenthesis B6, close parenthesis comma B6, close parenthesis, comma B6 and close parenthesis. Enter. The result is Alice mountain. Copy all the way down and resize the chart. When we hover the mouse over the cells and activates the whole cell, highlight the cell range from B26 to 039. Remove the grid lines from the View tab and add a thick outside border from the Home tab. Also removed the chart outlined from the Format tab. I'll also have to remind you that when you set up a rollover method, the Excel file must be saved as an enabled macros file because the UDF is a macro, a VBA code. The rollover ideas from the book dashboards for Excel. I'd like to thank Mr. goldmine for the great ideas in his book. This is an advanced video tutorial. I recommend watching it again, if you need to fully understand all the techniques and functions we used in this workbook. If you have questions, please let me know. Also, leave me a comment where else we can use the rollover method. Thanks for watching. I'll see you in the next lesson. Bye. 71. Introduction (Form Controls): Today, business professionals increasingly wanted to be empowered to switch from one view of data to another with a simple list of choices. For those who build dashboards and reports, this empowerment comes with a whole new set of issues. The overreaching question is, how do you handle the user who wants to see multiple views for multiple regions or markets. Fortunately, Excel offers a handful of tools called Form Controls that enable you to add interactively into your presentations. With these tools in a bit of creative data modeling, you can accomplish these goals with relative ease. You can find Excel's form controls on the Developer tab, which is initially hidden in Excel. To enable the developer tab, go to the ribbon and click the File button in the menu that appears, click the Options button. In the Excel options dialog box that appears click the Customize Ribbon button. The list bonds on the right you can see all the available tabs. Select the checkbox next to the Developer tab and click Okay. From the Developer tab you'll see two sets of controls, form controls and ActiveX controls. Form controls are designed specifically for use on a spreadsheet, whereas active X controls are typically used on Excel user forms. Because form controls can be configured far more easily than their active X counterparts, you should generally use form controls. In this section, you'll discover how to incorporate various controls such as buttons, checkboxes, and scroll bars into your dashboards and reports. Also, I present you with several solutions that you can implement. Let's dive in. 72. Interactive Chart with Drop_down Lists (Combo Box): Excel offers a set of controls called Form controls designed specifically for adding user interface elements directly into a worksheet. After you place a form control on a worksheet, you can then configure it to perform a specific task. Today we will learn how to dynamically change our chart data using a combo box. The combo box control allows users to select from the drop-down list of predefined options. When an item from a combo box control is selected in action is taken with that selection. So let us get started. Look at the data table from H7 E 12. It presents sales per product category and year. Sometimes we want to present a chart that we can select the year we want and not all years. Using a combo box, we can select the year we want and the chart will change dynamically. Near the raw data, reserve a cell where the combo box will output its value, cell F2. In this example, this cell will catch the index number of the combo box entry selected. Click on cell F1. It is the cell in which we would like to place the combo box. Go to the Developer tab and from this arrow of the insert icon, click on the Combo Box or form control. As you draw the box, hold the Alt key, we want to be perfectly aligned with the cell. The next step is to format the control, right-click and choose Format Control. New dialog box window appears. Click the Control tab to see the configuration options. The Input Range setting identify the range that holds the predefined items that you want to present as choices in the combo box. The input range will be for the years from 88 to A12 cell. The cell link box enter the cell that you want the combo box to output its value. That will be cell F2, just below the combo box. The cell will be used for the index formula later. A combo box control outputs the index number of the selected item. This means that if the second item on the list is selected, the number two will be output. If the fifth item is on the list selected, the number five will be output. In the drop-down lines box, enter the number of items that you want to be shown at one time. I have five years, so I'll type five or more if I plan to add years in the future, you can select the 3D shading checkbox if you want control to have a three-dimensional appearance, this is optional. Click Okay, now look at the combo box. When I click the arrow, it displays all the years. Let's test it. Select the year 2018. Notice that the number is changed to three because of the year 2018 is in the third row. You then create the analysis layer where the staging table that consists of all formulas, the chart reads from this staging table, allowing you to control what the charts sees. The first cell of the staging table contains the index formula. We are now ready to insert the index value. The index function converts an index number to a value that can be recognized. An index function requires two arguments to work properly. The first argument is the range of the list that you were working with. The second argument is the index number. Go to a2 cell, insert equal index, open parenthesis. The first argument is in the array. Let's look at the years from a, A2, A12, type a comma, and select the F2 cell with j is the row number. It will look at the number and then return the corresponding value. Make sure that the F2 cell will be absolute because we need to copy it. Press the F4 key and then the right parenthesis and look at the result. In this example, you're using the index number from the combo box in cell F2 and extracting the value from the appropriate range. Here's an eight dash 812. Again, notice the use of absolute dollar signs. This ensures that the cell references in the formulas don't shift when they're copied right at across. Let's copy this formula to the right. How does it work? Choose a different year from the combo box and look at the values on your left side. They've changed. After index formulas are in place, you have a clean staging table that you can use to create your chart. The final step is to select the data from A1 cell to E2 and create a chart, highlight these, go to the Insert tab from ribbon and select clustered column chart and place them somewhere there. We can hide F2 cell. Changing the color of the font to white. You can remove the grid lines and delete the border of the chart. Make sure that works. Let's make some tests. It works fine. 73. Dynamic Chart with Option Buttons: Option button allows users to toggle through several options at one time. The idea is to have two or more option buttons in a group. Then selecting one option button automatically de-select the others. Option buttons or radio buttons work in similar ways to list boxes. To add an option button to your worksheet. Click on the Developer tab. From the Insert button, Form Controls click on option button. Drawn beside the table like this. After you drop the control onto your spreadsheet, right-click the control and select Format Control from the menu that appears. Select the state in which the option button should open. The default selection unchecked typically works for most scenarios. So you'd really have to change this election in the selling box. Enter the cell to which you want the option button to output its value. A sixth cell is okay with me. The 3D shading is optional. Click Okay to apply these settings. By default and Option button controls output a number that corresponds to the order that it was put on the worksheet. For instance, the first option button you place on the worksheet outputs and number one, the second outputs a number to the third outputs and number three and so on. To give your option buttons and meaningful label, right-click on the control. Select Edit text from the menu that appears. Then overwrite the existing texts with your own. I'll rename it to tobacco. It's the first category of our table. To add another option buttons, simply click the button you created and paste as many option buttons as you need. The nice thing about copying and pasting is that all the configurations you made to the original persist in all copies. I will copy it and add three more buttons. Rename them with control, highlight them all so I can align them to the left from Format tab. Nice. If you want to use more than one set of options like this, then you have to group them from Developer tab, Insert button, and group box. When my buttons are in a group, this means that I can move them around and format them all in one go. Now I'm going to remove them. One of the ways you can use option buttons is to feed a single chart with different data based on the option selected. Let's see an example that you can use option buttons and upgraded chart based on them. Here I have a data table with four categories, tobacco, cosmetics, candy, and soft drinks, and the sales for each month. Now you could create four separate charts and show them all on your dashboard at the same time. However, using option buttons as an alternative, sales valuable real estate, by not having to show three separate charts. Plus it's much easier to troubleshoot format and maintain one chart than four. I want to give the user an option to select one of the four products. The first step is to create the analysis layer or the staging table that consists of all formulas the chart reads from the staging table allow you to control what the chart sees. Let's copy the first two rows from A1 to M2 and then paste them here from the eight-cell. Delete these. The first cell of the staging table contains the following nested IF formula. The formula turns Excel to check the value of cell A6, the cell where the Option button now put their values. If the value of the A6 cell is one, which represents the value of the tobacco category. The formula returns the value in the product dataset cell A2. If the value of a cell A6 is two, which represents the value of the cosmetics category. The formula returns the value of the product dataset cell A3. It, the value of the cell A6 is three, which represents the value of the candy category. The formula returns the value in the product dataset cell A4. If the value of the cell E16 is for which represents the value of the soft drinks category. The formula returns the value in the product dataset cell A5. Otherwise it returns a space. Notice that the formula uses absolute references with cell A6. That is the reference to cell A6 and the formula is prefixed with dollar signs. This ensures that the cell references in the cell formulas don't shift when they're copied right? And across. To test the formula is working fine. You could change the value of cell A6 manually from one to four or to click the option buttons. When the formula works, you can simply copy the formula across and right to fill the rest of the staging table. When this end up is created, all that's left to do is create chart using the staging table. It's time to create a chart, highlight the cells. I will quickly insert a column chart from the recommended ones. I'll move it below the data and increase the size of it. Makes him formatting like delete the grid lines and R chart is ready. You could move the option button inside the chart like this. In the upper left corner. I hope you understood how to create and use option buttons in a chart. 74. Dynamic Combo Chart with Check Box: Alright, let's talk about checkboxes. In this lecture, we're going to learn how to make a checkbox and how we can use it for a dashboard, the checkbox turns off or on a value that indicates an opposite and unambiguous choice when a checkbox is selected or returns a value of true. When it isn't selected, false is returned. Checkboxes are one of the simplest yet effective form controls to use in a dashboard. So let's get started. In this worksheet, you can see a table with three columns, month's sales for the year 2020 and sales for the year 2019. On your right through as a column chart showing each month with a sales for the year 20, 21st of all go to the Developer tab. Now from the Insert button, find the checkbox and click on it. Draw it somewhere here on the top right side of the chart. Changed the name of the checkbox to show 2019 sales. The second step is to format it. Right-click and select Format Control. As you can see, there are six tabs in this dialogue box. You can change the fill color, the line color, the size of the checkbox, et cetera. Leave it as it is for right now. From Control tab we can see some control properties under value. You have the initial state of the checkbox. We want to display clear checkbox. So click unchecked. Now from the cell link, you will enter a cell reference that contains the current state of the checkbox. Highlight A1 cell and check 3D shading also. Press Okay. When the checkbox is selected, the linked cell returns a true value. When the checkbox is cleared, the linked cell returns a false value. With the linked cell is empty. Excel interprets the checkbox state as false. We can use that information to control the data that will display in our table. Now we will copy the cells from 2019 sales and paste them here starting from D17. This is the analysis layer or the staging table. The chart will read the 2019 Trendline from these cells, delete the values and start typing. For the 2019 sales. Test the value of the A1 cell, the cell that contains the output from the checkbox. If A1 reach true, you reference the respective 2019 cell in the raw data. If one doesn't read through the formula, uses excels and a function to return an error. Excel charts can't read a cell with an N A error. Therefore, they simply don't show the data series for any cell that contains an a. This is an ideal when you don't want to have a data series to be shown at all. Notice that a formula uses an absolute reference with A1 cell, that is the reference to cell A1, and the formula is prefixed with the dollar sign. This ensures that the column references in the formulas don't shift when they're copied across. The last step is to add this dataset to the chart. So right-click on the chart, select data. Add a new series. As series name highlights the cell D7. As series values highlight the cells from D10 through D12, IE9. Press OK and OK again. The columns from 2019 sales show up from the chart design tab click the Change Chart Type button. Selected combo box. For 2019 sales. Select a line chart. Press Okay. If the checkbox is true, then the line chart of 2019 sales appears in the chart. Otherwise the chart shows only the 2020 sales. You could apply this technique to as many checkboxes as you need. The technique is simple and it's been used a lot in dashboards. Some other ideas that you can move the chart over and table if you want to hide data, or you can change the font color of a one-to-one white if you don't want the others to see it. I hope you've found this lecture helpful. If you have questions, please leave a comment and I'll answer as soon as I can. Finally, if you want to leave me an honest review, I'll really appreciate it. Thank you, and I'll see you in the next lecture. 75. Dynamic Column chart: Change Countries using List Box: Loose boxes are another great tool for creating dashboards. The list blogs controls allows the user to select from a list of predefined choices when an item from the list box control selected in action is taken with that selection. In this video tutorial, we're going to create a list box of countries to control two charts, sales and net sales will use three worksheets. The data with sales and net sales for each country in each month. The analysis worksheet that'll help us create our interactive charts. And finally, the presentation G, which is our storefront. We're also going to use two functions, index and column. I'll show you all the steps in detail. So don't worry if it sounds complex to you right now. Keep in mind that one of the key concepts of the data model is the organization of data into three layers, data analysis and presentation. Go to the presentation sheet and let's get started from Developer tab, Insert button, click list box and drawn somewhere here. Right-click and choose format control. The input range is going to be on our data sheet from B3, B4. It's the cell range with countries. The cell link will be on our analysis sheet, cell A2. Check 3D shading and okay. If you want to try, go to the sheet. Select the third country in from the analysis sheet. See the number three. We're going to use the index function. Index returns a value of a reference to a value within a table or range. Highlight B2 cell and type in equal index parenthesis. The array is the cell range with the countries. So go to the data sheet and select D3 to be 14. For row number, highlight a two cell from the analysis worksheet. We don't need to insert a column number. So close the parenthesis and enter. The formula returns the name of the country based on our selection from our presentation worksheet. Select for example, the USA, which is the seventh item on the list and see the results. Now we're ready to insert the formula for January sales type equal index. And now we're going to select all the sales data from S3 to enforce with absolute reference. We use absolute reference because later we're going to copy the formula to the right until the last month. For row number highlight again the A2 self from the analysis worksheet. Because now our data range covers more than one column and we have to use the column function. The column function returns a column number of the given cell reference. Select C2 cell, but we want to write it as a range. So type s2, s2 and find the first reference. As we are going to copy the formula, it will change only the second reference, close parenthesis and Enter. It returns this amount, which is correct if we check it from the data sheet, copy to the right and check again the amount. Great, they are all correct. We managed to create the data that we need to make our chart. Highlight the cell range B2 to N3. From the Insert tab, choose clustered column chart. Right-click on it and select Move Chart. We want the chart to be placed in the presentation worksheet. Move it somewhere there. Let's change the country from the list box. Works fine. As you can see. We will follow the same steps to create a second chart for net sales this time. Let's make it very quick to not always time. We learned list boxes, a great tool for dashboards. 76. Interactive Line Chart with Scroll Bar: Scroll bar enables a user to scroll to a value or position using a sliding scale that can be moved by clicking or dragging the mouse. Scroll bar is amazing and probably my favorite form control. It's simple but powerful. The basic idea is that you can link the scroll bars value to any available. So on a spreadsheet as the scroll panel, that's the gray bar between the upper and lower paddles increases, so does the value in specific cell. Similarly, as it decreases, the value in the same cell decreases. In this lecture, we will learn how to create dynamic charts that uses a scroll bar. And as we click on it, the element of the chart will appear piece by piece. Let's go to the Developer tab first and click on the Insert button. From form controls, we want to select scrollbar. We wanted to design it vertically from cell D3 to D15. Beside the sales column. The sales column has only one value, which is a function I will explain later. All the sales values are in column K, which you can't see because we decrease the size a lot. Look at that. They are somehow hidden. So make sure to transfer your values data in a separate column. Now right-click on the scroll bar and choose Format Control. You can use the minimum value and maximum value fields to set the upper and lower bounds of the scroll bar. January has 31 days, so the minimum value is one and the maximum value is 31, which is the last day of January. The current value is one. You can also use the incremental change field to set how much of the value increases or decreases when you press the scroll bars paddle. I'll leave it to one. Finally, the page change field refers to how much of an increase or decrease occurs when you click into the scroll bar itself and not on the upper or lower panel, leave the value to ten. We have to link the values with a cell. Let's click on the cell. You can enable the 3D shading if you want. Click Okay. Press down or upper arrow and see how the value changes in cell C1. The length cell doesn't have to appear, so it's better to hide it by moving the scroll bar or change the color font to white, but I will do it later. Well, let's create a line chart based on our table data. Highlight the data from A1 to be 32. From insert tab choose line chart with markers moving beside the scroll bar and make it bigger. We will notice that when the chart is empty except for the marker of the first day of January, no sales in it. This is normal as the sales column is also empty. The horizontal axis has the days of January from the first of the 31st of the month. Look at the cell B12. This is the secret IF formula that pulls all the data together. C1 was linked to the cell from switch box days, the function that shows the exact day of our date, for example, for the cell A3, the day is 29. The formula here is checking to see if C1 is greater than or equal to the entry on the left. If it is, it displays the sales for the specific cell dates, the sales data from column k, As we said before, for the first aid C1, which is one, is equal to the a2 cell, which is also one. If the value was way less than one, it would display the error NA, no value available. Now go to the B12 cell, drag down until cell B32. We can see the error and a, which means no value is available. Click on the down arrow from the scroll bar and look at the data which is in the chart. When the value of the C1 cell increases to two, then the sales and B3 appear because value two is equal to day two and so on. Now how cool is that? The NA error doesn't display on the chart. However, if you want to somehow remove the error, highlight the cells from V2 to B32, go to the Home tab, click the Conditional Formatting button and select a new rule. The rule type we want to use is a formula to determine which cells to format type in a box equal DNA. B12 in parentheses. Click on the Format button, fall on tab and from color select white. Click the Okay button twice. The Excel ISN a function returns true when a silica contains the NA error and false for any other value or any other error type. You can use the ISN a function with the IF function tasks for an error. So if there is an NA in the value of a cell, make it white. Pretty straightforward, right? Let's load the scroll Barnum, make sure they are all fine. Finally, let's make some changes to the chart. Delete the title in the grid lines. Also remove the border of the chart. Highlight the C1 cell, and change the font color to white. Put a fill color between cells D1 and Q nine. The last change has to do with the horizontal axis. Make sure to display the dates vertical. To do that, right-click on the axis, format axis, and from the number category changed the type. Also change the value from major unit to one. This is how you can use the scroll bar to create an impressive dynamic chart. I hope that from now on you will create fantastic dynamic charts for our business presentation or anything else. 77. Traffic Lights Dashboard: Part 1: In Excel, you can create dashboards using a traffic light technique. The following section takes you through how to create an Excel dashboard with traffic lights. The Excel dashboard uses generic organizational data to display products sale data, and shows total sales per month graphically at the side. In the dashboard there is a traffic light effect indicated color that depends on the range of sales. Also, we have a list of the month to choose from. Finally, we will design some sparkline charts showing the sales per month. Here's the end result. Let's dive in. In this workbook, I have two worksheets, dashboard and calculations. In dashboard we have a sales table with four Office products, Windows, Word, Excel, and PowerPoint, and the sales for each of them during the year. Also, I have a column with total sales in cell B9 and we'll create a list of months so that when we choose a month, it will appear with the sales until this month. Now let's go to the calculation sheet. We have a column B with the name of the products, column C with the sales in them and the indications of the traffic lights, red, yellow, or green. Also, we will calculate the average sales of the low, high range of the sales. Go back to the dashboard worksheet. Select B9 cell. We want with the help of the data validation technique to create a list of months. So when we choose a month, it will calculate the total sales until this month. Go to the Data tab and click the data validation drop-down arrow. You will see three choices. Choose the first one, data validation. The data validation dialog box appears from the Settings tab and validation criteria. Press the down arrow and select the list option. As I said before, I want to create a list of months to choose from. The source will be the name of the months from C2 to N2. Whereas this red arrow and select the cells. Then again from the red arrow, press okay, and, and the list of months is ready. You can try it if you want. What we want to do now is that we'll choose a month. That month will appear with a filled color cell. To do what we just did, You will use the conditional formatting feature. The first step is to select the cells from C2 to N2, the month names. Now from the Home tab go to conditional formatting. From this sub menu select new rule. The New Formatting Rule dialog box appears in the screen. From role-type options, I will select this one. Format only cells that contain. Now let's edit the rule description from the second selection I choose equal to. Let's explain that. We want to format only the cell when the cell value is equal to B9. From the third box, press the right arrow and click on the B9 cell. The next step is to select a color for the selected month with the Format button. Click the fill tap, press the fill effect button. And as the second color, pick the red one, we made a gradient fill with these two colors. Press the OK button three times. Now I will select a specific month. Excel highlights the name of this month with the color that we choose. It's time to calculate the total sales from column O for each one of the office products and until the month we choose, we will use the offset and match functions. Make sure you have watched the lessons for these two functions from the formulas section. But let's make a quick recap. Offset function returns a reference to a range that has a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single-cell or a range of cells. You can specify the number of rows and the number of columns to be returned. In cell T2, I have typed the syntax for the offset function to understand how the offset function works, Let's make an example. Go to sell a 20 and type equal offset. Press Tab. And type C3 cell is a reference or a starting range if you prefer. Type comma and then three for rows, it means move down three rows. Negative numbers mean to move up type comma and two for columns, it means move to the right two columns, negative numbers mean a move to the left. The other two parameters are the number of rows and columns that you want the return range to be. I will leave them empty and hit Enter. The function returns 207, the value of the E6 cell, which is correct. The match function searches for specify an item in a range of cells and then returns the relative position of that item in the range. For example, go to the A21 cell and type equal match parenthesis 70 comma s3 to N3 comma 0, close parenthesis and enter. The formula returns the number one because 70 is the first item in the range. Go to the cell O3 and type equal. We want the sum of the sales parenthesis offset. S3 is the starting range comma 0, because I don't want the cell to move down comma 0 because I don't want the cell to move to the right. Comma one comma match. B9 is the month we choose range of months, s2, n2, and 0 because we want the exact match. Close parenthesis, and Enter, copy the function to the other cells in the total sales per product are ready. For example, if I select February from the month list, then the total sales for the Windows product or 150, which is correct. Because the sales for January is 70 and the sales for February is 8070 plus 80 is equal to 150. Great. In the next lesson, we will continue with the calculations worksheet. Thanks for watching. 78. Traffic Lights Dashboard: Part 2: All right, let's continue with the formulas. In this lesson, we will use the nested IF and the average functions. So let's get started. Click on the calculations worksheet and highlight the C3 cell. We will associate this cell with the corresponding cell from the dashboard worksheet. So type in equal, click on the Dashboard Sheet. And click O3 cell, which is the total sales for the Windows product. Press Enter. We made a relative reference to the three-cell to refresh our memory, the cell reference in Excel is a cell address. It tells Microsoft Excel where to look for the value you want to use in the formula. Now copy the formula up to the C6 cell. That's it. The formula is copied to the other cells with relative references that are adjusted properly for each cell. Let's click on the C8 cell. We will create a formula to calculate the average total sales. So type in equal average parenthesis, return to Dashboard worksheet and highlight O3 to O6 cell. The total sales for all of the products. Close parenthesis and press the Enter key. Here is the average value for total sales. Now based on the average sales, we will create a range of two. Prices are high and a low. If the sales of a product are lower than your average price of the range, then we will have a red light. If they are between low and the high price range, then they will have a yellow light. And if they are greater than the high price, it will have a green light type, low and high two cells, D19 to D11. To calculate the low range, we will multiply the average sales by 80% or 0.8. This is a value that we set arbitrarily. Click on C9 and type equal C8, multiplying by 80%. The result is 252.6. Click on the C11 cell to calculate the high range, we will multiply the average sales by 120% or 1.2. Let's create now the traffic lights starting from D3. First we will create the formula for red lights, will use the IF function like this, equal IF parenthesis C3, which is the total sales of Windows product less than or equal to C9 with absolute reference to lock the cell. Comma double quotes, equal sign double quotes again, comma and double quotes two times. Close parenthesis and press Enter. The sales of a window product or lower than the low price of the range, then switch to the red light. We will use a symbol equal because we will convert it later to a bullet. Copy the formula down to cell D6 and change the font color to red. The next step is to create the formulas for the yellow lights. Let's move to the E3 cell and type equal IF parenthesis s3, which are the total sales for the Windows product less than C10 with absolute reference, C11 cell is a high range with the average of sales comma, if parenthesis again, C3 greater than C9 with absolute reference. C9 is a low range of the average of sales. Comma double quotes equal double quotes again. Comma double quotes two times. Close parenthesis, comma double-quotes again two times, and close the last parenthesis. Let's explain it because it's a bit tricky. If windows sales are less than the high price range and greater than the low price range, then type equal otherwise type blank. Copy the formula down and change the font color to yellow. Let's finish with the cell F3 and green lights type equal IF parenthesis C3 greater or equal to C11. With absolute reference comma double quotes, equal double quotes again. Comma and double quotes two times. Close parenthesis and press Enter. If the sales of windows are greater than the high price of the range, then switch on the green light, copy the formula through all cells and change the font color to green. To convert an equal symbol into a bullet, select the cells D3 to F6 and choose web dings, fonts. Put also an outside border and we're ready. 79. Traffic Lights Dashboard: Part 3: All right, Let's start designing the traffic light. Return to the worksheet dashboard will start to design our dashboard. First of all, insert the image of an empty traffic light. I have included the image from the resource files, but you can also find an empty traffic light from the Internet with a simple search. Let's insert the image. We have four products, so we need for traffic lights. Copy this traffic light image and paste it three times. Pleased to traffic lanes and one next to another. It is recommended to select all the images into align them middle. Below each traffic light, we can add a title from the Insert tab. The shapes select rounded rectangle. Carpet the shape and paste it three times. Align them and add a title for each of them by right-clicking each one of them and edit text like this. The titles of the traffic lights are ready. We'll make some formatting to the dashboard, decrease the width of a and P column and decrease the height of row one. Copy the cells B2 to B6 into cells B9 to B23. The gray color to the cell range from B7 to 018, fill with dark gray color from the following cell ranges A1 to P1, A1 to A24, P1 to P2 for a 24 to P24. Also remove the grid lines from the View tab. Here is the trickiest part of our dashboard. We have to find a way so that the bullets of each calculations worksheet appear in the empty traffic lights dashboard worksheet and the right order. For this purpose, we will use the Excel Camera Tool. If you didn't watch the video tutorial about the camera tool, I suggest doing it now. If you forgot how to add it into the Quick Access Toolbar, click down arrow, select more commands and from the new dialog box, go to Choose commands from option, select all commands. Find the Camera Tool icon and press the Add button. Go back to Calculations Worksheet and highlight cell D3. Click on the camera icon, return to the dashboard sheet. And click inside the first traffic light shape. Highlighted and remove the picture border from the Format tab. Selecting no outline. Remove also the fill color from the Home tab and bucket icon, select no fill. Increase the size of the light to fit exactly to the empty space of the first traffic light. Now copy the light, paste it two times. Move them just above the other lights. We had to fill the other three traffic lights. So to do it quickly, we can highlight all of them with the Shift key copy and paste them three times. Police them exactly inside the gaps. Keep in mind that each bullet is connected with the corresponding cell. With that being said, if you click on the first light, you can see the formula from the formula bar. We have to change all the lights with the correct cells. Let's get started. Click on the first light and check if it's correct from the calculation sheet and D3 cell. The red light for the Windows product is correct because the sales are 150, less than 252.6. Return to the dashboard sheet. Similarly, I will change the cell for D4 for word product, d5 or Excel product in D6 for PowerPoint product. Let's continue with the second signal, which is the yellow light. Click on the first product and the shape. Replace column D with column E and with the correct row. Do the same for the remaining yellow lights. The same for the green lights. In the next lesson, we will put the finishing touches and create sparkline charts. 80. Traffic Lights Dashboard: Part 4: Now it's time to create sparkline charts. First, Let's do some formatting with the help of the control key merged the cell ranges from C20 to F20, C21 to F21, C22 to F22, and C23 to Fe2O3. From the Home tab, click the merge and center button. In the same way, merge the cell ranges age 20, decay 20 and so on. Highlighted cell C120 from the Insert tab. Click on the line sparkline button. New dialog box appears. The data ranges from C3 to N3. Location range is C120. Click OK. And the first sparkline chart is ready. Do the same steps for the remaining products. Move to the age 20 cell. This time, select the column sparkline. Fill the correct information in the dialog box and click Okay. Do the same steps with the other products. Adjust the height of the rows. We managed to create an impressive Sales dashboard with traffic lights and sparklines. These techniques can be used on any dashboard. It was a bit tricky of a video tutorial, but I think it was worthwhile for this lecture. Feel free to download the template and use it. Thanks for watching. And if you have any questions, please post a comment and I'll answer as soon as possible.