Introduction

A spreadsheet is a set of one or more data tables that can do automatic calculations.

There are three commonly used free spreadsheet programs. This tutorial is for Google Sheets, but all three programs are very similar.

Program	Advantages
Microsoft Excel	used in businesses and organizations worldwide; very powerful
Google Sheets	easy to access online and share files; part of Google Drive
Apple Numbers	has Apple simplicity and elegance

➜ Try it

Open the Haunted House Expenses 2018 spreadsheet.
Choose File ⇒ Make a copy, and save it to your Google Drive.

Formatting

There is a lot of information in this spreadsheet, but it is not very accessible. Customizing the formatting can make it more usable and nicer looking as well.

Cells can be selected individually, in blocks, or as entire rows, columns, or sheets.

To select this	Do this
A single cell	Click on the cell.
A group of cells	Click on the first cell, and drag to the last cell or shift-click on the last cell.
A whole column	Click on the letter at the top of the column.
A whole row	Click on the number on the side of the column.
Multiple columns or rows	Click on a column letter or row number, and drag or shift-click to select additional columns or rows.
All cells in a data region	Click on a cell in the region and push ctrl-A.
All cells in the entire sheet	Click on a cell in the region and push ctrl-A twice.

Once cells are selected, they can be modified using the formatting tools in the formatting bar below the menu.

To do this	Do this
Make the text bold or italic	Click on or .
Change the text color	Click on to select a color.
Change the cell color	Click on to select a color.
Set the number of decimal places	Click on .0 and/or .00.
Set the format of a number	Click on 123 to format the data as plain text, dates, percentages, etc.
Align the text	Click on to choose left, center, or right alignment.
Set other aspects of the text	Use the settings in the Format menu.

➜ Try it

Select row 1, make the text bold, and change the cell color and/or the text color to your liking.
Select column G, and set the numbers to have 2 decimal places.
Select columns F through I, and align the text on the right.
Position the curser on the line between A and B at the top of the columns so that the curser changes to a horizontal arrow. Adjust the width of the columns automatically by double-clicking, or manually by dragging. Do this for the other columns as well.
Make any other style changes you like, including on the Options page.

Sorting

One very useful feature of spreadsheets is sorting. However, we have to be sure Sheets knows which data to sort and which data to leave in place.

To do this	Do this
Lock header rows in place	Click on the row, and choose View ⇒ Freeze ⇒ Up to current row.
Sort the whole sheet	Click on the column, and choose Data ⇒ Sort sheet by column.
Sort part of the sheet	Select the region to sort, and choose Data ⇒ Sort range by column.

➜ Try it

Lock the header row in place.
Sort the table by location.

Formulas

Sheets will do all our calculations for us if we tell it what we want. Better yet, we can give it a single formula and have it apply it to multiple rows.

To enter a formula, cells need to be referenced. This can be done by directly clicking on them or by typing a reference.

To reference this	Type this	Example
A cell on the same sheet	the column letter followed by the row number	A4
A cell on a different sheet	the sheet name, followed by an exclamation mark, the column letter, and the row number	Options!A4
A range of cells	the reference of the first cell, followed by a colon and the reference of the last cell	A4:C20
An indefinite range of cells	same as above, but leave off one or both numbers	A2:A

To enter a formula, type "=" followed by the expression.

Formulas can be repeated without retyping. Sheets can automatically change the cell references in the formula to adjust for the new location.

To repeat a formula	Do this	Example
With automatic adjustment	Click on the cell with the formula already typed, and double-click or drag the box in the lower right corner.	=F2G2 in cell H2 will change to =F3G3 when dragged down to row 3.
Without automatic adjustment	Add a "$" in front of each letter or number you don't want to change, and then do as above.	=F2G$2 in cell H2 will be =F3G$2 when dragged down to row 3.

➜ Try it

Type =F2*G2 in cell H2 to calculate the total price of the first item.
Repeat this formula all the way down the spreadsheet.

Functions

A function takes any number of inputs, called arguments, and creates an output. There are hundreds of functions available in Sheets, such as the examples below.

Function	Arguments	Example
SUM	range of values being added	=SUM(H:H)
COUNTA	cells in which to count all data values	=COUNTA(H:H)
TODAY	(none)	=TODAY()
IF	what to check, output if true, output if false	=IF(D2="food","nontaxable","taxable")
SUMIF	where to look, what to look for, what to add	=SUMIF(D:D,"construction",H:H)
VLOOKUP	range of where to look, what to look for in first column of range, which column of the range from which to return the output, allow nonmatch	=VLOOKUP("Amazon",Options!E:F,2,0)

➜ Try it

Change the code in H2 to =IF(G2,F2*G2,""). Now the total column will be blank in rows without a price entered yet.
Click on G and choose Insert ⇒ Column right. Title the new column "Taxable".
Change the F2*G2 in cell I2 to F2*G2*(1+.09*H2). Now the Total column will add 9% tax in rows where a 1 is entered in the Taxable column.
Change the .09 in cell I2 to VLOOKUP(C:C,Options!E:F,2,0). Now the tax rate will be correct in each row based on the location. (Click the Options tab at the bottom to see the various tax rates.)
Fill column I with the new formula.

Debugging

A formula will not always work the first time you try it. Debugging is a normal, expected part of the process, just as proofreading is for writing.

Check for punctuation errors. In particular, check that all commas, parentheses, and quotation marks are in place.

If an error is stated, google the error.

See the documentation for the function you are using.

For complex formulas, try a simpler version. If that works, add on a little bit at a time to see what part causes the error.

Ask me or someone else to take a fresh look at the formula.

Data Validation

Data validation provides two benefits for data entry.

Benefit	Description
Ease of data entry	Menus and checkboxes display possible values.
Accuracy of data	Invalid entries are prevented.

➜ Try it

Choose Data ⇒ Data Validation, enter the cell range Purchases!B2:B, and the criteria "List from a range Options!A2:A".
Choose Data ⇒ Data Validation, enter the cell range Purchases!C2:C, and the criteria "List from a range Options!E2:E".
Choose Data ⇒ Data Validation, enter the cell range Purchases!D2:D, and the criteria "List from a range Options!C2:C".
Clear all data from column H, choose Data ⇒ Data Validation, enter the cell range H2, and the criteria "Checkbox".
Check the box, and fill down the column so that taxable is the default.

Pivot Tables

A pivot table is a summary report of a spreadsheet created by collapsing the spread by given criteria, such as to show what percentage of expendatures were from each location.

➜ Try it

Choose Data ⇒ Pivot table...
Enter the data range Purchases!A:J.
Add Location as a row.
Add Total as a value. Show it as % of grand total.
Go back up to the Rows box and change Order to Descending and Sort by to SUM of Total.
Double-click on the Pivot Table 1 tab at the bottom to change the name of the tab.
Change the formatting of the table to match the rest of the spreadsheet or however you like.