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.

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