How to Create Self-Updating Excel Charts in Three Easy Steps
Whatsapp Pinterest

If you’re like me, you love the idea of charts and can think of dozens of useful applications for them, but when it comes to creating them, the frustration starts.

Unlock the "Essential Excel Formulas" cheat sheet now!

This will sign you up to our newsletter

Enter your Email

Charts often ease decision making. They’re a nice change from staring at rows and columns of numbers. They add some style and pizazz 6 New Excel Charts and How to Use Them 6 New Excel Charts and How to Use Them Need to get more out of your Excel charts? Here's a one-stop guide to some of the new charts introduced in the latest version of Excel. Read More to your documents or presentations. However, creating complex charts in Microsoft Excel can be intimidating, so it’s easy to end up settling for simple charts which require a lot of work to maintain.

Charts help shorten the decision-making process, as we can immediately see our results and where we need to make changes. The difficulty in handling data and charting is that you constantly have to go back to the chart and update it for new data.

Well, no more! I’m going to show you three easy steps to creating charts in Microsoft Excel that self-update. All you’ll have to do is add data to the spreadsheet, and the chart will automatically graph it. You won’t have to depend on others to manipulate or mess up the chart, and you won’t have to do all that extra work either. You don’t need any Visual Basic skills, but you do need to understand the basic fundamentals of Microsoft Excel charts.

1. Set Up a Spreadsheet

To create a self-updating chart, we first need to set up a spreadsheet 10 Amazingly Useful Spreadsheet Templates to Organize Your Life 10 Amazingly Useful Spreadsheet Templates to Organize Your Life Is your life a mess of missed deadlines, forgotten shopping, and broken commitments? Sounds like you need to get organized. Read More that can supply the data we require. The formatting of this document is important because we need to be able to add more data on an ongoing basis.

Lay out your information and make sure that each column has a header. For my example project, I’m creating a chart that tracks sales of each Harry Potter novel at a bookshop.

raw data

The kind of chart that we’re making works best if you can extend new entries into new rows below. In my example, as new sales data is recorded, you would add it to the spreadsheet starting in row 11.

raw data update

Lay out your information, double-check that every column has a sensible header, and you’ll be ready for the next step.

2. Create a Table

In Office 2016, tables have advanced 8 Formatting Tips for Perfect Tables in Microsoft Word 8 Formatting Tips for Perfect Tables in Microsoft Word Microsoft Word tables are essential formatting tools. Find out how to make a table and format it perfectly with these simple tips. Read More . Rather than just making your data look neat and tidy, they help you group information together. The end goal here is to create a table that feeds data to a chart. Linking these two elements together allows the chart to check for newly added data in the table.

To create a table, select all the data you want to turn into an Excel chart. Then head to the Insert tab and select Table — alternatively, you can use the shortcut CTRL + T.

insert table

In the Create Table dialog, you can tweak the cells included in the table. Tick the box labeled My table has headers, then press OK.

create table

Your data should undergo the following change of appearance:

table comparison

With that change made, we’re ready to work on our chart.

3. Insert a Chart and Add Data

Select the whole table and head to Insert > Charts to choose what kind of visualization to use. The correct chart will depend on what kind of data you’re working with. For my example, I’m using a line graph. This allows me to compare several different columns worth of data in one chart, and it works very well with automated updates.

basic graph

Now is a good time to make any formatting changes 9 Tips for Formatting an Excel Chart in Microsoft Office 9 Tips for Formatting an Excel Chart in Microsoft Office First impressions matter. Don't let an ugly Excel chart scare off your audience. Here's everything you need to know about making your charts attractive and engaging in Excel 2016. Read More  or visual tweaks. Once that’s done, we can test out adding new data to the chart. Next, we need to test whether our self-updating chart actually works. Fortunately, this is by far the easiest part of the process.

To add more data, simply add another line at the bottom of your existing chart. Since my Date column dictates the values on the X-axis of my chart, I’ll start there. Date formatting can be a little tricky, so I’ll drag the bottom right corner of the cell down to populate the cell automatically. Of course, I could do this manually — I’d just have to lay out my date in the same format as preceding rows.

new row

You may see a dialog warning you that the table inserted rows into the worksheet — this is absolutely fine. Your chart should have already updated to include the new entry on its X-axis. Assuming that’s correct, you’re safe to start adding new data to the table.

final chart

Above, you can see that I added a sales count of 10 for each book to prompt the chart to update. You can now use the chart indefinitely, simply by adding more rows to the table. However, you may have to tweak its size and formatting to present all the data properly Automatically Format Data in Excel Spreadsheets With Conditional Formatting Automatically Format Data in Excel Spreadsheets With Conditional Formatting Excel's conditional formatting feature lets you format individual cells in an Excel spreadsheet based on their value. We show you how to use this for various everyday tasks. Read More , depending on how much you’re planning to add.

Make Microsoft Excel Work for You

One of the most powerful aspects of Microsoft Excel is the fact that various aspects of spreadsheets can be automated. This might be something as simple as creating a basic self-updating chart, like we’ve seen here. It’s easy to make a box and whisker plot in Excel too.

By putting in a little effort up-front, you can save plenty of time later on. Challenge yourself to learn something new in Microsoft Excel, and it will pay off tenfold in the long run.

Is the chart you create most often a flowchart? Along with using Excel, you can look at free software specifically for flowcharts on Windows The Best Free Flowchart Software for Windows The Best Free Flowchart Software for Windows Flowcharts can visualize ideas and processes. Use flowchart software to streamline your life and break free from bad habits. Read More .

Explore more about: Microsoft Excel, Microsoft Office 2016, Spreadsheet.

Enjoyed this article? Stay informed by joining our newsletter!

Enter your Email

Leave a Reply

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

  1. crab
    March 28, 2019 at 2:50 pm

    Thanks, this was very helpful and the instructions were clear and simple. Everything works great.

  2. seun
    September 13, 2018 at 12:20 pm

    hi,i am new to excel and i need to create a work sheet where i can have many colums and rows and also whenever i input figures in it update the total there a constant formular to achieve summary i want the table to update it self as soon as i insert new values/ email is

  3. Mary Grace
    April 4, 2018 at 8:05 pm

    I recently wrote a VBA code that executes this function flawlessly; however, the graph is experiencing a significant lag in response time. For instance, my data is reading in continuously every second and instead of the graph depicting new data points every second, it lags for a minute or a few second and plots several points all at once. It is important to the project that I am working on that the graph update continuously, in real time, for visual purposes. Has anyone come across the same situation? If so, is there anything I can do to improve this issue?

  4. Tom C
    March 14, 2018 at 12:16 am

    Could use help with a program I want to make

  5. Tom Costantiello
    March 14, 2018 at 12:15 am

    I have a financial retirement planning calculator excel program I created. Would like to make inputs and have graphs automatically update

    Hoping you can offer some suggestions or possible a solution

  6. Jayasri
    January 19, 2018 at 10:46 am

    Thanks it was seriously helped me at the right time. Am doing a VBA coding to automatically update the graph value when a new entry is made. I checked all the codings and nothing workedout. Simply i changed the data to table it worked.

  7. Paras Khaitan
    December 19, 2017 at 1:28 am

    This is sick perfect. Precisely what I wanted. I work with 50 charts which require updating every week. And that was always very daunting. This technique works perfectly. Thanks a ton.

  8. lakdeepa
    October 19, 2017 at 10:38 am

    you are posting a good information for people and keep maintain and give more update too.

  9. Ann Archuleta
    May 19, 2016 at 2:36 am

    Savvy article ! I loved the details ! Does someone know if my assistant would be able to get access to a sample a form version to type on ?

  10. Anonymous
    June 22, 2015 at 7:02 am

    We have a chart to fill out daily about our hourly production which includes our hourly pitch, our actual pitch for the hour, a list of all the stations so we know exactly how many parts were lost there due to production downtime, scrap, and three other columns that would be the reason as to why we didn't make the hour (start up, cycle time, other), overproduction if we did over produce, and a column for comments as to noting when and why we were down or if something happened at that particular hour. Based on these columm headings, how do I connect this chart to auto populate in another sheet to automatically make a list based on my chart? I need to make lists of each seperate station and their down times and when, a list of scrap that we get and which kind of scrap, etc. This also has to be accessible to whomever and it will be updated daily so I also need new information entered to show up in the appropriate list according to conditions.

  11. Dan
    January 30, 2015 at 7:28 pm

    Does it matter if your data goes across say from B6-Q6 instead of going down from B6-B37? Would you need to change any part of the "Offset formula"?

    • Sandesh
      November 13, 2016 at 3:03 pm

      Please refer to formula
      OFFSET(reference, rows, cols, [height], [width])


      Add "1" before COUNTA that will be for 1 Row.

  12. Tim
    November 12, 2009 at 10:32 pm

    great looking arrow

  13. Greg
    October 31, 2009 at 9:59 am

    It's also may be good to note that if you are a bit more technically inclined, you can do some really cool dynamic charts using VBA. A few years ago I created a trending chart that used slider bars to zoom and scroll through the history of the chart.

    • Paul
      November 2, 2009 at 1:24 pm

      Good point, Greg. A while back I was wanting to create a self updating chart for my employees to use where they just entered the data and the chart work was done for them. I spent several weeks learning VBA methods and ended up using VBA for the entire workbook, including sorting, printing and "goto" buttons, checkboxes, and the works. Then I figured out this easy method for simple charts.

      I'm glad I got the VBA experience but it was frustrating knowing how simple what I really wanted was!

      Hence the "Easy Steps" in the title. I wanted to share this with other Excel "charters" out there. VBA in Excel will probably be a subject of some of my future articles.

    • Dan
      January 30, 2015 at 7:30 pm

      Paul does it matter if your data goes across from say cell b6-q6 instead of going down from b6-b37? Would you need to adjust the offset formula in any way?

  14. Arturas
    October 30, 2009 at 6:36 pm

    it might be interesting to check out web based pivot table and charts at