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 task
Create a "last 6 months" line chart with OFFSET names. Then make the number of months (3, 6, 12) come from a cell with a drop-down.