Ravindra BagaleCourses & study guides

8. Charts

8.15 Named Ranges with OFFSET (Rolling Charts)

By Last updated:

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

  1. Data in ChartData!A2:A100 (months) and B2:B100 (Pune), filled from the top without gaps.

  2. Formulas › Name Manager › New… Name LastMonths, Refers to:

    =OFFSET(ChartData!$A$1, COUNTA(ChartData!$A:$A)-6, 0, 6, 1)

  3. New name LastSales, Refers to: =OFFSET(LastMonths, 0, 1).

  4. 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.
  5. 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

Named range chart series madhe takta na khup students fakt =LastSales lihitat aani Excel error deto. Series madhe workbook/sheet che nav lagtach – ='File.xlsx'!LastSales. Ani COUNTA barobar chalnyasathi column madhe madhe rikami cells kiwa khali notes theu naka.

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.