spreadsheets

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.