Lesson 4: Set Up the Spreadsheet

Paper check register with a symbol suggesting "not allowed"
Figure 1: Stop the madness with paper check registers

A paper transaction register is part of the past, but its purpose never will be. With a spreadsheet, manual computing is no longer needed. And forget about sloppy handwriting. Typed entries are the way to go.

In order to use the data collected in Lesson 3: Get Ready, follow the steps below to get set up.

  1. Select a spreadsheet application
  2. Log into Google
  3. Set up the spreadsheet
  4. Add balance formula and duplicate
  5. Format the cells

Select a Spreadsheet Application

There are multiple software applications to choose from. For example, Microsoft Excel and Google Sheets. Given Google Sheets is free, this lesson will assume a Google solution. However, the steps to set up the spreadsheet can be used in Excel.

Log Into Google

If you have an account, login. In fact, it’s likely that you already are. If you aren’t sure, go to https://www.google.com/. Do you see the Sign In Button? If not, you are logged in.

Screenshot showing the blue google sign in button in the top right corner of the browser page
Figure 2: Google.com when you are not logged in

You if see the button, click it and log in. If you don’t have an account, create one with the following instructions. Don’t worry, you don’t need a Google email to create a Google account. I didn’t.

  1. Go to https://accounts.google.com/
  2. Click on Create account and follow the steps that Google asks you to complete.
Google sign in interface
Figure 3: Google Sign in Interface

Set Up the Spreadsheet

Now that you are logged in, go to your Google drive and start setting up your spreadsheet.

  1. Go to https://drive.google.com/ 
  2. Click on the New button.
Google Drive options with arrow pointing to New
Figure 4: Google drive menu
  1. Select Google Sheets then Blank spreadsheet.
Google drive menu with arrow pointing to Google Sheets
Figure 5: Google drive New blank spreadsheet option
  1. Observe the blank sheet. An Excel spreadsheet will be similar, if you choose that option.
Screenshot of an empty Google Sheet
Figure 6: Screen shot of Google sheet
  1. Enter a sheet name: Digital Tracker and Planner.
Google Sheet screenshot showing the sheet name
Figure 7: Google sheet with a title
  1. Enter column headers: Date, Chk # (i.e., check number), Transaction, Note, Withdrawal, Deposit, X, Balance.
Google Sheet screenshot showing the column headers
Figure 8: Google sheet with column headers for the digital tracker and planner

Add the Balance Calculations

There are two simple calculations performed when managing your checking account balance:

  • Balance minus check written = new balance.
  • Balance plus deposit = new balance

The power of the spreadsheet allows you to perform both calculations at the same time. The formula for row 3 under the balance column is =H2+F3-E3, where the balance cell equals the balance from the cell above plus the deposit minus the withdrawal. You can enter it once at the top and duplicate it down the balance column, per the instructions below. 

  1. Enter the formula in the third cell under the balance column: =H2+F3-E3. If you are familiar with building formulas by clicking on cells, feel free to do so.
Google Sheet showing the formula : =H2+F3-E3 entered in row 3 under column H
Figure 9: Google sheet balance formula
  1. Next, duplicate the formula by clicking on the formula cell. Hold down the shift key and arrow down to highlight cells below. 
Google Sheet showing cells highlighted in column H
Figure 10: Google sheet with highlighted cells in a column
  1. Click Ctrl+D on a PC or Command+D on Mac to duplicate the formula. Ultimately, you will duplicate the formula to the end of the 12 months of planned commitments, but you will do that later.
  2. To see what the formula in action, enter some numbers in the withdrawal and deposit columns. See the balance change from row to row.
Google Sheet showing the balance formula computing the values entered in columns E and F
Figure 11: Google sheet with practice numbers and balance formula

Format the Cells

The example numbers in figure 11 generated a negative balance. The little minus sign in front of the negative numbers doesn’t stand out. And, not all entries will be even dollars. Let’s apply a format to the three number columns. 

  1. Highlight the money columns E through H. Don’t worry about the G column not being a number column. It will still accept an X for each transaction checked against the bank.
Google Sheet showing columns E thru H highlighted
Figure 12: Google sheet with number columns highlighted
  1. Click on the 123 dropdown in the editor bar and select Custom number format.
Google Sheet 123 format option pointing at "Custom number format"
Figure 13: Menu that sets custom number format for a Google sheet
  1. Select the format $#,##0.00_);[Red]($#,##0.00).
Google Sheet screen shot of the custom number format with $#,##0.00_);[Red]($#,##0.00)
Figure 14: Number format that shows red for a negative number
  1. Observe the results.
Google Sheet showing the practice numbers as Dollars and a red balance
Format 15: Google sheet number columns formatted as dollars
  • The numbers now look like dollars.
  • The negative balance is now in red, much easier to notice. An important feature.

Imagine the numbers you entered were real and that they were predictions of future commitments and balance. The scenario above suggests that a $54.00 commitment will not be doable unless additional money is deposited or other commitments are postponed.

Next Lesson …

The next lesson depends on when you get paid. Choose the lesson that best applies to your situation. Lesson 5d assumes you have completed either 5a, 5b, or 5c.

Published by Cindy McCourt

I wear many hats: author, website planner, Drupal consultant, instructional designer, trainer.

5 thoughts on “Lesson 4: Set Up the Spreadsheet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: