Charting Data with Excel

Take a look at this spreadsheet and find the rows with the highest and lowest sums.

How long did it take? Did you get the right answers?

Now take a look at the same data, represented as a stacked column chart.

Each column in the chart represents the values in a row in the spreadsheet with each value a different color. It's easy to see that row 2 has the highest sum and row 3 has the lowest sum.

The point of this simple exercise is to demonstrate the power of visualizations, or charts. In many situations involving decision makers or managers, individuals in these positions cannot spare the time to crunch numbers or do not have the skills to do so. What they need is quick access to accurate information to make the best decisions for the business. And, as you just saw, charts make that possible. So there is a demand for charts.

Fortunately, users of Excel have access to a powerful application for creating charts like the one above and many more. This guide will introduce you to the world of charting in Microsoft Excel.

To make a chart, use the sample data from the beginning of the guide and select the first row.

Next, on the ribbon bar, select the Insert tab and click the Recommended Charts button.

The Insert Chart dialog appears.

Notice that at the top of the dialog are two tabs, and Recommended Charts is selected. Excel has examined the selected data in the spreadsheet and, based on the structure, suggested several types of charts that will best represent it. Here it has recommended, from top to bottom, a column chart, a bar chart, a line chart, a pie chart, and a funnel chart. Select the column chart and click OK.

This is the chart that Excel generated:

Excel has represented each column in the selected data as a column in the chart. The gridlines represent values in the range of the selected data and are labeled on the left-hand side of the chart. A title was also added. The chart can be moved to a different location in the spreadsheet and resized.

Now select all of the data in the spreadsheet and use the Recommended Charts dialog box to create a new column chart. This is the chart that Excel creates:

The selected data still has five columns but now has four rows instead of one. Each column in the data is now represented by a group or cluster of columns. Thus this chart is named a clustered column chart. Each cluster contains the values in a column, and each value is a different color.

There is also something else new. At the bottom of the chart is a legend. The legend identifies, in this chart, the color corresponding to each row. Another term for the values that correspond to each color is a series. And by default, the series are labeled Series1, Series2 and so on.

But these names aren't very descriptive. To get better names for the series, insert a column to the left of the selected data:

Now select all of the data again and create a clustered column chart. Notice the series in the legend are labeled with the values in the the first column of the selected data.

Now you know what the colors represent, but how about the columns? To get names for the columns, add a row at the top of the spreadsheet.

The chart now looks like this, with the values in the first row below each column:

By now you should be able to see how to build a chart very quickly. But there are are other types besides the column chart. With the data in the spreadsheet selected, click on the Recommended Charts button again. This time in the Insert Chart dialog, select the All Charts tab at the top:

The left side of the dialog shows all of the different chart types supported by Excel. We've already seen the column chart, so let's look at another one, such as the bar chart. Select it from the left of the All Charts tab and then click the OK button.

The bar chart is just a column chart rotated clockwise 90 degrees. Which one to use depends on your data and the story you are trying to tell with it. This data is represented equally well with the column or bar chart. But not every chart type works as well. Go back to the Insert Chart dialog, and with the data selected, create a line chart.

The line chart represents each value as a point. The points in a series are connected with a line, thus giving the chart its name. Take another look at the line chart. Even though it was generated with the exact same data as the column and bar charts, it looks busy. With the lines overlapping each other, it's easy to get lost. So the line chart might be better when you have fewer series.

Go back to the Insert Chart dialog, and this time create a pie chart.

At this point, it might look like something went wrong. The selected data has four series, but the pie chart only has one. The pie chart is different than the other charts we've seen. The previous charts visualize the exact values in the selected data. The pie chart visualizes values as percentages of the total of a series. A single pie chart can represent only a single series.

There are many more advanced chart types that you can experiment with in the All Charts tab. But the column, bar, line, and pie charts are among the ones you will see used most often. Again, which one to use depends on the data you are visualizing and the story you are trying to tell.

Let's take a look at how to customize the appearance of the basic chart types. For example, you saw the column chart at the beginning of the guide. This chart visualized column values in the same column of the chart instead of a cluster of columns. Since the values are stacked on top of each other, this chart is called a stacked column chart. To create one, select the data in the spreadsheet, and in the All Charts tab, select the column chart on the left. Next, at the top of the right side of the dialog, click the second icon representing the stacked column chart.

Click OK to see the chart Excel generated.

If you look back to the beginning of the guide, you'll see that each row in the data was a series in the chart. In this chart, each column in the data is a series. To make each row a series, go back to the All Charts tab. For the Stacked Column chart there are two choices. The one on the left charts each column as a series. The one on the righ, swaps the rows and columns. Select it and click OK to see the generated chart.

That title needs to be changed. Just double click on it to add a new one.

If the colors used to represent each series are not appropriate, you can change them as well. When the chart is selected, you'll see three icons outside the upper right corner.

The middle one that looks like a paintbrush will open a popup to select a new color theme or style for the chart.

You can also change the properties of individual elements of the chart. Notice that when the chart is selected, it open the Format Chart Area pane on the right side of the application. The arrow next to Chart Options is a dropdown with the chart elements that can be modified.

Select the Legend option and then in the Format Legend pane select the third icon to select a new position for the legend. The current style placed it at the bottom, below the title. But I can place it at the top.

There are many, many more options for formatting the different elements of a chart. Also, each chart type has different formatting options. Take the line chart. Using the Format pane you can choose a marker for each value represented on the line.

You can even label the points with the values. With the chart selected, click the first icon outside the upper right corner. This will show a list of the elements that can be added (or removed) from the chart. Select Data Labels.

And these labels can be formatted by selecting the Data Labels for a series in the Format pane.

A chart is perfect for communicating the story behind data. It eliminates the need for decision makers to crunch numbers. This saves time by giving them access to information at a glance. In this guide, you saw the basics of creating and customizing charts to tell the story of your data effectively and precisely. Using the tools in Excel, you can create almost any chart for any reason, easily and rapidly.