Rearrange data source in order to create a dynamic chart

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 press with right mouse button on on the destination cell, then press with left mouse button on "Paste Special...".

Press with mouse on checkbox Transpose to enable it.

Press with mouse 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.

Press with left mouse button on check box "My table has headers" then press with left mouse button on "OK" button. The dataset now looks like this:

Press with left mouse button on any cell within the Excel defined Table. Go to tab "Insert" on the ribbon. Press with left mouse button on "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.

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form

Rearrange data source in order to create a dynamic chart

Get the Excel file

Built-in Charts

Combo Charts

Chart elements

Chart basics

Custom charts

Sparklines

Highlight chart elements

Interactive charts

Animate

Advanced charts

Stock charts

Excel categories

One Response to “How to create a stock chart”

How to comment