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