Chapter 5: Working with Multiple Worksheets

we’ve covered data entry, formatting, and basic calculations. Now we move to Chapter 5: Working with Multiple Worksheets.

This chapter is super useful when your data grows and you need to organize it across several sheets (like monthly reports, departments, or different years). I’ll explain everything very slowly, step by step, like a patient teacher sitting next to you at your computer. We’ll use real-life examples (monthly sales tracking), clear instructions, and lots of practice tips. By the end, you’ll confidently manage multiple sheets, link data between them, and use powerful 3D formulas to summarize everything automatically.

Let’s start!

1. Adding, Renaming, Deleting, Moving Worksheets

Worksheets are the tabs at the bottom of your Excel window. A workbook (the file) can have many worksheets.

A. Adding a new worksheet

• Easiest way: Click the + button next to the last sheet tab (very bottom-right).
• Or: Right-click any sheet tab → Insert → Worksheet → OK.
• Shortcut: Shift + F11 (very fast!)

Example: You have Sheet1 with January sales. Click + → new sheet appears → now you have Sheet1 and Sheet2.

B. Renaming a worksheet (make names meaningful!)

1. Double-click the sheet tab → type new name → press Enter. Or: Right-click tab → Rename.
2. Names can be up to 31 characters, no / \ ? * [ ] :

Example:

• Double-click Sheet1 → rename to Jan-2026
• Rename Sheet2 to Feb-2026
• Add more: Mar-2026, Apr-2026, Summary

C. Deleting a worksheet (careful — permanent unless you undo!)

• Right-click the sheet tab → Delete
• Or: Select sheet → Home tab → Cells group → Delete → Delete Sheet

Tip: If Excel asks “Delete entire sheet?” → Yes only if you’re sure. Use Ctrl + Z immediately to undo.

D. Moving (reordering) worksheets

• Click and drag the sheet tab left/right to new position.
• Or: Right-click tab → Move or Copy → choose new location → OK.

Example: Drag Apr-2026 between Feb and Mar → order becomes Jan → Feb → Apr → Mar → Summary.

2. Grouping Worksheets

Grouping lets you do the same action on multiple sheets at once (e.g., add headers, format cells, enter same data/formulas).

How to group:

• Adjacent sheets (easiest): Click first tab (e.g., Jan-2026) → hold Shift → click last tab (e.g., Mar-2026) → all in between group.
• Non-adjacent sheets: Hold Ctrl → click each tab you want.

Visual sign: Title bar shows [Group] after file name. Tabs look white/selected.

What you can do while grouped:

• Enter text/numbers/formulas → applies to same cell on all grouped sheets.
• Format cells (bold, color, borders, number format).
• Insert rows/columns, change page setup, etc.

Example – Add company header to all monthly sheets:

1. Group Jan-2026 to Apr-2026 (Shift-click).
2. Click cell A1 → type “Tea Shop Sales – 2026”
3. Make it bold, size 16, center, merge A1:E1.
4. In A2 type “Item”, B2“Quantity”, C2“Price”, D2“Total”.
5. Format headers: Light blue fill, white text, bold. → All 4 monthly sheets now have identical headers!

Ungroup (very important!):

• Right-click any grouped tab → Ungroup Sheets
• Or: Click any ungrouped tab (gray one).
• Or: Right-click title bar area → ungroup.

Warning: If you forget to ungroup and type something → it changes all sheets! Always ungroup after finishing.

3. Linking Data Between Sheets

Linking means one sheet pulls live data from another. If source changes → linked cell updates automatically!

How to create a link: Method 1 (easiest – point and click):

1. Go to destination sheet (e.g., Summary).
2. Click cell where you want data (e.g., B5).
3. Type =
4. Click the source sheet tab (e.g., Jan-2026).
5. Click the source cell (e.g., D10 – total sales).
6. Press Enter.

→ Formula becomes =Jan-2026!D10 (or similar).

Method 2 (type manually):

• In destination: =Jan-2026!B5 (sheet name!cell)

Real example – Monthly sales summary:

• Each monthly sheet (Jan to Apr) has total sales in D20.
• In Summary sheet:
  • A5 → Jan
  • B5 → type = → click Jan-2026 tab → click D20 → Enter → shows Jan total.
  • Drag formula down (or copy to B6:B8) → change sheet names manually or use point-click for each.

Pro tip: If sheet name has spaces (e.g., Jan 2026), Excel adds single quotes: =’Jan 2026′!D20

4. 3D Formulas (Across Multiple Sheets – The Real Power!)

A 3D formula (or 3-D reference) summarizes the same cell/range across several consecutive sheets with one formula.

Syntax: =FUNCTION(FirstSheet:LastSheet!CellOrRange)

Supported functions: SUM, AVERAGE, COUNT, COUNTA, MIN, MAX, PRODUCT, etc.

Real-life example – Total yearly sales: Assume sheets: Jan-2026 : Apr-2026 (consecutive tabs), each has total sales in D20.

1. Go to Summary sheet.
2. Click cell B10 (Total Q1 Sales).
3. Type =SUM(
4. Click Jan-2026 tab.
5. Hold Shift → click Apr-2026 tab (selects range Jan to Apr).
6. Click cell D20 on Apr sheet.
7. Type ) → Enter.

→ Formula: =SUM(Jan-2026:Apr-2026!D20) → Adds D20 from Jan + Feb + Mar + Apr automatically!

Magic part:

• Add a new sheet May-2026 between Apr and Summary? → Move it inside the range → formula auto-includes it!
• Delete a middle sheet → formula skips it (still works).
• But: If you delete/move the first or last sheet (endpoint), you must edit the formula.

Another example – Average monthly sales:

• In Summary B11: =AVERAGE(Jan-2026:Apr-2026!D20)

Best practice: Keep similar sheets consecutive (Jan to Dec together, then Summary at end).

Quick Summary Table – Chapter 5

Homework / Practice (Do This Today!)

1. In your practice file:
   • Rename Sheet1 to Summary
   • Add 4 new sheets: Jan-2026, Feb-2026, Mar-2026, Apr-2026
2. In each monthly sheet (group them first!):
   • A1: “Tea Shop – Month 2026” (bold, merged, colored)
   • A2:D2: Item | Qty | Price | Total
   • A3:A6: Pen, Notebook, Eraser, Pencil
   • Fill some qty & price, calculate Total =Qty*Price in D
   • Put grand total in D10 =SUM(D3:D6)
3. Ungroup!
4. In Summary sheet:
   • A5:A8 → Jan, Feb, Mar, Apr
   • B5 → link to Jan-2026!D10 (point-click method)
   • Drag/copy down and adjust sheet names
5. In B10 → create 3D SUM: =SUM(Jan-2026:Apr-2026!D10)
6. Test: Change a qty in Feb-2026 → watch Summary update automatically!