PlayInfoBox

Home /Utilities /How to Sum Values Across Multiple Sheets in Excel

How to Sum Values Across Multiple Sheets in Excel

Tap through each step to total values from several sheets with one formula instead of adding them up one by one.

  1. Confirm each sheet uses the same layout

    For sheets like Jan, Feb, and Mar, the cell layout needs to match across all of them for one formula to work on all at once.

  2. Click the cell where the total should appear

    Click a cell on your summary sheet and type "=SUM(" to begin the formula.

  3. Click the first sheet tab, then Shift-click the last one

    Click the first sheet tab you want to include, hold Shift, and click the last sheet tab to select the whole range of sheets.

  4. Click the cell to sum and press Enter

    With that group of sheets selected, click the cell you want summed, then press Enter to complete the formula.

  5. Why it is worth using

    Instead of manually adding totals from several monthly sheets one at a time, a 3-D reference formula sums across all of them in one step.

Still adding up monthly sheets one by one?

When data is split across sheets β€” say, one per month β€” and you need a combined total, using a 3-D reference formula lets you calculate the grand total in a single formula instead of clicking through and adding each sheet manually.

Also worth checking: adding or deleting multiple sheets at once

Alongside summing across sheets, it is worth learning how to add or delete several sheets in one action, which speeds up setting up a monthly or per-category workbook.

Frequently Asked Questions

If I add a new sheet in the middle, does it get included automatically?

Yes β€” as long as the new sheet is inserted between the first and last sheet in the 3-D reference range, it gets included in the total automatically.

Can I use other functions besides SUM this way?

Yes. Any function that works across a range of cells, such as AVERAGE or MAX, can be used the same way across multiple sheets.