Al Ikram Amit
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.
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.
- As a consequence, you’ll see a Create Table dialog box appear. Click OK in the Create Table dialog box.
- You will be able to build the table seen in the picture below, after clicking OK.
- 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.
- Following that, we were able to make a 3-D Column.
- 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
- How to Plot Semi Log Graph in Excel (with Easy Steps)
- Plot Sieve Analysis Graph in Excel (with Quick Steps)
- How to Make an X Y Graph in Excel (With Easy Steps)
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:
- Whereas the previous method’s chart updates itself, this one does not refresh itself automatically.
- First, we will make the Named Ranges and the dynamic formula for every column. Now, from the Formulas tab, go to Define Name.
- 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)- Where the OFFSET function indicates the first data and the COUNTA function indicates the entire column data.
- At last, press OK.
- 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.
- Now, 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
- 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.
- 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
- 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.
