How To Use Excel As Checkbook Register

How to Create a Checkbook Annals in Excel

I am an accountant by trade, just I love to spend my free fourth dimension in my workshop building dissimilar things.

Checking Account Register Tab

Eric Cramer

Keeping an accurate record of your checking account is vital. Using the cheque annals that comes with your checkbook requires a transmission calculation every fourth dimension you make an entry. In my feel equally an accountant, a loftier frequency of transmission calculations results in a higher rate of errors. It is and so like shooting fish in a barrel to either add something wrong or fifty-fifty write it downwardly wrong. I used to use Quicken because it was fully automated and had a lot of great tools built in, but it became expensive as they would require you to upgrade every few years to keep it fully functional. Later that, I did a search for checkbook annals templates and did not discover anything that I liked. Most were very generic and were too basic for what I wanted. Using Microsoft Excel, I created my own checkbook annals template.

Easy-to-Follow Step-by-Stride Instructions

Ane of the best things about creating your own spreadsheet templates is that y'all tin customize it how yous desire. You can add a Dashboard Tab that tin exist a quick expect into what you decide to rail. It could be income tax related items, such as belongings taxes, charitable giving, etc. It could exist that you desire to track your balance over time and place a line chart for that. Permit us begin by opening a new workbook in Excel.

Category Tab - My list here is only a sample list of categories. — Category Tab - My listing hither is but a sample list of categories.

Eric Cramer

Categories Tab

Kickoff by renaming one of the tabs as the "Categories" tab. This tab will be where all of the potential income, expense, and whatever other categories you can think of are stored.

In cell B4, type "Categories" and then format the prison cell equally assuming and alter the alignment to centre.
In prison cell B5, enter "*** Income ***" as a section heading.
Beginning in Cell B6 and continuing downward, enter any income categories that you want to track. I propose having at least the following categories: Wages, Gifts Received, Interest Income, Dividend Income, and Miscellaneous Income. Feel gratuitous to customize as yous come across fit. Yous may desire to break wages into more than one category if you take more than i income stream.
Go out a blank after you last income categories and then enter "*** Expenses ***" as a department heading.
Enter all of the expense categories that you want beneath the expenses heading. These tin be as wide or every bit narrow equally you wish. If you choose to enter any other headings, be sure to go on the same format with the three asterisks before and afterward the championship.
Every bit a preference, I like to shade cell B4 equally a darker color and change the font color to white. Too, highlight from B5 to a few cells past your final expense entry, shade this area a lighter color, and put a border around information technology to make it expect dainty and dandy.
You lot tin nonetheless add together more than categories by inserting rows inside of the shaded area.
Finally, highlight from jail cell B5 to the end of the shaded section. In the Proper noun Box, which is to the left of the formula bar (information technology should read B5), type "Categories" into the box. This sets up a proper name range that we will use on the next "Checking Business relationship Register" Tab.

Checking Business relationship Register Tab

This will exist the main tab of the spreadsheet. All of your transactions will be entered on this tab. It volition also exist useful in reconciling your account residual. Start past renaming one of the blank tabs as "Checking Account Annals".

Data Validation Input Box

In prison cell A1, enter a title for your account register. I called mine "Checking Transaction Register." Modify the font size to 28.
Highlight cells A1:J1 and shade the expanse whatever color that yous like. If information technology makes sense, modify the font color so that it is easily readable and looks overnice. While you have this area highlighted, add a bottom border to it.
In cell A4, enter "Engagement".
In cell B3, enter "Reference/". In cell B4, enter "Number".
In cell C4, enter "Payee".
In jail cell D3, enter "Expense". In cell D4, enter "Category".
In cell E4, enter "Memo".
In prison cell F4, enter "Eolith".
In cell G4, enter "Payment".
In prison cell H4, enter "Clr".
In jail cell I3, enter "Account". In cell I4, enter "Balance".
In cell J3, enter "Cleared". In prison cell J4, enter "Balance".
Highlight A3:J4 and shade the area any color that you similar. Also, change the font color equally appropriate. Change the alignment for all of these cells to center.
Change the column widths so that the register takes upward the whole screen. I would give you my widths, but depending on your settings, they are going to be unlike.
Highlight cells A5:A4000 and change the format to "Short Date", which can exist done either by clicking the drop downward on Excel'due south "Home" tab or by right-clicking and selecting "Format Cells." I chose row 4000 randomly and then y'all would non have to insert any rows any time presently. I would advise typing "End" into cells A4000:J4000 as a placeholder for navigating the spreadsheet. I will explain why in more detail afterward on.
Highlight cells B5:B4000 by clicking B5 so agree downward Shift-End-Down, which will highlight all the mode down B4000. This is why I had you copy "End" beyond row 4000 in the concluding step. Using Cease-Down moves the cursor either down to the bottom of a list or to the next cell with something in information technology. Holding the Shift fundamental down makes Excel highlight that department. Change the alignment of the jail cell to Left.
Click on prison cell D5. On Excel's "Data" card, click on the "Data Validation" button and select "Date Validation" from the driblet down. This will bring up an input box. Wait for the "Allow:" driblet down box, select "Listing" from the available choices. Under "Source:" enter "=Categories" into the box. Click "Ok." Copy prison cell D5, using Shift-Terminate-Downwardly, paste the formula all the fashion downwards to row 4000. This will add a drop down box that has all of the expense categories that you entered on the "Category" tab.
Highlight cells D5:D4000 and click on the "Conditional Formatting" push on Excel'due south "Dwelling house" menu and select "New Dominion. . ." This volition bring upward an input box. Nether "Select Rule Type:", select "Format only cells that contain." Under the "Edit the Rule Description:" section, modify the drib downwards that says "Cell Value" to "Specific Text." In the box on the far right, enter "*" and so click on the "Format" button. Click on the "Font" tab and modify the "Font" colour to white. So click on the "Fill" tab and select 1 of the red colors. Click "Ok" to shut the format box and click "Ok" over again on the rule-formatting box. This will change the cell color under "Expense Category" (Column D), to get red if you select ane of the category headings.
Highlight cells F5:G4000 by clicking in F5 and using Shift-End-Downwards and while continuing to concord the Shift key, hit the Left Pointer button one time, which volition highlight cavalcade G. Alter the cell format to currency.
Repeat the aforementioned process in I5:J4000.
Highlight the entire Column H, modify the alignment to center, and format it equally assuming.
In cell I5, enter the following formula: =F5-G5.
In prison cell I6, enter the following formula: =F6-G6+I5. This will automatically update your business relationship full. Re-create this formula downward by copying cell I6 and holding down Shift-End-Delete and so paste the formula.
In cell J5, enter the following formula: =F5-G5.
In jail cell J6, enter the following formula: =if(H6="C",F6-G6+J5,J5). The thought of this formula is to help you to reconcile your checking account register versus your bank account balance. Information technology only calculates the rows that take a "C" (Cleared) in column H. Whereas, Cavalcade I is your real business relationship residue even though some things have not cleared the bank. Re-create this formula down using the aforementioned method as above.
Shade A5:H5 a darker color and change the font color to a lighter color.
In cell C5, enter "First Balance".
In prison cell F5, enter in your showtime checking business relationship residual.
I choose to format my template by shading every other row lite blue and the other row every bit white. A quick tip to doing this is to do it for two rows. Highlight those rows and click on the "Format Painter" Icon on Excel's "Habitation" tab. Highlight the side by side row (A-J) all the way to row 4000. I also added a border around every box in the register including vertical double lines in cavalcade F (left side), Cavalcade H (both sides), and Cavalcade J (Right side).

Using Your Checkbook Register

Your checkbook register is at present ready to employ. Enter in all of your transactions every bit they occur. Be certain to enter the date of the transaction, a reference number, expense category, any memo data, and enter the dollar amount in the appropriate column. Balancing the checkbook register can be done past typing a "C" in Column H when something clears the bank. The "Cleared Balance" should necktie to your bank account. Balancing your account ofttimes is a great style to brand sure that your records and the banks are accurate. I recommend taking a few minutes and reconciling it weekly. This is a great way to also guard against fraud because you will know if anything suspicious hits your account.

This article is accurate and true to the best of the author'due south knowledge. Content is for informational or entertainment purposes merely and does not substitute for personal counsel or professional communication in business, financial, legal, or technical matters.

Jessica on March 05, 2020:

Is there an alternative mode to the categories? I currently only have the web version and it will not let me rename the listing Categories... any help with another manner this can exist done?

Eric on January 22, 2018:

Great setup instructions. They are very helpful. Any mode to then create a list that shows the amounts for each category? For instance, the full for "Wages" at a glance. ideally it would be nifty to do that for a selected date range...

Gyre to Continue

Chandra on January 05, 2018:

This was a wonderful find. Exactly what I needed. I have spent the last year trying to teach myself how to use excel. I am still using the paper cheque annals book. Needed a more efficient mode to balance the cheque book. I could not go the shift-end-down to work. That would be so quick. Not sure what I am doing wrong. Also - practice y'all actually utilize the quotation marks when you lot enter "categories" or "deposit" for example? Thank y'all in advance.

Beth on August 06, 2017:

Great! Cheers then much :)!

Robbie on May 11, 2017:

Give thanks yous so much for the tutorial! I have been doing things the erstwhile fashioned way and it has go quite time consuming. This will definitely assistance a lot! I have a question virtually the "Category" column. When I click on the drop downwardly box next to the cell, it doesn't bear witness my list of categories. The only selection it givens on which to click is my formula of "=Categories". I am sure it'southward something I did or did not practice. Where did I get wrong? Thank you for your help.

Abby on December 07, 2016:

This was so simple to follow that even *I* was able to create it. Thank YOU. My only problem is what Jonathan and Amanda referenced in a higher place. I tried both formulas in the J column merely if a previous entry does non have a "C" entered in the H column, then the H column can't compute a value. I become a #VALUE! in that jail cell. Whatsoever suggestions for this incredibly technology-challenged individual?

Jonathan on November 12, 2016:

With Excel 2016 y'all need to remove the last J5 from "=if(H6="C",F6-G6+J5,J5)" then it would look similar: =if(h6="C",F6-G6+J5). I put above formula and Amanda's value in my checkbook and it looked like: =IF(H6="C",F6-G6+J5,IF(H6=ISBLANK(cell),"",jail cell)). Worked like a charm! Thanks for the ideas.

Amanda on October twenty, 2013:

AH! I figured it out. I embedded an IF(ISBLANK(cell),"",cell) within the 'false value' of the formula y'all suggested. Sugariness! thanks for the tutorial and the brainstorming!

Amanda on October 20, 2013:

Though, I must add that I agree with John: this tutorial is what I was looking for! I had my annals by and large fabricated, but needed a simple mode to chop-chop and hands reconcile my register with my account. I'm very pleased with your reconciliation method! Too bad I couldn't take thought of information technology on my own, that would have saved me a lot of googling fourth dimension. And so thank you for that thought! And thank yous for reminding me of provisional formatting; I had wanted to color code some of my categories but was stumped. Since that was a 'duh' moment, I'm hoping my prior question nigh the formula will also take a simple respond. :)

Amanda on October 20, 2013:

The formula for the "Cleared Balance" column does not let a way to continue the cell blank if there is no transaction data entered at all. I guess neither does the balance column. I hadn't noticed the Balance column having that problem, besides, because I used an IF formula (if the appointment is bare leave the balance blank, otherwise add it up). For the Cleared Balance formula I tried imbedding another IF in the "false" value to proceed the cell blank if there was no transaction entered. It didn't work. Any suggestions?

Eric Cramer (author) from Chicagoland on July 25, 2012:

You are welcome. I am glad that you can use the instructions. Let me know if you lot have any questions.

John on July 25, 2012:

Exactly what I was looking for. Give thanks yous for making this ever so piece of cake to follow tutorial for all of us out here.