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.
Weekly Blog EMAIL
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
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 […]
To be able to resize a chart you must first select it, you do that by press with left mouse button […]
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 […]
Use slicers to quickly filter chart data
Slicers let you control data displayed in a chart, simply press with left mouse button on a button to quickly […]
Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]
How to use mouse hover on a worksheet [VBA]
I recently discovered an interesting technique about using a user defined function in a HYPERLINK function. Jordan at the Option […]
Change chart data range using a Drop Down List [VBA]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
Color chart columns based on cell color
This article demonstrates macros that automatically changes the chart bar colors based on the corresponding cell, the first example is […]
How to create an interactive Excel chart [VBA]
This article describes how to create an interactive chart, the user may press with left mouse button on a button […]
13 Responses to “Rearrange data source in order to create a dynamic chart”
Leave a Reply
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.
Thank you so very much! It works now. I am not sure what had happened with the data but it took a couple of tries before I got it to work. You have been a tremendous help and a life saver. This will definitely cut down drastically the amount of time it will take to update my reports. I appreciate it. Merci Beaucoup.
Hi Oscar,
I have another question regarding charting the Last 12 months dynamically. Using the same example above, how can I chart the last 12 months when the data is laid out with Months on the columns and the datavalues are the rows.
I got it to work using the link you had for charting the Last 12 Months but my data is laid out differently.
Your help is greatly appreciated. Thank you.
Fatou.
Fatou,
Check out the attached file:
Fatou2.xlsx
Hi oscar,
I am quite new to excel for my project i wanted to create dynamic chart, and i am able to render the chart in excel sheet, using the name range and offset formulas , but i am stuck with one problem , i wanted to show two named series on axis label like this : =temp!$C$64:$D$88 for this i am writing named range =temp!nameRange1:temp!nameRange2 but its showing Error for me . please help !!
Thanks
malay,
Can you provide an example workbook?
Upload here
Oscar,
Thank you! It works. Another thing I did which makes it easier to update the names is to use the name definition in the data values. For example, instead of copying the same Offset formula for each data value like below,
xaxis =OFFSET(Sheet1!$C$1,0,COUNTA(Sheet1!$1:$1)-14,1,12)
EmoloyeeC =OFFSET(Sheet1!$C$3,0,COUNTA(Sheet1!$1:$1)-14,1,12)
EmployeeO =OFFSET(Sheet1!$C$2,0,COUNTA(Sheet1!$1:$1)-14,1,12)
I did this
xaxis =OFFSET(Sheet1!$C$1,0,COUNTA(Sheet1!$1:$1)-14,1,12)
EmployeeC =OFFSET(xaxis,3,0)
EmployeeO =OFFSET(xaxis,2,0)
It saves me time especially when updating a whole bunch of name ranges.
Thanks again for your invaluable help.
Fatou
Fatou,
thanks for your time saving techniques!
Hi Oscar,
Thanks for a very good example above.
I am new to excel, your solution works if the x-axis has dynamic ranges. In addition to having dynamic x-axis i have n no. of employees how would i approach it? Your comments will be of great help.
Hi all, thanks for the great thread & informative comments, it really helps me understand these functions!
I have a slight (but interesting) variation on this exact theme. Basically I have one sheet with a list of names across Row 1, and some raw data under each name. I don't know how long that list of names will be from month to month, but it changes each month.
In the next sheet I process the raw data captured under those names*. When I chart the data, I want to chart the processed data against those names (dynamically, of course), but I don't know how many names (or x axis categories) there will be.
Then if I name a range "xaxis", I think I want it to be as follows:
=OFFSET(Sheet1!$A$1,0,0,1,COUNTA(Sheet1!$A$1:$AR$1))
This appears to work, since when I select the formula for that named range, it only selects the cells with names in, and leaves the rest unselected.
The problem is when charting, the x axis contains 0 values for all the cells in Row 1 across the whole range, even if there is no name in that cell. The reason (I think) is the formulae processing the data in Sheet1 extend wider than the list of names, to dynamically update up to a given population size. I see a possible solution to my problem: name dynamic ranges on which to implement those processing formulae, then I don't have extraneous cells with formulae in them. However, I can't for the life of me figure out how just now.
So, my current problem is the chart data is squashed over to the left of the chart, to make room for all those 0's.
Any assistance to make the chart axis display only names and no 0's would be very much appreciated. One way to rephrase would be to get the chart to ignore data cells with formulae in, sticking to those x axis categories that have a value.
* Names are copied using a formula applied to more cells in Row 1 than I will ever need, formula:
=IF(Sheet1!A1>0,Sheet1!A1,"")
PS I found a possible solution here: https://www.excelforum.com/excel-charting-and-pivots/834300-dynamic-graph-do-not-want-x-axis-to-plot-blank-or-zero-values.html
But, that site won't let me access the files so I can't see what their solution was :(
I can make an example file if it will help :)
James,
Yes, please upload an example file.
Hi
I'm trying to build multiple graphs , each graph in a different sheet and from ranges of diferent series.All of it into a macro procedure.
My database has 3 columns:Block , Brix and Tan headers.In the first column i have a text format.The second and third column i have values.I 'm trying to get a different bar graph everytime the text in column a changes.Do you have any ideas how to build it ?Let me know if you have more questions.Thank you very much for your help.
Natan
natan,
Upload an example file and I´ll see what I can do.