Learn how to create a Chart in Excel.
Charts help take rows and columns of numbers and turn them into a story, a simple takeaway that viewers can absorb quickly and easily.
As shown here with two existing charts – a pie chart and a column chart – the population and housing cost data in the worksheet are brought into sharp focus.
Looking at the charts, let's identify the chart's components. First, we have a chart title. This is important because it immediately tells the viewer what the chart's goal is, what you want them to take away from viewing the chart.
Next, we have the legend, which is crucial to most charts so that the viewer can identify the separate groups of Data Points, each known as a Data Series. So here, the population data is in blue, the housing cost data in orange.
Column charts – and also line charts – have a value axis which runs vertically. It's what the columns and lines are plotted against. That's how we know, for example, that the average population of the cities we're tracking in Arizona is just under 1 million, and so is California's average housing cost.
The Category Axis identifies the data in the chart. Here, it's telling us which states are included.
Pie charts have and usually need Data Labels, which identify what the slices represent. These work in conjunction with or can replace a legend.
You'll still want to include a title, though.
The area that contains the pie – or the columns – is known as the Plot Area, because that's where the data is plotted—as slices, columns, lines, or whatever type of chart you're building.
As I mentioned earlier, a Data Series is a group of Data Points, which are the cells that contained the numbers you're including in the chart. So all my blue data points – the columns, in this case – are the data points in the Average Population data series, and the orange columns are the data points in the Average Housing data series. Click one point in a series and the whole series selects, as Excel assumes you want to address the formatting of the whole series. Here, I'm making a new pie chart from the state averages data, selecting the column headings from the data, and the state abbreviations and average population numbers for each of those states. You can only have 1 data series in a pie chart, because otherwise, I'd have 2 slices for each state, and thus the comparison message of a pie chart would be lost.
After moving and resizing the pie chart, I can click the Add Chart Element button to add the data labels. I can then use the Home tab's Font tools to make the numbers pop, and the Format tab's tools for editing the labels’ background color. I can also move them as needed, if they overlap or are hard to read where they are.
The chart title also needs to stand out, and again, the Home tab's Font tools come to the rescue. I can apply the same increase in font size to the legend, so it stands out, too.
At any time, feel free to mouse over and preview the various Chart Design options, available while the chart is selected. After checking a few of them out, I went back to my own original formatting.
Applying a chart background is easy – just select the area behind the chart – but click toward the edges. You don't want to only fill in the plot area. Then choose a Shape Fill from the Format tab. I chose a Gradient that lightens vertically.
Creating a new Column chart is equally easy. I select the headings and then a series of states, including both the population and housing cost data – because a column chart can support two or more data series in a single chart.
After moving and resizing the resulting chart, I can edit the chart title and apply formatting to the chart, increasing legibility of the axes and legend, including repositioning the legend by right-clicking it, and choosing Format Legend from the pop-up.
You can also move and resize a legend to appear in a location where it will be easily spotted.
After previewing some Chart Design options, I've decided to add a Data Table to this chart, which means I can also delete the legend. The table provides the legend information, plus the actual numbers plotted within the chart.
And then, for a finishing touch, I apply a Gradient fill to this chart, this time one that lightens from top to bottom.
Finally, a quick copy and paste allows me to put this finished chart on its own sheet. I could also have pasted it into a PowerPoint slide or a Word document, expanding my potential audience for the data depicted in the chart.