VOices Blog Members Home Events English | Français Shopping Cart
Skip to main content
VOices

Blog

posted on May 22, 2018
Excel%2bBeyond%2bThe%2bBasics-350px.jpg

Charts are invaluable tools to visualize data, to turn it into information which can help you make strategic decisions. We went through the basics of chart-making in a previous post. Here, I’ll tell you how to add data to a chart, to make comparisons between datasets.

Column Chart

In our previous post, we made a column chart out of monthly volunteer data for 2015. The 2016 data is tabulated in a different sheet. It is easy to add.

You can add data to a chart if it will fit on the same x-axis. In the case of a column chart, the x-axis categories need to be the same. In the case of a scatter chart, the two data sets need to have the same independent variable, likely within the same range (though the latter may not always be true, depending on the story you want to tell).

Click on the chart to select it. Then navigate to the Design tab and click Select Data.

2018_05_25_-_01_Add_Data_to_a_Chart_in_Excel.png

Click Add.

2018_05_25_-_02_Add_Data_to_a_Chart_in_Excel.png

You will be prompted for two pieces of information, listed below. You can type in the cell references, or navigate to the cells and select them. The latter is typically easier and more accurate.

  • Series Name: This is the heading for the data range you will be adding. Enter in the reference for the single cell which contains this heading information.
  • Series Values: These are the values for the new dataset; in this case, they are the monthly volunteer hours for 2016

2018_05_25_-_03_Add_Data_to_a_Chart_in_Excel.png

2018_05_25_-_04_Add_Data_to_a_Chart_in_Excel.png

Now you need to add a legend to make the chart make sense. Under the Design tab, click Add Chart Element and Legend. Then choose where you want the legend to appear.

2018_05_25_-_05_Add_Data_to_a_Chart_in_Excel.png

This is what it looks like:

2018_05_25_-_06_Add_Data_to_a_Chart_in_Excel.png

Notice that the legend labels are not very useful yet. Go back to your spreadsheet table, to the cells referenced by Series Name when you selected the data. When you change the contents of each header cell, the legend will also update accordingly.

2018_05_25_-_07_Add_Data_to_a_Chart_in_Excel.png

Scatter Charts

Adding data to a scatter chart is similar, but there is one additional piece of information you need to give, which is the data range of the independent variable. Following is a scatter chart which shows the relationship between Gross National Product and average life expectancy. The dots currently on the chart represent life expectancy of males. Let’s say we want to graph females on the same chart.

2018_05_25_-_08_Add_Data_to_a_Chart_in_Excel.png

As with any chart, select the chart, click Select Data under the Design tab, then click Add. Your selection box will look different from the one in the last example. This time you need to specify the x-axis data, as well. In the case of this graph showing life expectancy vs. GNP:

  • Independent variable (x-axis data) = GNP
  • Dependent variables (y-axis) = life expectancy. We will be adding the female life expectancy here.
  • Series name= table heading for female life expectancy.

2018_05_25_-_09_Add_Data_to_a_Chart_in_Excel.png

Here is what the graph looks like, with a legend added.

2018_05_25_-_10_Add_Data_to_a_Chart_in_Excel.png

Add a data set on a secondary y axis

Sometimes you will want to compare two datasets which have very different data ranges. In order to compare them on the same graph, you need to assign one of the datasets to a secondary y-axis, which will allow you to assign a different range for that data.

For example, we could add infant mortality to the above life expectancy graph. However, life expectancy varies between the numbers of about 40 and 80, while infant mortality varies between about 4 and 180. If we add infant mortality directly to the graph, we get this:

2018_05_25_-_11_Add_Data_to_a_Chart_in_Excel.png

It would be more useful to put infant mortality onto a secondary axis. To do this, select the graph, then click the Format tab. From there, select Series “Infant Mortality” from the Current Selection drop-down menu on the far left. Once it is selected, click Format Selection.

2018_05_25_-_12_Add_Data_to_a_Chart_in_Excel.png

Under Series Options, choose Secondary Axis.

2018_05_25_-_13_Add_Data_to_a_Chart_in_Excel.png

Then use Add Chart Element -> Axis Titles -> Secondary Vertical Axis to label your secondary axis. Once you’re done, you should have this:

2018_05_25_-_14_Add_Data_to_a_Chart_in_Excel.png

Charts are a powerful way to visualize and compare data, to help you understand what the numbers are telling you, in order to make better strategic decisions.

To learn more about turning data into information, come to an upcoming course.

Excel 101: Introduction to Excel

Click here to register Wednesday, September 19, 2018. 9:00 a.m. to 12:00 p.m.

Excel 201: Pivot Tables and Charts

Click here to register Thursday, June 21, 2018. 9:00 a.m. to 12:00 p.m.

Click here to register Wednesday, October 31, 2018. 9:00 a.m. to 12:00 p.m.

Like what you've read? Subscribe to our RSS feed so you never miss a post! We have a general RSS Feed for all VOices blog posts, as well as an Operations RSS Feed which will focus on topics related to organizational operations.

Related Blog Posts:

Filed under: Operations
new comment comment
view archives Archives
RSS RSS