How to Create a Basic Excel Ledger in 10 Minutes or Less

Whether you are trying to keep your checkbook balanced, keep an eye on cash flow, or simply trying to organize your finances, Excel is an invaluable tool. Below, I utilize some of Excel’s basic formulas and functionality to create a ledger that could be applied to a variety of purposes.

1.       First, create your ‘Title’ row. This will list all the information you will be including. I have included Date, Description, Type, Debit, Credit, and Total in this example.

·         Date will store the date of the transaction. For check balancing, the date would be the date on your receipt, not the date you input the information.

·         Description will tell the where/why etc. of the purchase/debit.

·         Type will allow you to keep track of debit purchases vs. checks vs. direct deposits, etc.

·         Debits are transactions that take money OUT of your account. Purchases, etc.

·         Credits are transactions that you credit TO your account. Deposits, etc.

·         Total: will contain a running total.

           

      Secondly, format your tabs. There are many options for how you set up your tabs. In this example, I put each month in a separate tab. However, if you have relatively few transactions each month, you could separate your tabs by year. Also, if you have multiple accounts you are keeping track of, you could separate them using the tabs.

3.       Thirdly, insert your formulas for credit/debit and total. These will be entered in the second row, below your titles. 

      Use the SUM() formulas for the credit/debit rows: 

      For the Total row, you will use ‘=credit cell - debit cell’:

4.      Fourthly, format and freeze. You will be entering data below your title row(s), therefore it’s important that they stand out! In this example,

·         I made all the titles bold (Ctrl+B), with a text size of 12.

·         I then made my credit/debit and total text red, with a text size of 12.

·         I then froze the top two rows so that now matter how long my data gets, I can always see the titles and totals!

     

      To freeze these rows, first click on ‘View’ on the ribbon. Then click on cell A3. While freezing panes manually, Excel always freezes the rows/columns directly above and/or to the left of the cell you have selected. By selecting cell A3, you will freeze the top to rows. Now, try scrolling. Your title/total rows stay in sight!

You are now ready to begin adding data to your ledger!

Example of Simple Ledger: