(Watch the “Spreadsheet Basics for Journalists, Part 3” video to see a demonstration of the techniques covered on this page)
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.” This window will open in front of your spreadsheet:
Basically, Google Sheets 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 chooses. Click the “Column chart,” which is the lower right of the four “Recommended charts.” The window should now look like this:
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 do these three things:
1. Click the “Customize” tab. Doing so lets you fiddle with some of the visualization’s display options.
2. Replace the generic “Chart title” with something like “Proposed department head raises” This phrase will become the visualization’s title.
3. Choose “Top” in the first box under “Legend.” Doing so moves the legend under the title, where it takes up a bit less room and makes the visualization more compact.
Here’s a picture showing where you do each thing:
Scroll the “Customize” menu down a bit, then follow these three additional steps:
4. Check the “Compare mode” box. This step makes the pop-up window show the information for each pair of bars rather than for each separate bar.
5. Replace the generic “Horizontal axis title” with something useful, like “Average: $2,747. Total: $60,422”
6. Click the down arrow here and choose “None.” This gets rid of the “Name” labels on the horizontal axis of the visualization. If there were fewer, I’d consider leaving them in place. Without them, it’s kind of hard to understand that each set of bars represents one department head. But Google Sheets is already trying to conserve space by showing only every other name. Maybe we can add a caption explaining this aspect of the visualization.
Again, here’s a picture showing where you do each thing:
When you’re done, click the blue “Insert” button to insert the visualization into your spreadsheet. Then grab the visualization with your mouse and move it to the right so it’s no longer covering up your data:
Any time you want to go back to “edit mode” so you can fiddle with the visualization’s options again, just click the visualization, then click the pencil icon that appears in the upper left corner of the visualization, then click whatever part of the chart you want to change:
Alternatively, you can click the small down arrow in the upper right corner, then choose “Advanced edit …” to open the full chart editor:
Incidentally, if you click that little “eye” icon right beside the pencil icon, you can switch to “view” mode and see how your visualization’s interactive features will work. Give it a try. As you mouse over the bars, the pop-up window show appear, telling you the name, old salary and new salary for each pair of bars.
Sorting the bars by “New salary,” from smallest to largest
The chart now presents a lot of information. However, it still doesn’t give the viewer an easy way to learn who got the biggest raise. And that is kind of the main point, right? So, let’s sort the data by “New salary,” from the smallest one to the largest one. When we do, the visualization’s bars will also fall into order, from the smallest “New salary” to the largest one.
First, use your mouse to select all of the name and salary data – from cell A1 to cell F23. Then click “Data / Sort range,” and set the sort window up like this:
When you click the blue “Sort” button, you should get 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:
The box shown below will open.
The default settings should work fine, but note that if you click the “Proposed department he …” button, you could select some other visualization in the sheet to publish, or publish the entire spreadsheet. Meanwhile, if you click “Interactive,” you can choose to publish a static image of the visualization rather than an interactive one. Why do that, though?
Click the blue “Publish” button, answer “OK” to the “Are you sure you want to publish this selection” prompt, and you’ll get a box that looks like this, although the URL shown in your box will be different, because it will be unique to the visualization you have created:
If you copy the URL, open a separate browser window, paste the URL in and hit “Enter,” your browser should call up the visualization. Pretty neat, huh? There are also buttons for distributing the visualization via Google+, Gmail, Facebook and Twitter. Clicking “Embed” will give you the .html code needed to embed the visualization on a Web page. The example at the top of this page is embedded.
We looked only at how to make a column chart, but Google Sheets makes many other types of visualizations available to you, including line charts, bar charts (basically, a column chart on its side), scatter charts, and pie charts. Here, for example, is a bar chart of the same data, with some tweaks to the color scheme and some clearer labeling. I don’t know; I might like this one better:
If the visualization you make seems too big and complicated, consider making a chart out of some important subset of the data. For example, here are the old and new salaries for the department heads with the 12 biggest proposed salaries. Note that I’ve also kept last names only:
And here’s a more basic bar chart that you can make from the subset:
You can even create and publish a data table showing the raw data in columns. You’ll find it under the “More” button on the “Charts” tab. A viewer can sort the data by any column, just by clicking on the top of the column. Check out this example:
By default, the table is sorted by the “New salary” column, from highest to lowest. But click “Pct. Difference,” and the table will instantly sort by that column, putting the largest percent change figure at the top and the smallest at the bottom. Click again, and the table will sort in the opposition direction, putting the smallest percent change at the top and the largest one at the bottom.
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