Article updated on January 26, 2018

This post demonstrates how to automatically add new values to a drop down list and a chart.

This tutorial contains three steps:

  • Create two named ranges
  • Create a drop down list
  • Set up a chart

Create two named ranges

  1. Click Formulas tab on the ribbon
  2. Click Name Manager button
  3. Click New...
  4. Name: xaxis
  5. Refers to:
    =OFFSET(Sheet1!$A$1, 1, 0, COUNTA(Sheet1!$A:$A))
  6. Click OK button
  7. Click New...
  8. Name: rng
  9. Refers to:
    =OFFSET(Sheet1!$A$1, 1, MATCH(Sheet1!$D$12, Sheet1!$B$1:$IV$1, 0), COUNTA(Sheet1!$A:$A))
  10. Click Close

Create a drop down list

  1. Select cell D12
  2. Click Data tab on the ribbon
  3. Click Data Validation button
  4. Click Settings tab
  5. Allow: List
  6. Source:
    =$B$1:INDEX($1:$1, 1, COUNTA($1:$1)+1)
  7. Click OK button

Set up a chart

  1. Click Insert tab on the ribbon
  2. Click Column chart button and select a clustered column chart
  3. Click Design tab on the ribbon
  4. Click Select data button
  5. Click Edit button in Legend Entries (Series) field
  6. Series name: =Sheet1!$D$12
  7. Series values: =chartandlist.xlsx!rng


  8. Click OK button
  9. Click Edit button in Horizontal (Category) Axis Labels field
  10. Axis Label range: =chartandlist.xlsx!xaxis

  11. Click OK button
  12. Click OK button again

Add values to table

New row headers and data values are instantly displayed in chart.  New column headers are automatically added drop down list.

Download excel file

(Excel 2007-2010 Workbook *.xlsx)

Recommended blog posts:
Excel 2007: How to create a dynamic chart
Create a dynamic named range