How to Build a Checkbook Register in a Spreadsheet

For decades, the checkbook register was the primary tool for tracking spending.

Because checks were used for most transactions, it was difficult to know your true account balance since it could take days or weeks for a check to be cashed by the recipient.

Checkbook registers eliminated the guessing game and gave you up-to-date information on your bank account and cash flow.

Despite numerous mobile and online solutions for tracking spending, a checkbook register’s simple design and ease of use still makes it ideal for budding and professional finance nerds alike.

However, for most people, keeping track of a physical register is tedious and inconvenient.

This is where spreadsheets come in.

Tiller is the only tool that automatically updates Google Sheets and Excel spreadsheets with your daily spending, account balances, and transactions. It’s the most convenient way to track your finances in a spreadsheet.

But if you want to build your own checkbook register in a spreadsheet, a combination of Google Forms and Google Sheets is an easy way to keep an eye on your account balance on-the-go.

Step 1: Create a checkbook register spreadsheet

Start by creating column headers just like the ones in a physical checkbook register–date, description, type, and amount.

As money flows into and out of your account, record the transaction. If the transaction reduces the overall balance in the account, it is a debit; if it increases the balance, it is a credit.

The sum of your previous balance, debits, and credits yields the current balance. 

To make your spreadsheet even more user-friendly, use a formula to autosum your current balance. 

  1. Enter the following formula into the cell below your beginning balance figure: =G2-E3+F3
  2. Click on the cell containing the formula. A blue border should appear around the cell.
  3. In the lower right corner of the blue border is a blue square. Click it and drag down the column about 50 lines or so. 
  4. If you need more lines, repeat Step 3.

Step 2: Create a Google Form to record transactions in real time

Now that the checkbook register Google Sheet is created, you’ll need to create a Google Form to enter your income and expenses as they happen.

Thanks to the Google Sheets app, your checkbook register spreadsheet is portable. However, entering data into a spreadsheet on a 5” or 6” screen can be difficult.

Using a Google Form allows you to capture all your transaction data with a simple form that will upload directly to your spreadsheet.

  1. In the menu bar of your checkbook register spreadsheet, choose “Tools” → “Create a Form.”
  2. Create your form using the same columns in your spreadsheet – Date, Description, Type, Debit, and Credit. Under “Type,” be sure to include all your budget categories. 
  3. Share the Form with yourself (and anyone you co-budget with) using the “Send” button in the top right of the screen. You will receive an email with a link to the Form. 
  4. Open the email on your phone and click on the link. 
  5. Save the link to your phone’s home screen so you can access it quickly.
  6. Any time you make a purchase or receive money, open the Form on your phone and enter the information. Your entries are automatically stored in your spreadsheet.

Step 3: Create a cash flow projection spreadsheet to monitor future spending

Cash flow is the net of money coming into and going out of your accounts. A positive cash flow means you are spending less than you’re bringing in, indicating that you have additional funds available for savings and/or debt repayment.

A negative cash flow means you are spending more than you’re bringing in, indicating that you are going into or further into debt.

To create your cash flow projection spreadsheet:

  1. Enter your starting balance and your budgeted income and expenses for each budget category. Allow for fluctuations such as weather changes that will affect your utility bills, birthday and holiday gifts, seasonal travel, back to school clothes and supplies shopping, etc.
  2. To calculate your ending balance, using the formula =B2+B4-(SUM(B7:B19))
  3. The starting balance for the next month is the ending balance for the previous month.

At the end of each month, update your cash flow projection spreadsheet with your actual spending to see how your projections change.

To calculate your total spending by category from your checkbook register spreadsheet, use the formula =SUMIF(D:D,”Category”,E:E)

If your projections show your account balance increasing each month, you may want to put additional money toward savings or debt repayment, getting you closer to your financial goals.

If your projections show your account balance decreasing each month or going into the negative, you can adjust your spending to prevent those projections from becoming reality.

As the saying goes, “What gets measured gets managed.”

By using a checkbook register Google Sheet and a linked Google Form to track your finances in real time, you can monitor your account balance and the day-to-day health of your finances.

Using this data, you can create a cash flow projection spreadsheet to help you manage your finances for the long-term.

Leave a Comment