Ahh…data types; Currency, Number, Percent. What a joy to have such a diverse set of numerical variations designed to make your life hell.
Anyone who has had to handle varying numerical formats knows how tedious it can be to build a reporting structure that can handle all formats.
If you’ve ever built one chart for Percents, one for Currencies, and one for Numbers, then you’re reading the right blog post.
Today, I’ll to show you a technique that allows one chart to accurately show any data type.
The Problem
For this example, I’m using the model shown here in this screenshot.
It’s a simple model that uses a ListBox to pull data from a table of metrics into a staging area.
The idea is to build a chart that responds to changes in the list box.
Here, I’ve built a chart that uses the values in C4:K4 as the source. As you can see, the chart formatting works fine for a Number metric.
The problem comes in when I choose a Percent metric. You’ll notice that when I select Resolution%, the chart Y Axis looks funny.
This is because the range I have my chart connected to is formatted as a Number format.
The Solution
The fix boils down to pointing your chart to a different range of cells, formatted appropriately for the metric selected.
You could do this manually by simply selecting a different range for your chart. The trick is to have Excel do it for you.
The solution is to tie your chart to a dynamic named range that will decide which range to point to.
Step 1: Tag your data
In preparation for this technique, add an identifier to your data, specifying the appropriate format for each metric.
In this example, I tag Percent metrics in a new field called Type with a P. I tag all Currency metrics with a C. And I tag Number metrics with an N.
You’ll notice that in Row 4, I’ve included the Type field in my staging area. This ensures that when the user selects a metric, the correct type tag will be identified.
I then add two additional ranges (Rows 5 and 6) that is a copy of my original staging area (Row 4). The difference is that I alter the number formatting so that each format type is represented. In this screenshot, you can see that row 4 is formatted as a Number, row 5 is formatted for Currency, and row 6 is Percent.
Step 2: Create a Named Range
The next step is to create a named range that will essentially point to the appropriate data range based on the tag type.
Do this, we’ll use:
=if(Sheet1!$C$4=”N”, Sheet1!$D$4:$L$4, if(Sheet1!$C$4=”C”, Sheet1!$D$5:$L$5, Sheet1!$D$6:$L$6))
This formula basically tells Excel that if the tag type is N then use the Number range, if the tag type is C then use the Currency range, else use the Percent range.
For this example, I’ve named my Named Range ‘ChartSource’.
Step 3: Use the Named Range as your Series Value
Edit the chart’s data source and change the ‘Series values’ reference to point to the named range.
In this case, the chart is now pointed to ChartSource.
Once that is done, your chart will keep up with the changes in number formats!
I know that’s a lot to take in, so I’ve made a sample file available for you to play with.
Check it out: Download the sample file here
Mike, a simple way to avoid having to set up an extra range would be to use a named range for ChartSource like =OFFSET(Sheet1!$D$10,Sheet1!$B$4,0,1,9)
I wonder if there might be a way to get 90% of the benefit with 10% of the work by using custom number formats:
e.g. “[<1]0%; 0" would format the values under 1 as percentages, larger numbers normally. When all the numbers in the series are under 1, the chart would show percentages.
Jon: That is the technique I typically use for PivotTables. You’re right. It’s a good technique and can get you 90% there. The extra work would get you the formatting changes between Number and Currency.
“Jon: That is the technique I typically use for PivotTables. You’re right. It’s a good technique and can get you 90% there. The extra work would get you the formatting changes between Number and Currency.”
But why go through the hassle at all when you don’t have to?
As a follow up to my previous post, I should mention that in most situations I encounter, the data is updated regularly, so apart from the unnecessary extra work in setting up a separate range and having to deal with number format issues, you have the issue of having to manually update this second range. Consider that instead of Div in your example, you have months (Jan, Feb, Mar and so on). It’s easy enough to create a dynamic named range to accommodate new months as they appear.
Colin: I understand what you’re saying. However, I often build dashboard models where disparate data is brought together in a staging area. The staging area allows you to incorporate your own analytics without having to build them into the actual data feed. I rarely build charts directly on the data feed. It’s the whole “separation of data layer and presentation layer”.
Mike, as much as possible, I try to construct queries that pull the data in the structure that I need for a chart or for other analysis. Sometimes the queries can get complicated, but it’s often worth the effort. The initial separation of data and presentation comes from not storing the source data in the Excel worksheet used for reports and charts.
For non-chart reports, clients usually construct ad-hoc reports from the queries. For example, it’s usually not possible to construct queries that match financial reporting requirements.
When it comes to charts, the data is always in a tabular structure, so an appropriate query can often be constructed. In cases where data is coming from multiple sources, or it’s impractical to combine all of the required data in a single query, then a separate consolidation range is necessary. But even in this case, it’s not clear why one would need to separate the data further i.e. after consolidation. In your example, if the range starting at C10 was a consolidation range, what is the practical advantage of separating the data further for your interactive chart? That’s four generations from the original source data, which seems like taking the separation of data and presentation to an extreme. I would love to hear from you the pros of this extra step. I’m too daft to figure it all out myself
Hi Mike,
I like your example, that’s a pretty neat way to update the format with the data.
Usually, I like to store the unit description with the data field name. The units would tell me how to format the data, so your approach would work well.
A VBA approach might be easier to implement, just assign a little macro to the drop-down. Like;
Sub FormatSeriesRange()
Dim R1 As Range
Dim R2 As Range
Dim i As Long
Set R1 = Range(“C4:K4″) ‘ Range of the cells to format
‘ same as the chart series range
With ActiveSheet.Shapes(Application.Caller).ControlFormat
i = .ListIndex
Set R2 = Range(.ListFillRange)
End With
R1.NumberFormat = R2.Cells(i, 2).NumberFormat
End Sub
Hi Mike,
I like you example very much and can see myself apply it in my work. May I ask a primitive question please? How can I achieve outputting listbox to B4. I understand when “Images” is selected, cell B4 changes to 3 whereas selecting “TotalFTE” will alter cell B4 to 6. How did you do it?
Many thanks in advance.
Hi Maggie: I am using a Form control in Excel. You can get to form controls from the Developer tab.
In Excel 2003, you’ll find the Form Controls toobar under View->Toolbars->Forms.
Once you add the the ComboBox/ListBox control, you can right click on it to format the control.
There, you assigned the “CellLink”. This is the cell that will accept the outputs from teh form.
Thanks Mike for the tips.
Since you have used named range, why not directly define the name from the source,and the chart will has no the problem as you show . you needn’t define the name from the 3 rows.