Ravindra BagaleCourses & study guides मराठी Track your progress

23. Formulas Across Sheets and Workbooks

23.1 Referencing Another Sheet

By Last updated:

Example first. The workbook has one sheet per month: Jan, Feb and Mar, each with the same four outlets in A2:B5. On a Summary sheet, Ravindra Bagale wants January Pune sales and the Pune change from January to February. The formulas reach into the other sheets.

Reference to another sheet — in 30 seconds

What: A formula can use a cell on another sheet. Write the sheet name, an exclamation mark, then the cell: Jan!B2.

Why: Real workbooks keep raw data on one sheet and the report on another. Cross-sheet references connect them without copying numbers by hand.

Tiny example: On Summary, =Jan!B2 shows ₹4,12,000. =Feb!B2/Jan!B2-1 shows the Pune change: -3.4%.

First look at the data (before → after):

Reference to another sheet: before and after tables, fictional Blinkit Maharashtra data

Expected result — Sheet Summary

A: Outlet B: Jan (₹) C: Feb (₹) D: Change
Pune 4,12,000 3,98,000 -3.4%
Nashik 2,68,000 2,81,000 4.9%
Nagpur 2,31,000 2,44,000 5.6%
Kolhapur 1,89,000 1,76,000 -6.9%
Before table and formula as text

Before — Sheet Jan (A1:B5)

A: Outlet B: Sales (₹)
Pune 4,12,000
Nashik 2,68,000
Nagpur 2,31,000
Kolhapur 1,89,000

Before — Sheet Feb (A1:B5)

A: Outlet B: Sales (₹)
Pune 3,98,000
Nashik 2,81,000
Nagpur 2,44,000
Kolhapur 1,76,000

Formula / click path

Summary!B2: =Jan!B2
Summary!C2: =Feb!B2
Summary!D2: =Feb!B2/Jan!B2-1   (format 0.0%)
Sheet name with a space: ='Mar Sales'!B2

Do it step by step:

  1. On the Summary sheet, click B2 and type =.
  2. Click the Jan sheet tab, click B2, and press Enter. Excel writes =Jan!B2.
  3. In C2 do the same with the Feb sheet.
  4. In D2 type =Feb!B2/Jan!B2-1 and format it with Home › Number › Percent Style.
  5. Select B2:D2 and fill down to row 5.
  6. Rename the Jan sheet to Jan 2026 and look at B2: Excel changes the formula to ='Jan 2026'!B2 by itself.

Rules for writing sheet references

Sheet name How to write it Example
One word, no spaces Name!Cell =Jan!B2
Has a space or a hyphen Single quotes around the name ='Mar Sales'!B2
Starts with a number Single quotes ='2026 Jan'!B2
A range on another sheet Name!Start:End =SUM(Jan!B2:B5)

Clicking is safer than typing: Excel adds the quotes and the exclamation mark for you. When you rename a sheet, every reference to it is updated. When you delete a sheet, formulas that used it show #REF!.

Practice task (try it before you open the answer):

  1. Create the Jan, Feb and Mar sheets with the numbers in this module.
  2. On Summary, show Mar sales for all four outlets and the change from Feb to Mar.
Answer

Change Feb → Mar: Pune 14.3%, Nashik 3.9%, Nagpur -2.0%, Kolhapur 14.2%. Formula in D2: =Mar!B2/Feb!B2-1.

Ravindra Bagale's Tip

Build cross-sheet formulas by clicking, not typing. Typing a sheet name is how Jan!B2 becomes Jna!B2 and a #REF! appears in the Monday report.

Loading practice tasks...

  1. Madrid sales in Jan (Easy): Get Jan!C2.
  2. Seville covers in Mar (Easy): Get Mar!B5.
  3. Total Jan sales (Easy): Total of Jan!C2:C5.
  4. Total Feb covers (Easy): Total of Feb!B2:B5.
  5. Madrid in 3 months (Medium): Jan!C2 + Feb!C2 + Mar!C2.
  6. Change Jan to Feb (Medium): Feb!C3 minus Jan!C3.
  7. Average Mar sales (Medium): Average of Mar!C2:C5.
  8. Jan target met? (Medium): Is the total of C2 to C5 on the Jan sheet at least B2? (TRUE or FALSE)
  9. Quarter sales (Hard): Total sales of Jan, Feb and Mar.
  10. Mar bonus (Hard): Mar total sales × B3.
  11. Sales per cover in Feb (Hard): Total Feb sales ÷ total Feb covers, rounded to 2 decimals.

See all practice tasks in the Task Hub →