8.15 Named Ranges with OFFSET (Rolling Charts)
Use when you need, for example, the last 6 months automatically, in any Excel version.
=OFFSET(reference, rows, cols, [height], [width]) returns a range shifted from a starting cell.
Steps in Excel
- Data in
ChartData!A2:A100(months) andB2:B100(Pune), filled from the top without gaps. -
Formulas › Name Manager › New… Name
LastMonths, Refers to:=OFFSET(ChartData!$A$1, COUNTA(ChartData!$A:$A)-6, 0, 6, 1) -
New name
LastSales, Refers to:=OFFSET(LastMonths, 0, 1). - Insert any chart › Chart Design › Select Data › Edit the series › Series values:
='Blinkit_Maharashtra.xlsx'!LastSales(workbook or sheet name is required) › Horizontal (Category) Axis Labels › Edit ›='Blinkit_Maharashtra.xlsx'!LastMonths. - Add a new month row – the chart shows the latest 6 months.
OFFSET is volatile (recalculates on every change); on large models prefer an INDEX-based name: =INDEX(ChartData!$A:$A,COUNTA(ChartData!$A:$A)-5):INDEX(ChartData!$A:$A,COUNTA(ChartData!$A:$A)).
Ravindra Bagale's Tip
When putting a named range into a chart series, many students write just =LastSales and Excel gives an error. The series needs the workbook/sheet name – ='File.xlsx'!LastSales. And for COUNTA to work correctly, don't leave empty cells in the middle of the column or notes below it.
Ravindra Bagale's Tip – मराठी
Named range chart series मध्ये टाकताना बरेच students फक्त =LastSales लिहितात आणि Excel error देतो. Series मध्ये workbook/sheet चं नाव लागतंच – ='File.xlsx'!LastSales. आणि COUNTA बरोबर चालण्यासाठी column मध्ये मधे मधे रिकाम्या cells किंवा खाली notes ठेवू नका.
Ravindra Bagale's Tip – हिंदी
Named range को chart series में डालते समय बहुत से students सिर्फ़ =LastSales लिखते हैं और Excel error देता है. Series में workbook/sheet का नाम चाहिए ही – ='File.xlsx'!LastSales. और COUNTA सही चले, इसके लिए column में बीच-बीच में खाली cells या नीचे notes मत रखो.
Practice tasks (10+)
Try these on a blank sheet. Open a hint only if you are stuck. No quiz — just practice.
1. Rebuild the smallest example of named ranges with offset rolling charts on a blank sheet for BrightLedger Finance.
Hint: Use only the steps on this page. Keep numbers numeric.
2. Change one input for Priya Deshmukh and confirm the result updates.
Hint: If the result is stuck, check absolute vs relative references.
3. Repeat the same steps with a £ or € value for Emma Clarke.
Hint: Formatting must not break SUM.
4. Write the result meaning in one plain sentence next to the cell.
Hint: Imagine explaining it to a new joiners.
5. Add a second row and fill the method down.
Hint: Double-click the fill handle if the pattern is consistent.
6. Break the example on purpose, then fix it using this lesson.
Hint: Common break: text instead of a number.
7. Apply named ranges with offset rolling charts to a 5-row made-up BrightLedger Finance table.
Hint: Keep column headers clear.
8. Check your answer with a simple total or COUNT.
Hint: A wrong total usually means a format or key mismatch.
9. Swap ₹ and $ on one column and re-check totals.
Hint: Stored values stay the same; only the display changes.
10. List two mistakes beginners make with named ranges with offset rolling charts.
Hint: Look back at the tip box on this page.
11. Teach the steps out loud in under one minute.
Hint: Why → What → How.
12. Save the sheet as BrightLedger_practice.xlsx.
Hint: Use a clear file name for class.
Loading practice tasks...
- Total nights (Easy): Add up Nights in D2:D11.
- Average rate (Easy): Average of the Rate per night ($) column, rounded to 2 decimals.
- Rows in Standard (Easy): How many rows have Category/group "Standard"? (column B)
- Largest nights (Easy): The largest value in D2:D11.
- Nights for Standard (Medium): Total of the Nights column where Room type is "Standard".
- Standard in Seattle (Medium): Total of the Nights column where Room type is "Standard" and City is "Seattle".
- Best in Standard (Medium): Largest E2:E11 value where B is "Standard".
- Value of row 2 (Medium): Multiply the first-row Nights by the first-row Rate per night ($).
- Look up Aarav Shah (Medium): The Nights for "Aarav Shah" (use a lookup on column A).
- Total value (Hard): Sum of D times E for all rows.
- Share of Standard (Hard): Share of D2:D11 that is "Standard", rounded to 3 decimals.
- Total "Nights" for Standard (Hard): Add up the "Nights" column for the rows where "Room type" is "Standard".
- Above average? (Hard): "Above" if E2 is above the average of E2:E11, else "Below".
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
