0
0
Excelspreadsheet~10 mins

Dynamic charts with data ranges in Excel - Cell-by-Cell Formula Trace

Choose your learning style9 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
Sample Data

Monthly sales data from January to June.

CellValue
A1Month
B1Sales
A2Jan
B2100
A3Feb
B3120
A4Mar
B4130
A5Apr
B590
A6May
B6150
A7Jun
B7160
Formula Trace
=OFFSET($B$1,1,0,COUNTA($B$2:$B$100),1)
Step 1: COUNTA($B$2:$B$100)
Step 2: OFFSET($B$1,1,0,6,1)
Cell Reference Map
    A       B
1 |Month | Sales |
2 | Jan  | 100   |
3 | Feb  | 120   |
4 | Mar  | 130   |
5 | Apr  | 90    |
6 | May  | 150   |
7 | Jun  | 160   |
The formula uses the Sales column from B2 down to B7 dynamically based on how many sales values exist.
Result
    A       B
1 |Month | Sales |
2 | Jan  | 100   |
3 | Feb  | 120   |
4 | Mar  | 130   |
5 | Apr  | 90    |
6 | May  | 150   |
7 | Jun  | 160   |

Chart uses range B2:B7 dynamically for sales data.
The dynamic range B2:B7 is used as the data source for the chart. If more sales data is added below B7, the range will automatically expand.
Sheet Trace Quiz - 3 Questions
Test your understanding
What does COUNTA($B$2:$B$100) return in this data?
A5
B7
C6
D100
Key Result
OFFSET with COUNTA creates a dynamic range starting from a fixed cell, extending down based on count of non-empty cells.