Excel 2007: How to create a dynamic chart
Question: How do I create a chart that dynamically updates the values, as i type them in the workbook?
Answer:
Table of contents
Excel 2003 - Dynamic chart
Let´s say you created this basic chart. Now you need to setup named ranges, they expand automatically whenever new data is added.
Create two named ranges
- Go to ribbon "Formulas"
- Click "Define name"
- Name it "Months"
- Type "=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A))" in "Refers to:"
- Click ok!
- Click "Define name"
- Name it "Numbers"
- Type "=OFFSET(Sheet1!$B$1,0,0,COUNTA(Sheet1!$B:$B)) " in "Refers to:"
- Click ok!
Setting up the chart
- Right click on a chart bar
- Click "Select Data..."
- Click Series1
- Click Edit
- Change "Series values:" to Sheet1!Numbers
- Click OK!
- Click Edit button in Horizontal (Category) Axis Labels
- Click OK!
Now, type another month and a number. Look at the chart, it expands automatically.
Add more dynamic series to the chart
Let´s say you want to add a new series
- Create a third named range Numbers1 (See instructions above)
- Right click on chart
- Click "Select Data..."
- Click "Add"
- Type in "Series values:" Sheet1!Numbers1
- Click OK!
- Click OK!
Excel 2007 - Dynamic chart
Create a table
- Select A1:B3
- Click "Insert" tab
- Click "Table" button
- Click OK!
Create a chart
- Select table
- Click "Insert" tab
- Click "Column chart" button
It is now possible to add more rows and columns to the table. The chart is instantly updated!
Download excel sample file for this tutorial.
Dynamic chart.xlsx
(Excel 2007 Workbook *.xlsx)
Functions in this article:
OFFSET(reference, rows, cols, [height], [width])
Returns a reference to a range that is a given number of rows and columns from a given reference
COUNTA(value1, [value2], )
Counts the number of cells in a range that are not empty
Welcome! I am Oscar and here to help you! Feel free to comment and ask excel questions.Don´t forget to add my
February 11th, 2010 at 12:12 pm
Thanks a lot for this illustrative and easy way to do a dynamic chart. Really helped me a lot!
February 26th, 2010 at 1:42 pm
Hi
This is great if your data (rows or columns) is increasing in size. However, doesn't seem to work if you reduce the number of columns, the graph still displays blanks for the 'missing' data
Can you help?
August 2nd, 2010 at 5:33 pm
Excel seems to not let me enter the new series equation in the formula bar. I can type it, but pressing enter or the check mark doesn't do anything. If I do it from "Select chart data", it determines the initial state and then uses that cell reference from then on.
For example, "=SERIES(,O!OpDescriptions,O!CycleTimeValues,1)" would become "=SERIES(,O!$C$2:$C$12,O!$E$2:$E$12,1)" after initial entry. Any ideas?
August 3rd, 2010 at 1:27 pm
erik,
change it manually via "Select Chart Data", series by series.
changing it via formula bar wont work (testd on Excel 2007).
October 3rd, 2010 at 12:40 pm
The reason cutting and pasting doesn't work is that his examples have commas, and instead it should be semicolon (;). In the description of the functions, in fact, he uses semicolons. If you substitute commas with semicolons it works. Thank you for this tutorial!
October 3rd, 2010 at 9:38 pm
giulia tonelli,
It has to do with regional settings. I use ; but most of my readers are americans and they use ,. That is why I use , on this blog.
February 18th, 2011 at 4:05 pm
I know david's post was a while back, but I really appreciate your post. I have the same problem Erik listed. I've spent hours trying to resolve - updates, etc. Using "Select Data" worked. Thanks for the article and allowing the comments.
February 19th, 2011 at 8:13 am
Marvin,
You are welcome!
February 25th, 2011 at 6:29 pm
If you have your data in a table, you don't need to use the OFFSET function or defined name ranges (Excel 2007 only)
March 10th, 2011 at 8:57 pm
How do you edit the data if you have multiple series? I do not want to edit each one manually.
April 15th, 2011 at 1:48 pm
if there are two or more blank rows? as shown the following
janurary 5
feburary 4
march 6
apr 2
july 7
aug 8
April 16th, 2011 at 9:22 pm
zhen qin,
I am not sure what you are trying to accomplish. If you want two or more blank nonexisting bars in your chart, try changing the named ranges to:
Named Ranges
Month: =OFFSET(Sheet1!$A$1, 0, 0, MATCH(9,9999E+307, Sheet1!$B:$B))
Numbers: =OFFSET(Sheet1!$B$1, 0, 0, MATCH(9,9999E+307, Sheet1!$B:$B))
May 18th, 2011 at 11:50 pm
Where exactly do i find the chart bar. I am working in EXCEL 2007 and I don't see that.
May 19th, 2011 at 7:06 am
Keith,
Thanks for asking, I have added pictures to this post.
July 11th, 2011 at 10:37 pm
This was a big help.
Thanks!!
tina
July 12th, 2011 at 9:16 pm
Tina,
I appreciate your comment!
Thanks!
July 14th, 2011 at 10:21 am
Help! What if my data range includes a set of "IF" formulae that sit blank until other datasets are completed elsewhere, (i.e. i have 20 rows, but currently only 5 have data, but the others are formulaed and the data will appear as data is added elsewhere). It appears that the dynamic graph still considers them as values and includes them?!
(btw, otherwise thanks for this - really useful!)
July 14th, 2011 at 3:01 pm
James,
Named range Month
Named range Number
July 14th, 2011 at 3:16 pm
You, my friend, are a legend. Many thanks!
August 2nd, 2011 at 5:58 pm
Thanks a ton !
August 3rd, 2011 at 9:33 am
James and Neha,
You are most welcome!
August 3rd, 2011 at 11:35 pm
Oscar,
I am trying to implement your response to James's question as I am working on the exact same problem. When I use the below;
=OFFSET(Sheet1!$B$1,0,0,SUM(--(Sheet1!$B$1:$B$20"")))
I get an error. "A formula in this worksheet contains an one or more invalid references"
Your insights are greatly appreciated
Thanks
CM
August 4th, 2011 at 9:30 am
CM,
Maybe this example file is helpful:
Dynamic-chart-IF.xlsx
Excel 2007 *.xlsx
August 5th, 2011 at 3:33 am
Oscar,
Thanks for the prompt response. I should have mentioned that I am trying to create a line chart. And if I have a month populated but not the number the line drops to the x axis which I am trying to avoid. This is a multi series chart. Any thoughts?
Thanks
CM
August 5th, 2011 at 8:36 am
CM,
And if I have a month populated but not the number the line drops to the x axis which I am trying to avoid.
Strange, the line doesn´t drop here. What formula is shown when you click on a chart line?
August 19th, 2011 at 4:39 pm
Hi
Thanks for this, it's just what I needed, however I need to take it a step further.
If looking at your example, my 'Numbers' are created from an If Statement. entering "" for blank cells. Excel doesn't seem to recognise this as an empty cell and thus plots it on the graph anyway.
Do you know of a way I can use get the chart to exclude these items?
Many thanks in advance
MK
August 22nd, 2011 at 12:49 pm
MK,
Create a table.
Click black arrow on column header.
Deselect blanks.
Download excel 2007 *.xlsx file
MK.xlsx
August 22nd, 2011 at 2:47 pm
Hi Oscar
Thank you very much. This has made my charts a thing of joy!
MK
August 29th, 2011 at 10:06 pm
Hello Oscar,
Using your example above, how would the "define name" functions/formulas change if I wanted to remove one data point for every one I add? (i.e. right after I add "April" to the data series, not only would I like "April" to automatically be added to the graph but also for "January" to be removed).
Thanks for your time!!!
August 30th, 2011 at 1:52 pm
Philip,
Named Range - Month
Named Range - Numbers
Download excel *.xlsx file
Dynamic-chart-dynamic-range.xlsx
September 10th, 2011 at 2:27 pm
Dear Oscar,
Many thanks for the interesting site!
Please could you advise how to apply this dynamic formating to include more data for example:-
Expenses January Febuary March
Petrol 45 60 50
Cellphone 25 30 25
Hotel 65 75 50
Thank you in advance for any assistance you may be able to offer!
Kind regards,
J13
September 12th, 2011 at 2:19 pm
Jungleist13,
Example, excel table and a bar chart.
What excel version are you using and what chart?
September 12th, 2011 at 5:51 pm
Dear Oscar,
Many thanks for your swift response!
Excel 2007 not really bothered about type of chart, column is sufficient I just need to understand haw to apply the dynamic features to it? I can,t figure out how to alter the formating to cover the additional series?
Kind regards,
Jungleist13
September 16th, 2011 at 11:34 pm
Hi -- I am trying to build a line graph that needs 2 series displayed dynamically. How would i go about building that? I underrstand how to do it for one series, but cannot figure out for 2.
Thanks.
Katy
September 18th, 2011 at 6:49 am
Katy,
Is this what you had in mind?
September 26th, 2011 at 9:31 pm
yes -- where the total number of records being plotted can vary
September 28th, 2011 at 3:42 pm
This is great! I was able to make it work. I do have a concern and a question on this issue that you may be able to help me with. 1) When adding a new date/month to the bottom of your list, the x-axis updates in the graph with the prior data remaining in place. This could lead to an incorrect graph if the data is also not updated. Shouldn't the data also shift over to the left so the added date/month shows blank data in the graph? If so, how? 2) How would this be adjusted for a table where the dates are already pre-populated, only adding in the data each day/week/month?
September 28th, 2011 at 3:44 pm
Clarification - 2) How would this be adjusted for a table where the dates are already pre-populated, only adding in the data each day/week/month?
In this instance, the data table is pre-populated with dates. The graph only shows the most recent 13 entries, rolling each week/month as data is added. To clarify, not all dates are showing in the graph at one time - only the most recent 13.
September 28th, 2011 at 3:47 pm
Eureka! I figured it out - I changed my offset formula for the "months" to reference the month row but count the data row. This results in the table updating the date and data only once the data has been added!!!
September 28th, 2011 at 3:53 pm
Oscar, Thank you SOOOO much. I cannot tell you how much time this will save me on my weekly/monthly reporting! I've been mulling this over for quite some time, not knowing how to do it. You've been a godsend!
September 30th, 2011 at 12:43 pm
Oscar, in line with the questions above, I'm looking to add further columns of data for each month, e.g. Months, Numbers1, Numbers2 etc.
How do you adjust the "=SERIES(,Book3.xlsx!Months,Book3.xlsx!Numbers,1)" formula to include third, fourth and fifth columns?
September 30th, 2011 at 2:41 pm
Question on the NAMES and "SERIES" formulas. Should the NAMES be saved in the SCOPE of "workbook" or the specific worksheet and does that impact the "SERIES" formula? Always use the workbook name or worksheet name instead, depending on how the NAMES were set up? Please advise.
September 30th, 2011 at 8:35 pm
Katy,
Download workbook *.xlsx
katy.xlsx
Instructions excel 2007
1. Create a table
2. Go to "Insert" tab
3. Click "Line charts" button
October 7th, 2011 at 3:36 am
New question - I've used these formulas successfully now on a number of graphs. Now, my data is designed horizontally, not vertically and I'm having difficulty converting the formula accordingly. I've tried using "COUNTA(sheet1!$B$2:$RXM$2)" and also changing the last two numbers from "0,13" to "13,0" to no avail. i.e. Months run across row 1 with data in rows underneath with row headers in column A. Please advise.
October 7th, 2011 at 6:05 pm
Another new question - I have difficulty each time I wish to automate a graph with multiple series even thought I have changed the final number in the series (i.e. 1,2,3, etc.) I have charts with stacked columns, and others with two columns with a third series line. When I attempt to change the series information on the graph, it doesn't accept it - no error, nothing, I hit enter to submit it and it just sits there and doesn't change it. Also, is there a limit to the number of names used within a single workbook? Please advise.
October 8th, 2011 at 9:09 pm
Max,
Oscar, in line with the questions above, I'm looking to add further columns of data for each month, e.g. Months, Numbers1, Numbers2 etc.
How do you adjust the "=SERIES(,Book3.xlsx!Months,Book3.xlsx!Numbers,1)" formula to include third, fourth and fifth columns?
Excel 2007:
Create a table
1. Select cell range
2. Click "Insert" tab
3. Click "Table" button
Create a clustered column chart
1. Select table
2. Click "Insert" tab
3. Click "Column" in charts window on the ribbon.
4. Click "Clustered Column Chart"
October 10th, 2011 at 6:41 am
Oscar,
I have used your suggestions above which work as expected, but I have a slight twist on what I am trying to do...
So, firstly I am trying to create a dynamic chart with daily data being added to a column with a weekly total in the eighth row.
I am trying to chart the weekly totals only (ie every eighth row). I seem to be able to do either:
1. Chart Dynamic data
2. Chart every eighth row
But not both together
Any suggestions? Any advice appreciated.
October 11th, 2011 at 9:29 pm
Missy,
Question on the NAMES and "SERIES" formulas. Should the NAMES be saved in the SCOPE of "workbook" or the specific worksheet and does that impact the "SERIES" formula? Always use the workbook name or worksheet name instead, depending on how the NAMES were set up? Please advise.
The names are Named Ranges and does NOT impact the "SERIES" formula. You have to use the full name: excelfile!Named_range in the series forumla.
Example:
Book3.xlsx!Month
Excel 2007 and 2010 users can happily create a table and then create a chart. Nothing to worry about series formulas or named ranges. And yes, I know, the blog post title says Excel 2007:How to create a dynamic chart..
October 14th, 2011 at 4:06 pm
Oscar, can you please help me modify the Name Ranges and SERIES formula for a table where the months (to use your example) are set up horizontally instead of vertically? I am using historical tables that go back five years or so. Changing their layout is not prudent. What I have is column headers with mmm-yy, with multiple row labels of varying categories. I have made attempts by changing the range from columns $A:$A, for example, to rows $A$2:$XX$2, to no avail. Please advise.
October 17th, 2011 at 8:44 am
Missy,
see this attached file:Missy.xlsx
October 17th, 2011 at 12:37 pm
Thank you Oscar. I shall try this and let you know how it turns out.
October 20th, 2011 at 1:01 am
Oscar,
Thank you so so much for providing this useful information - your site has the best instructions yet.
However, I do not understand your explanation to Max's question and I have the same question: How do you adjust the "=SERIES(,Book3.xlsx!Months,Book3.xlsx!Numbers,1)" formula to include third, fourth and fifth columns?
Can you please be a little more clear and provide more formulas? Dynamic charts are very new to me and I do not understand it fully yet.
October 21st, 2011 at 10:19 am
Jennifer,
You don´t need to adjust the formula to include a third, fourth and a fifth column.
Convert your cell range into a table and then create a chart.
In excel 2007 and later versions, a table automatically adds new columns or rows and the chart is instantly updated.
October 24th, 2011 at 12:13 am
I really like the idea of the dynamic chart, but it doesn't seem to work for me. I have tried changing the source data for the chart to use dynamic named ranges. However, it immediately extracts the range the name references and uses that instead of the keeping the same. What I mean is that, if the current state of the dynamically named range refers to B2:B6, then the chart sets the source data range to B2:B6 and keeps it at that setting, even when the dynamically named range changes. When I look at the source data selection again, it refers to whatever the initial state of the named range was, rather than the named range itself. Am I doing this wrong?
Thanks for any help you can offer.
October 25th, 2011 at 9:42 am
Paul,
Maybe you have workbook calculation: Manual?
Change it to automatic.
October 25th, 2011 at 5:31 pm
Thanks Oscar, but I think figured it out. I was putting the named range into the slot that pops up with you right click on the graph and shoose "select data." The box says "Chart data range" on it. When I used that box, it always converted the named range into an absolute reference. However, when I went to edit the series (there is only one in my graph), and put the named range in the "Series values" box, it stayed as a named range and my chart updates automatically. I'm not sure why the "chart data range" box would convert a named range to an absolute reference but the "series values" box would not, but that appears to be the situation.
November 22nd, 2011 at 9:02 pm
Thank you so much for the info on the charts.
Going back to the Katy chart example, can you convert a regular chart to one that pulls data from a table so that the data will automatically update when new lines/colums are added.
Thanks.
November 25th, 2011 at 10:14 am
Fatou,
I have simplified this post. I believe it now answers your question.
Thanks for commenting!
November 25th, 2011 at 3:47 pm
i would like my graph to show only the most recent 12 months data. What should i do?
November 27th, 2011 at 2:58 pm
Icjun:
You can have your named range find the last n rows or columns in your data set. Here is an example of some code I used to define a named range:
=OFFSET(Data!$H$43,0,COUNTA(Data!$H$42:$ZZ$42)-2,1,2)
This is based on the idea that there is a 2-row set of data that begins at H42. The values I really want are the last two numbers in row 43. My data set sometimes has holes, so instead of counting the number of items in row 43, I count the header row (42), which I know will be filled in completely, but only up until the last month I want to use. I create a named range using the code above, and then edit a series in my graph to refer to that named range. Now, since you are dealing with the latest 12 months, you might want to have "-12" instead of "-2" after the COUNTA function.
November 29th, 2011 at 12:49 pm
Hi, thanks for providing this information - it's been a great help.
However, I am still struggling to apply the logic to my solution. My data range is made up of: Products in Column A, Months in columns B to M, values (per month per product) in the cross sections.
The Products dimesnion is the one that can grow (or shrink) in my data range. Therefore I want my dynamic chart to show whatever products appear in the data dump
I have defined products as a name. Upon testing, whenever I add a new product it appears as a series in my chart.
However, I am not sure how to display the months (and the values per month, per product) on the chart. In teh "Select data source" tab/Horizontal axix labels - I have manually selected B1:M1. However, only one month is appearing on the X axix of the chart and no data.
Any help would be much appreciated.
Many Thanks
Mike
November 30th, 2011 at 2:48 pm
Hi Oscar,
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.
Fatou
November 30th, 2011 at 4:50 pm
Hi Fatou,
I was having the same problem with the chart converting named ranges to static references. My solution was to use a different named range for each series. Then, when I go to select data, I pick the particular series, edit the data for that, and enter the named range at that location. That seems to work much better than having a named range for the entire data set. The only problem is that it won't work if the number of series in your chart changes (in that case you would have to update the chart manually by adding or removing series).
December 1st, 2011 at 1:43 pm
icjun,
Here are some great resources:
Chart the Last 12 Months Dynamically.
Using named ranges to create dynamic charts in Excel
December 1st, 2011 at 2:09 pm
Mike,
Hi, thanks for providing this information - it's been a great help.
However, I am still struggling to apply the logic to my solution. My data range is made up of: Products in Column A, Months in columns B to M, values (per month per product) in the cross sections.
The Products dimesnion is the one that can grow (or shrink) in my data range. Therefore I want my dynamic chart to show whatever products appear in the data dump
I have defined products as a name. Upon testing, whenever I add a new product it appears as a series in my chart.
However, I am not sure how to display the months (and the values per month, per product) on the chart. In teh "Select data source" tab/Horizontal axix labels - I have manually selected B1:M1. However, only one month is appearing on the X axix of the chart and no data.
Any help would be much appreciated.
Many Thanks
Excel 2007 instructions
Convert data range to a table
1. Select your range (A1:M5)
2. Go to tab "Insert"
3. Click Table button
4. Click OK
Change chart data range
1. Right click on your chart
2. Click "Select data..."
3. Type =Table1 in chart data range:
Chart settings
1. Right click on chart
2. Click "Select data..."
3. Click "Switch column/row" button
4. Click Edit button in Horizontal (Category) Axis Labels window
5. Select column headers (B1:M1)
6. Click OK
7. Click OK
Download excel file
Sheet1 changes chart data range using vba. Right click on sheet1 and click view code.
Sheet2 changes chart data range using tables (no vba).
dynamic-charts-vba.xlsm
December 3rd, 2011 at 2:32 pm
Hi Oscar,
Very good and extensive instructions. Thanks alot!
December 5th, 2011 at 10:31 am
Fatou,
Can you provide some sample data?
December 5th, 2011 at 4:16 pm
Hi Oscar,
Thank you for responding back. 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
December 7th, 2011 at 3:39 pm
Fatou,
When I convert a cell range to a table, it automatically adds new values and the chart is immediately updated. It works here.
What is your excel version?
Did you change chart data range to the table? (See instructions above in my comment to Mike)
You can select an employee using the table filters and the chart is instantly updated.
December 7th, 2011 at 6:52 pm
Oscar,
Thanks for reviewing my question.
My Excel version is 2007.
Maybe my description is not very clear.
I changed the chart data range to the table and it shows me all the information in the table in one chart. However, I need to show each employee in a separate chart and have that chart update when a new month is added. In essence, I will have 8 charts updated when Nov data is added. I am using the one table to showcase all the employees. Do I need to have I need to have 8 tables for each chart in order for this to work.
Thanks!
December 9th, 2011 at 12:33 pm
Fatou,
read this: Excel charts: Multiple series and named ranges
December 9th, 2011 at 7:33 pm
Thank you so very much! It works now. I am not sure what had happened before and 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.
Fatou
December 20th, 2011 at 8:28 pm
Oscar,
Using the Missy.xlsx example above you provided on October 17th, 2011 at 8:44 am, I'd like to know how to fix the problem when you have no data to enter for month. For example, using your missy.xlsx file, I added april and may with a value of 4 and 5 respectively. I then deleted the number 3 value under March. When I do that, the chart messes up and doesn't include May. In fact, if you add new months, the chart always leaves off the most recent mont added if you have no value in a cell. It gets worse when you have multiple months with no values. I don't want to use zero as a value since I'm using error bars, which indicate error by 1 standard deviation. If I use zero as a value, the error bars adjust to reflect the zero value. In some months I just won't have data and I need the graph so simply reflect that without dropping off the most recent month or more.
December 21st, 2011 at 1:56 pm
Replying to Seth, because the formula is COUNTING non-blank cells, your theory is throwing off the count. I resolved the same issue using a "-" in an empty cell. It is counted because it is no longer blank, but it has no graphable value. Let me know if this helps.
December 21st, 2011 at 2:24 pm
Missy - Your suggestion fixes only one of my two problems. Yes, it does keep the count going so all months show up on the graph. However, I am using error bars on my graph, which places vertical lines representing 1 standard deviation. This is for my employees to see if their monthly value falls into an exceptable range. Unfortunately, even "--" or "-" or "'--" as a monthly entry makes affects the vertical error bars. The computer thinks the entry is equal to zero and therefore, the standard deviation widens allowing for more error, which is actually incorrect.
December 21st, 2011 at 2:27 pm
Seth, unfortunately I do not have experience using the error bars. However, might I suggest searching the Internet for entries on how to exclude zero value cells from your graphs. I bet you can find something there.
December 21st, 2011 at 2:29 pm
I've been looking and will continue to search. I bet there is something out there too. Thanks for your replies.
December 21st, 2011 at 3:15 pm
I believe I found the answer. Be entering "=NA()", excluding the quotation marks, for any month with no data, it counts the cell and it is not seen as a zero value. Works well!
January 2nd, 2012 at 10:37 am
Seth,
Named range Months formula:
Named range Values formula:
Download excel *.xlsx file
Seth.xlsx
January 3rd, 2012 at 8:14 pm
Hi Oscar,
I am using excel 2007.
I have created a dynamic chart for applications/ issue category.
I have 5 teams working on different applications. One team is having more than 1 application.
So how can I make one template, which can be used for all the teams. Also all the issues are not reported in a month so how to exclude those while creating chart.
January 4th, 2012 at 10:28 am
I am using Excel 2011 for Mac
I have data with alpha headers in A1:P1
Numeric data builds up in rows 2 to 16.
Headers and data are ALL formula derived from other sheets
Converting the data to a table and creating a chart works with 2 issues
1 - conversion to a table changes the alpha headers from formula to text after a warning
2 - the chart data is NOT dynamic and displays for all numeric data cells whether a value or a null
So is there a way to get around to make the chart dynamic where formula derived nulls are ignored?
Or do I have to create named ranges as shown by Oscar in his reply to James (July 14th 2011). If so do I have to define a series for each column, does the chart source data include the header row?
Finally an Excel complaint(!) the boxes to enter formulae when defining a range or chart source data do not expand as you type so it can be awkward to revisit or to find an error
January 6th, 2012 at 2:20 pm
Deep Singh,
Read my answer to seth: http://www.get-digital-help.com/2007/11/03/excel-2007-how-to-create-a-dynamic-chart/comment-page-2/#comment-42350
Can you describe how the template should look like in greater detail?
January 6th, 2012 at 2:22 pm
Paul,
1, I don´t have an answer to you headers problem.
2, You can´t use multicell formulas in a table, convert them into single cell formulas.
You could check if values are empty, something like: =IF(table[ALL]<>"", table[ALL], "")
January 7th, 2012 at 8:32 am
1. For now I could live with this but take your advice to check with the Office for Mac forum to see if anyone else has tried successfully the same approach to charting
2. Changing the data formula in the data cells to check for nulls in the source does stop the line chart plunging to nothing but the chart's X-axis still extends to the full extent of the table.
=IF(SHEET1!E2='"",NA(),SHEET1!E2)
Unless someone comes up with a solution I will next try a non table approach with a series for each column with a Named Range for the X-axis, somewhere else in my workbook is a count of the number of row entries
February 3rd, 2012 at 4:51 pm
Hi, I'm back! I've had great success with these formulas but now I have a new challenge for you. I'm trying to automate reporting as much as possible. This includes prepopulating cells in dependent workbooks with formulas referencing the master workbook, which will be updated with new data. How can I make a dynamic chart in these dependent worksbooks when the cells are already pre-populated? I've tried using date matching to no avail and have thought of adding a new row/column where I'd place a marker to satisfy the "count" but that's not ideal either - I want to not have to update the dependent workbook at all. Any ideas?
February 5th, 2012 at 4:39 pm
Missy,
Can you describe your workbooks in greater detail?
How can I make a dynamic chart in these dependent worksbooks when the cells are already pre-populated?
What do you mean by pre-populated?
February 5th, 2012 at 10:35 pm
Sure. What I mean is that the MASTER workbook is already set up with row labels for each month of the current year. The data columns for these rows are blank, with new data to be entered each month. The DEPENDENT workbook is set up with the same row labels for each month but the data columns are set up with formulas to read or calculate the currently blank cells in the MASTER workbook. This way, I don't have to update the DEPENDENT workbook each month - the formulas are already there waiting and the value will update once the data is entered in the MASTER workbook. If I am graphing from the DEPENDENT workbook, I cannot COUNT the non-blank cells, because there are none - the cells are populated with formulas. My temporary fix is to add a new column with an "X" value to identify the most recent graphing month and the COUNT would work on this column. Ideally, I don't want to have this "X" column or have to update the workbook at all.
February 6th, 2012 at 5:21 am
Hi Paul,
Re:"Finally an Excel complaint(!) the boxes to enter formulae when defining a range or chart source data do not expand as you type so it can be awkward to revisit or to find an error..."
Yes it does...
On the left of the formula bar you'll notice the "fx" sign, and on the right side of the formula bar you'll see a box with an arrow down...
Click on the arrow and drag the lower portion of the formula bar to the desired length...
2. ... but the chart's X-axis still extends to the full extent of the table.
mean to say your #N/A are not plotted, but the range in x are fixed to a value that is not present in your data? Is that correct? is your range set on manual or automatic? if auto it should resize with the new range...
I wish I could be of more assistance but I do not understand fully what problem you encountered...
can you share a sample of your file?
Cheers
Cyril.
February 6th, 2012 at 11:37 am
I would willingly send a sample file if I knew how to do it! Please let me know, feeble excuse is that I am new to this sort of thing!
February 7th, 2012 at 3:02 am
Paul you can use an external page such as (http://uploading.com/) but I suggest that you directly ask Oscar since this is is web page, I am quite sure he has all the answers you may ask, mine are quite limited... I am however quite interested in your query since you encounter some problems on excel2011.
I tried the create a file following your descriptions and so far it works as Oscar explained, but you might be referring to something different. (I am using v14.1.4).
February 7th, 2012 at 9:38 am
How do I contact Oscar? The link at the top of the post tries to access something that is not there.
My version of Excel for Mac 2011 is also v14.1.4
February 7th, 2012 at 11:08 am
Paul, upload a sample to the link I mentioned, i'll have a look since i am using mac, just let us know the name of the file... As I said Oscar is the authority here but since what is described here works with me, and since it might be a mac related problem... awaiting your feedback.
February 7th, 2012 at 1:23 pm
Missy,
Check out the attached file:
Missy1.xlsx
The chart uses a dynamic named range (Rng). Column B have formulas and they get their values from column A.
February 7th, 2012 at 1:33 pm
Cyril
file W_L test.xlsx should be available at uploading.com
Paul
February 8th, 2012 at 1:13 am
Paul, kindly give the file links it should look like this:
http://uploading.com/files/m6aa3fb3/W_L test.xlsx/
go to upload web site, login, click on your file, click on the "share" button and copy the "File Links"
sorry for the delay...
February 8th, 2012 at 3:45 pm
Having trouble signing on to Uploading site. Will get back to you ASAP
February 10th, 2012 at 6:17 pm
Cyril
Oscar has provided a solution. Simply when I defined the chart "data source" range I included empty row cells. I did not realise that the data source range expands as data is entered.
Have not heard from Uploading support as to why I cannot login
Thanks for your patience, it was/is appreciated
February 11th, 2012 at 2:16 am
Paul,
Glad you found out.
Cheers.