How to Create a Checkbook Register in Excel

Updated on October 19, 2016
Checking Account Register Tab
Checking Account Register Tab | Source

Keeping an accurate record of your checking account 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 accountant, a high frequency of manual 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 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. 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 Step Instructions

One of the best things about creating your own spreadsheet templates is that you can customize it how you want. You can add a Dashboard Tab that can be a quick look into what you decide to track. It could be income tax related items, such as property taxes, charitable giving, etc. It could be that you want to track your balance over time and place a line chart for that. Let us begin by opening a new workbook in Excel.

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

Categories Tab

Start 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.

  1. In cell B4, type “Categories” and then format the cell as bold and change the alignment to center.
  2. In cell B5, enter “*** Income ***” as a section heading.
  3. Beginning in Cell B6 and continuing downward, enter any income categories that you want to track. I suggest having at least the following categories: Wages, Gifts Received, Interest Income, Dividend Income, and Miscellaneous Income. Feel free to customize as you see fit. You may want to break wages into more than one category if you have more than one income stream.
  4. Leave a blank after you last income categories and then enter “*** Expenses ***” as a section heading.
  5. Enter all of the expense categories that you want below the expenses heading. These can be as broad or as narrow as you wish. If you choose to enter any other headings, be sure to keep the same format with the three asterisks before and after the title.
  6. 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 around it to make it look nice and neat.
  7. You can still add more categories by inserting rows inside of the shaded area.
  8. 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), type “Categories” into the box. This sets up a name range that we will use on the next “Checking Account Register” Tab.


Checking Account Register Tab

This will be the main tab of the spreadsheet. All of your transactions will be entered on this tab. It will also be useful in reconciling your account balance. Start by renaming one of the blank tabs as “Checking Account Register”.

How do you keep your checkbook register?

See results
Data Validation Input Box
Data Validation Input Box | Source
Expense Category Drop Down Box
Expense Category Drop Down Box | Source
Conditional Formatting Drop-down
Conditional Formatting Drop-down | Source
Conditional Formatting Input Box
Conditional Formatting Input Box | Source
Format Painter
Format Painter | Source
  1. In cell A1, enter a title for your account register. I called mine “Checking Transaction Register.” Change the font size to 28.
  2. 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 nice. While you have this area highlighted, add a bottom border to it.
  3. In cell A4, enter “Date”.
  4. In cell B3, enter “Reference/”. In cell B4, enter “Number”.
  5. In cell C4, enter “Payee”.
  6. In cell D3, enter “Expense”. In cell D4, enter “Category”.
  7. In cell E4, enter “Memo”.
  8. In cell F4, enter “Deposit”.
  9. In cell G4, enter “Payment”.
  10. In cell H4, enter “Clr”.
  11. In cell I3, enter “Account”. In cell I4, enter “Balance”.
  12. In cell J3, enter “Cleared”. In cell J4, enter “Balance”.
  13. Highlight A3:J4 and shade the area whatever color that you like. Also, change the font color as appropriate. Change the alignment for all of these cells to center.
  14. Change the column widths so that the register takes up the whole screen. I would give you my widths, but depending on your settings, they are going to be different.
  15. Highlight cells A5:A4000 and change the format to “Short Date”, which can be done either by clicking the drop down on Excel’s “Home” tab or by right-clicking and selecting “Format Cells.” I chose row 4000 randomly and so you would not have to insert any rows any time soon. I would suggest typing “End” into cells A4000:J4000 as a placeholder for navigating the spreadsheet. I will explain why in more detail later on.
  16. Highlight cells B5:B4000 by clicking B5 and then hold down Shift-End-Down, which will highlight all the way down B4000. This is why I had you copy “End” across row 4000 in the last step. Using End-Down moves the cursor either down to the bottom of a list or to the next cell with something in it. Holding the Shift key down makes Excel highlight that section. Change the alignment of the cell to Left.
  17. Click on cell D5. On Excel’s “Data” menu, click on the “Data Validation” button and select “Date Validation” from the drop down. This will bring up an input box. Look for the “Allow:” drop down box, select “List” from the available choices. Under “Source:” enter “=Categories” into the box. Click “Ok.” Copy cell D5, using Shift-End-Down, paste the formula all the way down to row 4000. This will add a drop down box that has all of the expense categories that you entered on the “Category” tab.
  18. Highlight cells D5:D4000 and click on the “Conditional Formatting” button on Excel’s “Home” menu and select “New Rule. . .” This will bring up an input box. Under “Select Rule Type:”, select “Format only cells that contain.” Under the “Edit the Rule Description:” section, change the drop down that says “Cell Value” to “Specific Text.” In the box on the far right, enter “*” and then click on the “Format” button. Click on the “Font” tab and change the “Font” color to white. Then click on the “Fill” tab and select one of the red colors. Click “Ok” to close the format box and click “Ok” again on the rule-formatting box. This will change the cell color under “Expense Category” (Column D), to become red if you select one of the category headings.
  19. Highlight cells F5:G4000 by clicking in F5 and using Shift-End-Down and while continuing to hold the Shift key, hit the Left Arrow button once, which will highlight column G. Change the cell format to currency.
  20. Repeat the same process in I5:J4000.
  21. Highlight the entire Column H, change the alignment to center, and format it as bold.
  22. In cell I5, enter the following formula: =F5-G5.
  23. In cell I6, enter the following formula: =F6-G6+I5. This will automatically update your account total. Copy this formula down by copying cell I6 and holding down Shift-End-Delete and then paste the formula.
  24. In cell J5, enter the following formula: =F5-G5.
  25. In cell J6, enter the following formula: =if(H6=”C”,F6-G6+J5,J5). The idea of this formula is to help you to reconcile your checking account register versus your bank account balance. It only calculates the rows that have a “C” (Cleared) in column H. Whereas, Column I is your real account balance even though some things have not cleared the bank. Copy this formula down using the same method as above.
  26. Shade A5:H5 a darker color and change the font color to a lighter color.
  27. In cell C5, enter “Beginning Balance”.
  28. In cell F5, enter in your beginning checking account balance.
  29. I choose to format my template by shading every other row light blue and the other row as 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 Column J (Right side).

Using Your Checkbook Register

Your checkbook register is now ready to use. Enter in all of your transactions as they occur. Be sure to enter the date of the transaction, a reference number, expense category, any memo information, and enter the dollar amount in the appropriate column. Balancing the checkbook register can be done by typing a “C” in Column H when something clears the bank. The “Cleared Balance” should tie to your bank account. Balancing your account often is a great way to make 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.

Questions & Answers

    Comments

      0 of 8192 characters used
      Post Comment

      • profile image

        Eric 

        9 months ago

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

      • profile image

        Chandra 

        10 months ago

        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 register book. Needed a more efficient way to balance the cheque book. I could not get the shift-end-down to work. That would be so quick. Not sure what I am doing wrong. Also - do you actually use the quotation marks when you enter "categories" or "deposit" for example? Thank you in advance.

      • profile image

        Beth 

        15 months ago

        Great! Thank you so much :)!

      • profile image

        Robbie 

        18 months ago

        Thank you so much for the tutorial! I have been doing things the old fashioned way and it has become quite time consuming. This will definitely help a lot! I have a question about the "Category" column. When I click on the drop down box next to the cell, it doesn't show my list of categories. The only option it givens on which to click is my formula of "=Categories". I am sure it's something I did or did not do. Where did I go wrong? Thank you for your help.

      • profile image

        Abby 

        23 months ago

        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 above. I tried both formulas in the J column but if a previous entry does not have a "C" entered in the H column, then the H column can't compute a value. I get a #VALUE! in that cell. Any suggestions for this incredibly technology-challenged individual?

      • profile image

        Jonathan 

        2 years ago

        With Excel 2016 you need to remove the last J5 from "=if(H6=”C”,F6-G6+J5,J5)" so it would look like: =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),"",cell)). Worked like a charm! Thanks for the ideas.

      • profile image

        Amanda 

        5 years ago

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

      • profile image

        Amanda 

        5 years ago

        Though, I must add that I agree with John: this tutorial is what I was looking for! I had my register mostly made, but needed a simple way to quickly and easily reconcile my register with my account. I'm very pleased with your reconciliation method! Too bad I couldn't have thought 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 conditional 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 about the formula will also have a simple answer. :)

      • profile image

        Amanda 

        5 years ago

        The formula for the "Cleared Balance" column does not allow a way to keep 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, too, because I used an IF formula (if the date is blank leave the balance blank, otherwise add it up). For the Cleared Balance formula I tried imbedding another IF in the "false" value to keep the cell blank if there was no transaction entered. It didn't work. Any suggestions?

      • ercramer36 profile imageAUTHOR

        Eric Cramer 

        6 years ago from Chicagoland

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

      • profile image

        John 

        6 years ago

        Exactly what I was looking for. Thank you for making this ever so easy to follow tutorial for all of us out here.

      working

      This website uses cookies

      As a user in the EEA, your approval is needed on a few things. To provide a better website experience, turbofuture.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

      For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://turbofuture.com/privacy-policy#gdpr

      Show Details
      Necessary
      HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
      LoginThis is necessary to sign in to the HubPages Service.
      Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
      AkismetThis is used to detect comment spam. (Privacy Policy)
      HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
      HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
      Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
      CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
      Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
      Features
      Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
      Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
      Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
      Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
      Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
      VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
      PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
      Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
      MavenThis supports the Maven widget and search functionality. (Privacy Policy)
      Marketing
      Google AdSenseThis is an ad network. (Privacy Policy)
      Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
      Index ExchangeThis is an ad network. (Privacy Policy)
      SovrnThis is an ad network. (Privacy Policy)
      Facebook AdsThis is an ad network. (Privacy Policy)
      Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
      AppNexusThis is an ad network. (Privacy Policy)
      OpenxThis is an ad network. (Privacy Policy)
      Rubicon ProjectThis is an ad network. (Privacy Policy)
      TripleLiftThis is an ad network. (Privacy Policy)
      Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
      Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
      Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
      Statistics
      Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
      ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
      Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)