(Watch the “Spreadsheet Basics for Journalists, Part 3” video to see a demonstration of the techniques covered on this page. This page has been updated to reflect changes Google made to the Google Sheets chart editor during the summer of 2017.)
Why just tell when you can show and tell?
One of the truly great things Web-based journalism can do is put data into readers’ own hands, allowing them to explore and learn from it themselves. Such interactive data visualizations can get truly complex and can require advanced coding skills, but Google Sheets will let you make some basic ones pretty easily.
Here, for example, is an visualization illustrating the raises data we’ve been working with. Each pair of bars represents a department head’s old and new salary. Furthermore, as you run your mouse pointer over each pair of bars, a pop-up window displays the department head’s name and his or her old and new salaries:
Interested in learning how to make this? I promise it’s easier than it looks. Just don’t tell anyone. Let them think you’re a total genius.
Making and configuring a visualization
To get started, log into Google Drive and open the “Raises Analysis” spreadsheet you worked on in Part 1 and Part 2:
The visualization is based on this sheet’s first three columns of data: “Name,” “Old salary,” and “New salary.” The height difference between each department head’s “Old salary ” and “New salary” bars ends up reflecting the information in the “Raise” column and, at least indirectly, the information in the “Percent change” and “Percent whole” columns.
To get started, use your mouse to highlight each department head’s name, old salary and new salary. Include the column headings, but omit the “Total” and “Average” figures. Like this:
Now, click “Insert / Chart.” Your screen will change to look like this:
Basically, Google Sheets has opened the “Chart editor” on the right side of the screen. It also has taken a look at your data and guessed what type of visualization you might want to use. In this case, though, there’s a better one that the one Google Sheets has chosen. In the Chart editor, click the down arrow under “Chart type,” scroll down until you see “Bar,” and click the first style from the left – the one with horizontal bars, each either red or blue.
This type of visualization expects there to be text data in the first column (like our “Name” column) and numeric data in one or more subsequent columns (like our “Old salary” and “New salary” columns). Applied to such data, the visualization will use a different-colored bar to represent the numbers in each numeric column (here, “Old salary” and “New salary”) and will show one of each for every item in the text column (here, “Name.)
Now, let’s try adjusting some things about how the chart looks. For example:
- That title, “Old salary and New salary,” is kind of lame. Google Sheets just used the column headings. Double click it, then change it to “Proposed department head raises.” Press “Enter” when you’re done. Notice that, over in the Chart editor, Google Sheets has switched to the “Customize” view, where you can click the down arrow next to “Chart title” and choose to add and edit a chart subtitle as well as titles for the chart’s vertical and horizontal axes. You also can adjust each title’s font, size, format, and color.
- Double click the chart’s “Old salary” and “New salary” legend. Over the the Chart editor, you now have options for adjusting the legend’s position, font, size, format and color. On this type of chart, I like to choose “Top” under “Position,” which puts the legend right under the title and allows more horizontal space for the chart’s bars and labels.
- Notice that some of the names shown in the chart’s vertical axis get cut off, like “Martinez, Christoph…” Worse, the chart displays only every other name. One solution is to double click on the names and decrease the names’ font size by clicking the down arrow to the right of “Label font size” over in the Chart editor. At least on my screen, decreasing the font size from 12 to 10 works nicely.
- You also can change the default red-and-blue colors for the horizontal bars. Click the down arrow to the right of “Series” in the Chart editor, and change the color for the “Old salary” bars to whatever you like. I like to use “dark gray 2.” Now, change the color of the “New salary” bars by clicking the down arrow to the right of “Apply to” and choosing whatever color you like. I went with “dark cornflower blue 3.” Note that, if you know how to use Hex color codes, you can click “Custom” and specify colors in that way.
- One other nice trick: Click the down arrow to the right of “Vertical axis,” then check the box next to “Reverse axis order.” Doing so will put the largest post-raise salary at the top of the chart and the smallest at the bottom, instead of the other way around, which is the default. I think having the biggest salary at the top is more intuitive.
By now, you probably have the hang of the chart editor. Just click on the feature you want to change (or click on the down arrow beside it in the Chart editor area) and modify it however you like. You also can click “Customize” in the Chart editor, then select and edit features that way. Once I had finished fiddling, my screen looked like this:
If you click anywhere outside the chart area, the Chart editor will disappear, and the chart will go inactive – that is, nothing will happen when you mouse over the chart. Click the chart once, and the chart will turn active, so that the pop-up windows appear when you mouse over them. Double click the chart, and the Chart editor will reappear.
Showing old and new salaries for all 22 department heads makes for a rather complicated chart. If you’d like a simpler version, you could try graphing just the raw amount of each raise. To do it:
- Highlight rows 1 through 23, and sort by “Raise” (Column D) in descending order (Z to A).
- Use your mouse to highlight the department heads’ names as well as the column heading (cells A1 through A23).
- Hold down the “Shift” key, and use your mouse to highlight the raises as well as their column heading (cells D2 through D23).
- Choose “Insert / Chart,” then proceed as before.
Google Sheets will build the chart using just the data you highlighted. By the time I was done, my screen looked like this:
If that’s still too complex of a chart for your taste, you could focus just on, say, department heads whose raises would exceed $3,000 and use a column chart rather than a bar chart. Like this:
Yet another option is to display the data in an interactive table like this one:
Notice how clicking at the top of any column in the table will sort the data by that column. The scroll bar on the right lets you scroll up and down, too. And a table like this one lets you show values that are in vastly different scales. For example, if you tried to make bar chart showing all of this information, the bars for the “Old salary” and “New salary” columns would be so large compared to the “Raise” and “Pct. Change” columns that the latter two would be practically invisible.
The steps for making a table like this one are the same as those described above. Just choose “Table chart” as the Chart type. Like this:
Going live: Publishing your visualization so others can see it on the Web
Once you have things the way you want them, you can “publish” your visualization so that anyone on the Web can find and view it. Click on the visualization, click the small down arrow in the upper right corner, and choose “Publish chart …” Like this:
On the next screen, click the big, blue “Publish” button …
Google Sheets will ask whether you are sure you want to publish the chart:
Click “OK,” and Google Sheets will make your chart viewable by anyone who visits the URL shown in the box. To test it out, copy the URL and paste it into your browser’s URL box. Cool, huh?
If you know how to make web pages from raw code or with something like WordPress, the “Embed” tab on this screen will give you the code you need to embed the chart in your page.
Recap
Part 3 has described how to create a simple, interactive data visualization and share it via e-mail, social media, and/or the Web.
Return to Part 1: Making a Plan & Getting Started
Return to Part 2: Describing and Comparing the raises