Working with several sheets

1 / 6

Each month has its own sheet, with the regions in the same order. We want the quarter total for each region in B2:B5.

keyboard Typing formulas is easiest on a computer with a keyboard.

A1
ABC
1RegionQ1 total
2West
3South
4North
5East
6
7
8
9
10
11
12
regions
totals here
Type to enter a value or a formula starting with =. F2 edits, F4 adds $ to a reference.
Study notes

How SUM works

  • How do I add up the same cell on several sheets?

    Add them one by one, with the sheet name in front of each cell: =Jan!B2+Feb!B2+Mar!B2. While typing, click each sheet's tab and then the cell, and Excel writes the sheet names for you.

  • What does Jan:Mar!B2 mean?

    Cell B2 on every sheet from Jan to Mar, including any sheets between them. =SUM(Jan:Mar!B2) adds them all up. This is called a 3D reference.

  • Can I click instead of typing it?

    In Excel, type =SUM(, click the Jan tab, hold Shift and click the Mar tab, then click B2 and press Enter.

  • Which functions work across sheets?

    SUM, AVERAGE, COUNT, COUNTA, MAX and MIN. Most others, such as VLOOKUP, give #VALUE! with a 3D reference.

  • What if I add another month?

    The span takes every sheet between the first and last name, in tab order. Put a new month's sheet between Jan and Mar and it's included. Keep the Summary sheet outside, or it would count itself.

Know someone fighting with a spreadsheet? Send them this page.