Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am working on some charts for financial reporting. One of them shows some values for individual companies.

I will need to add new companies on a monthly basis, and would like the diagram to automatically create a new data series when I create a row for a new company in my Excel sheet and insert data in it.

Alternatively, I can insert 12 new empty data series beforehand, in which case I'll need to know how I can keep the empty series invisible in the legend until they contain data.

Does anyone know how to solve this?

share|improve this question
    
Question unclear as to whether you need a series added to the chart, or simply extend the existing series (add points to them). – Jon Peltier Jun 8 at 13:44

"Named (cell) ranges" are one of the secrets to success in what you want to do; a great resource already exists online: http://pubs.logicalexpressions.com/pub0009/lpmarticle.asp?id=518

share|improve this answer
    
Dead link. Also, the original question asked for added series, which named ranges cannot do, they can only extend the number of points in a series. I would downvote this answer, except it's not clear what the OP really was asking for. – Jon Peltier Jun 7 at 11:59

I have solved this problem for an Excel course taught at my Company using the following resource: http://www.techrepublic.com/blog/microsoft-office/two-ways-to-build-dynamic-charts-in-excel/ I find the solution quite tricky to set up at first, but it works like a charm and avoids all VBA and Tables! Works for old Excel versions too.

One disclaimer is that it uses column grouped column charts with groups playing the role of a "series". It goes as follows:

Create a data range that expands downwards: enter image description here

Next prepare named ranges that we will load into the chart. You create named ranges by clicking in the ribbon Formulas -> Defined Names -> Define Name. enter image description here

Define the names within Sheet, and not within the workbook - it is a good practise to separate name spaces.

Create the following three ranges:

KPI1Range =OFFSET(Sheet1!$B$2;0;0;COUNTA(Sheet1!$B$2:$B$13);1)
KPI2Range =OFFSET(Sheet1!$C$2;0;0;COUNTA(Sheet1!$C$2:$C$13);1)
LabelRange =OFFSET(Sheet1!$A$2;0;0;COUNTA(Sheet1!$A$2:$A$13);1)

Next create a grouped bar chart and select data: enter image description here

For the labels range, input the following: enter image description here etc for KPI2

Then add the horizontals similarly enter image description here

Now you can add rows to your data table with KPIs for your new Company! enter image description here

share|improve this answer
    
Sure! :) In a sec! – LudvigH Mar 5 at 20:53
    
The original question asked for added series, which named ranges cannot do, they can only extend the number of points in a series. I would have downvoted, but it's not clear what the OP really wanted. – Jon Peltier Jun 7 at 11:59
    
I agree the question is slightly vague. I wrote my answer based on my interpretation of the need he had. "I create a new row for a new company". But sure, my solution does not add new data series - which could deserve a down vote. – LudvigH Jun 7 at 12:36
    
I commented on the actual question itself about the vagueness. Your answering one possible interpretation was fine, not deserving of a downvote. – Jon Peltier Jun 8 at 13:46

If you don't mind using VBA, you can follow the protocol in Dynamic Chart Source Data on my blog. The article shows how to run some code from a worksheet change event to update the chart's source data, including both the length of each series (rows of a table) and the number of series (columns of a table).

share|improve this answer

If you store your chart data in an Excel Table, when you add a new row of data to the Table, it will automatically add the data to the associated chart. This is NOT a Pivot Chart-unlike a Pivot Chart, all the data series are maintained like a standard chart and you have complete control over customizing your chart. The only thing you need to do is make sure that your data is organized in such a way that it can be stored as a Table.

Note: This solution is for Excel 2010. It may work in earlier versions, but they did not include Table functionality.

EDIT: Based on Jon's comment I verified that it does work in Excel 2010, but I dont' have any other versions to try, so I'm not sure about Excel 2007. Also, I did try it with a bar chart, xy chart and line chart and they all seem to work the same. It also deletes series when they're deleted from the table.

share|improve this answer
    
Unfortunately the Table feature (present in 2003 under the name List) will not add additional series to a chart, it will only extend each series as the table is expanded. There is no way to automatically add or remove series from a chart. – Jon Peltier Jul 29 '12 at 16:18
    
@JonPeltier when I create a chart by selecting a datapoint within a table and choosing insert>charts>barchart, the chart actually does update based upon adding rows to the table. I'm using Excel 2010, so I'm not sure if it's something new. I'm also not sure which chart types it works with-other than bars (I use those most often). Please give it a try and see if it works for you. – dav Jul 30 '12 at 11:19
    
Using a table allows more points to be added to each series when rows are added to the table. More series are not added to the chart when columns are added to the table. The OP wanted a dynamic number of series (columns). – Jon Peltier Nov 27 '12 at 19:57
    
Actually, if you set up the chart so it uses the first column for the X values of all series and the rest of the columns for Y values of each series (so that the entire table is used in the chart), adding a row will extend each series (more rows = more points), and adding a column will add a new series to the chart for that added column. This works in Excel 2013 and presumably in 2010 and possibly 2007. – Jon Peltier Jun 27 '15 at 15:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.