How to Create a Checkbook Ledger in Excel (2 Useful Examples)

Looking for ways to keep track of your transactions in a checkbook ledger? Then you’ve come to the right place. In this article, we’ll show you 2 handy examples of how to create a checkbook ledger in Excel.


Download Practice Workbook

You can download the practice workbook from the link below.


What Is a Checkbook Ledger?

In short, a checkbook ledger stores the information of every transaction like purchases, payments, bills, deposits, etc. for a given period. Generally speaking, it is a common practice in Accounting to record transactions in a ledger.


2 Examples to Create a Checkbook Ledger in Excel 

Now, in our first example, we’ll construct a simple checkbook ledger while in the second example we’ll build a more dynamic ledger using Excel’s Table feature. Therefore, without further delay, let’s see the examples one by one.
Considering the dataset shown in the B4:D13 cells. Here, the dataset shows the transaction Date, Number, and Description. Then, the last two columns show the types of transaction Debit, and Credit respectively.

Dataset 1

Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience.


Example-1: Create a Simple Checkbook Ledger in Excel

Microsoft Excel makes it very easy to make a simple checkbook ledger. So, let’s just follow these steps.

Steps:

  • Firstly, our Starting Balance for July was $500 and we want a running total to appear in the Balance column. Hence, we insert the expression below in the G6 cell.

=IF(AND(ISBLANK(E6),ISBLANK(F6)),"",G5-E6+F6)

Here, the E6 and the F6 cells refer to the Debit and Credit columns respectively while the G6 cell indicates the Balance of $500.

Formula Breakdown:

  • ISBLANK(E6),ISBLANK(F6) → the ISBLANK function checks whether a reference is to an empty cell, and returns TRUE or FALSE. Here, E6 is the value argument that refers to the Debit value for Salary. Next, the F6 is the value argument that represents the Credit value for Salary. Now, the ISBLANK function checks whether the Debit and Credit cells are blank. It returns TRUE if blank and FALSE if not blank.
    • Output → TRUE, FALSE
  • AND(ISBLANK(E6),ISBLANK(F6)) → becomes
    • AND(TRUE,FALSE) → the AND function checks whether all the arguments are TRUE, and returns TRUE if all the arguments are TRUE. Here, TRUE is the logical1 argument and FALSE is the logical2 argument since one argument is FALSE so the AND function returns the output FALSE.
    • Output → FALSE
  • =IF(AND(ISBLANK(E6),ISBLANK(F6)),””,G5-E6+F6) → becomes
    • =IF(FALSE,””,G5-E6+F6) →  the IF function checks whether a condition is met and returns one value if TRUE and another value if FALSE. Here, FALSE is the logical_test argument because of which the IF function returns the value of G5-E6+F6 which is the value_if_false argument. Otherwise, it would return “” (BLANK) which is the value_if_true argument.
    • 500 – 0 + 2000 → 2500

Using IF AND ISBLANK Functions

How to Create a Checkbook Ledger in Excel

Lastly, the results should look like the image given below.

How to Create a Checkbook Ledger in Excel

Read More: How to Make General Ledger in Excel (With Easy Steps)


Example-2: Using Excel Table to Create a Checkbook Ledger 

Our second example takes the simple checkbook ledger a step further. Simply put, we’ll utilize Excel Table so that the Balance column is updated automatically whenever a new entry is added. It’s simple and easy, just follow along.

Steps:

  • To start, insert the following expression in the G6 cell below.

=IF(AND(ISBLANK(E6),ISBLANK(F6)),"",G5-E6+F6)

In this formula, the E6 and the F6 cells refer to the Debit and Credit columns respectively while the G6 cell indicates the Balance of $500.

How to Create a Checkbook Ledger in Excel Using Table

  • Then, copy the formula into the cells below and the results should look like the picture shown below.

How to Create a Checkbook Ledger in Excel Using Table

  • Secondly, move to the Insert tab >> click the Table option.

Using Table

  • Now, Excel automatically selects the B4:G14 range of cells for the Table. Also, make sure to check My table has headers option.

How to Create a Checkbook Ledger in Excel Using Table

Next, the Table should look like the screenshot below.

How to Create a Checkbook Ledger in Excel Using Table

  • Thirdly, navigate to the G13 cell and press the TAB key on your keyboard.

How to Create a Checkbook Ledger in Excel Using Table

  • The Table expands automatically to the next row. Finally, you can add a new entry to your checkbook ledger and the Balance is also updated.

How to Create a Checkbook Ledger in Excel Using Table

Read More: How to Make a Ledger in Excel (with Easy Steps)


Practice Section

We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.

Practice Section


Conclusion

I hope this article helps you understand how to create a checkbook ledger in Excel. If you have any queries, please leave a comment below. Also, if you want to read more articles like this, you can visit our website ExcelDemy.


Related Articles

Eshrak Kader

Eshrak Kader

Hello! Welcome to my Profile. I completed my BSc. at Bangladesh University of Engineering & Technology from the Department of Naval Architecture & Marine Engineering. Currently, I am conducting research & posting articles related to Microsoft Excel. I am passionate about research & development and finding innovative solutions to problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo