Skip to content
Reading settings
ExcelBeginner~20 min practiceLesson 3 of 34

Manage worksheets and cross-sheet references

Build a monthly summary and keep original and scenario sheets separate.

Content reviewed · How we check lessons

Loading your progress…

In this lesson

Create cross-sheet totals, rename a sheet and copy a scenario without changing the base month.

Example

Compare monthly supply costs

On Jan, enter Item and Cost, then Paper 40 and Ink 60. On Feb, use the same headers with Paper 55 and Ink 65. A Summary sheet will compare the months; a separate February copy will test a higher ink cost.

How it works

A cross-sheet reference includes the worksheet name. Excel surrounds names containing spaces with single quotes, as in a reference to Jan Costs.

A copied sheet is useful for a scenario. Renaming the original does not create a copy, and deleting a sheet can break formulas that refer to it. Save a backup before removing tabs.

Steps

  1. Create Jan, Feb and Summary. Enter the two monthly lists and check that both use Item and Cost as their headers.

  2. In Summary, enter Month, Total and Note in A1:C1 and Jan and Feb in A2:A3. Enter the formula below in B2 and =SUM(Feb!B2:B3) in B3.

    =SUM(Jan!B2:B3)

    English function names. Formula settings →

  3. Check totals of 100 for Jan and 120 for Feb. Rename Jan to Jan Costs with the sheet-tab menu. Select Summary!B2 and look at how Excel updated the sheet name in the formula.

  4. On desktop, right-click Feb, choose Move or Copy and tick Create a copy. In web Excel, use Duplicate sheet. Name the new tab Feb Scenario.

  5. Change Ink on Feb Scenario to 75. Add a separate Summary row for its total. Leave the existing Feb total pointing to the original sheet.

  6. Save a backup. Before deleting Feb Scenario, remove its Summary row or replace that row with a value and label it as a snapshot. Then remove the scenario tab and check the remaining formulas.

Try it yourself

  1. Add March with Paper 50 and Ink 70, then insert a summary row that explicitly references March. Move the March tab next to Feb and confirm the total stays the same.
  2. Add a Readme sheet listing the roles of source, summary and scenario tabs, and explain why hiding a source tab is not the same as deleting its data.
Show expected result
  • January is 100, February is 120 and March is 120. Moving or renaming a sheet does not change these totals when its references update correctly.
  • The February scenario totals 130 while the original February remains 120; each summary formula points at the intended sheet.

If something goes wrong

A total shows #REF! after a tab was deleted.
Use Undo if available, or reopen the backup. Restore the source sheet and rebuild the reference; IFERROR would hide the broken link.
The base month changed along with the scenario.
Check the tab names and each Summary formula. Restore the original values, then use Create a copy or Duplicate to make a separate scenario sheet.

Quick check

Try these questions after the exercise. Check the result in your app before marking the lesson complete.

1. What is the correct base February total?
2. Which operation is most likely to break a reference permanently?
3. Why use a separate scenario tab?

Choose an answer for all 3 questions to check your understanding.

Optional: try it with Copilot

If you have Copilot, try this after the exercise. Keep the original file and compare its suggestions with your own result.

Read these Summary formulas and tell me which use the base month and which use the scenario. Flag any broken sheet references.

More from Microsoft

These Microsoft guides cover the tools used in this lesson.

Loading your progress…