Author: Oscar Cronquist Article last updated on September 29, 2018

Fatou asks:

Going back to my question, I had created a table and used the data to create a chart. However, something happened with the chart and now it is not updating when new data is added to the table.

I have multiple employee names in column A, Open and Closed Tickets for each employee in Column B and the date ranges in columns C- X. I have to create an individual chart for each employee showing Open and Closed Tickets per line chart.

I was trying to use the named ranges and that is not working. I believe using table and charts will be the best option but something has happened and now the chart is not updating. It has converted back to static where I will have to update it manually.

It is very frustrating and time consuming to have to manually update each series for each employee. Your help is greatly appreciated!!!!

Thank you so much.

Here is the sample data

Column A - Employee Name
Row A2 - Employee1
Row A3 - Employee1
Column B - Activity
Row B2 - Open Activity
Row B3 - Close Activity
Column C:X - Date Range (Jan 2010-Oct 2011) with additional months added
Rows C2:X3 - # of tickets for each activity
Row A4 - Employee2
Row A5 - Employee2
Column B - Activity
Row B4 - Open Activity
Row B5 - Close Activity
Rows C4:X5 - # of tickets for each activity

There are about 8 employees I am tracking.

The data was arranged in one table and I was trying to create a chart for each employee such that when a new month is added, I will not have to manually go in and update the chart but it will automatically update with the new month data for each employee. am creating a line chart to show each activity per employee and adding trend lines.

Thank you so much for your assistance.



You need to set up two named ranges for each employee and one named range for the x axis.

Setting up named ranges

    1.  Go to "Formulas" tab
    2. Click "Name Manager" button
    3. Click "New" button
    4. Type following formula:
      =OFFSET(Sheet1!$C$2, 0, 0, 1, COUNTA(Sheet1!$1:$1)-2)
    5. Click OK!
    6. Click "New" button
    7. Type following formula:
      =OFFSET(Sheet1!$C$3, 0, 0, 1, COUNTA(Sheet1!$1:$1)-2)
    8. Click OK!
    9. Click "New" button
    10. Type following formula:
      =OFFSET(Sheet1!$C$1, 0, 0, 1, COUNTA(Sheet1!$1:$1)-2)
    11. Click OK!

Chart settings

  1. Go to "Insert" tab
  2. Click "Line" chart button
  3. Click "Line" button
  4. Right click on chart
  5. Click "Select data..."
  6. Click "Add" button
  7. Click "Select Range" button
  8. Select A2:B2
  9. Click in "Series values:" field
  10. Type: =Sheet1!Employee1O
  11. Click OK button
  12. Click "Add" button
  13. Click "Select Range" button and select cell range A3:B3
  14. Click in "Series values:" field and type: =Sheet1!Employee1C
  15. Click OK
  16. Click "Edit" button
  17. Type in "Axis label range" field: =Sheet1!xaxis
  18. Click OK!

Your first chart out of eight is done!

Download Excel *.xls file