Home	Case Studies	MS Office Tips and Tricks	Sample Code	Helpful Videos	Custom Development	Custom Training	Join Us	Contact Us
Search Our Site:

Dynamic Excel Charts

You’ve probably wished that you could keep adding data to your spreadsheets and have your charts display only the most recent rows. The trick is to create a named range that is dynamic—in other words, it changes as the spreadsheet changes. Then build a chart based on the dynamic range so it will update along with the range. The Excel functions named OFFSET and COUNTA are the keys to this functionality; we’ll use them to define our named range.

The OFFSET function returns a cell or range of cells some distance from a reference cell. Look at the following formula and the Excel data pictured here from the sheet named Revenues:

=OFFSET(Revenues!$A:$A, 4, 0, 1, 1)

Our formula looks at Column A, moves down four rows, moves over 0 columns, then returns 1 row and 1 column (just one cell) which is 4/30/2004. When we define our dynamic range, we’ll pick up more than just one number!

Go to the Insert menu and choose Name > Define. We need to define two ranges: one for the labels and one for the data. For the name of the labels range, use LabelsRange and type in the following formula in the Refers to: box at the bottom:

 =OFFSET(Revenues!$A:$A,COUNTA(Revenues!$A:$A)-3,0,3,1)

We want only the last 3 months worth of labels. COUNTA gives us the number of nonblank rows in column A. This formula counts down the current number of rows (to get to the last row) minus three (the third-to-last row). It then returns a set of data 3 rows high and 1 column wide.

A Word From Our Sponsor

Great Toys For Kids!

For the data, create another named range called DataRange and use this formula:

=OFFSET(Revenues!$B:$B,COUNTA(Revenues!$B:$B)-3,0,3,1)

Now it’s time to create the chart. From the Insert menu, choose Chart. Pick whichever chart type you prefer (the example uses a simple Column) and click Next. In step 2 of 4, you should see two tabs. The second tab is named Series—click on that.

For values, type =Revenues!DataRange
For  Category (X) axis labels: type =Revenues!LabelsRange.

Click Finish to create the chart or add your own changes.

Try adding values for July and August and watch the chart update with the new information. For other information on charts see our article on special formatting and a success story on automating the creation of charts.