How to Keep Track of Credit and Debit Using Excel

By Shawn McClain

For a business, keeping track of money coming in and going out is essential. Microsoft's Excel spreadsheet program can easily help you record these debits and credits, but a long list of numbers can become difficult to sort through and understand. By including a formula for calculating a running total and taking advantage of Excel's built-in filter, you can turn a simple sheet of credits and debits into a powerful tool for managing your business.

Click on cell "A1" in a new Excel 2010 spreadsheet. Enter "Name" into this cell, as this column will hold the name of the person or business associated with the credit or debit. Then click on cell "B1" and enter "Info," so you'll have a column to enter any necessary information or notes about the credit or debit.

Select cell "C1" and type in "Debit," then select cell "D1" and type in "Credit." Finally, select cell "E1" and type in "Total."

Click on cell "E2." Type "=D2-C2" into the cell and press "Enter." Then click on cell "E3." Type "E2+(D3-C3)" and press "Enter." These formulas create a running total that will track the current monetary balance after all the credits and debits are entered.

Click on cell "E3," then move your mouse over the small box in the lower-right corner of the cell. Click and hold the mouse button, then drag the mouse down the column until you reach the last cell where you want to track a running total. If needed, you can always repeat this process and extend the running total down the column even further.

Enter your data into the "Name," "Info," "Debit" and "Credit" fields. Be sure to enter positive values in the "Debit" field, as the formula you built will automatically turn these into negative numbers in your running total.

Click "Home," then click the "Sort and Filter" button. Choose "Filter" from the menu that appears. A set of drop-down arrows will appear at the top of each column.

Click the drop-down arrow over the "Name" field and remove check marks from any fields that you want to filter out. Click "OK" and the viewable data will shift to only show the entries that match the names you left in. The running total will automatically update to exclude any entries that you filtered out. You can change your filter at any time to adjust the spreadsheet to your liking.


Information in this article applies to Microsoft Excel 2010. It may vary slightly or significantly with other versions or products.