0
0
Excelspreadsheet~8 mins

OFFSET for dynamic ranges in Excel - Dashboard Guide

Choose your learning style9 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
Dashboard Mode - OFFSET for dynamic ranges
Dashboard Goal

Show total sales and average sales dynamically based on the number of months entered by the user.

Sample Data
MonthSales
Jan100
Feb150
Mar200
Apr250
May300
Jun350

User enters number of months to analyze in cell B8.

Dashboard Components
  • Input Cell: B8 - Number of months to include (e.g., 3)
  • Dynamic Range for Sales: =OFFSET(B2,0,0,B8,1) - Creates a range starting at B2 down for B8 rows
  • Total Sales: =SUM(OFFSET(B2,0,0,B8,1)) - Sums sales for the selected months
  • Average Sales: =AVERAGE(OFFSET(B2,0,0,B8,1)) - Calculates average sales for selected months
  • Displayed Months: =OFFSET(A2,0,0,B8,1) - Shows month names for selected months
Dashboard Layout
+----------------------+----------------------+
| Enter Months (B8)    | [User input cell]    |
+----------------------+----------------------+
| Total Sales          | [Total Sales value]   |
+----------------------+----------------------+
| Average Sales        | [Average Sales value] |
+----------------------+----------------------+
| Months Included      | [List of months]      |
+----------------------+----------------------+
Interactivity

User changes the number in cell B8. This updates the dynamic ranges created by OFFSET. The total sales, average sales, and displayed months all update automatically to reflect the selected number of months.

Self Check

If you change the value in B8 from 3 to 5, which components update?

  • Total Sales
  • Average Sales
  • Displayed Months

All these components update to include sales and months for the first 5 months.

Key Result
Dashboard dynamically calculates total and average sales for a user-selected number of months using OFFSET.