Saturday, 10 August 2013



Quick Weekend Tips


About the Quick Weekend Tips (QWT) post series



Starting from this weekend I will try to establish a new series of posts which will be different than the “typical” posts that are published in this blog. The name of this series is called “Quick Weekend Tips” since the posts will be published during the weekends (Saturday or Sunday) and their subject will be quick/short tips about various applications (Excel, Power Point, AutoCAD etc.). These quick tips will be accompanied with step by step instructions, as well as with many pictures.



QWT 1: Create an Excel chart with dynamic data range



Today we will see how to create an Excel chart with dynamic data input. The tip is based on OFFSET formula and on defined names. For simplicity reasons assume that you have the following annual sales data in the range A1:B13 of Sheet1.

Create An Excel Chart With Dynamic Data Range Data 1

Step 1: First we will create two name ranges, “Years” and “Sales”. In the Formulas tab, select the Define Name dropdown list and select the Define Name… option.

Create An Excel Chart With Dynamic Data Range - Define Name 1

Step 2: In the New Name form enter in the Name textbox the word “Years”, leave the dropdown list in Scope to Workbook, enter the following formula to Refers to field: =OFFSET(Sheet1!$A$2;0;0;COUNTA(Sheet1!$A:$A)-1) and press the OK button. If you receive an error while entering the formula change the “;” with “,” (this is related with the Windows regional settings of your computer). If the sheet’s name is other than “Sheet1”, change the formula accordingly – i.e. =OFFSET(Data!$A$2;0;0;COUNTA(Data!$A:$A)-1). Note the use of “!” after the sheet’s name.

Create An Excel Chart With Dynamic Data Range - Define Name 2

Step 3: Repeat step 1 and 2, but in the Name textbox enter the word “Sales”, while to the Refers to field enter the formula: =OFFSET(Sheet1!$B$2;0;0;COUNTA(Sheet1!$B:$B)-1). Again, if the sheet’s name is other than “Sheet1”, adjust the formula accordingly.

Create An Excel Chart With Dynamic Data Range - Define Name 3

Step 4: Since we defined the necessary ranges we are now ready to create the chart. In the Insert tab, select the Insert Column chart dropdown list and select a 2-D Clustered Column chart. Of course you can select any chart type you like. The new chart will be empty.

Create An Excel Chart With Dynamic Data Range - Chart 1

Step 5: Right click on chart and click the Select Data… option on the menu.

Create An Excel Chart With Dynamic Data Range - Chart 2

Step 6: In the Select Data Source form press the Add button.

Create An Excel Chart With Dynamic Data Range - Chart 3

Step 7: In the Edit Series form enter the Series name (optional) – here the “Annual Sales” is used – and fill the Series values with the following formula: =Sheet1!Sales (or alternatively: Workbook Name.xlsx!Sales). Similar to steps 2 and 3, if the sheet’s name is other than “Sheet1”, change the formula accordingly. Finally, press the OK button.

Create An Excel Chart With Dynamic Data Range - Chart 4

Step 8: In the Select Data Source form press the Edit button.

Create An Excel Chart With Dynamic Data Range - Chart 5

Step 9: In the Axis Labels form fill the Axis label range with the following formula: =Sheet1!Years (or alternatively: Workbook Name.xlsx!Years) and press the OK button.

Create An Excel Chart With Dynamic Data Range - Chart 6

Step 10: Finally, in the Select Data Source form press the OK button.

Create An Excel Chart With Dynamic Data Range - Chart 7

If you have completed the previous steps successfully your chart will look like this.

Create An Excel Chart With Dynamic Data Range - Chart 8

And here is the “fun” part. If we enter new data in the next rows of the input data, for example in the range A14:B15:

Create An Excel Chart With Dynamic Data Range - Data 2

The chart automatically will show the new data:

Create An Excel Chart With Dynamic Data Range - Chart 9

I have used this technique in an older post, although I didn’t analyze then the way it was done..



Sample file




The file was created with Excel 2013, but it can be opened with Excel 2007 or newer.

Did you like this post? If yes, then share it with your friends. Thank you!




Mechanical Engineer (Ph.D. cand.), M.Sc. Cranfield University, Dipl.-Ing. Aristotle University, Thessaloniki - Greece.
Communication: tel. +30-6973513308, e-mail , Facebook , Twitter , Google+ and Linkedin . Full CV