How to Make a Graph in Excel That Updates Automatically

We occasionally need to update our data with our graph when working in Microsoft Excel. It is simple to refresh data charts in Excel. In this lesson, we’ll discover two simple and effective approaches on how to make a graph in excel that updates automatically.


Download Practice Workbook

You can download the practice workbook from the following download button.


2 Simple Methods to Make a Graph in Excel That Updates Automatically

Assume we have a dataset including data from three different retailers’ sales. The months’ names and sales figures for Store 1, Store 2, and Store 3 are provided. We will construct a table and utilize a dynamic function to produce an automatically updating graph in Excel. Here’s a look at the dataset for today’s job.

We are gonna make a graph from this data in Excel that updates automatically


1. Create a Graph from an Excel Table

If we have the following data range and column chart, we want the chart to automatically update as new information is entered. We may increase the data range by creating a table in Excel 2007, 2010, or later versions, and the graphic will automatically update. Just do as follows:

📌 Steps:

  • First, define the data range. For the sake of simplicity, we will choose B4 through E11 from our dataset. As a result, under the Insert tab, select the Table command.

Select the Table command form the Insert tab

  • As a consequence, you’ll see a Create Table dialog box appear. Click OK in the Create Table dialog box.

Click OK in the Create Table dialog box

  • You will be able to build the table seen in the picture below, after clicking OK.

an Excel table

  • We will also create a chart that will be refreshed. To do so, first, choose a cell from the table. Second, from the Insert tab, navigate to Charts and select any of the graph styles that best convey your work. For example, we’ve chosen 3-D Column.

from the Insert tab, navigate to Charts and select any of the graph

  • Following that, we were able to make a 3-D Column.

a graph created in Excel

  • Now, if you add data to the table, the graph will update accordingly.

Read More: How to Make a Graph from a Table in Excel (5 Suitable Ways)


Similar Readings


2. Set a Dynamic Formula to Each Data Column of Graph in Excel

However, in some situations when we do not wish to shift the range into a table, and as the solution described above is not accessible in Excel 2003 or previous versions, I’d like to expose you to a complicated dynamic formula approach. Consider the following data and graphics:

📌 Steps:

  • Unlike the previous way, we will now insert a chart from a range rather than a table. Just follow the steps like the image below:

inserting a chart from a range in Excel

  • Whereas the previous method’s chart updates itself, this one does not refresh itself automatically.

an Excel chart

  • First, we will make the Named Ranges and the dynamic formula for every column. Now, from the Formulas tab, go to Define Name.

making named ranges in Excel

  • Hence, a New Name dialog box will appear. From the New Name dialog box, firstly, type STORE1 in the Name typing box. Secondly, select the current worksheet named Dynamic Formula from the Scope drop-down box. Thirdly, type the below formulas in the Refers to typing box. The formulas are:
=OFFSET($C$5,0,0,COUNTA($C:$C)-1)

type STORE1 in the Name typing box in "New Name" window in Excel

  • Now, repeat Step 1 for columns C, D and E. The formula for the STORE2 column is,
=OFFSET($D$5,0,0,COUNTA($D:$D)-1)
  • Again, the formula for the STORE3 column is,
=OFFSET($E$5,0,0,COUNTA($E:$E)-1)
  • After that, press right-click on the chart and click Select Data.

press right-click on the chart and click select data

  • Now, select the Edit option under the Legend Entries (Series).

select the Edit option under the Legend Entries (Series)

  • Hence, again, a window named Edit Series pops up. From the Edit Series dialog box, type the following formula in the Series values typing box. At last, press OK.
='dynamic formula'!STORE1

a window named Edit Series pops up

  • Similarly, do the same for STORE 2 like the below image and for STORE 3.

  • Then, under the Horizontal (Category) Axis Labels option, click the Edit button.

Select Data Source window: under the Horizontal (Category) Axis Labels option, click the Edit button

  • Axis Labels dialog box appears as a consequence. Enter the following formula in the Axis label range typing box from the Axis Labels dialog box and press OK twice.
='dynamic formula'! MONTHS

Axis Labels dialog box appears

  • Now, we will add two rows to our range to refresh the chart. Assume we aggregate the sales from the three stores in October and November and update our range accordingly. Our chart will instantly refresh, as shown in the picture below.

Read More: How to Create a Chart from Selected Range of Cells in Excel


Conclusion

Follow these steps and stages to make a graph in excel that updates automatically. You are welcome to download the workbook and use it for your own practice. If you have any questions, concerns, or suggestions, please leave them in the comments section. For more article like this, visit our blog.


Related Articles

Al Ikram Amit

Al Ikram Amit

Hi there. I am Amit, completed my study from BUET. Currently, i am working as an Excel & VBA Content Developer at ExcelDemy. Now you can see my articles in ExcelDemy blog.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo