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
  4. Add values

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

How I made this worksheet

I created five named ranges.

Named ranges

Month - Formula:

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

Region - Formula:

=INDEX(Table1[#Headers],0,0)

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:

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

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..."
  4. Click "Add" button
  5. Type =Sheet1!Series in Series name:
  6. Type =Sheet1!Chart in Series values:
  7. Click Ok
  8. Click "Edit" button
  9. Type =Sheet1!ChartCat in Axis label range:
  10. Click ok
  11. Click ok

 Download excel *.xlsx file

Dynamic chart - Display any row or column.xlsx