Editing Excel Spreadsheet by Adding and Deleting Line Accounts in the Regular Accounting Spreadsheets

Okay...let’s do the easy one first

Deleting Line 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 so you will not have to reset the balance column formulas again. If you need to take out one of the income accounts, you will need to reset the balance formulas again. See this page for those required steps), on the Home tab, in the Cells group, click the drop down arrow next to Delete, and then click Delete Sheet Rows.
2. Select cell E80, hit F2, Backspace, stop when you reach the last expense account in the debit column E, hit enter.
3. Repeat for the credit column F.
4. Repeat steps 2 and 3 for the remaining fund accounts.

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 back the borders, 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 income account (you can always delete it or use your back arrow to get rid of it later).
2. Select rows 33-43, on the home tab, in the cells group, click on insert arrow, and go to Insert Sheet Rows.
3. Yikes! What a mess! But don’t worry...we are going to fix it now....
4. Select rows 44-54, click copy, select cell A33, hit enter...better huh!
5. Select cell B33, on the home tab, in the font group, click on fill color arrow (looks like a tipping paint can), and click on desired color.
6. Select cell B43, click on fill color icon.
7. Select cell B44, hit F2, backspace, hit 3, enter.
8. Select last cell in row 34, hit F2,, hit backspace 2 times, type 31, and hit enter.
9. Select last cell in row 45, hit F2, hit backspace 2 times, type 42, and hit enter.
10. Select cell E107 (Subtotal in debit column of Fund Acc. #1), hit F2, hit the plus sign + , select cell E43, and hit enter.
11. Repeat for cell F107.
12. Repeat for remaining fund account subtotals.

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 income account we just added onto 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 12, click “Insert Sheet Row”.
3. Select cell B12, type in Income #2 or actual account name.
4. Select cell D12, hit equal sign = and go to 1st worksheet (GL-Jan), select cell F43, hit minus sign - , select cell E43, then hit enter.
5. Repeat for remaining fund accounts.
6. Select last cell in row 11 (Total), grab fill handle and pull down to row 12.
7. Repeat 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.

Cash Flow Worksheet

If you requested your regular accounting spreadsheet after August 13th 2009 , you do not have a cash flow worksheet in your regular accounting workbook. I took it out because most small churches do not use this sheet.

If you requested your spreadsheets before Aug. 13th , you have the cash flow in your workbook. However, unless you are proficient in Excel...it would be best to just delete this sheet instead of trying to reset all the formulas in this worksheet.

Annual Report Worksheet

1. Go to the annual report
2. Repeat steps 1 through 2 in “Adding Line Accounts to the Monthly Report ”.
3. Select cell D11 (Fund Acc#1), grab fill handle and pull down to cell D12.
4. Repeat for remaining fund accounts and total column

That’s it! You’re finished! Don’t forget to save a copy of all your hard work and keep in a secure location.

Return to Home page from Editing Excel Spreadsheet