A summary overview of multiple sheets: expenses, income and balance

Many people keep track of their expenses and income on separate sheets. That's fine, as it keeps each list clear. But then you want to see in one place how much came in, how much went out and what you have left. You do this with a third sheet that pulls in the totals from the other sheets. If you change an amount later, the overview is updated straight away.

As an example, we'll use the annual figures of Bloemisterij Het Zonnetje, a WebOké customer, with three sheets: Expenses, Income and Overview.

Step 1: the Expenses sheet

1. Create a new spreadsheet and name the first sheet Expenses: right click the tab at the bottom and choose Rename (see Managing sheets).
2. Type Item in A2 and Amount in B2. Below that, in A3 to B11, enter your expenses: rent, energy, purchasing, insurance, your WebOké Nextcloud and so on.
3. Type Total in A12 and the formula =SUM(B3:B11) in B12.

Tip: leave a few blank rows above the total row. If you add an item later in one of these blank rows, SUM automatically includes it. If you insert a whole row within the range, the range grows automatically too.

Step 2: the Income sheet

Click the plus sign at the bottom for a new sheet and name it Income. Set it up exactly like Expenses: items in A3 to A11, amounts in column B and =SUM(B3:B11) again in B12. Using the same layout makes the overview easier later on: the total is in B12 on both sheets.

Step 3: the Overview sheet

1. Create a third sheet and name it Overview.
2. Type Income in A3, Expenses in A4 and Balance in A5.
3. Type the formula =Income!B12 in B3. This means: the value from cell B12 on the Income sheet. The exclamation mark separates the sheet name from the cell.
4. Type =Expenses!B12 in B4.
5. Type =B3-B4 in B5: income minus expenses.

You don't need to type the reference yourself. Type an equals sign in B3, click the Income sheet at the bottom, click cell B12 and press Enter. Euro-Office then adds the reference to the formula for you and jumps back to the overview.

Names with a space

If a sheet is named Expenses 2027, with a space, put the name in single quotation marks: ='Expenses 2027'!B12. Without a space, you can also use quotation marks (='Expenses'!B12); Euro-Office will remove them automatically. If you click the cell instead of typing, Euro-Office adds the quotation marks correctly for you.

Expanding: by month or by category

• A percentage: type =B4/B3 in C4 and give the cell the Percentage format. This shows what portion of your income goes towards expenses.
• By category: on the Expenses sheet, add a category in column C (for example Housing, Purchasing, Car). On the overview, =SUMIF(Expenses!C3:C11,"Purchasing",Expenses!B3:B11) adds up only the purchasing costs.
• The same cell across many sheets: if you have twelve sheets, January to December, each with a total in B12, then =SUM(January:December!B12) adds them all up at once. The sheets do need to be next to each other, in that order.

Pitfalls

• #REF! on the overview: you've deleted a sheet or removed a row that the formula pointed to. Set the reference again.
• Renamed a sheet? No problem: Euro-Office automatically updates the formulas on the overview.
• Total shifted? If you insert a row above the total row, the total moves to B13 and the reference on the overview shifts along neatly. If you type the reference again afterwards, check carefully where the total is now.
• Amount not included? If an amount is left aligned in the cell, it's text. See Formatting numbers, dates and currency.

Tip: if several people work in this file, protect the formulas on the overview so no one overwrites them by accident. See Protecting formulas in a shared spreadsheet.