Find us on GitHub

Teaching basic lab skills
for research computing

Spreadsheets: Charting

Spreadsheets/Charting at YouTube

Hello, and welcome to the seventh episode of the Software Carpentry lecture on spreadsheets. In this episode, we'll show you how to create simple charts to help you understand your data better.

Our starting point is our CO2 spreadsheet with regional emissions totaled up in two rows at the bottom. We'll come back to those in a moment.

First, let's select the population and emissions for 2008, including column headers.

Then go to the insert menu and select a scatter plot. Excel creates a plot and places it on top of our data.

Since we don't have much real estate in this screencast, let's get rid of the default title and the key, then go to "Layout" and add titles for the horizontal and vertical axes.

Next, let's cut the chart, create a new sheet in our spreadsheet, change its name to "charts", and paste the chart there so that it isn't sitting on top of our data.

This chart isn't actually very helpful because too many data points are crowded together in the bottom left corner. If we change both axes to be logarithmic rather than linear, we can now see a much clearer pattern: emissions grow in proportion to population, with a couple of outliers above and below the main trend line. This kind of insight is what charts are for.

Let's shrink our chart to make room for another one, then go back to our data.

Select the regions and total emissions, then go to "Insert" and choose "Bar Chart".

We want a column chart, which isn't one of the quick-reach defaults, so we select "All chart types", select the one we want, and click "OK".

Once again, let's cut the chart, paste it back in on our charts sheet, resize it, and get rid of the key.

At first glance it looks like there are no emissions at all from the northern region. If we resize the chart, though, we can see that there is a column there—it's just very short compared to the others.

We'll devote an entire lecture later in the course to the art and science of chart design.