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

Step 1 – Create a Layout of a Ledger in Excel

In the range of cells B4:B5, B7:B8, and E7:E8, write down the following entities and format the corresponding cells as the input cells of these values.

In the range of cells B11:G19, create a tabular format with the following heading titles.
Format the cells with the All Border option from the Font group located in the Home tab.

Select the cells in the B11:G18 range.
Go to the Insert tab.
Select the Table option from the Tables group.

The Create Table input box will open.
Check the box My table has headers.
Click on the OK button.

We converted the data range into a table.
Move to the Table Design tab.
Select the Table Style Options group.
Uncheck the Filter Button option.

The table will show without the filtering option.

Note: We can do the same work by pressing CTRL + SHIFT + L.

Select the cells in the B11:G11 range.
Move to the Home tab.
Select the Fill Color drop-down on the Font group.
Choose any color (we have chosen Blue, Accent 1, Lighter 80%).

Do the same thing to cells in the B12:G18 range with another color (we’ve chosen Orange, Accent 1, Lighter 80%).

The cells in the B11:G19 range look as in the image below.

Select cells D8, G8, and cells in the range of E12:G19 (hold Ctrl and click or drag through cells to select simultaneously, then release Ctrl).
Press Ctrl + 1.

The Format Cells dialog box will open up.
Go to the Number tab.
Select Accounting from Category.
Write down 0 in the box of Decimal places and choose the dollar sign ($) from the Symbol drop-down list.
Click OK.

Step 2 – Make a Monthly Ledger in Excel

Select cell G3 and insert the following formula.

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)&" "&2022

Formula Breakdown
This formula returns the sheet name in the selected cell.

CELL(“filename”, A1): The CELL function gets the complete name of the worksheet
FIND(“]”, CELL(“filename”, A1)) +1: The FIND function will give you the position of ] and we’ve added 1 because we require the position of the first character in the name of the sheet.
255: Excel’s maximum word count for the sheet name.
MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,255): The MID function uses the text’s position from start to end to extract a specific substring

Press Enter.

We can see the name of our Sheet on this cell with 2022.

Note: While typing this formula, make sure to enter any cell references on this sheet. Otherwise, the formula won’t work properly. For example, here we’ve entered the reference of cell A1.

Change the name of the sheet to Jan. The month’s name is automatically input into cell G3.

Select cell D7 and insert the following formula.

=DATEVALUE("1"&G3)

The DATEVALUE function converts a date in the form of text to a number that represents the date in Microsoft Excel date-time code.

Select cell G7 and paste in the formula below.

=EOMONTH(D7,0)

The EOMONTH function gives the inferred number of months before or after the start_date. It is the sequential number for the closing day of the month.

Step 3 – Provide Some Sample Data as Input in the Ledger

Input the name of the company and address into cells D4 and D5.
Put the Balance at the start date in cell D8.

Fill up the cells in the B12:F18 range with proper data for Date, Bill Ref, Description, Debit, Credit, and Balance.

Select cell G12 and insert the following formula.

=D8-E12+F12

+D8, E12, and F12 represent the Opening Date Balance, Debit, and Credit, respectively.

Select cell G13 and insert the formula below.

=G12-E13+F13

G12, E13, and F13 serve as the corresponding Balance of the previous entries, Debit, and Credit.

Drag down the Fill Handle icon to copy the formula down to cell G18.

The Balance column looks like below.

Select cell E19 and insert the following formula.

=SUM(E12:E18)

It calculates the total Debit in the E12:E18 range.

Select cell F19 and enter the following formula.

=SUM(F12:F18)

It calculates the total Credit in the F12:F18 range.

Select cell G19 and enter the following formula.

=D8-E19+F19

D8, E19, and F19 represent the Opening Balance, Total Debit, and Total Credit, respectively.

Notice that the amount in cell G18 and in cell G19 are the same. So, we can be sure that the calculation is correct.

Select cell G8 and insert the following formula.

=G19

The ledger for the month of January looks like the image below.

Step 4 – Add Other Months

Right-click on the sheet name Jan.
Select Move or Copy from the context menu.

It will open up the Move or Copy dialog box.
Select move to end in the Before sheet box.
Check Create a copy.
Click OK.

We created a new sheet Jan (2).

Edit the sheet name to Feb.
The month, Opening Date, and Closing Date will be changed.

Select cell D8 and use the formula below.

=Jan!G19

The Opening Balance is equal to the Closing Balance for January month.

Clear the previously entered data for the month of January in the B12:F18 range.

Enter the data for the month of February.

We have entries until Row 16. If we want to add other entries below, we can do that because we’ve transformed the data range into a table previously.