How To Design A Formulas For A Bank Register In Excel

How to Create a Checkbook Register in Excel

I am an accountant by trade, but I beloved to spend my free time in my workshop building different things.

Checking Account Register Tab

Eric Cramer

Keeping an authentic record of your checking business relationship is vital. Using the check register that comes with your checkbook requires a manual calculation every time you make an entry. In my experience as an auditor, a high frequency of transmission calculations results in a higher rate of errors. It is so easy to either add something wrong or even write it down wrong. I used to utilise Quicken because it was fully automatic and had a lot of great tools built in, but it became expensive as they would require y'all to upgrade every few years to go along information technology fully functional. After that, I did a search for checkbook register templates and did not find anything that I liked. Most were very generic and were too basic for what I wanted. Using Microsoft Excel, I created my own checkbook register template.

Easy-to-Follow Step-by-Stride Instructions

One of the best things near creating your own spreadsheet templates is that y'all tin customize information technology how you want. You can add a Dashboard Tab that can be a quick expect into what you decide to track. It could be income tax related items, such every bit property taxes, charitable giving, etc. Information technology could be that you desire to track your balance over time and place a line chart for that. Allow u.s.a. begin past opening a new workbook in Excel.

Category Tab - My list here is only a sample list of categories.

Eric Cramer

Categories Tab

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

In cell B4, blazon "Categories" and then format the cell as bold and change the alignment to center.
In cell B5, enter "*** Income ***" as a section heading.
Beginning in Prison cell B6 and continuing downward, enter whatsoever income categories that you lot want to track. I suggest having at least the following categories: Wages, Gifts Received, Interest Income, Dividend Income, and Miscellaneous Income. Feel costless to customize as you see fit. You lot may want to pause wages into more than one category if yous have more than than one income stream.
Leave a bare after you last income categories and and then enter "*** Expenses ***" as a section heading.
Enter all of the expense categories that you want below the expenses heading. These can be equally broad or as narrow as you wish. If you choose to enter whatsoever other headings, exist sure to keep the aforementioned format with the iii asterisks earlier and afterwards the title.
As a preference, I like to shade cell B4 as a darker color and change the font color to white. Also, highlight from B5 to a few cells past your last expense entry, shade this area a lighter color, and put a border effectually information technology to get in await nice and peachy.
You can still add more than categories by inserting rows within of the shaded area.
Finally, highlight from cell B5 to the end of the shaded section. In the Name Box, which is to the left of the formula bar (it should read B5), blazon "Categories" into the box. This sets up a name range that we will use on the side by side "Checking Account Register" Tab.

Checking Business relationship Register Tab

This volition exist the chief tab of the spreadsheet. All of your transactions will exist entered on this tab. Information technology will also be useful in reconciling your account rest. Start by renaming one of the blank tabs every bit "Checking Business relationship Register".

Information Validation Input Box

In cell A1, enter a title for your account register. I called mine "Checking Transaction Register." Change the font size to 28.
Highlight cells A1:J1 and shade the area whatever color that you like. If it makes sense, change the font color so that it is easily readable and looks squeamish. While you have this expanse highlighted, add together a bottom border to it.
In prison cell A4, enter "Appointment".
In cell B3, enter "Reference/". In cell B4, enter "Number".
In prison cell C4, enter "Payee".
In cell D3, enter "Expense". In cell D4, enter "Category".
In cell E4, enter "Memo".
In jail cell F4, enter "Deposit".
In cell G4, enter "Payment".
In cell H4, enter "Clr".
In prison cell I3, enter "Account". In prison cell I4, enter "Balance".
In cell J3, enter "Cleared". In cell J4, enter "Balance".
Highlight A3:J4 and shade the expanse whatever color that y'all similar. Too, change the font color as appropriate. Change the alignment for all of these cells to heart.
Alter 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 dissimilar.
Highlight cells A5:A4000 and alter the format to "Brusk Date", which can be done either by clicking the drop downwards on Excel'southward "Habitation" tab or past right-clicking and selecting "Format Cells." I chose row 4000 randomly and and then you would not take to insert any rows whatsoever fourth dimension soon. I would propose typing "End" into cells A4000:J4000 as a placeholder for navigating the spreadsheet. I will explain why in more item later on.
Highlight cells B5:B4000 past clicking B5 and and then agree downward Shift-Cease-Down, which volition highlight all the fashion down B4000. This is why I had you lot re-create "Cease" across row 4000 in the last step. Using End-Downwardly moves the cursor either downward to the bottom of a list or to the adjacent jail cell with something in it. Holding the Shift key down makes Excel highlight that section. Alter the alignment of the cell to Left.
Click on cell D5. On Excel's "Data" menu, click on the "Information Validation" button and select "Date Validation" from the drop downward. This volition bring upwards an input box. Look for the "Allow:" drop downward box, select "List" from the available choices. Under "Source:" enter "=Categories" into the box. Click "Ok." Copy cell D5, using Shift-End-Downwardly, paste the formula all the manner down to row 4000. This will add together 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's "Home" menu and select "New Rule. . ." This will bring upwardly an input box. Under "Select Rule Blazon:", select "Format only cells that contain." Under the "Edit the Dominion Description:" section, modify the driblet down that says "Jail cell Value" to "Specific Text." In the box on the far right, enter "*" and and so click on the "Format" button. Click on the "Font" tab and change the "Font" color to white. Then click on the "Fill up" tab and select ane of the ruddy colors. Click "Ok" to close the format box and click "Ok" once again on the rule-formatting box. This volition alter the cell color under "Expense Category" (Cavalcade D), to go ruddy if you select ane of the category headings.
Highlight cells F5:G4000 by clicking in F5 and using Shift-Cease-Down and while standing to hold the Shift key, hit the Left Arrow push once, which volition highlight column Yard. Change the prison cell format to currency.
Repeat the same process in I5:J4000.
Highlight the entire Column H, change the alignment to center, and format it equally bold.
In cell I5, enter the following formula: =F5-G5.
In jail cell I6, enter the following formula: =F6-G6+I5. This will automatically update your business relationship total. Re-create this formula downwards by copying cell I6 and holding downwardly Shift-End-Delete and so paste the formula.
In cell J5, enter the following formula: =F5-G5.
In jail cell J6, enter the post-obit 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 banking concern account rest. It only calculates the rows that accept a "C" (Cleared) in cavalcade H. Whereas, Column I is your real account balance even though some things accept not cleared the banking concern. Re-create this formula down using the same method as in a higher place.
Shade A5:H5 a darker color and change the font color to a lighter color.
In prison cell C5, enter "Beginning Residual".
In cell F5, enter in your outset checking account residue.
I choose to format my template past shading every other row light blue and the other row equally 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 "Home" tab. Highlight the next 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 column F (left side), Column H (both sides), and Cavalcade J (Right side).

Using Your Checkbook Annals

Your checkbook register is at present prepare to use. Enter in all of your transactions as they occur. Be sure to enter the engagement of the transaction, a reference number, expense category, any memo information, and enter the dollar corporeality in the appropriate cavalcade. Balancing the checkbook register can be done past typing a "C" in Column H when something clears the bank. The "Cleared Remainder" should tie to your bank business relationship. Balancing your business relationship frequently is a great way 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 style to also baby-sit against fraud because you will know if anything suspicious hits your account.

This article is accurate and truthful to the best of the author's knowledge. Content is for informational or amusement purposes only and does not substitute for personal counsel or professional advice in business, financial, legal, or technical matters.

Jessica on March 05, 2020:

Is at that place an culling fashion to the categories? I currently simply have the spider web version and information technology volition non let me rename the listing Categories... any assist with another way this can exist done?

Eric on January 22, 2018:

Nifty setup instructions. They are very helpful. Whatever way to then create a list that shows the amounts for each category? For instance, the total for "Wages" at a glance. ideally it would be great to do that for a selected date range...

Ringlet to Go on

Chandra on January 05, 2018:

This was a wonderful detect. Exactly what I needed. I have spent the terminal year trying to teach myself how to use excel. I am still using the paper cheque register book. Needed a more efficient way to balance the cheque book. I could not get the shift-stop-downwardly to piece of work. That would be so quick. Not sure what I am doing wrong. Also - exercise you lot actually use the quotation marks when yous enter "categories" or "deposit" for example? Give thanks you in advance.

Beth on August 06, 2017:

Not bad! Thank you and so much :)!

Robbie on May 11, 2017:

Cheers so much for the tutorial! I have been doing things the onetime fashioned way and it has go quite time consuming. This volition definitely help a lot! I have a question nearly the "Category" column. When I click on the drop downwardly box next to the cell, information technology doesn't prove my list of categories. The only option it givens on which to click is my formula of "=Categories". I am sure information technology's something I did or did not practise. Where did I go wrong? Thanks for your help.

Abby on December 07, 2016:

This was so elementary to follow that even *I* was able to create it. THANK You lot. My only problem is what Jonathan and Amanda referenced above. I tried both formulas in the J cavalcade but if a previous entry does non have a "C" entered in the H cavalcade, then the H column can't compute a value. I go a #VALUE! in that cell. Any 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)" so it would await 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(jail cell),"",cell)). Worked similar a amuse! Cheers for the ideas.

Amanda on October 20, 2013:

AH! I figured information technology out. I embedded an IF(ISBLANK(jail cell),"",prison cell) within the 'fake value' of the formula you suggested. SWEET! cheers 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 register generally made, but needed a unproblematic way to quickly and easily reconcile my register with my account. I'thousand very pleased with your reconciliation method! Too bad I couldn't have idea of it on my own, that would have saved me a lot of googling time. SO thank you for that idea! And thank you for reminding me of provisional formatting; I had wanted to colour code some of my categories merely was stumped. Since that was a 'duh' moment, I'm hoping my prior question nigh the formula will also accept a unproblematic answer. :)

Amanda on October twenty, 2013:

The formula for the "Cleared Balance" cavalcade does non permit a way to keep the cell bare if at that place is no transaction data entered at all. I guess neither does the balance column. I hadn't noticed the Residual cavalcade having that problem, likewise, because I used an IF formula (if the engagement is blank leave the remainder bare, otherwise add it up). For the Cleared Balance formula I tried imbedding another IF in the "fake" value to keep the cell blank if in that location was no transaction entered. Information technology didn't work. Any suggestions?

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

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

John on July 25, 2012:

Exactly what I was looking for. Thank yous for making this ever and then easy to follow tutorial for all of united states of america out here.