Free Excel Check / Expense Register Spreadsheet
|File Type:||Excel Spreadsheet Template (.xlt);|
OpenOffice Spreadsheet (.ods)
|File Size:||~169kb (.zip) // ~598kb (.xlt)|
Looking for an easy-to-use, free check register spreadsheet? You've found the right place!
My free Check Register spreadsheet has become awfully popular, as you might imagine. The nice thing is that I know all my users (like you, hopefully) have taken the time to read my Disclaimer.
There. Now that that's out of the way, grab your copy of the Check Register Spreadsheet ...
...and keep reading to see everything it can do for you!
Here's the thing: I love Quicken as much as the next guy, but it's not for everyone. It takes a lot of time to learn; it isn't the most bug-free piece of software ever churned out; and it can be pricey and even frustrating. The simple fact is that not everyone needs Quicken or Money.
If you're just beginning your journey toward Money Smarts, then all you really need to get started is a simple check-register spreadsheet. You need something that can help you track your bank- and credit-card balances, right? And maybe show you where your money's going with the help of spending categories.
I absolutely believe in categorizing one's spending. That, to my mind, is probably the single biggest benefit of Quicken and Money — they let you see exactly where your money is going. As I looked around the internet, I found exactly ZERO free check-register spreadsheets that allow a way to categorize one's money flow. One site, in fact, suggests that it can't be done with Excel — that you'd require Quicken or Money to group your spending into customizable categories.
That would be true ... except for the fact that it's false.
Click this image to get a quick look at my free Check Register:
Yes, believe it. This spreadsheet does offer spending categories. Even better, they're customizable. So use them. See where your money's going. You might be surprised.
One more thing: Just because I call it a "checkbook register" doesn't mean that that's all it can do. A small-biz owner might use it to help track a receivables/payables account. And since the categories are entirely customizable, they could prove very useful for this sort of business-account tracking, too. You could use it to track a credit-card account, or an auto-loan account, or pretty much any account where there are inflows and/or outflows. (Which means pretty much any account.)
Interested in learning more? Just read on! (This isn't your average checkbook register; there are some things you need to know to get the most out of it.)
Spreadsheet Requires Excel 97 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 97 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 should work on Windows 98 and later versions.
Mac Users: It should work fine on Macs. But since I don't have ready access to one, I can't prove it.
What Can This Thing Do?
Well, it can do all the nasty math required to keep a running balance in your account, of course. That's kid's play. Aside from that, it can:
- Monitor your spending with up to 200 user-created spending categories — all selectable from a drop-down menu, if you so choose.
- Allow 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!
- Take advantage of Excel's AutoFilter (if applicable) for some heavy-duty, categorized-spending analysis!
And the best part of all: You can get rid of your paper checkbook registers once and for all. (They make spiffy coloring pads for 3-year-olds, by the way. Keeps 'em entertained for at least 10 to 20 minutes.)
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.
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.
Really. I've been doing this for years! I tuck all my receipts into my wallet, and my wife stores hers in her purse. Then, every few days, I sit down and log all our expenditures. (For a little more detail, read my blog post entitled Cash Flow in a Box.)
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!
Some changes — especially adding/deleting rows and columns — can wreak havoc on formulas that the spreadsheet uses to function!
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:
TOOLS → PROTECTION → UNPROTECT SHEET
You should now be able to customize the spreadsheet however you like.
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:
- 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
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.)
Memory fading? Use the Recurring Transactions Log!
My latest addition to the Check Register spreadsheet is the Recurring Transactions Log. It looks like so:
List all your periodic, recurring bills in the Recurring Transaction Log, and your days of "forgetting what bills are due when" are over.
NOTE: The spreadsheet won't automatically enter your recurring transactions — at least, I'm not enough of an Excel Master Ninja Guru to make it do that. But what the Recurring Transaction Log will do is show you which transactions are due either today or tomorrow. For an example, notice the transaction with the differing background colors in the screenshot above. That's the spreadsheet telling you which ones might need attention.
Yes, You Can Do Split-Category Transactions
(A Bit Imperfectly)
In a previous section, 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:
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 totalling $120 — one for each spending category.
And that's how I handle split-category transactions.
Tabulate Your Spending Categories with Excel AutoFilter
Right here is where all our work with spending categories pays off.
Excel users know how powerful its sorting capabilities are. With SORT, we can see, for example, exactly how much we spent last month on groceries. First, we'd unprotect the sheet:
TOOLS → PROTECTION → 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 unclick 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!
Check Out My Other (Better!) Check Register Spreadsheet
Once you've tried out my free version, please consider upgrading to my Check Register w/Sorting Macros and Reconcile. It's pretty darn cool, actually. And dirt cheap.
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 a Category Report worksheet which allows you to see your categorized spending and earning in any given time period.
- Adds reconciliation ability, so you can verify your spreadsheet transactions against those on 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!
Oh yeah — it also carries a full 60-Day, Money-Back Guarantee.
Any other Check Registers?
If you're one of those folks who follows the "envelopes" method of budgeting, you'll want to check out my Envelopes Check Register. It's free, too, but it works on a somewhat different level than this one.
Also, if you're interested in learning how to make your own check register in Excel, read my tutorial entitled "How to Make an Excel Check Register."
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!