How to create an accounting spreadsheet

Updated April 17, 2017

No matter your income, monitoring your bank account is a wise thing to do. It can prevent you from overdrawing your account, as well as alert you when any suspicious activity occurs. You can monitor your account simply by making an accounting spreadsheet. It will tell you the dates and amounts of transactions in your account.

Open a spreadsheet program on your computer. Click "File" on the main menu and open a new file. A spreadsheet will appear that contains vertical columns and horizontal rows of rectangular boxes called cells. You will input information to these cells to make your spreadsheet.

Click inside of the first cell on the spreadsheet, in the top left corner. Type "Date". Continue across the first row typing "Amount," "Transaction" and "Balance," each in a separate cell. These will be the headings for the data on your spreadsheet.

Highlight the headings. Click the "Bold" and "Underline" buttons on the program toolbar. This styling will make the headings stand out from other data. Click on the first cell in the second row, then click the "Bold" and "Underline" buttons again to deselect them and revert to normal font.

Click "File" on the main menu and select "Save". Name the spreadsheet after the current month and year, then save it as an .xls file (for example, "august2010-accountingspreadsheet.xls"). Save the file again every time you make a change to it.

Type your current bank account balance in the second row under the "Balance" heading. Place a hyphen under the other headings in the row to show that there is no data. This is the starting balance from which you will add and subtract transactions.

Type the date when you receive money into your account in the third row, under the "Date" heading. Type the amount of the deposit under "Amount" and type the depositor's company name under "Transaction" (for example, the name of the company you work for). Use a calculator to add this amount to the balance you typed in the second row of the spreadsheet and type the sum--which is the new balance in your account--under "Balance".

Repeat Step 6 when someone withdraws money from your bank account, but type the amount as a negative number (for example, "-$37.02"). Use a calculator to subtract the amount from the previous balance and type the new balance in the appropriate cell.

Repeat Steps 1 through 7 each month to keep track of your bank account throughout the year. The information may prove useful for tax, identity theft or household management purposes, because it requires you to retrace your steps financially to give a constant account for where your money goes.


If you are new to spreadsheets, using the addition and subtraction icons within the program can be overwhelming. Use a separate calculator until you become proficient with the program.

Things You'll Need

  • Spreadsheet program, such as Microsoft Excel, Apple Numbers or OpenOffice
Cite this Article A tool to create a citation to reference this article Cite this Article

About the Author