To add the same cell from several sheets in Excel, enter a SUM formula such as =SUM(Jan!B2,Feb!B2,Mar!B2) in the cell where you want the total. If the sheets are next to each other in the tab order, a 3-D reference can add the same cell or range across all of them. The steps below explain both approaches, how to total ranges, and how to fix common reference problems.
What You’ll Need
- An Excel workbook containing the sheets you want to add. These methods work in Microsoft Excel for Microsoft 365 on Windows.
- A destination cell for the result. It can be on one of the source sheets or on a separate summary sheet.
- The cell address or range to total on each sheet. For example, you might use B2 for each monthly total, or B2:B10 for a list of amounts.
- The sheet names, and, for a 3-D reference, the names and tab order of the first and last sheets in the group.
Step-by-Step
- Check how the data is arranged. Open the workbook and look at the tabs along the bottom. Confirm which sheets contain the values and whether the data is in the same cell or range on each sheet. For example, if each monthly sheet stores its total in B2, you can add those cells directly. If the monthly data is in B2:B10 on every sheet, you can sum those matching ranges instead. Make sure the cells contain numbers, not text that only looks like a number.
- Choose or create a destination cell. Click the cell where you want the answer to appear. A separate summary sheet is useful when you want to keep source data and results apart. To add a sheet in Excel for Microsoft 365 on Windows, select the plus button beside the sheet tabs, then rename the new tab if helpful. You can also click an existing cell on a source sheet, but avoid placing the result inside the range you are adding; doing so can create a circular reference.
- Enter a SUM formula for separate, nonadjacent sheets. This is the flexible option when the sheets you need are not next to each other, or when you want to list exactly which sheets to include. With monthly totals in B2 on the Jan, Feb, and Mar sheets, enter this formula in your destination cell:
=SUM(Jan!B2,Feb!B2,Mar!B2). If those cells contain 1250, 980, and 1170, the result is 3400. The sheet name, exclamation mark, and cell address together identify each reference; commas separate the references in the SUM function. - Use single quotes when a sheet name contains spaces or special characters. If the sheets are named North Region, South Region, and West Region, and each value is in B2, enter:
=SUM('North Region'!B2,'South Region'!B2,'West Region'!B2). With values of 400, 350, and 250 in those cells, the result is 1000. Excel generally adds the quotes automatically when you build a formula by clicking cells. Including them yourself makes the references clear and avoids errors with names that require quoting. - Build a reference by clicking each source cell. If you do not want to type sheet names, select the destination cell and type
=SUM(. Click the first source tab, select the cell to add, type a comma, and repeat for each other sheet and cell. Type a closing parenthesis and press Enter. Excel inserts the sheet references as you click. This is especially helpful when sheet names are long or easy to mistype. If you add many sheets this way, check the completed formula in the formula bar to confirm that every intended sheet appears once. - Use a 3-D reference for matching cells on adjacent sheets. A 3-D reference includes the same cell or range on every sheet between two named tabs, inclusive. If Jan, Feb, and Mar are consecutive tabs in that order and each monthly total is in B2, enter:
=SUM(Jan:Mar!B2). With values of 1250, 980, and 1170, the result is 3400. The colon between the sheet names indicates the span of tabs; the exclamation mark separates that sheet span from the cell reference. The tabs do not need to be next to the destination sheet. - Apply the 3-D method to a matching range. If each sheet has amounts in B2:B10, and the sheets Jan through Mar are consecutive, enter:
=SUM(Jan:Mar!B2:B10). Excel adds B2:B10 on Jan, Feb, and Mar, rather than adding only one cell from each sheet. For instance, if the three sheets contain 10, 20, and 30 in B2 and all other cells in the range are blank, the result is 60. Use this approach only when the corresponding entries occupy the same positions on each sheet. - Confirm the sheet span before relying on a 3-D total. A 3-D formula includes sheets between the start and end tabs, not just sheets whose names suggest a particular month or category. It also includes a sheet you move into that span later. If the tabs Jan and Mar are endpoints with an extra sheet called Notes between them, the reference Jan:Mar includes Notes too. Keep only intended source sheets within the span, or use a formula listing individual sheets when the group is not truly continuous.
- Press Enter and check the result. Excel calculates the formula and displays the total in the destination cell. Click that cell to see the formula in the formula bar. Compare the result with a quick manual check, such as adding the three sample values or checking a small range on each sheet. If you see the formula text instead of an answer, check that the cell is not formatted as Text and that the formula begins with an equals sign. You may need to change the format to General and re-enter the formula.
- Copy the formula when you need more than one total. If you are totaling several corresponding cells, enter the first formula and use the fill handle (the small square at the lower-right corner of the selected cell) to copy it into neighboring cells. Excel adjusts ordinary cell references as formulas move. Check the copied formulas before using the results, especially if your layout is not identical across sheets. For repeated totals in separate locations, editing and verifying each formula can be safer than dragging.
Common Mistakes
Leaving out a sheet or cell reference: A formula that lists Jan and Feb will not include Mar unless Mar appears in the formula or falls inside a 3-D sheet span. Check each reference against the tabs and the cell addresses you intend to add.
Using a 3-D reference when the sheets are not arranged as a continuous group: The formula =SUM(Jan:Mar!B2) includes every sheet between Jan and Mar in tab order. It is not a shortcut for selecting only sheets with month names. Use separate references if an unrelated sheet lies in the middle, or if you want only specific sheets.
Typing a sheet name incorrectly: A misspelling or an omitted space can produce a reference error. Sheet names with spaces should be enclosed in single quotes in a reference. Clicking the source cell while building the formula reduces the chance of a typing error.
Adding the wrong range or mismatched cells: A formula can calculate without an error and still answer the wrong question. For example, summing B2 on one sheet and B3 on another may not compare equivalent values. Confirm that every reference points to the corresponding data.
Expecting text to be added as a number: SUM ignores text in referenced cells. A value imported as text may therefore be left out of the total without an obvious warning. Check the source cells if the answer is lower than expected, and convert text-formatted numbers to numeric values before relying on the result.
Creating a circular reference: If the result cell is included in the range being summed, Excel may warn about a circular reference or produce an unintended result. Put the total outside the source range or use a separate summary sheet.
Tips
Use individual sheet references when you need precise control over the included sheets. They are easier to audit when your source tabs are separated by other content. A 3-D reference is more compact when every sheet in one continuous tab group should contribute the same cell or range.
Keep monthly or departmental sheets consistent: use the same cell addresses and column layout on each one. Consistency makes both methods more reliable and makes it easier to check a total by comparing the same position across tabs. Consider naming sheets with short, clear labels so references are easy to read.
For a large workbook, place the result on a clearly labeled summary sheet and identify what the total covers. A label such as Total for Jan–Mar helps another person understand the calculation. When you add or rename sheets, revisit formulas that refer to them; a formula listing sheet names individually may need to be updated.
On Excel for Mac, the formula syntax is the same, and you can use the sheet tabs and formula bar to select references. Ribbon labels and window layout can differ slightly from Windows. In Excel for the web, these SUM formulas also work; use the sheet tabs to select source cells if typing references is inconvenient. A 3-D formula still depends on the order of sheets in the workbook.
Troubleshooting
The formula shows as text instead of calculating: Select the cell and check its number format. If it is Text, change it to General from the Home tab, then re-enter the formula. Also confirm that there is no apostrophe before the equals sign and that the formula starts with =.
You see #REF!: One of the referenced sheets or cells may have been deleted, or the formula may contain an invalid reference. Inspect the formula in the formula bar, restore the missing sheet if possible, or replace the broken reference with the correct existing sheet and cell.
The total is zero or too small: Confirm that the source cells contain numeric values and that the formula points to the correct addresses. For a 3-D formula, check the first and last sheet names and inspect every tab between them. A number stored as text can be ignored by SUM, so correct the source data if needed.
The total is too large: Check for a duplicated sheet reference in a manually written formula. With a 3-D reference, look for extra tabs between the endpoints, since those sheets are included too. Also verify that the range does not include both detail rows and a subtotal that already counts those rows.
Excel does not update after a source value changes: Check whether calculation is set to Automatic. On Windows, open Formulas > Calculation Options and select Automatic if it is not already selected. You can also recalculate the workbook with F9 on Windows; keyboard behavior can differ on Mac depending on the keyboard and Excel settings. If the formula is correct but the displayed answer is stale, recalculation may resolve it.
A formula errors after a sheet is renamed: Excel usually updates references when you rename a sheet within the workbook, but inspect the formula if it was edited, pasted, or linked from elsewhere. Re-select the correct source cell to replace a broken reference. Sheet names typed manually must match the current tab names exactly.
Frequently Asked Questions
Can I sum the same cell across every sheet in a workbook?
Yes, if the sheets you want form a continuous tab span, use a 3-D reference with the first and last sheet names. It includes all sheets between those endpoints. If you mean selected sheets scattered through the workbook, list those sheets individually in SUM.
Can I add cells from sheets with different names?
Yes. SUM can use individual references to sheets with different names. Put each sheet name and cell address in the function, and use single quotes around names that contain spaces or certain special characters.
Can I sum different cells from different sheets?
Yes. Include each specific cell as a separate argument in SUM. Make sure the cells represent values you actually intend to combine; unlike a 3-D reference, individual references do not have to use the same cell address.
Does SUM across sheets update when I change a source value?
Yes, Excel normally recalculates the result when a referenced value changes. If it does not, check that calculation is set to Automatic and verify that the formula still points to the right cells.
What happens if I insert a sheet inside a 3-D reference?
A sheet inserted between the first and last sheets in a 3-D reference can become part of the total. Check the tab order and formula span whenever you add or move sheets so the formula includes only the intended data.
Can I sum across sheets in Excel for the web?
Yes. SUM formulas with sheet references work in Excel for the web as well as the desktop app. You can type the formula or select cells by clicking the relevant sheet tabs, then check the result in the destination cell.
Related
- Remove Strikethrough in Excel Numbers and Cells
- Excel Strikethrough Shortcut: How to Remove It Quickly
- Remove a Word in an Excel Cell With Find & Replace
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets VLOOKUP: Formula, Examples, and FixesIn Google Sheets, VLOOKUP searches for a value in the first column of a range and returns a…
- How to Turn On Track Changes in Microsoft Word DesktopTo turn on Track Changes in Word desktop, open the document, select Review > Track Changes, and then…
- Set Print Area in Excel: Steps, Pages, and FixesIn Excel for Microsoft 365 on Windows, select the cells you want to print, then choose Page Layout…
- Excel Solver: What It Does and How to Use It EffectivelyIn Excel, Solver is an add-in that finds values for selected cells to maximize, minimize, or reach a…
- Excel AVERAGE: What It Ignores and IncludesIn Excel, AVERAGE ignores empty cells, text, and logical values such as TRUE or FALSE when they are…
- How to Auto Forward Emails in OutlookTo auto forward every new message in Outlook, open Settings, go to Mail > Forwarding, turn on forwarding,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.