How to Create a Financial Ledger on a Spreadsheet

By Elizabeth Mott

Tracking income and expenses digitally saves time and forestalls errors.
i Thinkstock/Comstock/Getty Images

Financial ledgers document the income and outgo that define business profitability or reveal a fiscal shortage. Because spreadsheets enable you to track these kinds of monetary balances using functions and formulas, they spare you the pen-and-calculator math that can result in inaccurate entries and other errors. To set up a Microsoft Excel worksheet that automatically shows you your business finances, use some of the software's basic features to create a useful spreadsheet tool.

Press "Ctrl-N" to create a new, blank Microsoft Excel workbook. Use row 1 to enter headings for your data columns. Click in cell A1 and type the following items without quotation marks, pressing the "Tab" key between entries to move to the next cell: "Date," "Debit $," "Credit $," "From/To," "Description" and "Balance." The "From/To" column holds the client name or supplier name information, and the "Description" column gives details about each entry.

Click on the heading for column B to select the entire column. "Shift-click" on the heading for column C and "Ctrl-click" on the heading for column F to add them to your selection without also selecting columns D and E. Switch to the Home tab of the Excel ribbon and locate the Number group. Click on the arrow to the right of Number-group identifier -- its dialog box launcher -- to open the Format Cells dialog box to the Numbers tab.

Click on the "Currency" item in the Category list at the left side of the dialog box. Use the default of two decimal places unless your business deals in transactions that result in fractional cents. Add a currency symbol, unless seeing an entire column of numbers with dollar signs makes the information harder for you to read, and select a negative-number format. You can choose to display negative numbers preceded with a minus sign, in red, in parentheses, or both in red and in parentheses. Click on the "OK" button to apply your formatting.

Click on the heading for column A to select it. Click on the number-format dialog box launcher again and choose "Date" from the category list. Select the date style you prefer and click on the "OK" button to apply it to the Date column.

Switch to the View tab of the Excel ribbon and locate the Window group. Click on the "Freeze Panes" arrow and select "Freeze Top Row" from the menu that opens. With the top row of your spreadsheet frozen, you can scroll vertically as far as you wish and keep your header row visible.

Click in cell F2 and type "=C2-B2" in the cell. Press "Enter" to confirm your formula. Disregard any error messages or text that appear on cell F2, representing the fact that the calculation references empty cells until you input data.

Click in cell F3 and type "=(C2-B2)+F2." Place your cursor at the bottom right corner of cell F3. When you see the auto-fill indicator – a solid-black plus sign – drag it down through column F to populate several pages of cells with the formula in the F3 cell. Auto-fill automatically updates the cell references in your original formula to apply correctly in each successive row.

Enter an opening balance on line 2, with the current date and a description of "Opening Balance." Continue entering debits and credits in successive rows, using positive values for each entry and allowing Excel to calculate the balance.


To display values of zero as dashes and align currency symbols at the left edge of number-format cells, choose the Accounting number format instead of the Currency format. Accounting format only allows negative numbers displayed in black in parentheses unless you create a custom numeric format for negative values. Select the Number format to control whether values greater than 999.99 use a comma – the thousands separator – between their first two digits.

As you use this file, you'll need to populate additional cells in column F with the formula that calculates your running balance as you enter more data. Avoid selecting the entire column and prefilling it, however, because that action automatically adds the maximum number of pages that a single worksheet can contain, making your file tens of thousands of pages long and correspondingly slow to work within.

If you see a sequence of pound signs – "#####" – in a cell, the width of the value exceeds your column width. Place your cursor at the right edge of the column header and click to drag it to the right to widen the column when the sizing arrow appears


Entering a negative number in the "Debit" column will force your Balance formula to treat it as a credit, because subtracting the negative number creates a double negative, which becomes a positive. Debits represent expenses and credits represent income.