Knight Digital Media Center Multimedia Training

Tutorial: Spreadsheets

By Paul Grabowicz

For updates and discussion on this tutorial, visit:
http://kdmc.berkeley.edu/tutorials/spreadsheets/

Introduction

A spreadsheet is a software program you use to easily perform mathematical calculations on statistical data and, such as totaling long columns of numbers or determining percentages and averages.

And if any of the raw numbers you put into your spreadsheet should change – if you obtain final figures to substitute for preliminary ones, for example – the spreadsheet will update all the calculations you’ve performed based on the new numbers.

You also can use a spreadsheet to generate data visualizations like charts to display the statistical information you've compiled on a website.

You can use free applications like Google Docs to create a spreadsheet. You just need to sign up for a free Google account.

You also can purchase Microsoft's Excel spreadsheet program to run on your computer.

Both spreadsheets programs work in similar ways.

Excel has far more sophisticated tools and features.

A Google Docs spreadsheet has the advantage of allowing you to easily create a data visualization or map mashup using other Google Apps such as Google Gadgets (see, for example, this story on how The Guardian uses Google Docs spreadsheets to produce its databases and data visualizations).

This tutorial describes how to create and do calculations on a Google Docs spreadsheet, with some notations about differences in Microsoft Excel.

Spreadsheet Layout

To create a new spreadsheet in Google Docs, sign into your Google Docs account. Then click on the Create new button on the top left and select Spreadsheet.

On your screen will appear a basic spreadsheet, divided into numbered rows and lettered columns.

spreadsheet grid

The rows and columns intersect to create small boxes, which are called cells.

Each cell is identified by its column letter and row number.

Thus the very first cell in the upper left-hand corner is called A1.

Just below A1 is A2. Just to the right of A1 is B1. Just below B1 is B2, and so on.

In the image below, for example, cell D9 is highlighted.

cell D9 on the spreadsheet grid

Setting the View Options

You can select some settings to change the view of the spreadsheet or display toolbars you frequently use, such as the one for entering formulas to make calculations.

To do this, in the menu at the top click on View and make sure there's a check mark next to Show Formula Bar (to display a box to enter formulas).

Entering Information in a Cell

You enter information into a spreadsheet program by typing it into each of the cells.

You can enter three different types of information into a cell:

To enter information into a cell, simply click on the cell and type in the information.

When you're done, press the enter/return key on your keyboard to store the information in that cell.

Then click on another cell and repeat the process.

Each time you type information into a cell, you'll notice the information also appears in the Formula bar, the box just above the columns and rows.

For example, if you click on cell:

B3

And type in the number:

100

You'll see the number 100 displayed in the formula bar above.

100 in cell B3

To enter text headings for the various columns and rows to identify them, follow the same procedure as you would with entering numbers.

Click on the cell, type in the name of a heading and press the enter/return key.

For example, if you click on cell:

B1

And type into that cell:

Numbers

You'll see the word Numbers displayed in cell B1, just above the number 100 in cell B3.

Numbers in cell B1

Once you've entered some numbers, you then can apply formulas to analyze them - adding them together, finding their average value, determining percent changes from one number to the next and so on.

Saving Your Spreadsheet

After you've entered some data into a Google Docs spreadsheet, make sure you save it by clicking on File...Save and typing in a name for the spreadsheet.

Once you've saved the spreadsheet, Google Docs will automatically save any additional changes you make to it.

Importing Data Into a Spreadsheet

Many government agencies and private organizations provide data on their websites in a spreadsheet or other format that you can download onto your computer.

See our guide on Databases of Statistical Information for locating websites with demographic and other data, including data that can be downloaded as a spreadsheet or in a format like .csv (which stands for comma separated values), which then can be easily imported into a spreadsheet program.

To import a spreadsheet, .csv or other file you've downloaded on your computer into a Google Docs spreadsheet, first create a new spreadsheet in Google Docs. Then in the menu at the top click on File...Import and then Browse and select the downloaded file.

Note: You also can import data on a website directly into a Google Docs spreadsheet. For instructions on doing that, see the section on Functions later in this tutorial.

Importing Sample Data

Let's download some data to demonstrate how to import it into a Google Docs spreadsheet, and also to give us some sample data to use to show how to do calculations and use other features of a spreadsheet.

The FBI compiles national crime statistics, including data on the types of weapons used in homicides.

This data is in an Excel spreadsheet (.xls) file that can be downloaded from the FBI website and then imported into a Google Doc spreadsheet.

To download the file go to this FBI web page:

Expanded Homicide Data Table 8

At that page, under the heading for:

Murder Victims
by Weapon, 2004–2008

Click on the link at the top for:

Download Excel

The file will be downloaded onto your computer.

(if for some reason you have trouble downloading this file, you can click here to download the file from our website)

To import the file into a Google Docs spreadsheet, create a new spreadsheet and in the menu at the top click on:

File...Import

Click on the Browse button and navigate to the downloaded FBI file which is named 08shrtbl08.xls.

Select that 08shrtbl08.xls file and click on Import.

After a few seconds you'll be prompted that the file has been successfullly uploaded. Then click on Open now. 

You should see a Google Docs spreadsheet that looks like this:

sample data in a spreadsheet

This spreadsheet shows the number of murder victims in each year from 2004 to 2008 in five columns, with the columns labeled by year in cells B4 to F4.

Below that the spreadsheet shows the weapon used in the murders in 18 rows of data, with the rows labeled by type of weapon in cells A5 (which is the overall total for all weapons) to A22.

Resizing Columns or Rows

You can improve the display of the data in a spreadsheet by increasing or decreasing the width of a column or the height of a row.

To change a column's width, in the gray bar at the top of the spreadsheet where the letters of the columns are displayed, move your mouse cursor to the border between any two columns.

Your mouse cursor will change from a hand icon to a vertical bar with a right-pointing arrow.

Click, hold down your mouse button and drag the bar to the right to increase the width of a column, or to the left to decrease the width of a column.

Similarly, to change a row's height, in the gray bar at the far left of the spreadsheet where the numbers of the rows are displayed, move your mouse cursor to the border between any two rows.

Your mouse cursor will change from a hand icon to a horizontal bar with a upward-pointing arrow.

Click, hold down your mouse button and drag the bar down to increase the height of a row, or up to decrease the height of a row.

Note for Excel:  if you narrow the width of a column displaying a number too much, you will see a series of pound signs displayed in the cell:

###

This doesn't mean you've lost any data - you just made the column width too narrow to fit some of the numbers in the cells in that column.

To fix this, in the gray bar at the top of the spreadsheet just drag the right vertical border of that column to the right to increase the width enough to display all the contents of each cell. The pound signs will disappear and be replaced by the original data in the cells.

Note for Excel: You can speed up the resizing of columns and avoid making them too narrow by moving your mouse cursor to the border separating two columns in the gray bar at the top and double-clicking on the border.

This will automatically resize the column to the left, making it just wide enough to fit the longest entry on any row in that column.

Deleting or Adding Columns or Rows

You can get rid of unwanted data or other information by deleting rows or columns.

For example, in our sample spreadsheet of weapons used in homicides, we might want to get rid of row 23, which is just a footnote stating that one murder in which the victim was pushed to his/her death has been included in the "Personal weapons" listing in row 14.

row 23 in a spreadsheet

To delete a row, hover your mouse cursor over a row number in the gray area to the left, in this case row 23. Right click and in the pop-up menu select Delete row.

deleting a row in a spreadsheet

Use the same procedure for deleting a column.

Hover your mouse cursor over a column letter in the gray area at the top, right click and in the pop-up menu select Delete column (you also can click on the tiny downward-pointing arrow to get this pop-up menu).

If you want to add a column or row, again hover your mouse cursor over the appropriate column or row in the gray area above or to the left, right click and in the pop-up menu select one of the Insert options.

Formulas - Adding, Subtracting, Multiplying and Dividing

Learn to work with data in a spreadsheet and to create engaging charts, maps and graphs in the kdmcBerkeley Data Visualization Workshop.

With a spreadsheet you can insert a formula that will instantly add, subtract, multiply or divide numbers in columns or rows.

To do this you select a cell in a new column or row and then type in a formula.

A formula starts with an equals sign (=) that tells the spreadsheet you want to do a calculation.

A formula then has a symbol for what kind of calculation you want to perform (add, subtract, multiply, divide, etc.). The symbols a spreadsheet uses for calculations are:

Then you type in the letters/numbers for the cells (A1, A2, B1, B2, etc.) to which you want to apply the calculation, separated by the symbol for the type of calculation.

Adding Numbers in Columns

Let's write a formula for adding together a series of numbers.

In the spreadsheet for types of weapons used in murders that we downloaded from the FBI website, the spreadsheet already included the total number of homicides in which any kind of firearm was used each year from 2004 to 2008. Those numbers are in row 6.

But what if these totals hadn't been included in the original data and you needed to calculate them yourself using the spreadsheet (or if you wanted to use the spreadsheet to double-check the FBI's calculations).

This would require totaling up for each year the column of numbers for the five weapon types in the spreadsheet:

highlighting rows 7 - 11

To do this we need to insert a formula for adding a series of numbers in a column.

Let's start by doing this for the year 2004. Click on cell:

B23

Which is in the column that shows the numbers for weapons used in 2004.

In that cell, type:

=B7+B8+B9+B10+B11

(note: the letters are not case sensitive. So for example so you could type in either B7 or b7)

This tells the spreadsheet to add up the number of murders committed with handguns (B7), rifles (B8), shotguns (B9), other guns (B10), and firearms, type not stated (B11) for the year 2004.

adding numbers in column B

You should type cell letters/numbers into a formula rather than the actual numbers.

That way if the numbers ever change (for example, if the FBI released updated murder weapon statistics for 2008), you won’t have to re-enter the new numbers in the formula. Instead you'd just type the updated numbers into the appropriate cells and the spreadsheet will apply the existing formula to the new numbers in those cells.

To see the result of this calculation press the enter/return key.

The formula will disappear, replaced in cell B23 by the number 9,385, which is the number homicides committed using some type of firearm in 2004 (which also is the same number included in the original FBI data in cell B6).

Applying a Formula to Multiple Cells

If we now wanted to calculate the total number of gun related homicides for the other four years, 2005, 2006, 2007 and 2008, we could repeat the process of typing an addition formula into each cell in the rest of row 23.

But a spreadsheet has a much faster way of accomplishing this - by letting you simply copy the formula to one or more of the other cells in the same row.

To do this, click on cell:

B23

Where we typed in our addition formula (=B7+B8+B9+B10+B11).

Pass your mouse cursor over the bottom right corner of cell B23 and notice your cursor changes from an arrow pointer to a thin crosshairs.

Click on that crosshairs, hold down your mouse button and drag your mouse to the right over the rest of the cells in row 23.

An outline will appear around the cells you've selected.

Continue dragging your mouse until you get to cell:

F23

Release your mouse button and the total number of homicides involving firearms for each year from 2004 to 2008 will appear in row 23.

applying addition formula to multiple cells

Which again confirms the totals in the original FBI spreadsheet in row 6.

The spreadsheet has calculated these totals for you by applying the formula you first typed in cell B23 (=F6-B6) to the rest of the cells in row 23.

The spreadsheet keeps the formula (addition) the same, but shifts the cell numbers as it applies the formula to the other cells to the right (so the formula in cell C23 is =C7+C8+C9+C10+C11, the formula in cell D23 is =D7+D8+D9+D10+D11 , and so on).

Subtracting numbers in a Row

Another formula you can use in a spreadsheet is subtraction.

In our sample spreadsheet of weapons used in homicides, you might want to calculate if the numbers for each type of weapon used increased or decreased between 2004, the first year of the data, and 2008, the most current year of data.

To do this, you need to type a formula in a cell in one of the columns to the right of the existing data that will subtract the earliest number in 2004 from the most recent number in 2008.

Let's start with the total number of murders by firearms (row G) to see how they have increased or decreased between 2004 and 2008.

Click on cell:

G6

Which is in the row that shows the total number of murders each year in which a firearm was used.

In cell G6 type the following:

=F6-B6

=F6-B6 in cell G6

We're using the minus sign symbol (-) for the subtraction formula and telling the spreadsheet to take the number in cell F6 and subtract from it the number in cell B6. Thus we're subtracting the number of firearm murders in 2004 (B6) from the number of firearm murders in the most current year, 2008 (F6).

Now press the enter/return key.

The formula will disappear and the number 99 will appear in cell G6.

calculation in cell G6

This tells us there were 99 more homicides by firearm in 2008 than in 2004.

Now we can apply this same formula to each of the different types of weapons used to see how the numbers for each one increased or decreased from 2004 to 2008. We do this by copying the formula we just wrote in cell G6 to the other cells in the G column.

So click on cell:

G6

If you move your mouse cursor over the bottom right corner of the cell, your cursor changes from an arrow pointer to a thin crosshairs.

When this happens, click on that crosshairs, hold down your mouse button and drag directly down over the rest of the cells in the G column.

An outline will appear around the cells you've selected.

Continue dragging your mouse until you get to cell:

G22

Then release your mouse button.

In your spreadsheet will appear numbers in each of the G column cells.

applying subtraction formula to multiple cells

These numbers represent the increase or decrease in each type of weapon used in a homicide between 2004 and 2008.

We can use the same process to apply this formula to the data in row 5 above, which is total number of murders using all types of weapons.

Click on cell G6 and move your mouse cursor to the bottom right corner so the crosshairs are displayed. Click on that, hold down, drag up to cell G5 and release.

You'll see the number -30 displayed in cell G5, which shows the total number of homicides using all types of weapons has decreased by 30 from 2004 to 2008.

Editing a Formula

When you type a formula into a cell and then hit the enter/return key, the formula will disappear, replaced by a number that's the result of the calculation.

If you need to change the formula, you won’t be able to do so by clicking in the cell, because all you'll see there is a number. If you start typing, it will write over the number.

So how can you edit the formula?

There are two ways:

You can double click on the cell to display the formula in the cell and then edit or retype it there.

Or you can click once on a cell and use the Formula bar above to edit it.

If you click once on a cell that has a formula hidden in it (replaced by a number that's the result of the calculation), the formula you originally typed will appear in the Formula bar above the columns and rows.

For example, click on cell G6, in which in the previous section we typed in the formula =F6-B6. In that cell the number 99 is now displayed.

But look above the columns and rows and you'll see the =F6-B6 formula displayed in the Formula bar.

editing a formula

To edit the formula you can click in the formula bar where the formula for this cell is displayed. Then change the existing formula or type a new one into the Formula bar, press the enter/return key and the new formula will be applied and the numbers will be recalculated in the cell.

Percent Changes and Multiplying and Dividing

You also can use a spreadsheet to calculate percent changes between two numbers.

For example, in our spreadsheet on murder weapons from 2004 to 2008 we can calculate not only how much the use of each weapon increased or decreased, but by what percent each one did so.

This way we can better compare how the use of each type of murder weapon is changing relative to the others.

To do this, we need another formula, in which we'll divide and multiply some numbers to get the percent change.

First let's tidy up the spreadsheet by resizing the G column, in which we previously did calculations of the changes in the numbers of each murder weapon used between 2004 and 2008. This column is much wider than it needs to be for the numbers displayed.

To resize a column, in the gray bar at the top of the spreadsheet where the letters of the columns are displayed, move your mouse cursor to the border between the G and H columns. Your mouse cursor will change from a hand icon to a vertical bar with a right-pointing arrow. Click and drag the bar to the left to decrease the width of the G column.

resizing column

Now let's do the percent calculation, starting with the percent change in the total number of homicides (row 5).

First click on cell H5 to the right.

And type in the following formula:

=(F5-B5)/B5*100

formula in cell H5

This is the formula for calculating the percent change between two numbers.

This formula tells the spreadsheet to subtract the number of homicides by all types of weapons in 2004 (B5) from the number of homicides by all types of weapons in 2008 (F5), then divide that by the original number of homicides in 2004 (B5), and finally multiply the result by 100 (which converts this to a percent figure).

The backslash ( / ) is the symbol for dividing, while the asterisk ( * ) is the symbol for multiplying.

(the parentheses in this formula are also important - see the next section on why we're using those)

Now hit the enter/return key to see the final result of the percent formula in cell H5:

-0.2111189

This shows that the total number of homicides by all types of weapons declined by 0.2 percent from 2004 to 2008 (in other words a decline of about two tenths of one percent).

Note: We could have accomplished this same calculation by using a simpler formula:

=G5/B5*100

That's because we've already set up the G column to calculate the B column minus the F column (that is, to calculate the difference in the number of homicides between 2004 and 2008). So we could have simply divided the number in the G column by the number in the B column and multiplied by 100 to get the percent change.

Now let's apply the percent change formula to the rest of the murder-by-weapon numbers.

Click on cell:

H5

Pass your mouse over the bottom right corner of the cell until the cursor changes to thin crosshairs.

Click and drag the mouse cursor down over the rest of the cells in the H column.

Release your mouse button when you get to cell:

H22

The percent changes for all the different types of weapons used in homicides will appear on your screen.

applying percent change to multiple cells

Parentheses in a Formula

In the formula for percent change we used in the previous section, parentheses ( ) were included in the formula:

=(F5-B5)/B5*100

The parentheses in this formula are very important. These tell the spreadsheet to subtract the number of homicides in 2004 (B5) from the number of homicides in 2008 (F5) first, and then divide that amount by the number of homicides in 2004 (B5) and finally multiply by 100.

If you didn't include the parentheses and had just typed in =F5-B5/B5*100, the spreadsheet first would divide B5 by B5 (yielding 1). Then it would multiply that by 100. Finally it would subtract the result from F5.

The resulting calculation would be the total number of homicides in 2008 minus 100, a meaningless number.

So if you are doing a calculation involving several steps, it is important to include parentheses so you can group the numbers properly and the spreadsheet thus knows the order in which to do the calculations.

Percentages of a Constant Number

Another calculation you can perform with a spreadsheet is determining the percentage of a fixed number.

In our spreadsheet of weapons used in homicides, for example, we might want to know what percentage of the total number of homicides involved each different type of weapon.

Thus for the year 2008, we'd want to know what percentage of the total homicides that year (cell F5) was caused by each of the 17 different categories of weapons (cells F6 through F22).

F column and fixed percentage calculation

We can start by calculating the percentage of murders involving all kinds of firearms (cell F6) and then applying that same formula to the cells for the other types of weapons.

To create our percent formula click on cell:

G6

And type in this formula:

=F6/F5*100

This formula tells the spreadsheet to divide the number of homicides involving firearms in 2008 (F6) by the total number of homicides that year (F5), and then multiply the result by 100 (which converts this to a percent figure).

Press the enter/return key and you'll see the total is:

66.88...

So firearm related homicides were about two thirds of the total number of homicides in 2008.

You might then try to apply the same calculation to the cells for the other types of weapons using the applying a formula to multiple cells method we covered earlier.

Thus you might click on the crosshairs in the bottom right corner of cell G6 and drag the formula to the cells below, on down to cell G22.

But this will produce bizarre numbers in the G column, including that some weapons-related homicides are more than 100% of the total.

nonsensical numbers from formula

What went wrong?

The problem is that when the spreadsheet copies a formula using this method, it shifts the letters for both cells in the original formula (F6 and F5) as it applies that formula to other cells (resulting in F7 divided by F6 in the next cell down).

But we want the number in cell F5 (the total number of homicides) to always remain constant in the formula.

To fix this, we need to force the spreadsheet to always divide the numbers for each type of weapon used by a constant number – the total number of homicides in cell F5.

To do this we need to anchor the F5 cell in our formula, and force the spreadsheet always to divide the number for each particular type of weapon by that same total number of homicides in F5.

You accomplish this by adding some $ signs to the formula that instruct the spreadsheet not to change cell F5 when applying the formula to other cells.

So go back and click on cell:

F6

Which is where we typed the original formula: =F6/F5*100.

Delete that formula, and instead type in this:

=F6/$F$5*100

The dollar signs tell Excel to always keep anchored on cell F5 and the data in it when applying this formula to other cells.

fixed formula in cell F6

When you now press the enter/return key you’ll see the same number in the F6 cell as before:

66.88...

But now we can drag the formula down through the column of cells and get the correct results.

So hover your mouse over cell:

F6

Then click on the crosshairs in the bottom right corner of the cell and drag down to cell:

F22

And release your mouse.

correct formula applied to all cells

The correct percentage figure for each weapon type will now appear in the spreadsheet.

Thus the percentage of homicides in 2008 involving a firearm of any kind was about 66.9 percent of the total, those involving a handgun was about 47.6%, knives or cutting instruments about 13.4%, strangulation about 0.6%, and so on.

Adding Numbers Using the SUM Formula

If you want to add a large group of numbers in a row or column, there's another way to do that quickly in a spreadsheet by using the SUM formula.

For example, in the spreadsheet on weapons used in homicides, what if you wanted to know the total number of homicides in which a firearm was not the weapon used.

To calculate that, you could add up the numbers in rows 12 - 21 for each year using the SUM formula

non firearm homicide numbers

(Note: row 22 - "Other weapons or weapons not stated" - may or may not involve a non-firearm-related homicide, so we're leaving that out of this calculation)

To use the SUM formula to calculate the number of non-firearm-related homicides in rows 12 - 21, first click on cell:

B23

In that cell type this formula

=SUM(B12:B21)

SUM formula

Then press the enter/return key.

You'll see there were 3,969 non-firearm-related homicides in 2004.

In our formula, =SUM is shorthand for telling a spreadsheet to add up a series of numbers.

After typing =SUM, you type an opening parentheses, followed by the letter/number of the first cell in the column you want to add.

After that type a colon, then the letter/number of the last cell in the column you want to add, followed by a closing parentheses.

The colon instructs the spreadsheet to apply the SUM calculation to the numbers in all the cells between the first and last cell you’ve entered.

Adding Select Numbers with the SUM Formula

You also can add up select numbers in a column, rather than a span of them, using the SUM formula.

To do that, in the SUM formula you replace the colon with either plus signs ( + ) or commas ( , ), to separate the specific cells you want to total up.

Thus if you wanted to total up only the number of homicides in 2004 in which either poison (cell B15) or narcotics (cell B18) was involved, you would type this formula:

=SUM(B15+B18)

or

=SUM(B15,B18)

Either of which will add the number in cell B15 to the number in cell B18 and give you the total number of homicides involving poison or narcotics in 2004 (93).

Shortcuts to Writing Formulas

There are a number of shortcuts for writing formulas in a spreadsheet.

To illustrate these, in our spreadsheet on types of weapons used in homicides, let’s add up the total number of firearm-related homicides from 2004 to 2008.

This means adding together the numbers in cells B6, C6, D6, E6 and F6.

firearm homicides 2004- 2008

To do this, to the right click on cell:

G6

Here you could type in the SUM formula to add up the numbers: =SUM(B6:F6).

Formulas Tool

But the spreadsheet has a Formulas tool that will shorten what you have to type.

Look to the far right in the toolbar just above the spreadsheet for a symbol that looks like this:

formulas tool icon

(if you hover over it with your mouse, the word Formulas will appear)

Click on it and you'll see a series of formulas you can select to insert into your spreadsheet.

formulas tool

In this case pick SUM and the formula =SUM() will be inserted into cell G6.

Now you just need to insert between the parentheses the span of cells you want to have totaled, such as B6:F6.

Or in between the parentheses you can type in the individual cell letters/numbers separated by commas or plus signs, such as B6,C6,D6,E6,F6 or B6+C6+D6+E6+F6

Press the enter/return key and the calculation will be performed.

Selecting Cells in the Spreadsheet

You also can add the span of cells you want in the SUM formula by selecting in the spreadsheet the first and last cells in the span (such as B6 and F6 in this example).

To do this, first add the SUM formula as described above to cell G6.

Make sure your cursor is positioned in between the parentheses in the =SUM() formula (if needed, double-click on cell G6 to display the formula and then click in between the parentheses)

Now click on cell:

B6

The spreadsheet will add that to the SUM formula:

=SUM(B6)

Now hold down the shift key on your keyboard and click on cell:

F6

The spreadsheet will add that to the SUM formula as the last cell in span that you want totaled:

=SUM(B6:F6)

It will also highlight on the spreadsheet the span of cells you've selected.

selecting cells to add to sum formula

Then press the enter/return key to apply the formula and calculate the total: 49,381 firearm related homicides between 2004 and 2008.

An alternative to holding down the shift key is to click on the first cell you want in a span, hold down your mouse button and drag left-right or up-down to add more cells to the span to be totaled.

Note for Excel: In Excel you can click on individual cells to total up, rather than a span of cells. Thus you an add together numbers that aren't adjacent to each other.

To do this, position your cursor between the parentheses in the =SUM() formula and hold down the Command/Apple key instead of the Shift key. Then just click on the cells you want to add together, and Excel will insert them into the SUM formula, separated by commas.

This feature doesn't currently exist for a Google Docs spreadsheet. So if you want to total up numbers in cells that aren't adjacent, you'll have to type the letter/number for each individual cell into the SUM formula, separated by commas.

Averaging Numbers

Another common calculation is averaging a series of numbers.

In our spreadsheet on the types of weapons used in homicides, for example, what if we wanted to know the average number of firearm-related homicides each year between 2004 and 2008 (cells B6 to F6).

To do this, click on cell:

G6

And in that cell type:

=AVERAGE(B6:F6)

averaging numbers formula

Or you can use the shortcuts to writing formulas described before and apply it to averages.

Click on cell G6 and then in the far right of the toolbar just above the spreadsheet click on the Formulas tool symbol that looks like this:

formula tool icon

Scroll down and select:

AVERAGE

averages using formula tool

The formula =AVERAGE() then will be inserted into cell G6.

Now you can type B6:F6 between the parentheses.

Or, with your cursor positioned between the two parentheses, click on cell B6, hold down the shift key and click on cell F6.

The spreadsheet will add that span of cells to the AVERAGE formula:

=AVERAGE(B6:F6)

Press the enter/return key and you'll see there was an average of 9,876.2 firearm-related homicides a year from 2004 to 2008.

Using Functions to Import Website Data

One advantage to Google spreadsheets is that it is designed to work with the Web. Specific functions allow you to load data dynamically directly from a website. 

Import a data file published on the Web into your spreadsheet

CSV files (comma separated values) can be imported directly into a spreadsheet from anywhere on the Web.  CSV is one of the most common data formats and can be found with a simple Google search. 

Type ucla crime filetype:CSV in a Google search page and you should see this in your browser:

search for ucla crime csv file

Select the green URL below the first item and copy it.

Open a new spreadsheet and click in cell A1. 

Type =ImportData("

Paste the URL after the quote mark then type ") and hit enter.

After a moment the data will load and should look like this:

 crime stats imported into google spreadsheet

Many files will not be this clean and may require cleanup. But if you can use the file as is, it's especially useful. Governments regularly update CSV files on their servers. This may happen frequently with certain files such as election results.

A Google gadget made with one of these files also updates automatically if the CSV file is updated.

Import a table or list directly from a Web page

Tables can frequently be imported directly from a Web page into a spreadsheet. Let's import the same data from the Expanded Homicide Data Table 8 used earlier. Right click on the link and select "copy link" from the menu.

Open a new spreadsheet and click in cell A1. 

Type =ImportHtml("

paste the URL after the quote mark then type "; 

Then type "table";   This tells the function to look for the <table> html tag.

Now type 1)  This tells the function to pull data from the first table. To pull data from the second table on a page, replace the 1 with a 2.

Hit enter and the spreadsheet should look like this:

import data from html table

The "table" setting can be replaced with "list" so that it will look for the contents of <ul> <ol> and <dl> tags.

Load dynamic financial data

Live data from Google finance can be imported into your spreadsheet. The data updates automatically every time the spreadsheet is loaded. Quotes can have up to a 20 minute delay, which is common for financial data. 

Create a new spreadsheet that looks like this:

Google Finance live data

Type =GoogleFinance("DJI", "price") in cell B2

Type =GoogleFinance("INX", "price") in cell B3

Type =GoogleFinance(".IXIC", "price") in cell B4

The initials at the beginning of the parentheses are stock ticker symbols. You can find the symbol for any stock at Google Finance.

The cells should update in a few moments and your spreadsheet should look like this:

Google Finance market index spreadsheet

Load historic financial data

The same function can be used to load historic data. Let's pull in the daily closing price of Google stock for 2009.

Create a new spreadsheet. 

In cell A1, type  =GoogleFinance("GOOG", "close", "01/01/2009" , "12/31/2009", "DAILY") 

Hit enter and the daily closes for 2009 should load into your spreadsheet.

To do more with the GoogleFinance function, check out the documentation page

Sorting Results

After you've entered numbers or done calculations in a spreadsheet, you may want to sort the results from highest to lowest or lowest to highest.

With the spreadsheet on types of weapons used in homicides, for example, you could more easily see which weapons are most frequently used by ranking them from the highest number to the lowest number for any given year.

To do this, you first need to highlight the area of the spreadsheet that you want to sort.

Don't just highlight a single column of numbers to sort because the spreadsheet then will sort only the cells in that column and not change the order of the corresponding cells in other columns (such as the headings that tell you which type of weapon corresponds with the numbers of homicides).

To keep the headings and other data in order in our spreadsheet, in the gray area to the far left that lists the row numbers in the spreadsheet, click on the number for row:

5

Which is the row with the totals for all homicides in each year.

Now hold down the shift key on your keyboard and in gray area below click on the number for row:

22

Which is the row with the totals for homicides by other weapons or where the weapon wasn't stated.

You should see everything on the spreadsheet between rows 5 and 22 highlighted.

selecting cells to sort

The highlighted area now includes the headings for the types of weapons used and then the numbers for each type of weapon for each year.

 To sort the data, in the menu at the top, click on Tools...Sort...

tools menu item to sort

In the box that appears, you'll see the range of selected cells displayed at the top (in this case, cells A5 to J22).

You now can select the column by which you want to sort the data.

You also can select whether to sort that data in ascending order (A - Z) so the smallest number appears at the top of the sorted data, or descending order (Z - A) so the largest number appears at the top.

sort box with options

In our case, we can select:

Column F

Which will sort the data by the most recent year, 2008.

And select:

Z - A

So the numbers for weapons that are used the most appear at the top of the sorted data.

Then click on Sort to rearrange the data.

sorted data

The spreadsheet now will display the types of weapons most frequently used in homicides in 2008 at the top of the chart (total firearms, then handguns, then knives...), and the least frequently used weapons at the bottom (explosives, poison, drowning).

If you then want to go back to the original form of the spreadsheet, in the menu at the top click on:

Edit...Undo

Note for Excel: you re-sort columns by clicking in the main menu on Data...Sort or by clicking on the Sort buttons in the toolbar.

Formatting Cells

A spreadsheet provides a lot of options for re-formatting the information being displayed. These are similar to the options in a word processing program like Microsoft Word or many other applications. They include:

Some of these options are available by selecting Format in the menu at the top and then picking one of the choices in the drop-down menu.

Or you can click on the icons in the middle of the toolbar for other options.

toolbar icons for formatting

To change the formatting, first select a cell.

Or you can format a range of cells by clicking on one cell, holding down your mouse button and dragging up or down, left or right.

Then use the File menu or the toolbar icons to select a new format, which will be applied to the cells you've selected.

And remember you can always click on Edit...Undo to undo any formatting changes you've made.

About this Tutorial

This tutorial was originally written by Paul Grabowicz for students in his Computer Assisted Reporting class, and later modified for public use. Len De Groot contributed to this tutorial.

A special acknowledgment to Brant Houston and his book, Computer-Assisted Reporting: A Practical Guide. I first learned how to use the Microsoft Excel spreadsheet program many years ago using that book, which took a step by step approach based on how a journalist uses a spreadsheet. I tried to use the same approach with this tutorial. But I highly recommend reading Brant Houston's book as it is more in depth and has many examples of how reporters have used spreadsheets.

Republishing Policy

This content may not be republished in print or digital form without express written permission from KDMC. Please see our Content Redistribution Policy at kdmc.berkeley.edu/license.