Excel Sum Same Cell Across Tabs: The 3-D Formula Guide

If the sheets you want to total are next to one another in the workbook, use a 3-D SUM formula. With monthly sheets named Jan through Mar and the values in cell B2 on each sheet, enter =SUM(Jan:Mar!B2) on a separate summary sheet; if B2 contains 120, 85, and 95, the result is 300.

How the 3-D SUM formula works

A normal sheet reference identifies one worksheet, as in a formula that points to a single tab. A 3-D reference identifies the same cell or range across a span of worksheets. In the example, Jan and Mar are the first and last sheets in the span, and Excel includes B2 on every worksheet between them in the tab order.

The colon between the sheet names marks the start and end of that span. The exclamation mark separates the sheet reference from the cell address. SUM adds the referenced values. You can place the formula on a summary tab, such as Summary, and use a matching cell there to display the total. Keep that summary sheet outside the referenced span so the formula does not include itself and create a circular reference.

A 3-D reference is not a list of particular sheet names. It is a range of sheets in workbook order. That distinction is useful when a workbook has one worksheet per month, department, location, or reporting period, provided the sheets you intend to include form one continuous block.

Set up the tabs and enter the formula

Before entering a formula, check that the worksheets have the same layout: the value you want to add should be in the same cell on each sheet. For example, if monthly revenue is in B2 on every month tab, the reference should point to B2. Then put the formula in the corresponding total cell on a summary sheet.

Arrange the worksheets to be included next to one another, in the order you want them totaled.
Click the summary cell where the result should appear.
Type the SUM formula using the first and last worksheet names, followed by the cell address.
Press Enter, then compare the result with a few source values to confirm the selected sheet span is right.

With sheets named Jan, Feb, and Mar in that tab order and the target values in B2, enter =SUM(Jan:Mar!B2). If the three cells contain 120, 85, and 95, Excel returns 300. The formula does not require the sheets to have identical names or content; it requires the cell address to be correct and the intended sheets to fall between the endpoints.

Worksheet names containing spaces or certain special characters need single quotation marks around the sheet-range reference. With sheets named January Sales through March Sales and the values in B2, enter =SUM('January Sales:March Sales'!B2). The quotes belong around the sheet names and colon together. If you rename a worksheet through Excel, Excel generally updates references that point to it.

Choosing the right start and end sheets

The first and last sheet names in the reference define the boundary, not the calendar dates in their names. If the tab order is Jan, Feb, Mar, then the span from Jan to Mar includes all three. If you drag Mar ahead of Jan, the range boundaries change in workbook order. Check the tabs themselves whenever you rearrange a workbook or copy in a new period.

Worksheets inserted between the boundary sheets are part of the 3-D range. For example, inserting a new monthly tab between Feb and Mar places it inside a Jan-to-Mar span. A sheet placed before the first endpoint or after the last endpoint is outside that span unless you change the reference. Moving a sheet can likewise bring it into or take it out of the range. This makes 3-D SUM convenient for regularly maintained tab groups, but it means that workbook organization affects the result.

Hidden worksheets between the endpoints are still included in a 3-D reference. Hiding a tab is not a way to exclude its values. If a hidden sheet should not contribute to a total, move it outside the referenced span or use a method that names only the sheets to include.

When the sheets are not next to one another

A 3-D reference includes every sheet between its endpoints, so it is not suitable when you want only selected, nonadjacent tabs. In that case, list the sheet references individually in SUM. With values in B2 on Jan and Mar only, and with Feb deliberately excluded, enter =SUM(Jan!B2,Mar!B2). If the two selected values are 120 and 95, the result is 215.

For worksheet names with spaces, quote each sheet name separately. With values in B2 on North Region and West Region, enter =SUM('North Region'!B2,'West Region'!B2). For a short fixed list, this is clear and precise. If the list grows, however, the formula must be edited to add each new sheet. A 3-D reference is easier to maintain when all of the desired sheets can be kept together in one block.

You can also select several tabs and enter a value or formula into the same cell on all selected worksheets at once. That is a data-entry shortcut, not a different way to total them. When tabs are grouped, changes can affect every selected sheet, so ungroup them after the edit by selecting a single tab. To sum values across tabs, the summary formula is still needed.

What gets added, and what does not

SUM adds numeric values in the referenced cells. If a source cell is blank, it contributes zero; if it contains text rather than a number, that text is not added as a numeric amount. A formula in a source cell is fine: SUM uses the value that formula returns. If the displayed total seems low, inspect the source cells for numbers stored as text, missing entries, or unexpected blanks.

Formatting does not determine whether a value is included. Currency formatting changes how a number looks, not its underlying value. Similarly, a value shown with no decimal places may still contain decimals that contribute to the total. To check a suspicious result, select a source cell and inspect the formula bar or change its number format temporarily.

Be careful to use the correct address. If each sheet stores its total in B2 but you point the 3-D reference at B3, Excel will add B3 across the sheets without warning that you meant another cell. A useful check is to compare the formula result with a hand calculation from two or three known source cells, especially when first setting up a template.

Common problems and quick checks

Wrong total: Confirm both endpoint names and the order of the tabs. A middle sheet may be included even if you did not mean to include it.
Formula error after renaming: Check that the worksheet still exists and the reference syntax is intact. If typing a sheet name manually, match its spelling and use single quotes when needed.
Unexpectedly high total: Look for an extra worksheet inside the span, including a hidden one, or a duplicate period tab.
Unexpectedly low total: Verify the cell address on every sheet and check whether some entries are blank or stored as text.
Circular reference warning: Make sure the summary cell is not on one of the sheets included in the 3-D span at the referenced address.

Excel may use a different list separator in formulas depending on regional settings. The examples here use commas, which are the standard argument separators in many installations. If Excel rejects a formula that otherwise appears correct, check the separator used by your installation rather than changing the sheet-range logic.

3-D SUM versus other ways to organize totals

Use a 3-D SUM when each worksheet follows the same layout and the sheets to include are contiguous. It keeps the summary formula short and can adapt when a new sheet is inserted inside the range. Use an explicit list of sheet references when the included tabs are scattered or when you need to exclude particular sheets between two others.

For ongoing records, another option is to store all transactions in one Excel table, with a column for month, team, or location. A single structured dataset can be easier to filter, summarize, and maintain than many nearly identical tabs. A PivotTable can then summarize that table by the relevant category. This takes a different setup, so it may not be worthwhile for a small, established workbook, but it can reduce the risk of inconsistent layouts as a dataset grows.

These approaches solve related but distinct problems. A 3-D formula totals corresponding cells across worksheets; an explicit formula totals selected references; and a consolidated table organizes records in one dataset for broader analysis. Choose based on how the workbook is likely to change, not just on which formula is shortest today.

Frequently Asked Questions

Can Excel sum the same cell across all tabs automatically?
Not with an unrestricted reference that means every worksheet in the workbook. A 3-D SUM covers the sheets between two named endpoints, including intervening tabs. Set the endpoints to cover the intended group, and review the result if sheets are added or moved.

Does the order of tabs matter?
Yes. The sheet names mark the boundaries of a range, and Excel includes worksheets between those boundaries according to tab order. Reordering tabs can change which sheets are included.

Will a 3-D SUM include hidden sheets?
Yes. A hidden worksheet between the two endpoint sheets remains part of the 3-D reference. Hiding it does not remove its value from the sum.

Can I sum the same cell from only two tabs?
Yes. If the tabs are not adjacent or you want to exclude the sheets between them, use separate references in SUM. For values in B2 on Jan and Mar, enter =SUM(Jan!B2,Mar!B2); if those cells contain 120 and 95, the result is 215.

Can the cell address be a range instead of one cell?
Yes. A 3-D reference can identify a matching range on each sheet, not just one cell. For example, if the same sales figures are in B2:B10 on every sheet from Jan through Mar, a 3-D SUM can add that range across those sheets. Verify that the layouts match and that the endpoints enclose only the intended tabs.

Does this work in Excel for Mac or Excel for the web?
3-D references are an Excel formula feature, so the same basic formula concept applies in current Excel for Mac and Excel for the web. Ribbon placement and some worksheet-management steps can differ from Windows, but the sheet-range syntax remains the key part of the formula.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.