How to Set Up an Accounting Spreadsheet in Excel
Monitoring your bank account is essential when managing your budget and can also help you spot suspicious or fraudulent activity. Many people use accounting spreadsheets to monitor the transactions in their account, including both withdrawals and deposits. The spreadsheet generally includes the date, reason for the transaction, along with the amount and current balance of the account. You can create your own accounting spreadsheet to help monitor your transactions using Excel, a spreadsheet application which is included with the Microsoft Office Suite and is also available standalone.
Open Excel by double-clicking its icon on your desktop. The program automatically opens a new workbook entitled "Book1." Click on "Save" in the "File" menu to save the workbook to your computer in the desired location. Name the spreadsheet with the current month and year. If you store each month's transactions in a separate spreadsheet, you can use the naming convention yyyy-mm-dd. This will ensure that when you view the spreadsheets in a folder, they can be sorted in date order from most recent to oldest.
Click the first cell in the top-left corner of the spreadsheet, which is known as cell A1. Each spreadsheet is a collection of cells organized in a grid. Each cell is known by its location within the grid, specified as its column and row coordinate. Cell A1 is the cell in Column A, Row 1 -- that is, the first, or top-left, cell in a spreadsheet.
Type "Date" into the first cell. Click the following cell, which is B1, and type "Transaction." Type "Amount" in cell C1 and "Balance" in cell D1 to create the headings for your accounting spreadsheet.
Change the typeface for the headings by highlighting the headings. Click in cell A1 and drag your mouse to the last item in row 1 to highlight the cells. Press the "Ctrl" key and hold it down while pressing the letter "B", to apply bold type to the headings. This will ensure they are visually distinguished from the rest of the spreadsheet.
Enter the amount of your current bank account balance in Row 2 under "Balance", which is cell D2. This is the balance from which you will subtract and add transactions as they appear.
Highlight and format all the cells in the "Amount" column. The simple way to select all the cells in a column is to click the column header (in this case, "C"). Now, right-click the column header and select "Format Cells" from the pop-up menu to open the "Format Cells" window. You can apply the "Currency" style by selecting that option in the left-hand side of the window. By default, Excel formats currency values based on your locality. Optionally, you can select a format for displaying negative values by choosing an option in the list to the right. Alternatively, you can apply the "Accounting" style in the left window. This will automatically align currency symbols and decimal points within the column, and will display negative numbers in parentheses: ($1,234.10). This will clearly distinguish withdrawals from deposits. Click "OK" to finalize the procedure.
Repeat the previous steps on the Balance column, which is column D.
Enter the date of the first transaction into the "Date" column in the third row. The purpose of the deposit or withdrawal should be listed in the "Transaction" column in the third row. The amount of the deposit or withdrawal should be in the "Amount" column in the third row. If you are entering a withdrawal, enter the minus sign (a hyphen) before the amount.
Click the "Balance" cell at the end of the desired row (for the first transaction, this will be cell D3). On your keyboard, type the "=" sign, then click the cell above, D2, use the shift key to enter a "+" sign, then click the cell to the left, C3, which contains the amount of the latest transaction. Then press the "Enter" key. This will enter a formula, "=D2+C3", that will calculate a new balance by adding the latest transaction to the previous balance value. Although you used a plus sign, Excel will correctly calculate the new balance even if the transaction in C3 was a negative value for a withdrawal.
Repeat Steps 8 and 9 to add more transactions. For each new transaction, you can calculate the new balance in column D by copying the cell above. For example, if you are entering a new transaction in Row 4, you will want cell D4 to calculate a new balance based on the previous Balance (cell D3) and the latest Amount (cell C4). Instead of retyping the formula from cell D3, select cell D4, then press the "Ctrl" key and hold it down while typing the letter "D" (for "down"). This will copy the formula from D3 into D4 and automatically adjust it so that it now refers to the latest information in cells D3 and C4.