 |
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.
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.
Click Add.
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.
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.
This is what it looks like:
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.
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.
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:
Here is what the graph looks like, with a legend added.
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:
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.
Under Series Options, choose Secondary Axis.
Then use Add Chart Element -> Axis Titles -> Secondary Vertical Axis to label your secondary axis. Once you’re done, you should have this:
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.
Click here to register Wednesday, September 19, 2018. 9:00 a.m. to 12:00 p.m.
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:
 |
comment |
 |
Archives |
 |
RSS |
