Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

3 Answers 3

"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

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

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.