Excel Checkbook / Register Spreadsheet (w/Sorting Macros and Reconcile)
| Price: | |
| File Type: | Excel Macro-Enabled Spreadsheet (.xlsm) |
| Operating System: | Microsoft Windows 7 & Later |
| Software Req'd: | Microsoft Excel 2007 or Later |
| File Size: | ~940kb (ZIP file) |
| Last Update: | 2020-09-27 |
| Current Version: | 6.71 |
| Macros Required? | Yes (Excel Macros "How To") |
In the years since I began offering Excel financial spreadsheets at It's Your Money, I've noticed lots of people searching Google and Yahoo for terms like "checkbook register spreadsheet," "Excel checkbook," and "checking account spreadsheet." And often they'd end up at IYM.
But I didn't have a really nice checkbook spreadsheet to offer them ... until now.
Click these images (shown here in Excel 2007) to check it out:
If you've spent much time reading my site, you'll know that I'm a longtime, super-devoted Quicken user. But I also understand that not everyone needs (or wants!) all the fancy stuff that programs like Quicken offer.
Sometimes, especially if you're just beginning to embark on the journey to Financial Independence, all you really need is a simple spreadsheet that can help you track your bank- and credit-card balances. Oh, and show you what you spend your money on — that's where Categories come in.
I created this Check Register spreadsheet expressly for those folks. There are other check-register spreadsheets out there, of course. And some pretty basic ones are free.
But they won't let you categorize your spending.
My spreadsheet DOES.
And I absolutely believe in categorizing one's spending. That, to my mind, is probably the single biggest benefit of Quicken and similar programs — they let you see exactly where your money is going — and none of the less-than-twenty-bucks check-register spreadsheets I found offered a way to categorize one's money flow. One site, in fact, even suggested that it can't be done with Excel — that you'll need Quicken or the now-defunct Microsoft Money to help your organize your spending into customizable categories.
They're wrong. And I have the spreadsheet to prove it.
My spreadsheet does offer Spending Categories. And they're customizable. And you can use them to see where your money is going. Plus I threw in a few other geeky spreadsheet goodies.
One more thing: Just because I call it a "checkbook register" doesn't mean that that's all it's good for. My wife and I currently use it to track some of our small-business-related receivables/payables accounts. Since the categories are entirely customizable, they come in pretty handy for this sort of business-account tracking, too.
Interested in learning more? Just read on!
Watch an Intro Video
Interesting in watching a quick Flash video tour of my Check Register spreadsheet? Well, here you go:
Spreadsheet Requires Excel 2007 or Later
This isn't a stand-alone program. It's an Excel spreadsheet, which means it requires that you have Microsoft Excel installed on your computer for it to operate. Further, I've only tried it on Excel 2007 and later versions — so it might work on earlier versions of Excel, but I can't guarantee it.
As far as operating system requirements, it works on Windows 7 and later versions. (Mac users see the note below!)
What Can This Thing Do?
What can my Checkbook Register spreadsheet do for you? Glad you asked.
For starters, the spreadsheet consists of seven separate worksheet pages.
And with those seven worksheets, you can:
- Monitor your spending with up to 200 user-created spending categories. When entering your transactions, Categories are selectable from a drop-down menu, if you so choose.
- Create and use up to 15 transaction types (check, ATM, ETF, etc.), all customizable by the user. So it works great as a register for credit-card and loan accounts too!
- List up to 50 recurring transactions in the Recurring Transactions Log. No more forgetting what bills are due, and when!
- Comes with three Register worksheets, so you can track multiple accounts together in one spreadsheet!
- Rapidly sort Transaction Types, Spending Categories, and Account Transactions with one click.
- Show or hide the spreadsheet Reconcile Form with one click. It allows for simple, computer-assisted reconciliation of your Register spreadsheet and bank statement.
- Easily move between worksheets.Convenient hyperlink buttons let you quickly move between worksheets.
- Category Report shows you how much you've spent in each of your categories in any time period!
And oh yes ... you can get rid of your paper checkbook registers once and for all. (They make pretty nice coloring pads for the toddler in your life — once you yank out the staples, of course.)
Your Download Includes More Stuff...
Which Means There's a Freebie!
When you buy the Check Register spreadsheet, you'll be able to instantly download the .ZIP file (~560kb) from my site. This file will contain one PDF (a User Guide) and three spreadsheets:
- A Check Register Spreadsheet optimized for Excel 2007 and later.
Additionally, I'm happy to include my newest Excel Balance Sheet (Statement of Net Worth) Spreadsheet (v2.0) with my Check Register download. So at no extra charge you'll also get:
- Excel Net Worth Spreadsheet (my Version 2.0) compatible with Excel 97 through 2003.
- Excel Net Worth Spreadsheet (my Version 2.0) optimized for Excel 2007.
Curious? Here are a few screenshots of the Net Worth Spreadsheet (click to enlarge in new window):
As for the Net Worth Spreadsheet, personally, I think it's pretty sharp. For now, this is the only way anyone will be able to get it. I know of at least two other sites which offer Excel net worth spreadsheets that are similar to mine, and they're both asking $15 for it. So by purchasing my Check Register spreadsheet, it's sort of like you're making money on the deal — and that's not even counting the cash you'll save because you'll be able to track your spending more diligently!
For more info on just what a Balance Sheet is, or why monitoring your net worth is such a big financial deal, please check out my "Worth of Net Worth" page.
Try Out My Free Check Register Spreadsheet
Want to see the spreadsheet for free? No problem. It'll look about the same as the pay version, but the functionality is reduced — no sorting macros, only one Register worksheet, and no reconciliation features. Download it from my Free Excel Check Register page and take it for a spin.
Nifty Features of the Pay Version
The pay version adds a few niceties to the free version — nothing earth-shattering, I suppose, but worthwhile to users like me who prefer to let Excel do as much of the grunt work as possible!
- Adds one-click sorting to your transaction Types, Categories, and register transactions (requires macros enabled).
- Adds reconciliation ability, so you can effectively match your spreadsheet transactions to your bank statement.
- Reconciliation form is hideable/viewable with toggle button (requires macros enabled).
- Hyperlink buttons allow you to easily switch between worksheets without having to use Excel's bottom-of-the-screen worksheet tabs.
- Plus it's dirt cheap and a super-easy way to support It's Your Money!
- The Category Report can tally your spending from ALL 3 Register worksheets!
Future Updates at No Charge
I occasionally tweak and update my for-pay spreadsheets, and I'm sure the Check Register will be no exception. If you purchase the full version, you're entitled to all future updates at no charge. If you'd like to be kept abreast of these updates as they happen, just email me and I'll add your email address to my update-notification list.
It Carries a Full 30-Day Guarantee
So you paid for and downloaded my Check Register spreadsheet, and it isn't what you expected. Or you just can't make the thing work. Or your version of Excel is too old.
No problem. Just let me know within 30 days of purchase, and I'll refund your money.
There are a lot of things in life that I really like. One of them is happy customers.
Some Screenshot Goodness
NOTE: Screenshots are from Excel 2007. Spreadsheet appearance will vary in other versions.
The main register worksheet looks like this (the pay version, anyway) with the Reconcile Form showing:
And here it is with Reconcile Form hidden, which I like because the Reconcile Form (if you decide to use it) is needed only once per month. The screen just looks a bit neater this way:
Below are three more shots (click each to enlarge) that show (1) the Types worksheet, and the Types drop-down box on the Register itself; (2) the Categories worksheet, and the Categories drop-down box on the Register itself; and (3) the Recurring Transactions Log.
Not Just for Checking Accounts
While I put this spreadsheet together with the idea that it could function as a simple check register, the truth is that it will probably work as a register for most any type of basic bank account: savings, loan, credit card, or whatever. You could even use it to track your cash spending. Just open a new workbook (spreadsheet file) for each account that you wish to track.
Basically, if the account you're dealing with uses credits and debits, or deposits and withdrawals (which is pretty much every account known to man), then this spreadsheet could probably be used to track it. In the case of an investment account or something similar, you'd just have to track it at a very simplistic level. (Meaning this spreadsheet won't compute fancy percentage-rate returns, or anything close to it.)
Credit Card Accounts: The spreadsheet should work fabulously for tracking credit-card accounts. Your purchases would be entered as debits, as would interest charges and other fees. Payments would be entered as credits.
Loan Accounts: Yep, it should be track these just fine, too. You would enter interest charges and such as debits. Enter your loan payments as credits.
If you're looking to track "what comes in" and "what goes out" on your account, along with categorizing your transactions and letting the spreadsheet do the yucky math, then this spreadsheet is for you.
Note: It Utilizes Visual Basic Excel Macros
Before you get started with the paid version of the spreadsheet, you'll need to configure Excel to let the spreadsheet's macros execute.
For example, each workbook page has a command button which makes sorting the data on that page much easier. It's a streamlined, one-click task, so it requires some fancy code to run. By default, Excel won't allow macros to run. So if you wish to use these macros, you must enable macros in your version of Excel.
For instructions on enabling Excel macros, plus some screenshots, see my Excel Macros "How To" page.
If I don't have quick access to Excel and the Check Register, how am I supposed to remember how much I spent, and what I spent it on?
It's quite easy: Save your receipts in your wallet or purse. Or utilize a Cash Flow box as I do!
Really. I've been doing it this way for years! I tuck all my receipts into my wallet, and my wife stores hers in her purse. Once we're home, we remove the receipts and toss them in our Cash Flow box. Then, every few days, I sit down and log all our expenditures.
Once you get into the habit, it's a snap!
Customize the Check Register Spreadsheet
Obviously, the Transaction Types and Categories are customizable right out of the box. Wouldn't make much sense otherwise, would it?
Beyond that, if you know your way around Excel, then feel free to customize the Check Register spreadsheet as you like. Before you do, though, be sure to make a backup copy of the spreadsheet!
The spreadsheet is protected (no password, though), so if you want to make changes to locked cells, you'll have to unprotect the worksheet(s) you wish to change. On Excel's menubar, select:
REVIEW → UNPROTECT SHEET
You should now be able to customize the spreadsheet however you like.
Use the Reconcile Feature. It's Easy!
Reconciling your account, also sometimes called balancing, is the process whereby you compare your register's account transactions against those on your bank's monthly statement. The idea is to resolve any differences between the two. At the end of reconciliation, your register balance should exactly match your bank statement's ending balance for the specified date.
Reconciliation is important because it's the only surefire way to truly ascertain the exact balance in your bank account — to make sure that you and your bank haven't made any mistakes or wrongly recorded any transactions!
When reconciling your account for the very first time, be ready to spend some extra time looking for discrepancies. This is because reconciliation will be affected by transactions from the period shown in your current bank statement, as well as transactions before that period.
Clicking the "RECONCILE FORM: SHOW" button (Box 1 in the image below) will unhide the spreadsheet rows which pertain to (you guessed it!) reconciling your account.
Grab your latest bank statement for whatever account your spreadsheet tracks, and in cell H2 (Item 2 in image), type in the Ending Balance from your bank statement.
- Click the "SORT BY DATE" button to sort all your transactions from oldest to newest.
- Scan thru your bank statement. As you come across transactions in the statement that match exactly those in your register, type a "C" or "c" in column J. This is shown at right. (FYI: The "C" stands for "cleared.")
- If your bank statement contains transactions that you missed, simply add them at the end of your register, and click "SORT BY DATE" to move them. Mark them with a "C" or "c" as necessary.
- If your bank statement contains transactions that you recorded incorrectly, simply fix them. Once they match the statement, mark them with a "C" or "c".
- When the amount in cell H3 ("Amount left to reconcile") equals zero, you're done and reconciled!
Create Your Own Transaction Types
Simply put, "Transaction Types" are the method by which a transaction takes place. For instance, if you're logging transactions that relate to a standard checking account, you might use Transaction Types such as:
- Deposit
- Check (Paid by check)
- CKCD (Checkcard / debit card)
- Online (Online use of debit card)
- TXFR (Transfer to/from another account)
- EFT (Electronic Funds Transfer)
- ATM (Withdrawal from ATM)
And there could even be more. As another example, if you were logging transactions that related to a credit-card account, you might use:
- Charge (Normal credit-card purchase)
- BTXFR (Balance transfer)
- Online (Online purchase or transaction)
- C-Check (Convenience check)
You don't have to enter a Transaction Type with every transaction, of course. But I've found that there are times when knowing how a transaction took place can be quite helpful.
Create Your Own Transaction Categories
Some people think I'm exaggerating when I say that categorizing your expenditures is a huge key to mastering your money.
I'm. Not. Exaggerating.
Why is it so important? Because until you know what you're spending your money on now, you have zero effective groundwork for making spending changes in the future.
Think about it this way: Let's say you know that your last three trips to Wal-Mart cost you a total of $450. You could figure that out easily enough from your paper checkbook register, or even from your bank statement, if you'd used a debit or credit card.
But what did that $450 get you? $450 worth of groceries?
Or $375 worth of groceries, plus $75 worth of other necessary household consummables?
Or $300 worth of groceries, plus $150 worth of stuff that you and your kids really didn't need?
Telling yourself, for example, that "We really have to cut down our spending at Wal-Mart," is one thing. But how do you make a workable plan to pull that off when all you really know is that you tend to spend a bunch of money at Wal-Mart?
The answer: You can't.
On the other hand, you certainly can make changes when you know what actually comprises your Wal-Mart spending. You know where to make spending changes when you know how much you've been spending on "Groceries," or "Household Consummables," or "Child - Toys."
In any case, my Check Register spreadsheet allows you to create and enter up to 200 of your own spending categories. You then categorize each transaction as you enter it.
You'll do this in your Check Register workbook by clicking the "Categories" tab and entering your categories in the space provided. You don't need to come up with a hundred of them right when you get started — in fact, I'd wholeheartedly advise against that. Instead, keep this as simple as possible. Start with the basics. More workable and meaningful categories will occur to you, over time, as you enter more and more transactions.
When you have a transaction that needs a new category, just click over to your "Categories" worksheet and add it to the end of your current batch. Then sort your category list. It'll now be available from the drop-down box in your Register's Category column. (See the screenshot at the beginning of this section.)
Yes, You Can Do Split-Category Transactions
(A Bit Imperfectly)
In our previous section "Working with Transaction Categories," we talked about having three Wal-Mart visits that tallied a total of $450. Let's say that the last of those three visits saw us spend $120. Because we're dutiful spending categorizers, we used our free Receipt Splitter spreadsheet to find that the $120 spent at Wal-Mart was comprised thusly:
Groceries: $90
Household Items: $20
Pet Items: $10
And because we want our spreadsheet to reflect our "spending reality" and show where our money is really going, that's the way we should enter the Wal-Mart transaction. One transaction, split into three categories. But how to do this?
I'll be completely honest: This is where Excel simply cannot keep up with Quicken and Money and programs of that ilk. They're database-oriented, which means they're built precisely for this sort of thing. However, we Excel-users do have a workaround, clumsy though it may be. We just enter our $120 Wal-Mart trip as three separate transactions, each with its correct category. Click the following image for an enlarged visual:
Notice how each part of the split has the same ITEM #. In this case, it was a debit card purchase, so I arbitrarily assigned it Item #1234. This, plus the note I added to the MEMO of each transaction telling me that it was part of a split for my ITEM #1234, should keep me lined-out when it's time to reconcile my account.
By doing this, when it comes time to reconcile the account, I'll remember that those three items were all just part of one transaction — not three truly separate transactions. In my bank statement, of course, I'd just see a single $120 debit-card transaction from Wal-Mart. But because I know the value of categorized spending, in my register, I split that $120 expense into three separate entries — one for each spending category.
When reconciling, as far as the spreadsheet is concerned, putting a "C" in the Cleared column of each of these three entries accomplishes the same as putting a "C" in the Cleared column of a single $120 entry, had I done it that way.
And that's how I handle split-category transactions.
Total Your Spending Categories:
Method #1 - Category Report
Users tell me the thing they like best about my Check Register is its CATEGORY REPORT worksheet:
Just enter the starting and ending dates for your report, and Excel shows you how much you've spent or earned in each of your Categories.
Seeing where your money goes couldn't be easier!
Total Your Spending Categories:
Method #2 - Excel AutoFilter
Longtime Excel users know how powerful its sorting capabilities are. With SORT, we have another way to see, for example, exactly how much we spent last month on groceries. (Although the CATEGORY REPORT worksheet can do this for you, too.) First, we'd unprotect the sheet:
REVIEW → UNPROTECT SHEET
Then we'd select all data (from columns B to J) the date range we wish to see — in this case, the month of January:
Then we'll SORT the selected data (DATA → SORT) ...
... as shown here. We wish to sort by Category, so we select Column F in the "Sort by" dialog box:
Then we'd click the OK button, and get something like the following (click to enlarge). We use our cursor to select only those "Grocery" category entries from January, and Excel's AutoSum feature tallies the three entries in the bottom right corner of the screen:
In this case, we spent a whopping $148.89 on groceries in January. To get our data back to the way it was, we would just click the "SORT BY DATE" button.
But There's An Even Better Way ... AutoFilter!Or we could do it the easy way. Excel AutoFilter (if your version has it) comes to the rescue!
Just select AutoFilter from the Data drop-down, like so:
You'll then see drop-down boxes at each column heading, like this:
Click the drop-down icon next to the Category heading, and since we want to see our grocery spending, we select "Grocery" from the drop-down options:
And like magic, Excel filters out all data except that which has as its category "Grocery." Click the image to enlarge:
Use your cursor to select the Grocery expenditures from January, and Excel's AutoSum will tally it for you in the lower right side of the screen, just like it did for the SORT method above. How easy was that?
To get your data back to the way it was — with all of it showing — simply head back to the menubar and select DATA → FILTER → AUTOFILTER and uncheck AutoFilter.
NOTE: If you've scrolled down your spreadsheet at all, thus hiding data behind the locked blue rows (the last of which is Row 7), then this hidden data will not show up when Excel does its AutoFiltering thing. I often use this "feature" to pre-eliminate any data that's earlier than I wish to analyze. For example, if I had a year's worth of transactions in my register, and I only wanted to see my Grocery spending from June, then I could scroll down so that all data from before June would be hidden behind the locked rows at the top of the screen. AutoFilter would then ignore the hidden data.
Feel free to play around with AutoFilter to see all the great stuff it can do!
Using the Recurring Transactions Log
Another goodie I've added to the Check Register is the Recurring Transactions Log:
The Recurring Transactions Log is simply designed to help you remember what regularly-recurring bills you have, and when they're due.
Items you enter on the Recurring Transactions Log have no effect on your Register; they won't be automatically entered by Excel or anything like that.
But what Excel does do here is highlight those transactions which have a RECURRANCE DATE which matches today's or tomorrow's date.
Make sure your recurring bills are listed here, and it ought to be a breeze to remember them at crunch time!
Beginning a New Spreadsheet
Use my Register spreadsheet long enough, and it's inevitable: You'll run out of rows and need to begin a new spreadsheet.
Numerous readers have contacted me with this concern. "How do I get my Types and Categories and at least some of my transactions into a new spreadsheet?" they ask. "I tried to copy and paste the stuff into a new Register, but it blew everything up."
That's because Excel's copy-and-paste function, by default, copies more "stuff" than you want! Not sure what I mean? Well, you'll get the explanation — as well as the correct way to bring your data from one spreadsheet to another — in this detailed tutorial:
Questions and Comments? Contact Me!
Got a question or issue? Don't hesitate — drop me a line. I'll get back to you as soon as possible!
