Author: Oscar Cronquist Article last updated on October 01, 2018

The following animated gif shows you a sheet where you can select column (Region) or a row (Month) and the chart updates correspondingly. I am only using named ranges and a table to create this effect.

The beauty with this dynamic chart is that you can easily add more rows or columns to the table!

Example,

1. Select cell F14
2. Press Tab
3. A new row is created

The table automatically expands and the drop down lists and chart are instantly refreshed with the new row or column values.

I created five named ranges.

Named ranges

Month - Formula:

=INDEX(Table1[Month/Region],0,0)

Region - Formula:

Chart - Formula:

=IF((Sheet1!\$C\$16="")*(Sheet1!\$C\$17=""), 0, IF(Sheet1!\$C\$16="", OFFSET(Table1, MATCH(Sheet1!\$C\$17, Table1[Month/Region], 0)-1, 1, 1, COUNTA(Table1[#Headers])-1), INDEX(Table1, 0, MATCH(Sheet1!\$C\$16, Table1[#Headers], 0))))

ChartCat - Formula:

Series - Formula:

=IF((Sheet1!\$C\$16="")*(Sheet1!\$C\$17=""), "", IF(Sheet1!\$C\$16="", INDEX(Table1[[#All], [Month/Region]], MATCH(Sheet1!\$C\$17, Table1[[#All], [Month/Region]], 0)), INDEX(Table1[#Headers], 1, MATCH(Sheet1!\$C\$16, Table1[#Headers], 0))))

Create two drop down lists

1. Select cell C16
2. Go to tab "Data"
3. Click "Data Validation" button
4. Select List
5. Type =Region in Source:
6. Click OK
7. Select cell C17
8. Go to tab "Data"
9. Click "Data Validation" button
10. Select List
11. Type =Month in source
12. Click OK

Setting up the chart

1. Create a bar chart
2. Right click on chart
3. Click "Select Data..."