Checkbook Register

• Version
• 5'995 Downloads
• 225 KB File Size
• January 3, 2022 Updated
• 5 Number of comments
• ★★★★★

  ★★★★★
  Rating

The days of writing checks are gone – or at least going fast. Like most people I know, I don’t even own any physical checks for my “checking” accounts. In the rare case that I do need a physical check (twice in the last two years), my bank is more than willing to print one. Either as a cashier’s check (at a cost) or as a blank check with the bank’s information, my account number, and any check number I would like printed on it. And if I don’t want to take the time to go to the bank, a quick online search of “check template” results in several sites that offer a blank check template or software. As easy as this is, I’m sure there are plenty of checking account owners today that have never “written” a check. Services such as automatic bill pay, online bank transfers, credit cards, debit cards, and instant payment methods (requiring only your cell phone) have almost replaced the need for checks.

So, why download a personal checkbook register template? Even though the check book might be vanishing, we still need to track the money that enters and leaves our accounts. The popular personal finance website, thebalance.com, gives four reasons to keep a physical check book register. I’d like to focus on two of those reasons: Accountability and Budgeting.

Accountability (thebalance.com refers to this as “Control”) means knowing that all your money is being accounted for correctly. Banks operate financial systems that manage millions of transactions and they are governed by strict regulations. They have to get the calculations right. But, have you heard of a “bank error.” Yes, human error is possible in banking. (And, as this NBC article suggests, if the error is in your favor, don’t keep the cash). Maintaining a checkbook register will help you get on top of the error fast, get back on track, and hopefully prevent any overdrawn checks.

Budgeting requires an accurate picture of your spending. Keeping track of income and expenses with a transaction register is a great way to record the comings-and-goings of your money.

Quick Overview

Let’s take a quick look at this Checkbook Register spreadsheet to see if it’s a template you would like to use to help balance your checkbook. The main worksheet appears like a basic checking account statement you would receive from your bank. It has name, period, beginning balance, statement balance, and checkbook-balance fields. The “Type” and “Category” columns offer customizable drop-down menus for easy data entry and detailed record keeping. Debits and Credits are individual columns. The beauty of this digital register is that the “Balance” column keeps a running tally of your checkbook. (See the Checkbook Register Overview image for a graphic view.)

Checkbook Register Overview

Template Contents

There are two worksheets in this Checkbook Register spreadsheet: “Checkbook” and “Type and Category.”

Checkbook

The “Checkbook” worksheet is the main component of this spreadsheet (displayed in the Checkbook Register Overview image). It provides the control fields (changeable fields that define the spreadsheet boundaries) and the data entry rows that define each transaction.

I’ll cover how to use the spreadsheet in the next section, but first I’d like to point out three features of this worksheet: the drop-down lists, the highlighted debit and credit columns, and the clear check option.

The first drop-down list is the “Type” column. In our example (see the Type Drop-Down image below) you can see some of the options available when clicking the drop-down arrow. The option selected with will populate the field. In this example, I chose “Check.”

Type Drop-Down

The next drop-down list is in the “Category” column. This drop down is a list of income and expenses. The selection also takes us to the next feature in this worksheet – the highlighted debit and credit column. When you select an “Expense” option in the menu, such as the “Expenses - Household” in our example (see #1 in the Category Drop-Down image below), the “Credit (+)” field switches to a dark blue format (#2 in the image below). This is to make it easier to visualize our debits and credits. Based on the chosen “Category,” the workbook is programmed to block the incorrect column with a blue highlight and diagonal lines. So, if you choose an “Expense” item, the “Credit (+)” column will be blocked. An “Income” item blocks the “Debit (-)” column. (Note: Although the column is visually blocked, you can still enter numbers into this column. Make sure to enter the number into the non-formatted cell.)

Category Drop-Down

So, to complete our sample data for check #104, we would enter the expense ($75.00) into cell H15 (see the Expense Example image below).

Expense Example

The last worksheet feature I’d like to point out is the clear check option. Notice that column G is titled “C.” That stands for “Cleared.” When a check has been processed through the bank and the funds distributed to the payee, then the check is considered “cleared” through the bank. With a physical check, this process can take several days and is dependent on when the payee deposits the check. Since most of today’s transactions happen with some form of digital transaction, this process is immediate in most purchases. When looking at your online checkbook register, you might see a transaction in a “Pending” state. This is equivalent to an uncleared check and has not yet been deducted from your account. But, since you’ve authorized the purchase, you must not consider it available money. The “C” entered in cell G12 indicates that the funds for this purchase have been distributed and the transaction has “cleared” the bank. The status of each transaction will come from the bank statement.

In our worksheet, when we enter a “C” in column G the entire transaction (row) is highlighted in light blue. This creates a visual indicator that the check has cleared and that it should be counted in the bank’s summary of our account. See the Cleared Check Example image to view what our newly entered check #104 looks like when a “C” is entered in cell G15 (similar to the transaction entered on row 12).

Cleared Check Example

Type and Category

The “Type and Category” worksheet is simply a list of “Types” and “Categories” of transactions (see the Type and Categories Lists image). Columns C and D from the “Checkbook” worksheet use these lists for their drop-down menus.

Type and Categories Lists

These lists can easily be modified by adding new entries. The drop-down menus will automatically pick up the new entry as an option. To customize the lists in this way, simply make the following entry in D16: “Expenses - Internet” - without the quotes (Note: The entries must either begin with “Expenses” or “Income” in order for the highlighted debit and credit column feature of the “Checkbook” worksheet to function correctly).

Once you’ve made that entry, go into the “Checkbook” worksheet, select cell D16, and then click on the drop-down list arrow. You should see the new entry at the bottom of the list - you might need to scroll up in the drop-down list to see the entry (see the New Category image below).

New Category

How To Use the Checkbook Register Template

Now that you’ve seen the details of the spreadsheets, let’s enter our own sample data to see how we can use this workbook. Let’s start by clearing out the existing data on the “Checkbook” worksheet. First, delete the entries in the four control cells (C4, C6, C8, and H6 – leave H8 as is, since it’s a reference cell). Then select range A12:I15 and also delete the contents in those cells. We'll leave the data in the “Types and Categories” worksheet alone. This should now give you a blank template with the calculation and reference cells intact.

(Note: If you deleted some of the calculation cells, you can try to repopulate them with the “Undo” button – or keyboard keys “Ctrl + Z”. If that doesn’t get you back to where you want, you can always re-download the original file from The Spreadsheet Page.)

Entering Transactions

Start by entering your name in the “Name” field of the “Checkbook” worksheet. We’ll fill the “Checkbook Period” and “Statement Balance” later in our scenario – that will be pulled from a bank statement (either one mailed to you or pulled from your online bank account). If your account is new, the “Beginning Balance” is simply the amount you deposited when you opened the account. If you’re starting this register after using your checkbook for a few months (or years) you will pull this “Beginning Balance” field from the “Statement Balance” of your last statement.

But this is where you need to be careful! The statement’s current balance doesn’t mean that’s how much money you have available in your account. It’s the amount that’s available after the cleared transactions. So, it’s important to add any non-cleared transactions (transactions not yet reported on the statement) into this register so you can account for that money. Non-cleared transactions are those that appear on your online balance (either as “actual” or “pending”) but not on your statement. The reason they are not on your statement is that the bank didn’t know about them at the time they printed your statement.

In our scenario, we will use $1,000 for the “Beginning Balance” and, to keep the scenario simple, we will assume this is a new account (no non-cleared transactions). Use the data in the Dataset image and fill in the rest of the worksheet.

Dataset

Referring to the Dataset image, note that the “Statement Balance” (#1) is still blank. We’re waiting for the end of the month bank statement but, in the meantime, still filling in our register as we make daily transactions. Also, note the blank, green highlighted fields (#2). There was no appropriate Category for these transactions, so I left them blank (also, note that not selecting a “Category” leaves the “Credit (-)” column unblocked). Now that we’ve entered the data, we need to fix this.

To do so, I simply opened the “Type and Category” worksheet. At the end of the “Categories” column, I entered “Expenses - Rent” and “Expenses - Car.” Then, I went back into the “Checkbook” worksheet and select “Expenses - Rent” for cell D13 and “Expenses - Car” for cell D16. The “Credit (+)” fields in those rows were then appropriately blocked (see Updated Dataset image).

Updated Dataset

(Note: Since we add new entries at the bottom of the “Type” and “Categories” list, our drop-down menu is not alphabetized. You probably noticed as you entered this sample data and had to search for the options. See the Advanced section towards the end of this article to learn how to sort those lists.)

Entering Bank Statement Information

Now the month is over and the bank has created our bank statement. For our example, let’s enter the following statement information:

Checkbook Period = 09/30/2019

Statement Balance = $1,500

Also, in our scenario, all the transactions from September 1 to 25 are listed on the statement (the September 28 and 30 didn’t hit the bank until after they printed the statement). So, let’s “clear” the first six transactions by adding a “C” (see the Reconciliation image.)

Reconciliation

Now our checkbook register is complete. But let’s take a closer look at what the numbers mean. If we relied on the bank statement to tell us our available funds in this account, we would think we had $200 more that we actually do. Why? Because those transactions didn’t clear the bank before they printed the statement. Instead, they’re off by $200 (Statement Balance - Checkbook Balance = Available or $1,500 - $1,300 = $200). This is why we should keep track of our budget with a checkbook register, and not trust the bank. Not only can banks make mistakes, but they also aren’t always up-to-date.

(Note: If you operate off the actual online bank display of your account, they will be more updated than your statement. Unless the transaction was a check that was being held too long by the payee, then the bank will probably be updated with all the transactions. The September 28 and 30 transactions still might not have cleared but they would probably show as “Pending.” That would let you know that you’ve already authorized those purchases and you should consider them in your balance. But, if you only went with the online version and didn’t check the balance against your register, you might not catch any bank errors.)

Printing the Checkbook Register

This is a printable template. Simple choose “File… Print…” and make sure the scaling is set to “Fit Sheet on One Page.” It will produce a Portrait oriented printout.

Advanced Tips

Sorting the Drop-Down Lists

As I mentioned earlier, the drop-down lists on the “Type and Category” spreadsheet are not sorted alphabetically. Let’s correct that in this section so our data entry goes a little smoother. The trick when sorting a list in a template is knowing if that affects any other automatic calculations built into the spreadsheet. In this case, the process to sort them is the same as sorting any other list – it won’t affect the spreadsheet function. Highlight the list (don’t include blank cells), then choose “Data” from the toolbar and “Sort A to Z” – it’s that easy. You can confirm the sort by taking a look at the drop-down lists from the “Checkbook” worksheet.

Removing the Blank Spaces from the Drop-Down Lists

When choosing one of the options from the drop-down menus, you might have noticed all the spaces at the end of the list. Those are there because of the blank cells at the end of the actual lists – which allow you to add more “Type” and “Categories.” In order to speed up our data entry, this template allows us to remove those blank spaces and still have the ability to add more items. First, open the “Types and Category” worksheet and then select the blank, highlighted cells at the end of one of the lists (I chose the “Type” list which is displayed in the Drop-Down - Remove Spaces image [#1]). Right-click and choose “Delete” [#2]. You will then get the “Delete” option window. Chose “Shift cells up” [#3].

Drop-Down - Remove Spaces

After doing the same process for the “Categories” list, your worksheet should look like the Drop-Down - Final image. You can confirm that the blanks are removed by taking a look at the drop-down lists from the “Checkbook” worksheet.

Drop-Down - Final

When you need to add a new item in either list, simply select the last two cells of the “Type” list (or the last cell of the “Categories” list), right-click, and click “Insert.” Choose the option to “Shift cells down” and you will create a new line that’s highlighted in blue. Add your entry in the new cells and then re-sort the list.

Summary

If you’d like to start using this Checkbook Register template, simply download it from The Spreadsheet Page. With actual “checks” on the decline, this template is more of a checking account register template (it can even be applied to debit and credit card accounts). Hopefully, this article has shown you how using a physical checkbook register can help you keep track of the money in your accounts, allow you to keep financial institutions accountable for your balance, and help you record your transactions for budget planning.

Let me leave you with some advice from financial guru Dave Ramsey, who said, “You must gain control of your money or the lack of it will forever control you.”