Another application for Google Sheets is to keep track of the ages of characters in a novel or memoir that spans several years. The example below also shows the use of functions, the difference between relative and absolute addresses. and how to freeze row and column headings while scrolling the rest of the cells down or to the right.
Make a new spreadsheet
In row 3 of a new spreadsheet, type in the field name followed by names of your characters, tabbing between each entry: for example,
AGES Me Mom Grandma
In row 2 above each name, type the birth date. Try
BIRTHDAY 3/18/1988 6/9/1963 9/26/40
In row 4, type today’s date in the first column, A, and tab to column B. Now look for the date difference formula, DATEDIF:
- Click or tap the summation symbol on the menu bar; it looks like a printed letter M on its side.
- Select “More functions…” (You need an internet connection at this point.)
- Scroll all the way down the Date functions to locate DATEDIF. Notice the Syntax column 3, and the Description beside, with an opportunity for a fuller explanation of each parameter within parentheses on a separate page.
- Type =DATEDIF(B$2, $A4,”Y”) in the formula bar, meaning that you want the difference between the birthday above ‘Me’ and the date that you typed into A4.
- The equal sign always precedes a formula.
- The dollar sign before a row or column freezes that value.
- In first parameter the row, 2, is fixed; the column, B, is relative; it will change when the formula is copied to another column.
- In the second parameter the column, A, is fixed, the row is relative and will change when you copy the formula to another row.
- Quotation marks surround text. The letter Y is defined in the full explanation as returning the value in years.
- Press enter to put the formula into cell B4.
- Now select B4 and drag to the end of the list of names; in this example, D4. Enter to propagate the formula and calculate all the ages.
- Near the middle of row 1, type “Table of Ages.”
Check the ages. Then put a different date–maybe your last birthday–into cell A5. Copy the formulas from row 4 into row 5, or in as many rows as you like. That is the power of spreadsheets.
Freezing row and column headers
When the row or column you want to work on does not appear within your view of the spreadsheet, you can scroll down or to the right. To keep the row or column headers in view, look in the View menu.
- Select any cell in row 3.
- Select the View menu. Notice that you can freeze rows, columns, or both.
- Select “Up to current row.” You will see a gray line separating the top 3 lines from the rest of the sheet.
- Select a row beyond the your current view. Everything above the gray line will stay on screen, while the cells below will move.
If you created the Expense Journal, go back and freeze the top 4 lines.