How to Use Excel for Accounts

Written by contributing writer
• Share
• Tweet
• Share
• Email

You can use Excel to track information for accounts. If you are running a small business and only need to keep track of simple accounting, then Excel might meet your accounting needs. You can set up a spreadsheet in Excel that holds the information for one account in each row.

Skill level:
Moderate

• Excel

Instructions

1. 1

Identify the data that you want to track for your accounts. Before you can set up a spreadsheet to track accounts, you need to know what information you will be tracking.

2. 2

Use Row 1 as headings for each item that you plan to track for each account. For example, include account name, invoice date, invoice amount and other headings across Row 1.

3. 3

Format Row 1. Change the formatting of Row 1 to separate the headings from the account data. To do this, click the number one next to Row 1, which highlights the entire row. Add your formatting, such as bold, centre and a grey background.

4. 4

Freeze the heading row. Use the freeze pane feature so that your headings will always appear at the top of the account information. In Excel 2007, click on the number two next to Row 2 to highlight the first row under the headings. Click the View tab. Click Freeze Panes and then Freeze Top Row. In Excel 2003, click on the number two next to Row 2 to highlight the first row under the headings. From the menu bar, click Window>Freeze Panes.

5. 5

Type in the data for each account. Use one row for each account, keying in the information to match the heading information.

6. 6

Set up formulas. For example, if you want to track how much money a customer owes you, set up a formula to calculate this amount for you. See the next section for instructions on how to do this.

1. 1

Decide what formulas you want to set up. For example, if you want to keep track of how much money is owed on an account, you can set up a formula for Excel to do the calculating for you.

2. 2

Select a column to track the amount of the invoice sent to a customer. For this example, enter the amount owed in cell C2.

3. 3

Select a column to track the amount received from the customer. For this example, enter the amount received in cell D2.

4. 4

Select a column to display the amount that the customer still owes. For this example, click on cell E2.

5. 5

Type in the formula to calculate the amount due. In this example, type “=C2-D2”; then, press Enter. If you change the variables in cells C2 and D2, Excel will automatically calculate the amount due with the updated information.

6. 6

Copy the formula for all accounts. If you click on cell E2 and drag your mouse down the column, then this formula will be applied in that column to all accounts.

7. 7

Format the cells to display currency. To do this, highlight the cells, right click, select Format Cells, click the Number tab, click Currency and then click OK.

1. 1

Recognise the value of having a global view of the status of your accounts. You can set up Excel to show you how much money is owed to you from all accounts. Excel will do the work for you so you do not have to calculate this information yourself.

2. 2

Click on Sheet2 at the bottom of the spreadsheet. This will activate another sheet in the workbook.

3. 3

Type in the global information you want to track. Use one cell in Column A for each piece of information you would like to track for all accounts.

4. 4

In Column B, set up a formula for the information you are tracking that corresponds with Column A. To use the example above, you can track the amount of all invoices sent to all customers by typing “=SUM(Sheet1!C:C)” and then pressing Enter. The amount still due from all customers in the above example will display by typing “=SUM(Sheet1!E:E)” and then pressing Enter.

5. 5

Format the cells to display currency. To do this, highlight the cells, right click, select Format Cells, click the Number tab, click Currency and then click OK.

Tips and warnings

• If you are not familiar with Excel formulas, consider taking a basic Excel class or purchasing a book about Excel to walk you through the process of defining formulas in Excel.
• While Excel can be useful in tracking a small number of accounts for small businesses, it is no replacement for business software. If an Excel spreadsheet cannot meet your needs for tracking accounts, consider downloading a free version of QuickBooks to track your accounts.

Don't Miss

• All types
• Articles
• Slideshows
• Videos
Sort:
• Most relevant
• Most popular
• Most recent