How To Create Self-Updating Excel Charts In Three Easy Steps

header   How To Create Self Updating Excel Charts In Three Easy StepsIf 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.

Charts often make decision making easy. They’re a nice change from staring at rows and columns of numbers. They add some style and pizazz to your documents or presentations. Creating charts in Excel seems complicated, so you end up settling for simple charts which require a lot of work to maintain.

In my full time job, charts help shorten the decision making process, as we can immediately see our results and where changes need to be made. The difficulty in handling data and charting it is you constantly have to go back to the chart and update it for new data.

Well, no more! I want to show you three easy steps to creating charts in 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 Excel charts.


To walk you through the process, I have created a fictional pet store called “Paws n’ Fangs“ and I have begun an Excel workbook titled “pnfinv.xls“. Here is the October inventory of pets as of the seventeenth of the month, along with a basic chart of the dog inventory. This is the starting point and step one ““ a spreadsheet with a basic chart.

step1   How To Create Self Updating Excel Charts In Three Easy Steps

The problem is updating the chart for new daily inventory numbers. I may not want store clerks to mess with the charts and I may be tired of doing it all the time as well. I can quickly look at these charts and determine when and how many pets I need to order from my supplier, so I don’t want to abandon the use of the charts.

Step two is to name the column that I want to chart. This is done through the menu bar by clicking Insert>Name>Define. When the “Define Name“ window pops up, we start by clicking inside the text box under “Names in Workbook“. In this blank space, we type in the label for the column we plan to chart.

For our example, we would type in “Dogs“ exactly as it appears in the spreadsheet. Now we have defined a Name, but we have to further define what the name means.

step2   How To Create Self Updating Excel Charts In Three Easy Steps

At the bottom of this window is a blank area for “Refers to:“. This is where the magic happens for our chart. In this box we use the OFFSET function. Notice the formula is “=OFFSET(Sheet1!$B$7,0,0,COUNTA(Sheet1!$B$6:$B$37)-1)“. This looks complicated, but let’s break it down.

What we have done in this formula is to define that the name “Dogs“ refers to data in Sheet1, with data starting in cell B7. The “0,0″ pinpoints to B7, as these are “Row, Column“ variables. If we used “$B$7, 1,0″ we would be referencing B8, as the “1″ in the row variable position would mean that we were referring to one row below the stated cell. In our simple example, it is much easier to directly reference the starting point of the data by using “$B$7, 0,0″.

Next, the formula counts the non-blank cells in the entire column, including the header (B6:B37) and subtracts 1 (for the header-necessary for sorting functions). Notice two important points. First, the “Sheet1″ portion of the formula is simply the name of the sheet on which the data is located. Excel’s default is Sheet1. Your charts could be in the same Excel workbook, but on a page titled “Charts”, while you may have various sheet titles such as “Oct 09 Inv”, “Nov 09 Inv”, etc. Be sure to properly reference the sheet title or you’ll have a mess on your hands.

The second point is that for our example, we have a defined number of days in the month (31), so our maximum number of entries at one per day is thirty-one. Thus, our defined range to count extends to cell B37, where the 31st piece of data will be. You may have hundreds of data points you will eventually want to fill in and chart, or you may have a defined number. The point is to use a large enough reference so that you won’t have to go back and readjust the Names. If your spreadsheet is a defined size, simply use the maximum cell reference here.

Now we have defined the column name and the data referred to by the name “Dogs“. The third and final step is to point the chart to the name “Dogs“. This is pretty simple. Right click on the chart, and select “Source Data“. Click the “Series“ tab and in the text box beside “Values“, simply type in the name of the worksheet and the name of the range in this format – “=workbook name!RangeName“, which for our example will end up as “=pnfinv.xls!Dogs“. The workbook name is used here instead of the sheet name, as the Defined Names from step one are rooted to the workbook.

step3   How To Create Self Updating Excel Charts In Three Easy Steps

Click “OK“ and the chart will now self-update! We can test this by entering more data for the next several days. If the syntax of your formulas was entered correctly, your charts should update before your eyes! No more clicking and dragging cell reference outlines all over the place every time you add a piece of data to your spreadsheet. You don’t necessarily have to create a chart to begin with. You can wait until after you have defined a Name, and then begin the charting process.

result   How To Create Self Updating Excel Charts In Three Easy Steps

Finally, for all the other pets in my imaginary store I can go back through the simple three step process and work on creating self-updating charts in Excel  for my entire inventory.

repeat   How To Create Self Updating Excel Charts In Three Easy Steps

For other tips, tricks and cool free charting applications check out the MUO Charts tag directory and also check out the Excel tag for more Excel posts.

The comments were closed because the article is more than 180 days old.

If you have any questions related to what's mentioned in the article or need help with any computer issue, ask it on MakeUseOf Answers—We and our community will be more than happy to help.

4 Comments -

0 votes

Arturas

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

0 votes

Greg

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.

0 votes

Paul

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.

0 votes

Tim

great looking arrow