Hi everyone, Kevin here. Today, we're going to learn how to make a chart in Excel. Charts are a great way to visualize and analyze your data, and Excel offers a variety of different chart types.
You have line charts, pie charts, pivot charts, and many more. We're going to start with the basics of creating a chart, formatting it, and then also modifying it, and we'll also cover some tips and tricks to make your charts more effective. Let's check this out.
Here I am in Excel, and if you'd like to follow along today, I've included a link to this workbook in the description of this video. On this worksheet, I have all of this data for the Kevin Cookie Company cookie sales, and currently it's just in a table format. Now I can look at it and try to make sense of it to see what was the best month or what was the worst month, but I think it would be a lot easier to understand and also analyze if I inserted a visual, say a chart.
To insert a chart, highlight all of the data that you would like to analyze. I'll select the headers, the month, and cookie sales, and then highlight all of this data down below. I also have the total at the very bottom and let's exclude that, and in a little bit, I'll show you why it's generally not a good idea to include the total down at the bottom.
With all of this data highlighted, up on top, let's click on the insert tab. And right in the center, we have a category for charts. Over here, we have recommended charts.
That's where Excel looks at the data that we've highlighted and it recommends the best chart for that data. And over here, maybe you already have a chart in mind and you can choose one of these types right here. Let's see what Excel recommended by clicking on recommended charts.
This opens up the insert chart dialogue, and here we can see all the different charts that Excel recommends based on the data that we selected over here. And at the very top, we see that Excel is recommending a line chart. Over on the right-hand side, we can see an example of what this line chart looks like.
And here we can see that, look, January is the worst month of the year. Everyone has a new year's resolution to not consume as many calories or sugar. And here, as we approach the end of the year, for whatever reason, people seem to forget about calories altogether and they buy a lot of cookies.
Over on the left-hand side, we see a few other chart types. Here we see a variation of the line chart where it highlights the different data points. We also have a column chart, and that could be a good way to represent this data.
We also have an area chart and then also a funnel chart. Funnel chart probably doesn't work as well, but we have a few different options here. Up at the very top, we can also jump into the All Charts view.
Let's click on that. Within All Charts, over on the left, you'll see all the different chart types that you have available within Excel. And you have over 17 different types.
And when you click on any one of these chart types, you also have variations of it. Here, for example, in column chart, here we have a clustered column. You could have a stacked column, a hundred percent stacked column, and you also have some 3D variations of that.
So, there are many different options that you can choose from. On the left-hand side, you'll find that you'll tend to use the top four most often. A column, a line, a pie, and a bar.
Those are the most popular, and then the ones down below, these tend to be more specialized where you'll use them in unique situations. Let's click into line chart up above. And here again, we see an example of what our data looks like as a line chart.
When I hover over it, you'll see a large version that shows you what this will look like. For this data, since we're trying to show changes over time, I think a line chart will work the best. Down below, let's click on okay.
This has now inserted a line chart and it's so much easier to understand what's happening with this data by looking at a visual versus trying to make sense of it in this table. When I click on the chart, you'll notice the table highlights, and this is showing me what data is being used to populate the chart over here. This is the selection.
Now I could also change the data that's being fed into the chart. Say for example, I just want to look at the first three months of the year. I'll go down to the selection area and I could change it just to look at the first three months, and when I change the selection, that also updates the chart.
So here I just see January, February, and March. Let's say I just want to look at the last three months of the year. I could also click on the selection, drag it down to the bottom, and now we're just looking at the last three months.
I'll adjust this so we're looking at everything once again. Earlier, I mentioned that you generally do not want to include totals in your chart. Let's expand the selection to see why.
I'll include the year total down at the bottom. Now we see this massive spike for the total and it overwhelms all the other data, and it's really hard to discern if there are any trends in this data. So, let's deselect that just so we have all of the individual months showcased in the chart.
You can also select non-contiguous periods for your chart. Let's say I just want to look at January through March and then also October through December. I'll press the control key and select the first three months, and then I'll press the control key and select the last three months.
Let's go to insert up on top, and for this, let's try a column chart. I’ll click on this button and let's insert this right here. We see the first three months and the last three months.
I don't think this analysis really makes sense, but it shows that it is possible to select different pieces of data. I'll delete this chart by pressing the delete key, and here we have our line chart again. Although we decided to go with a line chart, you may be having second thoughts and maybe you'd actually like to try a different type of chart.
With the line chart selected, up on the top tabs, we have a new option for chart design. Click into that, and over on the right-hand side, we have the option to change the chart type. When I click on this, that again opens up that dialogue where we can choose from any one of these types of charts.
Let's say actually I want to go with a column chart. I’ll select this, select this option, and then click on okay, and that changes the type. Now that we've inserted a basic chart, next I want to show you a trick that'll help you make charts fast.
Select the cookie sales chart, and then let's press the delete key to remove it. Now don't worry, we'll get it back very, very fast. Over on the left-hand side, highlight all of the data, and instead of going up to insert and selecting the type of chart, instead on your keyboard, press the alt key together with F1.
And check that out, we now have a chart on our sheet again. That was really easy. By default, using that shortcut key combination inserts a column chart.
But let's say maybe you want that to be a different default. Up on top, let's click on change chart type. And let's say that maybe instead of a column chart, you want it to be a line chart.
Let's click on line, and right up here let's say we want this to be the default. You can right-click on it, and here I can set that as the default chart. I'll select that, then let's click on okay, and this has now changed it to a line chart.
Let's now delete the chart one more time, and let's look at yet another shortcut key that you can use to very quickly create charts. I'll highlight all the data over here on the left, and this time let's press the F11 key. And this inserts a chart.
Here we see that by default it used a line chart because we set that as the default. This is now on its own separate worksheet, and it's not alongside the data. Maybe you prefer this view, so that's another shortcut key that you have available to you.
But I'd rather have it alongside the data. Up on top within the chart design tab over on the right-hand side, you can move the chart. I'll click on this, and currently it's on a new sheet, but let's move it to this line chart sheet that we were previously on.
I'll select that, and then click on okay, and here we have it alongside our data again. Next, I want to start modifying my chart, starting with the title. Up on top it says cookie sales, but I'd like to be a little bit more specific.
Instead, I'd like to say Kevin Cookie Company Sales 2026. To edit one of the elements on your chart, you can simply click on it, and here I can highlight all of the text, and I'll delete that, and in place of it, I'll type in Kevin Cookie Company Sales 2026. Now as a little trick, instead of just typing in the text, you can also make this dynamic, so it uses one of the cells on your worksheet.
Right up on top, I can type in a formula for the title. I'll type in equals, and then I get reference cell A1, which says Kevin Cookie Company Sales 2026. I'll hit enter, and that's now looking at that cell.
Now one of the neat things is if this cell value changes, let's say instead of Kevin Cookie Company, I enter in KCC Sales 2026, hit enter. Over in the chart, you'll see that it automatically updates based on the value in that cell. It's a neat little trick.
Looking at the chart, it's not quite clear what this number refers to. Is this the revenue? Is it the number of cookies sold?
It would probably help to clarify that by maybe including a label here or an additional chart element. I'll click on the chart, and over on the right-hand side, we see these three different squares, and up on top, we have a plus icon. Let's click on this.
This allows us to add different chart elements. You could turn on or off any one of these different categories. And if you're ever unsure of what something is, you can simply turn it off to see what happens.
I'll turn off the axes, and that's important information to show, so I should probably turn that back on. Now, I want to include a label alongside this vertical axis. So right here, we have axis titles.
That sounds right, so let me check this box. When I check this, this adds a title under the date and alongside the numbers. I think the date's pretty clear what that is, so I don't think I need that down there.
Now, when I hover over axis titles, we get this icon. And when I click on this, this gives me additional options. And here, we have a primary horizontal and primary vertical.
Now, I think these are pretty clear, but if you're ever unsure of what something is, again, you could simply turn it on or off to see what it does, or you could also hover over the different elements. When I hover over the date, here you see that this is the horizontal axis. We get that little tool tip.
Or here, when I hover over the numbers, we see that that's the vertical axis. So, I want a label next to the vertical. So over here, let's turn off the horizontal, and now I just have a label here.
Just like we did with the title, I could highlight all this text, and here, this is the number of cookies sold. So now I have that label there. That looks a lot better.
I want to add one more chart element that shows a trend line. I'll click on the chart, and let's go back to that plus icon. Here you see all those different chart elements.
And down at the bottom, there's the option for a trend line. When I hover over it, it shows me a sample of what it'll look like on my chart. I think that's a good addition.
I'll check this box, and that's now included a trend line. Underneath the plus icon, I also have other options. For example, I could change the chart style.
And just like with the different elements, I could hover over and see what change it'll have. Right up on top, I could also adjust the color, and at the Kevin Cookie Company, we have a light blue color for our brand. I’ll select this option, and that way it aligns with our brand.
Down at the bottom, you can also filter the data. Let's say, for instance, that I don't want to include January. It was such a bad month; I'd rather not show it on the chart.
I could deselect January, and then down at the bottom, click on Apply. And that's now removed it from my chart. But that might be a little misleading.
I should probably represent the entire year. I'll click on Select All, then Apply, and that brings it back and turns off that filter. When the chart's selected, up on the top tabs, you'll see that we have two new tabs.
If you click away from the chart, you'll see that those tabs disappear, and when I click back into the chart, you'll see them reappear. One of them is called Chart Design, and the other is Format. Let's click into Chart Design, and you'll see many of the same options as what we saw with these squares on the side of the chart.
For instance, we can turn on or off the different chart elements. Here, you can see all those different elements. We also have something called Quick Layouts, and this adds or removes different elements from your chart.
Maybe one of these quick layouts has what you need, so it might make sense going through this to very quickly format your chart. I'll stick with what I have. You could also change your chart colors, and here, too, we could also choose from all of these different chart styles.
Again, you have many ways to get to the same functionality. Up on top, we can also click into Format, and this allows you to make formatting changes to your chart. I could select the title, and let's say I want to add a background.
I could do that. I don't know if that color makes sense, and there I could remove it. I could also change the font color.
Maybe we go with a blue for the title as well. You could click on the different elements, and you can modify them. For example, let's say I want to go with a slightly different shade of blue.
Let's go with maybe this one right here. I could change the color all using the Formatting tab up above. I've now made several different formatting changes to my chart, and perhaps I'd like to use this for other data in the future.
I can save all of these different changes as a template. I'll right-click on the chart, and here's the option to Save as Template. I'll select that, and I can now give this template a name.
I'll stick with the default and then click on Save. Let's now highlight all of this data, and then we'll press the F11 key. That's the shortcut to make a new chart on a new sheet, and we see that it doesn't have the coloring for the line, the title, and I'd like to apply all of that.
With this chart selected, let's go up to Chart Design and then change the chart type. On the left-hand side, there's a folder for Templates, and here I see the template that I just created. I can click on OK, and look at that.
It automatically applies the title style, the line coloring, it adds a trend line. To manage all of your templates, with the chart selected, up on top, let's click on Change Chart Type. On the left-hand side, click into Templates, and then in the bottom left, you have the option to manage your templates.
You can delete, move, or rename any of your templates. Let's click on Cancel and then go back to Line Chart. So far, we've looked at a number of different ways that you can modify the way your chart appears, but what if you want to adjust how high the vertical axis goes, or what if you want to forecast forward with the trend line?
To modify any of the specific elements, simply double-click on it. Here, I'll click on these numbers in the vertical axis, and that opens up a pane over on the right-hand side that gives me additional controls. I could click on the paint bucket, and here you could adjust things like the fill, or the way the line appears, or the transparency, and many different options.
You can also apply a shadow, a glow. You could adjust the alignment. And over in the last one, we have some axis options.
Currently, it goes up to 90,000 cookies sold, but if we look at December, it goes right under 80,000. I could set the maximum bounds to 80,000. Adjust that, hit Enter, and here we see it's now adjusted it.
I want to forecast forward with the trend line. I’ll double-click on that, and that modifies the right pane to now look just at the trend line. Here, too, I could also adjust the fill, the line, the shadow, the glow.
And here, I also have trend line options. I could adjust the type of trend line. Currently, I have it set to linear.
And if I go down, I could also forecast forward. So, let's say I want to see 12 periods forward. I'll type in a 12, hit Enter, and now we're forecasting forward.
Look at that. The cookie sales are up and to the right, exactly what we like seeing in this business. In the top right-hand corner, I can click on the X to close out this pane.
On second thought, I don't know if I need this trend line on my chart. I think it's already pretty apparent that we've been growing nicely throughout the year. I can click on the trend line on my chart, double-click on that, and then I can press the Delete key, and that'll remove the trend line.
Alternatively, I also could have clicked on this plus icon, and I could remove the elements here, but I think delete's just a little bit faster. The chart is now looking good, but I want to position it in a slightly different spot. To move your chart around, simply click on it, and you can drag and drop it anywhere on your sheet.
When you click on the chart, you'll notice these handles around the edges. You can click on those and then drag to adjust the size. Here I can make it smaller.
I could also make it wider. If I press the Shift key, that'll maintain the height to width ratio. You can also press the Alt key, and that'll lock the chart to the different cell boundaries.
So that way you can get a little bit of a cleaner look. One thing to be cautious of when you make, say, a line chart smaller or less wide, it makes changes look more extreme. And here, if I make it a lot wider, it'll make changes look less severe.
So just keep in mind that the way you position and lay out your chart can very much affect the way people interpret the data within the chart. Down at the very bottom, let's now switch into the row and column worksheet. On the row and column worksheet, here we see four different cookie types and the associated sales over six months.
Let's press Alt F1, and that'll automatically insert a chart. And here we see all the cookie sales grouped by month. So here in July, we could see what the sales were for chocolate chip, oatmeal raisin, sugar, and fortune.
But maybe instead of grouping by month, I'd rather group by the cookie type. With the chart selected, up on the top tab, we have chart design, and there's a button here called switch row and column. Now it's not entirely clear what that means.
What that's doing is it's taking the date and it's taking these rows and it's going to swap them. So, when we click on this, instead of organizing it or categorizing it by the month, it now categorizes it by the cookie type. So here we see chocolate chip for July all the way through December.
So just a quick way that you could toggle back and forth to categorize your data in different ways. Let's now click on the chart and then press delete, and let's see how we can chart out how these sales performed directly in the cell. To insert a chart directly into the cell, let's highlight all of the data that we would like to include in a chart.
Then up on the top tabs, click on insert. And this time, instead of using the charts category, let's go over to this other category called sparklines. This allows us to place a chart directly in a cell.
Let's click on line. This opens up the create sparklines dialogue. And here we've already identified all of our data.
It's in this range of cells. Over here, we can choose where we want to place our sparklines. I'll highlight all of these cells and then click on okay.
This is now inserted a sparkline directly onto my sheet that helps visualize what happened to this data over this time. With the sparkline selected, up on top, we have a new tab called sparkline. And over here, you have all sorts of different ways that you can adjust the sparklines.
Maybe instead of a line, I'd like to see it as a column or a win-loss. So, you have all these different options. You can also indicate whether you'd like to see the high points or maybe the low points and a few different options here.
You could also adjust the style, the sparkline color, also different marker colors. So again, lots of different options and controls that you can play with. Down at the very bottom, let's now shift into the pie chart worksheet.
On the pie chart worksheet, here we have different cookies that we sold here at the Kevin Cookie Company and the numbers sold. But just looking at this table, it's hard to get a sense for what part of the whole each cookie type made up. So, with chocolate chip, more than half, less than half.
It's hard to tell, but I think a pie chart can help us with this. Let's press Alt F1 and that inserts a column chart. But this doesn't quite answer that question.
So, with this chart selected, within chart design, let's click on change chart type. Over on the left-hand side, let's click on pie chart. And here I'll stick with the default and then click on okay.
So here we have a basic pie chart. But which one is chocolate chip? Which one is oatmeal raisin?
I could hover over and I get a tool tip that tells me that this dark blue is chocolate chip. But other people won't know that. Up in chart design on the left-hand side, let's click quick layouts.
And I could test out a few of these different quick layouts to see if this provides the information that I need. And here this first design, I think this one works well. Here it places the name and the percent directly on top of the slice.
Now the color is probably not the best. I can click on this. Let's go up to format and I'll change the font color.
Let's say to white. I think that's a little more readable. So, there you could use a pie chart to effectively communicate the part of a whole that each individual item makes up.
Let's now shift into the pivot chart worksheet to finish up. This brings us to the last worksheet titled pivot charts. And this is perhaps the most powerful way to create charts in Excel.
If you've ever used pivot tables before, pivot charts are very closely related to pivot tables. If you'd like to learn more about pivot tables, be sure to check out the video in the top right-hand corner. On the pivot chart worksheet, to create a pivot chart, you'll want to organize your data in this way.
You'll want to place your column headers up at the top and then all of the associated data below the headers. Here, for instance, I have a column with cookie type and here I list out all of the different cookie types. And you'll notice that some of these cookie types repeat because each cookie type has some sales in an associated month.
And over on the right-hand side, we have the cookie sales. To insert a pivot chart, up on top with your mouse within the data area, let's click on insert and within the charts category, you'll see the option for pivot chart. Let's click on this.
This opens up the create pivot chart dialogue. Over here, we could select a table or range, but Excel has automatically identified the data. That looks good.
So, I'll leave it as is. You can decide where to place your pivot chart. You can place it on a new worksheet or an existing worksheet.
I'll go with a new worksheet and then click on okay. This now drops us on a brand-new pivot chart, but you'll notice there's no chart here yet, and that's because we need to start pulling it together by dragging and dropping different fields. Over on the right-hand side, we have a pane titled pivot chart fields.
And you might recognize some of these. We have cookie type, month, and sales. Each field aligns with one of the columns in the original source data.
Let's go back to the pivot chart. This is the beauty of pivot charts. You can start building your chart simply by dragging and dropping this data.
Let's say, for example, I would like to see a column chart showing or visualizing the total sales here. I'll click on sales and drag and drop it into values. You typically place something in values if you want to calculate something.
So here I see the sum of all of the sales, and it looks like we had almost $700,000 worth of sales. Now let's say I want to see that by cookie type. I'll go up to the cookie type field and I could drag that down to the categories and I'll release.
And now look at that, the chart automatically updates it to show the number or the total sales by cookie type. And let's say I'd like to see it maybe by month. I'll go up to this field, drag it into the legend, release, and here I see the month as the legend.
So here I see the type as the category and I see the associated sales. That was really easy to pull this chart together. Now I could also take these different fields and I could drag something into filters if let's say I want to filter it to a specific type of cookie, I could very easily do that.
With this chart, I can click into the chart and just like we could do with our standard charts, up on top, I'll click into the design tab and here I can click into change chart type. So, let's go with a line chart instead. I'll select that and then click on okay.
Now we have a line chart. So, we have all the same controls that we walked through earlier with charts and all of those also apply to pivot charts. Pivot charts are an extremely powerful tool that allow you to generate different views just by dragging and dropping the different fields.
All right, well you now have the power to visualize your data and to also understand the story behind your data. To watch more videos like this one, please consider subscribing and I'll see you in the next video.
