Dynamic charts automatically adjust when new data is added in Excel. I’ll show you several ways to create dynamic charts and a few bonus tricks.
Why Dynamic Charts?
Because most of us don’t enjoy manually adjusting chart ranges after adding data!
Each method below has pros and cons. Select the one that works for you.
My Excel file has detailed instructions. Below is a summary of each method.
Table Method
Once your data is structured as a Table you add new data below it and both your Table and Chart expand automatically. It’s worth the time to learn how Tables work!
Insert Rows Method
Highlight cells in your data, right click, ‘Shift Cells Down’. Add data and chart updates!
Filter Trick Method
Place NA function in empty cells. Filter out these NAs and chart updates. Replace these cells with new data later on.
Formula Method
=OFFSET(Sheet1!$D$2,1,0,COUNTA(Sheet1!$E$3:$E$16))
OFFSET and COUNTA functions combined in a named range to create expanding chart range.
The set-up steps are initially tricky. Advanced charting tricks build on this method. It’s worth learning!
Formula Method (blanks)
If your data is separated by blank rows then use this method (along with offset & counta formulas above).
=LOOKUP(2,1/(A4:A100<>””),ROW(A4:A100))-ROW(A3)
Formula determines the required height of your data so that no values are excluded.
Bonus Tricks!
Some practical and just for fun chart tricks.
- Select any 2 fields to display in chart
- Display an average line in your chart
- Add a trend-line and create its points
- Nerd Chart. Press F9 key to see them move
About Me
My name is Kevin Lehrbass. I’m a Data Analyst from Markham Ontario Canada (near Toronto).
Excel charts are one of my weak points so I practice techniques to keep sharp.
Maybe I should have included my picture in the nerd chart!
Visit my recommended training page.