Author: Oscar Cronquist Article last updated on March 01, 2019

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.

Fatou

Answer:

I recommend that you rearrange your data, that way you don't need to set up complicated dynamic named ranges. You can use an Excel defined Table, it is dynamic by default.

To rearrange data simply select data, in the image above it is cell range B2:H6. Copy the cell range, you can do that quickly by using the shortcut keys CTRL + c.

Now right-click on the destination cell, then click on "Paste Special...".

Click on checkbox Transpose to enable it.

Click on OK button. To convert the transposed range into an Excel defined Table simply select any cell within the dataset.

Press short cut keys CTRL + T, a dialog box appears.

Click check box "My table has headers" then click "OK" button. The dataset now looks like this:

Click any cell within the Excel defined Table. Go to tab "Insert" on the ribbon. Click "Line chart" button.

The chart appears. When you add new records to the Excel defined Table it automatically expands and the chart grows accordingly as well.

Download Excel file


Fatou1-1.xlsx

Related articles

How to create a dynamic chart

Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: [โ€ฆ]

How to align chart with cell grid

This trick is so simple and also an incredible time-saver if you want to build beautiful worksheets or dashboards where [โ€ฆ]

Four ways to resize a chart

To be able to resize a chart you must first select it, you do that by left-click on the chart with [โ€ฆ]

How to add lines between stacked columns/bars [Excel charts]

The image above shows lines between each colored column, here is how to add them automatically to your chart. Select [โ€ฆ]