Pinterest Stumbleupon Whatsapp

headerIf 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.

creating charts in excel


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.

creating charts and graphs in excel spreadsheet

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.

line charts in excel

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.


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.


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.

Leave a Reply

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

  1. 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 ?

  2. 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.

  3. 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.

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

    great looking arrow

  5. 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?

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

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