Using Google Spreadsheets for Tables and Graphs

November 17, 2010, By Christian Cawley

With the basics of Google Docs Spreadsheet understood, you should now be able to carry on and use the browser-based application as you would any other spreadsheet program.

Typically, spreadsheets (such as Microsoft Excel or Lotus 123) would be used for building tables from which graphs and charts are then created, as well as acting as single level databases.

All of these functions are possible with Google Docs Spreadsheet and in this tutorial we will take a look at each of them in turn.

Google Docs Spreadsheet

Building Tables with Google Docs Spreadsheet

I use a spreadsheet to keep a count of my earnings. This is a useful means of staying on top of incomings and outgoings, not to mention calculating tax, etc.

In a cell away from the upper and left borders of your spreadsheet, use the formatting trick we looked at last time to create a list of months, say from January to December, in a column. Leaving the cell next to January in the second column blank, instead go up a row (you can add a new row via Insert > Add row above) and enter a list of income sources. I use web | book | newsletter on my own spreadsheet; you might have a single source of income, but get lucky on the horses or online casino every so often, so you might enter these as column headings. In the last column of your table, enter the title “Total”

The next step is to populate the chart with figures. Leave the Total column blank while you do this, as we’ll be populating this column in the next step. Once your figures are entered, select the row beneath December’s figures with your mouse, and use the Text background color tool (a square with small coloured squares within it) to choose a highlighting colour; yellow is good.

Next, in the first highlighted cell, enter:

=sum()

…and with the insertion point “|” between the brackets, select the cells in that column from January through to December. (You might just opt to enter =sum(cellref1:cellref2), where “cellref” is the cell name; for instance the first cell on a spreadsheet is always A1.)

Tap enter to confirm this and the total will be displayed. Select the cell again and copy this formatting across using the square in the lower right corner of the highlighted column – you’ll know you’ve got it when the mouse pointed turns into a +.

In the Total column, repeat this procedure, but instead of selecting the populated cells in the column above, use the cells in the row to the left.

Creating Graphs and Charts with Google Docs Spreadsheet

By counting my earnings in this way I can then convert the table into a useful chart, showing me in graphical form how my income has developed over the months. I can also see how much I have made in each income stream over the year.

Creating a graph or chart is much easier than creating a table – start by selecting the data you wish to include in the chart, for instance the Total column.

With this highlighted, go to Insert > Chart and choose a graph of your choice. I prefer a line chart for this, and these are listed in the Charts tab. Finally, you can add a chart title and axis labels on the Customize tab, and select Insert when you’re done to display the chart on your spreadsheet.

All of that should have taken you about 5 minutes – a quick demonstration of the power of Google Docs Spreadsheet!

© 2008-2012 DeviceMag.com - All rights reserved | Privacy Policy