We often come across situations where we need to create a line/column chart to see the performance of sales/share price etc. However expanding the chart series every time there is an update/new data to be added is always time consuming. Therefore if in some way we can make the series of chart data dynamic then it will also make the chart dynamic.
Let see an example: Suppose we have to create a line chart for monthly price for 3 stocks for last 1 year and every month there will be a new addition for the next 1 year. We can easily create it by selecting line chart from excel and having months on column A and share price of stock on columns B,C & D respectively. However if we select a long range to fit the data then wherever there is no data the chart will show blank/space left in the chart which is not a good way to construct, and if we don’t select a long range we have to manually add it every time.
Now let see how we can make the chart dynamic in such case.
Step 1: Create a data table as shown and save file as chat.xlsx
Step 2: Create a name range using Name Manager in Excel 2007 as shown below
MonthValues =OFFSET(StockAvalues,0,-1)
StockAvalues =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1,1)
StockBValues =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1,1)
StockCvalues =OFFSET(Sheet1!$D$2,0,0,COUNTA(Sheet1!$D:$D)-1,1)
Step 3: Now you can create a chart using the Chart wizard but make to have the series data in the following way:
=SERIES(Sheet1!$B$1,chart.xlsx!MonthValues,chart.xlsx!StockAvalues,1)
=SERIES(Sheet1!$C$1,chart.xlsx!MonthValues,chart.xlsx!StockBValues,2)
=SERIES(Sheet1!$D$1,chart.xlsx!MonthValues,chart.xlsx!StockCvalues,3)
And there you go…..Download the file from here: