Data visualizations, frequently referred to as information graphics, are a powerful tool that will inform and educate your readers. Often important data-heavy information can bog down a narrative or slow the pacing of a story. That data might be edited out to streamline a story or put into a table for readers to browse.
A better solution is to use simple graphics that can be created in minutes and delivered for free using web tools. These information graphics will compliment and add context to your stories. Graphics can help you highlight important information from a database in a way much easier to understand than a text-only presentation.
What is a database? Simply put, a database is information organized into rows and columns. A spreadsheet or table is one of the most basic forms. If you can make a spreadsheet, you can build a database. The challenge lies in accessing the data from a graphic on the Web. In this tutorial, we will use Google Spreadsheets and Google Gadgets.
Before starting, download the sample spreadsheets and unzip.
Using Google Docs
Google offers a free office suite that you access with free registration. This includes a spreadsheet application. Google Docs uses the same login used for Google Mail, Google Reader and other Google services.
Go to google.com
Login to Google Accounts
Navigate to Google Docs
Note the Create New and Upload buttons. All of your docs will be listed on the right.
Click on the create New button and select Spreadsheet. A new window should open. Note the following features: The menu bar lets you select different commands to change your spreadsheet. Use this instead of your browser’s menu bar. Shortcuts are additional menus of commands that you can use. A cell is and individual square where you can double-click and type in information. The cells are organized into rows (assigned numbers) and columns (assigned letters).
Make a pie chart
The pie chart is the most ubiquitous of charts. Here’s what it is and when to use it.
- It is a circle divided into segments.
- It should illustrate the relationship of the parts of a total.
- The data may be numeric but it is usually displayed in percentages.
- Never more than 5 parts. If you have more than five subsets, consider a TreeMap.
Save your spreadsheet and call it “Pie Chart.” You only have to do this once. The document will autosave as you make changes.
Fill in the data as shown below. Select cell A1 by clicking it once. Hold down the Shift key and click in Cell B4 to select the range of data.
Let’s add a Google gadget to make an interactive pie chart that you can embed in your site. Click on Insert in the Google Menu Bar and select Gadgets and a new window will appear.
A new window will appear in your spreadsheet. This is the gadget that you will eventually embed on your page. But first, let’s take a look at the editing menu and and how to refine your chart.
- This is the range of cells you selected earlier. If you select the wrongs cells, don’t fret. You can edit them here. Just replace A1 or B4 to change the range.
- This is the number of rows you are using to label your columns. “Auto” is fine 90 percent of the time. But if you use two rows for some reason or if you have no column headers, you can change it here.
- This is the title that Google uses to identify your gadget. It will NOT show up in the graphic.
- This is the headline for your chart. It WILL show up in your graphic. Describe what you are illustrating in the chart.
- Choose “On top” whenever possible. This helps readers because the read from top to bottom and expect to find out what the legend is before they read the chart. However, with a pie chart, it is also acceptable to select “Labeled.”
- Stop! Hold it right there! Never, ever select the 3D button. Making a chart 3D distorts it’s data. This can make large areas seem smaller than they are or small areas look larger. It is very easy to accidentally mislead your readers.
- Once everything is done, select “Apply and close.” If you want to make further changes, click on the gadget. A title bar will appear. Click “Edit”to continue making changes.
To embed the chart, click the the triangle on the top right of the gadget. In the menu, select “Publish Gadget.” This opens a new window with an embed code that you can copy and paste into a page on your site.
Resize your gadget. Once you’ve pasted into a page on your site, you can change the size of your gadget. Make sure you are editing in HTML mode. Near the bottom of your code, there is a “height” and “width” measurement. This is the size of your gadget in pixels. If either is changed, the gadget will redraw to avoid looking stretched.
Upload bar chart data
A bar chart can be horizontal of vertical (sometimes called a column chart). Here’s what it is and when to use it.
- It compares totals
- Totals should be consecutive. (daily, monthly. yearly, etc.)
- It should compare at least three totals. (If you only have two totals, just tell the reader the percent change.)
- It should illustrate a trend.
- It can include groups of bars to compare multiple totals.
From any spreadsheet or text document, you can click on the word “docs” to return to the main Google Docs page. Do that now from your pie chart spreadsheet, which will autosave and close.
From there you can click “Upload” to import an Excel spreadsheet or text file with values separated by commas. This text file must end in “.csv”
On the Upload page, click “Select files to upload.” This will open a dialog box. Navigate to your sample files and select “BarChart-CARobberyStats.xls” and hit the “Select” button. At the bottom of the page, click the “Start upload” button.
Once the upload finishes, the name will appear as a link. Click the link to open the spreadsheet.
Fix bar chart data
Take a look at your spreadsheet. Notice that the numbers are totals, that categories of crimes are in columns and that the years are labels. But there’s a problem here. See it yet?
Sometimes when you upload data, it may be interpreted incorrectly. In our example, the Year labels have been imported as numbers (flush right). We need to change them to text (flush left). This is shown in the image to the right.
1. Shift-select the years from A2 to A11.
2. Click the “123” button
3. Choose “Plain Text” from the drop-down menu. This converts the years (numbers) to labels (text).
Notice that the “Year” labels are converted to text but they are still aligned to the right. We don’t have a visual clue that you converted your labels to text.
To avoid converting multiple times, lets align the text to the left. This is shown in the image below.
1. Click on the paragraph button.
2. Choose Flush Left (in the top-left position).
3. Deselect column A by clicking in an empty cell.
Make a bar chart
OK. We’re ready to make our chart. Go to the Insert menu and select Gadget. In the pop-up box, click the “Charts” link and scroll down until you find the interactive column chart. Then click “Add to spreadsheet.”
We forgot to select a range, so let’s fix that. Change the Range field to read “Sheet1!A1:B11” Change Legend to “On Top.” Change the “Axis minimal value” to zero. This field sets your baseline. In most cases, bar charts should have a baseline of zero. Then click “Apply and close.”
Your chart should look like this.
If you want to compare the crimes to each other, you can make a grouped bar chart by extending the range. Click “Edit” and change B11 to D11. Then click “Apply and close.”
The result will be similar to the second chart on the right. This can be an effective way to compare multiple totals/trends.
Tip: A word of caution about grouped bars. They often need space to be readable because they visualize a lot of data. Remember, your job is to simplify the information for the reader, so don’t be afraid to edit.
Get your embed code: Click the the triangle on the top right of the gadget. In the menu, select “Publish Gadget.” Copy and paste the code into a page on your site.
Make a line chart
A line chart, also referred to as a fever chart, is an effective way to show trends with specific types of data.
- It compares changes in a single value, like a rate or index.
- Values should be consecutive. (daily, monthly. yearly, etc.)
- It should compare at least three changes in the value. (If you only have two changes, use percent change.)
- It should illustrate a trend.
- It can include multiple lines to compare multiple values.
Upload your data
From any spreadsheet, click on the word “docs” to return to the main Google Docs page and click the “Upload” button.
On the Upload page, click “Select files to upload” and navigate to “LineChart-CrimeRates.xls” and select it. At the bottom of the Upload page, click the “Start upload” button.
Once the upload is done, click the file link to open the spreadsheet.
Select your Gadget: Go to the Insert menu and select Gadget. In the pop-up box, click the “Charts” link and scroll down until you find the interactive line chart. Then click “Add to spreadsheet.”
Change the Range field to read “Sheet1!A1:B8”
Change Legend to “On Top.”
Change the “Axis minimal value” to zero and the “Axis maximum value” to 30. Then click “Apply and close.”
Tip: As we discussed earlier, a baseline is the bottom of a chart and where the chart is drawn from. A bar chart usually uses a baseline of zero, but a line chart can use whichever baseline best illustrates a trend. This does distort data somewhat so clear labeling is critical. Let’s look at some examples.
Your chart should look like the one below on the left. With a zero baseline, the data is flat and some nuance is missing. Click “Edit” and change “Axis minimal value” to 25. Then click “Apply.” The result will be similar to the chart in the middle. While this does distort the data, it is much easier to see the trend. So when is this bad? If you were to change the dimensions of the chart as done in the one on the right, the data shows radical swings up and down. We know that is not the case, so avoid doing this.
If you want to compare the crime rates to each other, extend the range. Click “Edit” and change B8 to D8. You should change the “Axis minimal value” to 0 in this case and delete the “Axis maximum value” so the gadget can figure out how to redraw the chart. Then click “Apply and close.” The result will be similar to the chart on the right. This can be an effective way to compare multiple rates/trends.
Get your embed code: Click the the triangle on the top right of the gadget. In the menu, select “Publish Gadget.” Copy and paste the code into a page on your site.
Make an area chart
An area chart is kind of a cross between a pie chart and a line chart
- It compares changes in a total over time.
- totals should be consecutive. (daily, monthly. yearly, etc.)
- It should compare at least three changes in the value. (If you only have two changes, use percent change.)
- It should illustrate a trend.
- It can include multiple lines to compare multiple values.
Upload your data
From any spreadsheet, click on the word “docs” to return to the main Google Docs page and click the “Upload” button.
On the Upload page, click “Select files to upload” and navigate to “AreaChart-2009EmploymentSeasAdj-inThds.xls” and select it. At the bottom of the Upload page, click the “Start upload” button.
Select your Gadget: Go to the Insert menu and select Gadget. In the pop-up box, click the “Charts” link and scroll down until you find the interactive area chart. Then click “Add to spreadsheet.”
Edit your gadget: When your gadget window appears, change the range to “A1:B13” and set the Legend to “On top.” Always set the “Axis Minimal Value” to zero with area charts since they illustrate totals. Click “Apply and close.”
Your chart should look like the one at right.
Make a stacked area chart: Area charts looks like a line chart but can display totals like a pie. Click Edit and change your Range to A1: D13. Now check the box next to the word Stacked and click “Apply and close.” This will stack the area for each column of data.
Get your embed code: Click the the triangle on the top right of the gadget. In the menu, select “Publish Gadget.” Copy and paste the code into a page on your site.
Area charts on steroids
Area charts are being used to create some of the most interesting visualizations on the Web. Using some simple math will allow us to create something similar to a New York Times visualization about how people spend their day.
This graphic takes the area chart a step further by converting the totals to a percentage of the whole. You can do this in Google docs with some simple math. Don’t worry, the spreadsheet is going to do all the work.
1. Move the existing gadget out of the way and add the label “Total” in cell F1. In cell F2, type “=SUM(B2:D2)” and hit Enter. This function adds all the cells in the range (B2:D2).
2. Once the total appears, click on cell F2. Notice the box that outlines the cell has a small box in the bottom right corner.
3. Click on that box and drag down to cell F13. This adds the SUM equation to each row and adjusts the range. You now have a new total of each row.
Build a second grid
1. Select (shift click) cell A1 through cell A13. Copy and then click on cell A21 and paste.
2. Create new column labels: Type “Full time” in cell B21; Type “Part time” in cell C21; Type “Unemployed” in cell D21.
3. Now we need to calculate the percentage for cell B22. That equation looks like this: “Full time employment” divided by “Total” X 100. So in cell B22, type “=B2/F2*100” and hit enter.
4. You now have all the percentages calculated for Full time employment.Once that’s done, select cell B22, click on the square in the lower-right corner and drag down to cellB33.. Repeat in cell C22 using the formula =C2/F2*100. Repeat in cell D22 using the formula =D2/F2*100
5. Time to clean up the data. Let’s get rid of all those decimals. Shift-select cells B22 through D33. Click on the 123 button to get a drop down menu and select the second option “1,000.” This will round the percentages.
6. Move your gadget back the the center of your spreadsheet. Edit the gadget by changing the range in to A21:D33 and click “Apply and close.” The chart now shows how the percentage change over time.
While this doesn’t have all the functionality of the New York Times graphic, you can still visualize a large dataset and build an intricate, interactive graphic.
About this Tutorial
This tutorial originated as hands-on training featured in a Web 2.0 seminar for journalists by the Knight Digital Media Center at the Berkeley Graduate School of Journalism.
Republishing Policy
This content may not be republished in print or digital form without express written permission from Berkeley Advanced Media Institute. Please see our Content Redistribution Policy.
© 2020 The Regents of the University of California