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.
img2

 

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

 

img3

 

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

 

img4

 

  • 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

 

img5

 

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

img6

 

  • From Legend Entries (Series), click on Edit

img7

 

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

img8

  • Press F3 shortcut to enter the defined name

 

img9

 

  • Click on OK

 

img10

 

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

img11

 

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

 

img12

 

  • Click OK

img13

 

  • 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.

 

img14

 

 

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 info@exceltip.com

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

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.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube