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.
|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|
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 format_bold or format_italic.|
|Change the text color||Click on format_color_text to select a color.|
|Change the cell color||Click on format_color_fill 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 format_align_left to choose left, center, or right alignment.|
|Set other aspects of the text||Use the settings in the Format menu.|
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.|
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.||=F2*G2 in cell H2 will change to =F3*G3 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.||=F2*G$2 in cell H2 will be =F3*G$2 when dragged down to row 3.|
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.
|SUM||range of values being added||=SUM(H:H)|
|COUNTA||cells in which to count all data values||=COUNTA(H:H)|
|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)|
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.
Data validation provides two benefits for data entry.
|Ease of data entry||Menus and checkboxes display possible values.|
|Accuracy of data||Invalid entries are prevented.|
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.