UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> Dynamic Charting    
Dynamic Charting

Note: Dynamic charting techniques require a knowledge of Excel’s OFFSET() command. To learn more see this wiki.

Contents

Introduction

Excel does a pretty decent job of figuring out what it is you want to chart. Most of the time, highlighting the range of data and selecting the chart type is all you have to do. However, there are occasions when you only need to plot only part of the data and you would like to do this “on the fly.”

This can be done by having the chart read its data from a dynamic range instead of a fixed range.

The combination of dynamic ranges and Excel charting is powerful. It can give you dashboard-like results where charts are displayed based on the data selected. This wiki explains how to set the data to read from these named ranges.

In this example we have a series of metrics for 4 different models for 10 days. This data could be coming from anywhere such as a query being read into Excel.

This wiki has a downloadable zip file with two spreadsheets: one with the finished product, the other with only the raw data and a pivot table for practice. media: Charting Exercise.zip

The data is summarized on the Report Tab using a pivot table. The data doesn’t have to be a pivot table but Pivot table displays are a “worst case” scenario for charting since the ability to manipulate a pivot table chart is limited. However, the techniques described in this wiki can be applied to any data range similarly organized.

The pivot table in the example illustrates the issue. In the sample spreadsheet, if you put the cursor anywhere in the pivot table and try to apply a chart, you get the model names on the X-axis and each of the dates as a separate series.

image: chart01.jpg

The chances are, this isn’t the way you want to view the data.

With pivot table charts, there is no way to switch rows and columns on the chart. You have to rearrange the pivot table to list the dates down and models across to get what you want. This may not be the tabular display you want.

Also, to display just one model on the chart, you need to filter it out of the report meaning you don’t get to see the other models in the tabular display. Sometimes this changes the value of the metric charted such as when the metric is a percent based on total of a column or row.

Instead, wouldn’t it be nice to be able to pick a model and chart only it while preserving the tabular display you want?

image: chart02.jpg

Setting the Named Ranges

To make the spreadsheet behave like a dashboard, the first thing to do is set up a cell in which a valid model name can be selected and then chart the data associated with that model.

To do this, we need to set up a couple of named ranges using the offset command.

The first of these named ranges is a list of models to pick. Starting with the base cell being the first model name in the pivot table, Cell A20, build the named range, Model_List.

=OFFSET(Report!$A$20,0,0,COUNTA(Report!$A:$A)-3,1).

This named range will include only the models shown in the pivot table. So if the pivot table is filtered on Vendor A, only the models for Vendor A are available for selection in the data validation (shown below).

Next, set up a cell to select the Model Name. This is Cell B1 and it has a Data Validation of type List with the valid values being =Model_List.

The next two ranges to set up are the date and the value ranges.

Setting the date range, MyDate, is relatively simple:

=OFFSET(Report!$B$19,0,0,1,COUNT(Report!$19:$19))

The dates are on row 19 and they happen to be the only numeric thing on row 19. So COUNT() is a good formula to determine how many of them there are.

Setting MyValue is a little trickier. To find out which model is selected, look for its position in Model_List. There is a formula in Cell B1 of the Parameters Page that does this:

=MATCH(Report!B1,Model_List,0)

This formula returns how many rows down the selected model in Cell B1 shows in Model_List. This number also happens to be the number of rows that the Model is offset from MyDate. So MyValue is defined as:

=OFFSET(MyDate,Parameters!$B$1,0)

There is one more piece of cosmetic setup and that is to create a chart title. This is done in Cell B2 on the parameters page.

="Net Sales for " & Report!B1

We are now ready to chart.

Charting the Values

Pick a blank cell on the page. Then select Insert -> Line Chart. This creates a blank chart. Move it to the vicinity of Cell A3 and resize it so it doesn’t cover the pivot table. Hint: holding down the Alt key while moving or resizing the chart will snap the chart to cell boundaries.

Right click on the chart and select Select Data


image: chart03.jpg

From the resulting dialog box, select Add

image: chart04.jpg

Type in a series name that makes sense.

image: chart05.jpg

In the series value box, type in <Sheet_Name>!Range_Name. In this case Report!MyValues. Remember to wrap the sheet name in single quotes if it has a space. Also the entire sheet name is required. So if the sheet name is, “Net Sales for the Month of January,” there is a lot of typing to do. Rename the sheet to something simple like “x” and work with that name. When the name is changed back, all will be well.

As soon as OK is clicked the chart will reflect the new series.

image: chart06.jpg

Now do the same thing for the X-axis

Click Edit and fill in the sheet and range name

image: chart07.jpg

You are done with the data. Click OK.

One minor cosmetic adjustment is to make the title dynamic too.

Click on the title. Then in the formula bar type an equal sign and select Cell B2 on the parameters page. The result should look like:

image: chart08.jpg

From here on in, play with and modify the chart using the regular charting tools.

Final Notes and Tweaks

If Model A or Model B is selected and the filter on the pivot table is changed to display OEM B a message indicating that a formula on the worksheet contains an invalid reference will appear. This is because neither Model A nor Model B belongs to OEM B, so neither of them is displayed on Model_List and the MATCH() formula fails.

It is beyond the scope of this paper to explain in detail how to deal with issues of this type. However some suggestions are presented below.

One way to avoid the error is to use a VB code Change Event so when Cell B16 (the pivot table filter) is changed, a valid default value is assigned to Cell B1 such as “Grand Total”

If you really want to impress your users, set an On Double Click Event to the range Model_List so when the user double clicks on a model on the list, the value in Cell B1 is set to the value in the cell just clicked. This technique is handy when you have a long list of models and the dropdown becomes unwieldy.

Edit Discussion
Custom Search
Thank you for your support!
This page has been accessed 4,101 times.  This page was last modified 20:37, 8 July 2013 by dflak. Contributions by Jack Leach  Disclaimers