Working Across Sheets
By the Syncfusion DevTutorials team · Last updated
Reference other sheets, combine data from several sheets, and protect sheets and workbooks. Then build a quarterly summary in the Chapter 6 project.
Once your data is spread across several sheets, like one sheet per month, you often need a summary that brings everything together. Excel formulas can pull data from other sheets, and protection can stop people from accidentally changing your work. In this lesson, you will work with multiple worksheets, reference another sheet in a formula, combine data from several sheets, and protect sheets and the workbook. At the end, you will build a quarterly summary in the Chapter 6 project.
Open the Excel sandbox and follow along.
Multiple worksheets
A common, neat layout is:
- One sheet per period or group, all with the same layout:
Jan,Feb,Mar, each with totals in the same cell. - One summary sheet that pulls the main numbers from the others.
Keeping the layouts the same is what makes cross-sheet formulas simple. If every month’s total is in B6, the summary always looks in B6.
Reference another sheet
To use a cell from another sheet, put the sheet name and an exclamation mark before the address:
=Jan!B6
=Jan!B6*2
='Sales 2026'!B6 (sheet names with spaces need single quotes)
The easy way: type = in the cell where you want the result, click the other sheet’s tab, click the cell you want, and press Enter. Excel writes the reference for you, and takes you back to where you started.
Using data from multiple sheets
Add values from several sheets with +: =Jan!B6+Feb!B6+Mar!B6
Or use functions across sheets: =SUM(Jan!B2:B5) adds a range on another sheet, and =AVERAGE(Jan!B6, Feb!B6, Mar!B6) averages one cell from each.
In Excel, a 3-D reference adds the same cell across a run of sheets in one go: =SUM(Jan:Mar!B6) adds B6 on every sheet from Jan to Mar. Keep the sheets next to each other in that order.
Cross-sheet formulas update automatically. If you change a number on the Feb sheet, the summary changes too.
If you rename a sheet, Excel updates every formula that refers to it. If you delete a sheet, those formulas show #REF!.
Workbook protection
There are two kinds of protection:
Protect a sheet stops people from changing cells on that sheet.
- Go to Review > Protect Sheet or right-click the sheet tab and choose Protect Sheet.
- Optionally type a password, and choose what people may still do, such as select cells.
- Click OK.
To let people type in some cells, like an input area, first select those cells, open Format Cells > Protection, and untick Locked. Then protect the sheet. Only the unlocked cells can be edited.
Protect the workbook stops people from changing the structure: adding, deleting, renaming, moving, or hiding sheets.
- Go to Review > Protect Workbook.
- Optionally type a password, and click OK.
To remove protection, use Unprotect Sheet or Unprotect Workbook on the same tab.
Protection prevents accidents, not determined people. If you set a password, write it down. Without it, you cannot unprotect your own work.
Common mistakes
| Mistake | What happens | How to fix it |
|---|---|---|
Missing ! in a reference | Excel shows an error | Write SheetName!Cell |
| Sheet name with spaces, no quotes | Excel rejects the formula | Use 'Sales 2026'!B6 |
| Different layouts on each sheet | The summary picks up the wrong numbers | Make every sheet’s layout match |
| Deleting a sheet the summary uses | #REF! on the summary | Restore the sheet from a saved copy |
| Protecting before unlocking input cells | Nobody can type in the input area | Unprotect, unlock the input cells, protect again |
Exercise
Click Reset in the sandbox header. Rename Sheet1 to Shop, and type in it: A1 Sales, B1 400. Add a second sheet named Cafe, and type: A1 Sales, B1 250. Add a third sheet named Summary. Then do each task and press Check.
Chapter project
Put Chapter 6 together. You will organize three months of a small business’s expenses into tidy sheets and build a protected quarterly summary.
Quarterly expense summary
What to remember
- Keep the same layout on every period sheet, with a separate summary sheet.
- Refer to another sheet with
SheetName!Cell; use quotes for names with spaces. - Build references by typing
=and clicking the other sheet’s cell. - Functions work across sheets:
=SUM(Jan!B2:B4). - Protect Sheet locks cells; Protect Workbook locks the sheet structure.
- Protection prevents accidents; keep any password safe.
That is the end of Chapter 6. In Chapter 7, you will turn numbers into charts and use conditional formatting.