Auto Expanding a Date Type Chart in Microsoft Excel

While working on reports where data will get updated, you may require updating your chart manually. With the use of OFFSET & COUNTA functions, you can get the dynamic updated chart. In this article, we will learn how to auto expand a date type chart.


Let us understand with an example:

We have data in which column A contains Date field, column B contains Sales.


  • Click on Formulas ribbon
  • From Defined Names group, click on Defined Names




  • In New Name dialog box, enter name as Sale, enter the OFFSET formula =OFFSET($B$2,,,COUNTA($B:$B)-1)in Refers to box




  • Click OK
  • Similarly, define a Date as named range
  • In New Name dialog box, enter name as Date, enter the OFFSET formula =OFFSET($A$2,,,COUNTA($A:$A)-1)in Refers to box




  • Create the Chart
  • Right click on Chart & select Select Data



  • From Legend Entries (Series), click on Edit



  • Click on Series values & delete the range $B$2:$B$10, do not remove the Data sheet which is a reference sheet


  • Press F3 shortcut to enter the defined name




  • Click on OK




  • From Horizontal (Category) Axis Labels, click on Edit button



    • In Axis Labels dialog box, remove the range $A$2:$A$10
    • Press F3 shortcut for define name, and select Date




  • Click OK



  • By clicking on ok you will find the Interactive Sales Chart

We have added Sales data for October month & immediately the Chart has been updated dynamically.





image 48

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.