Editing Excel Spreadsheet

Adding and Deleting Revenue and Expense Accounts

Make your accounting spreadsheet fit your small business by editing excel spreadsheet: Automatic Accounting.

(Don't have time to modify it yourself? I'll do it for you. See this page for details.)

Okay...let’s do the easy revenue and expense account modification first

Deleting Revenue and Expense Accounts in the First Worksheet (Gl-Jan)

1. In the first worksheet(GL-Jan), select the rows to need to delete with the desired amount of line accounts. (Hint: Take the bottom line accounts if possible so you will not have to reset the balance column formulas again.
2. For an example, we are going to take out the bottom 2 expense accounts.
3. Highlight rows from bottom of the Expense #8 account to the bottom of Expense #10.
4. On the Home tab, in the Cells group, click the drop down arrow next to Delete, and then click Delete Sheet Rows.
5. Select the cell named Total Debits (at the bottom of worksheet), hit F2, delete all of the +#REF!, hit enter.
6. Repeat for the cell named Total Credits (at the bottom of worksheet),
7. Repeat steps for the rest of the business account columns (if you have any more.

• Select the top gray cell in the first expense account in the balance column.
• Hit F2, hit backspace till you come to the plus sign..then click on last cell in the account above it. Hit Enter.

Deleting Line Accounts in the Monthly and Annual Report (Jan)

1. In the Jan monthly report, select all the rows with the #REF!s, delete.
2. If it took out the last line under expense, you will need to put the border back in, go to the home tab if not already there, in the font group, click on the borders arrow and go down to double border...or whatever border you want), click where you want them.
3. Repeat steps for the annual report.

Not bad...huh?

Adding more line accounts isn’t too difficult either...just takes some time.

Adding Line Accounts in the First Worksheet (GL-Jan)

1. Let’s practice by adding another expense account (you can always delete it or use your back arrow to get rid of it later).
2. Important: Make sure you are in the GL-Jan worksheet!
3. Highlight and select the row above expense #10 and all of expense 10.
4. On the home tab, in the cells group, click on insert arrow, and go to Insert Sheet Rows.
5. Highlight and select all of expense 10, click copy, select third “A” cell beneath expense 9, hit enter.
6. Rename and re-color new expense account.
7. Now to fix the balance column. Select the top gray cell in the new expense account in the balance column.
8. Hit F2, hit backspace till you come to the plus sign..then click on last cell in the account above it. Hit Enter.
9. Do the same thing for the next expense account.
10. When you are finished adding accounts, you will need to add them to the Total debits (expenses) cell at the bottom. Hit F2, then the plus sign and then click on the red debit total...if you just added 1 account hit enter...if you added more...hit the plus sign and pick them all up before hitting Enter. Do the same for the Total credit (income) cell.
11. Repeat for remaining businesses (if you have any)

Okay...now you know how to add more line accounts, and you are thinking...that wasn’t too bad...hold that thought while we go to the monthly report and add that expense account we just added into the general ledger.

Adding Line Accounts to the Monthly Report

1. Look at the bottom tabs...click on the tab named “Jan”.
2. Select row with expense #10, click “Insert Sheet Row”.
3. Select E cell in the new row, hit equal sign (=), go to 1st worksheet (GL-Jan) and then click on the red debit total of the new expense account you just created, hit enter.
4. Repeat for remaining biz accounts (if any).
5. Repeat all steps for remaining 11 months.

That’s it for the monthly report! You will need to repeat the steps listed above for each line account you add.

Annual Report Worksheet

1. Go to the annual report
2. Select row with expense #10, click “Insert Sheet Row”.
3. Select Expense #9 cell in the E column, grab fill handle and pull down to last expense account.
4. Repeat for remaining business accounts (if any) and total column

That’s it! You’re finished editing the Excel spreadsheet: Automatic Accounting! Don’t forget to save a copy of all your hard work and keep in a secure location.

Other pages that may interest you:

• Number One Accounting Rule - Accounting 101
• Is Double Entry Bookkeeping Best?
• How to post journal entries and know when to debit and when to credit.
• Accounting Basic Terms

Go to Basic Accounting Concepts from Editing Excel Spreadsheet: