Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to auto update a chart after entering new data in Excel?

Supposing you have created a chart to track the daily sales based on a range of data in your workbook. But you need to change or edit the number of the data every day, in this case, you have to update the chart manually so it includes the new data. Are there any quick tricks to help you auto update a chart when you add new data to an existing chart range in Excel?

Auto update a chart after entering new data with creating a table

Auto update a chart after entering new data with dynamic formula


Tabbed browsing & editing multiple Excel workbooks/Word documents as Firefox, Chrome, Internet Explore 10!

You may be familiar to view multiple webpages in Firefox/Chrome/IE, and switch between them by clicking corresponding tabs easily. Here, Office Tab supports similar processing, which allow you to browse multiple Excel workbooks or Word documents in one Excel window or Word window, and easily switch between them by clicking their tabs. Click for free 45-day trial of Office Tab!

ot excel

arrow blue right bubble Auto update a chart after entering new data with creating a table

If you have the following range of data and column chart, now you want the chart update automatically when you enter new information. In Excel 2007, 2010 or 2013, you can create a table to expand the data range, and the chart will update automatically. Please do as this:

doc-update-chart1

1. Select the data range and click Table under Insert tab, see screenshot:

doc-update-chart2

2. In the Create Table dialog box, if your data has headers, please check My table has headers option, then click OK. See screenshot:

doc-update-chart3

3. And the data range is formatted as a table, see screenshot:

doc-update-chart4

4. Now, when you add values for June, and the chart will be updated automatically. See screenshot:

doc-update-chart5

Notes:

1. Your new entering data must be adjacent to the above data, it means there is no blank rows or columns between the new data and the existing data.

2. In the table, you are able to insert data between the existing values.


arrow blue right bubble Auto update a chart after entering new data with dynamic formula

But sometimes, you don’t want to change the range to table, and the above method is not available in Excel 2003 or earlier version. Here I can introduce you a complex dynamic formula method. Take the following data and chart for example:

doc-update-chart6

1. First, you need to create a defined name and a dynamic formula for each column. Click Formulas > Define Name.

2. In the New Name dialog box, enter Date into the Name box, and choose current worksheet name from Scope drop down list, and then enter =OFFSET($A$2,0,0,COUNTA($A:$A)-1) formula into the Refers to box, see screenshot:

doc-update-chart7

3. Click OK, and then repeat the above two steps, you can create a dynamic range for each series using the following range names and formulas:

  • Column B: Ruby: =OFFSET($B$2,0,0,COUNTA($B:$B)-1);
  • Column C: James: =OFFSET($C$2,0,0,COUNTA($C:$C)-1);
  • Column D: Freda: =OFFSET($D$2,0,0,COUNTA($D:$D)-1)

Note: In the above formulas, the OFFSET function refers to the first data point, and the COUNTA refers to the entire column of data.

4. After defining the names and formulas for each column data, then right click any column in your chart, and choose Select Data, see screenshot:

doc-update-chart8

5. In the Select Data Source dialog box, from Legend Entries(Series) section, click Ruby and then click the Edit button, in the popped out Edit Series dialog, enter =Sheet3!Ruby under Series values section, see screenshots:

doc-update-chart9
-1
doc-update-chart10

6. And then click OK to return to the Select Data Source dialog, repeat the step 5 to update the remaining series to reflect their dynamic ranges:

  • James: Series values: =Sheet3!James;
  • Freda: Series values: =Sheet3!Freda

7. After setting the left data, now you need to click Edit button under Horizontal (Category) Axis Labels to set this option, see screenshots:

doc-update-chart11
-1
doc-update-chart12

8. Then click OK > OK to close the Select Data Source dialog, after finishing these steps, you'll find that the chart updates automatically when you add new data to the worksheet.

doc-update-chart13

Notes:

  • 1. You must enter new data in a contiguous manner, if you skip rows, this method will not work as expected.
  • 2. If you enter new column data, this method will not take effect.

Tip.If you want to quickly export range contents from sheet to a graphic, please try to use the Kutools for Excel’s Export Range as Graphic as shown in the following screenshot. It’s full function without limitation in 60 days,please download and have a free trial now.

Export graphics (Pictures/Charts/Shapes/All Types) from workbook to a folder as Gif/Tif/PNG/JPEG

If there are multiple type of graphics in a workbook, and you just want to export all charts across worksheet to a folder as gif of other type of picture, you can use Kutools for Excel's Export Graphics utilty, which only need 3 steps to handle this job .  Click for full-featured 60 days free trial!
doc export graphics
 
Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 60 days.

Related articles:

How to add a horizontal average line to chart in Excel?

How to create combination charts and add secondary axis for it in Excel?


Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Mufaddal · 11 days ago
    HI,
    This trick doesnt work if you want to add new columns. This trick is for fixed columns. What if there is a data yearwise in columns and every year a new year column is added so how will it get added to the chart ??

    Any idea you can suggest ??

    Cheers,

    Mufaddal
  • To post as a guest, your comment is unpublished.
    MANISH · 21 days ago
    when I type in cell any no. like - 210 it reflect as = 2.10 , no formula is taking place why? any no. shows devide by 100 as I mentioned , decimal comes automatically how to resolve this ?


  • To post as a guest, your comment is unpublished.
    TB · 2 years ago
    It works well! Thank you!
    One question:
    what if my drop down list is not on the same sheet with the "table" where i have datas?
    how can I modify the function?
    • To post as a guest, your comment is unpublished.
      Sunny · 1 years ago
      Thanks for the message. But I do not get your question clearly, why don't you upload a screenshot of your problem for me to understand easily? More description, easier to understand. Thank u.
    • To post as a guest, your comment is unpublished.
      chris · 1 years ago
      did you manage to solve this issue? having same issue at the moment
  • To post as a guest, your comment is unpublished.
    Kalishwaran A · 2 years ago
    Airtel mobile Bill Payment
  • To post as a guest, your comment is unpublished.
    jasmith4 · 2 years ago
    A chart's ranges can depend on names or not. If a chart range depends on, say A1:E5, and you insert a row at row 3, and a column at column C, the chart will automatically depend on A1:F6. Similarly, if you have a name defined as A1:E5, whether you use it as a chart's range or not, and you insert a column and row at C3, the name's definition will expand to A1:F6. But in either case, if you insert a column and A or E (the endpoints), or a row at 1 or 5, the behavior isn't so well defined: maybe the chart range or name's definition will expand; maybe it won't. To answer Melissa, you'd have to insert rows or columns before or to the left of the chart range.

    I think it's better to use names, because names can be defined using formulas that involve OFFSET, COUNT, INDEX, MATCH, whatever else. So the better answer to Melissa is to define a name for one cell, namely the last of the eight columns or rows, then define the name in term of an OFFSET from that cell: OFFSET(cell,-8,-8,8,8) to go back and up eight cells and use an 8x8 range.