In my previous post in this tutorial, I described how to assemble the data to create your bubble chart. Now comes the relatively painless part: Pasting it into Google Docs and inserting a Google Gadget – the motion chart itself.
To review, the required columns in your spreadsheet should be in this order:
- A column to define the bubbles (in our case, this is Class Decade)
- A column to define the time series (Year, i.e. fiscal year of giving)
- At least two columns of numerical data for the x-axis and y-axis. (You can have more than two columns, to give you more options for charting, but you need at least two. I used Median Gift for the y-axis, and a choice of either Number of Donors or % Participation for the x-axis.)
- You may also have a column for Category, which just labels the circles in the legend (in our example, this is just a duplication of the data in the Class Decade column)
Assuming you already have a Google or Gmail account, navigate to Google Docs and click on ‘Create New’. Choose ‘Spreadsheet’ from the drop-down menu. Copy all the cells of your Excel spreadsheet, and paste them directly into the Google spreadsheet. Give the file a name, and Save.
(I’m going to assume that you have permission to post your institution’s data online. Keep in mind that you can block public access to the data, or limit it to select invitees who have to log in, or make it wide open and available to all. In any case, it would be best to seek approval.)
Select all of the cells in your sheet that contain your chart data, including the column headers. (Don’t select whole columns – click on cell A1, then hold shift down while clicking on the rightmost cell in the very last row of the sheet.)
In the spreadsheet menu, choose Insert. Click on Gadget.
A window of options will open. You might have to scroll down to find Motion Chart. Click the ‘Add to spreadsheet’ button.
The chart settings window will appear on top of your spreadsheet. (If you don’t see it, scroll up!)
The Range field will already be populated, because you had those cells selected before inserting the gadget. You can modify the range if need be.
Enter a title in the Title field. Ignore the other fields for now.
Click Apply and close.
The chart will take a second or two to appear. It won’t look right – we need to tweak it a bit.
It will also be rather small and hard to work with. To move it to its own sheet, clicking on the little down-arrow at the top left of the chart title bar, and select “Move to own sheet …” from the drop-down menu.
(For additional help at this stage, select Help from the More drop-down menu at top right.)
Now let’s choose the correct values for our x-axis and y-axis.
Click on the x-axis name, and choose the desired value from the options that pop up. (We’re using % Participation.)
(Ignore the Lin and Log menus for now. We’ll leave the scale as Linear, rather than Logarithmic.)
Now click on the y-axis name, and choose Median $.
Notice that the bubbles adjust their orientation accordingly.
Other items that you’ll want to tweak are below. All of these are able to be saved as the default state of your chart:
- Colour: This should be set to ‘Category‘
- Size: Set this to ‘Number alumni‘. For fun, you can also set this to ‘Number of donors’ – then the bubbles will change size over time!
- Playback speed: The little triangle to the right of the Play button. I usually set this on the slowest speed.
- Starting year: Push the slider all the way to the left.
- Labels and trails: You can also click on individual bubbles to label them, or display their trails as they move.
If you play around a bit, which I know you will, you’ll notice that it’s very easy to lose all your settings. And if you try to share your chart with someone else, it won’t display in their browser the way you want it to.
The method for saving your default chart state will be covered in Part 5.