Filtering is one of the most used feature in Excel. It is a quick way to take lots of data and narrow down to the subset we want.
Naturally, there are many powerful ways to work with filters. To name a few,
- Using CTRL+Shift+L shortcut to quickly turn on / off the filters
- Right clicking on a cell value and choosing filter > filter by selected cell’s value
- Using advanced filters to set up complex filtering conditions
But here is one common filtering scenario that is slow as snail.
Imagine you are looking at some sort of sales data (if you can’t imagine, look at the below demo).
Now, you want to filter this list for a combination like, gender=male, profession=self-employed, product category = chocolates and quantity = 1.
If you use the right click, filter > filter by selected value approach, this will take several clicks.
Wouldn’t it be cool if you can select the entire combination and say filter?
Unfortunately, no such feature exists in Excel.
But you are not aiming to be ordinary in Excel. You are aiming to be awesome in Excel. That means, you don’t take no for answer.
Fortunately, we can quickly write a VBA macro that filters a list by selection. So let’s do that. Here is what you will learn to create:
Filtering a table by selected combination of values using VBA
What we need to achieve?
Our goal is simple. User (that is you) selects a range of cells depicting the conditions for filtering. Something like this.
After selection, we fire up the filtering macro and instantly our list is filtered.
We can select a single-range or multiple cells (using CTRL+select technique)
Just to keep things simple, let’s assume the data is always in a table.
Algorithm / Steps for the VBA macro
Whenever you attempt to write VBA code, it is a good idea to start by writing down the steps in plain English. This is called as algorithm. By writing down the steps, we force our mind to think clearly about the problem at hand and come up with best possible solution.
Here are the steps for filtering the table by selected combination
- Make sure user has selected some values in a table
- Check if more than one row is selected. If so, exit as we don’t want to filter based OR conditions, we just want to filter based on AND conditions.
- For each cell in the selection
- Find out the corresponding column number
- Apply filtering on the table for corresponding column number with the cell’s value
- Repeat for next cell
- Done
VBA code – Filtering based on selected combination
Here is the VBA code for filtering based on selected combination. First examine the code. Then, we will understand key segments of it.
Sub combinationFilter()
Dim cell As Range, tableObj As ListObject, subSelection As Range
Dim filterCriteria() As String, filterFields() As Integer
Dim i As Integer
'If the selection is in a table and one row height
If Not Selection.ListObject Is Nothing And Selection.rows.Count = 1 Then
Set tableObj = ActiveSheet.ListObjects(Selection.ListObject.Name)
i = 1
ReDim filterCriteria(1 To Selection.Cells.Count) As String
ReDim filterFields(1 To Selection.Cells.Count) As Integer
' handle multi-selects
For Each subSelection In Selection.Areas
For Each cell In subSelection
filterCriteria(i) = cell.Text
filterFields(i) = cell.Column - tableObj.Range.Cells(1, 1).Column + 1
i = i + 1
Next cell
Next subSelection
With tableObj.Range
For i = 1 To UBound(filterCriteria)
.AutoFilter field:=filterFields(i), Criteria1:=filterCriteria(i)
Next i
End With
Set tableObj = Nothing
End If
End Sub
How does the combinationFilter() macro work?
Checking if selected cells are inside a table
We start by checking if the selection is inside a table by checking if the Selection.ListObject is not nothing. (Aside: there is no direct way to ask if there is a listobject. So we ask indirectly, by saying Not Selection.ListObject Is Nothing.)
Once we know that Selection is inside a table, we grab the table object and set it to the variable tableObj.
Finding out what to filter
To set filters on a table, we need to know the field number (ie column number inside the table) and filter criteria.
Filter criteria is denoted by cell values in the selection.
We are extracting filter criteria values & determining the column numbers for each of the selection’s cells using a simple For Each loop.
Setting up the filters
Once all the filter criteria are determined, we simply loop thru the criteria and set the filters on table using tableObj.Range.AutoFilter method.
How to use this macro for your data?
This macro is designed to work with any table. I have tested it in Excel 2010 & Excel 2013 and it seems to work alright.
To use it with your data, follow below steps.
- Open your personal macros file
- Copy the combinationFilter() macro and paste it in your Personal Macros workbook in a module
- Save and close personal macros file.
- Add this macro to Excel ribbon or quick access toolbar (QAT)
- To add to ribbon: Refer to below picture.
- To add to Quick Access Toolbar – click here for instructions.
- To add to ribbon: Refer to below picture.
- Once you select the combination to filter, click on the ribbon / QAT button.
- Done!
Download Selected Combination Filter Macro
Please click here to download the example workbook. Play with the macro to understand it better.
New to VBA? Learn how to exploit its awesome power
If you are new to VBA, you might find above example both awesome & hard to digest. But don’t worry. Start with this simple crash course on VBA. Check out more VBA examples. Very soon you will be automating parts of your work and impressing your boss. All the best.
Do you find the combination filter useful?
When I first thought about this macro, I feared the code might be too long or confusing. But I am happy with the outcome. It is a crisp, simple and powerful macro that I can use often when working with lots of data.
What about you? Do you find this macro useful? How are you planning to deploy it for your work situations. Let me know in the comments area.
18 Responses to “Best Charts to Compare Actual Values with Targets – What is your take?”
Great post. I can't vote, though, because the answer I want to put down is "it depends". As with all visualisations, you've got to take into account your audience, your purpose, technical skills, where it will be viewed, etc.
I'm with Andy: It depends. Some I would use, some I might use, some I won't touch with a barge pole.
Naturally I have comments 🙂
The dial gauge, though familiar, is less easy to read than a linear type of chart (thermometer or bullet). It's really no better than the traffic lights, because all it can really tell you is which category the point falls in: red, yellow, or green.
By the same token, pie charts are so familiar, people don't know they can't read them. Remember how long it takes kids to learn to read an analog clock?
Bullet charts don't show trends.
With any of the charts that have a filled component and a marker or ine component, it makes more sense to use the filled component (area/ column) for target, and the lines or markers for actual.
[...] Best Charts to Compare Actual values with Targets (or Budgets … [...]
I voted for #6 even though I agree with the other comments that it depends.
The majority of the votes are for the #2, thermometer chart. I still have yet to understand what happens when you are above plan/goal, which was brought up in yesterday's post.
Also, I agree with Jon in that it would be better to flip the series and make the filled part the target or goal and the line or marker the actual.
I am also a fan of using text when appropriate if the data is among other metrics in a type of dashboard. Calling it out by saying actual and % achievement is a good option.
Another "it depends" vote. Are you just looking at one or are you comparing a number of targets with actuals? You didn't include a text box. The problem with sentences is that they can get lost in a page of gray text. A text box can call attention to the numbers and line them up effectively.
I'm with Jon: "Some I would use, some I might use, some I won’t touch with a barge pole" and I'm surprised that some of your readers voted for the last group.
Jon says:
With any of the charts that have a filled component and a marker or line component, it makes more sense to use the filled component (area/ column) for target, and the lines or markers for actual.
Why does this make more sense? I like 6 the way it is, although I would use a heavy dash for the plan/target marker.
"It depends" is also my take. What I usually try to drill into my clients dashboard design is the fu ndamental difference between spot results (am I on target for this month) and long term trends.. I always try to create 3 different set of graphs to represent real perormance:
- spot results vs objectives
- cumulative results vs objectives
- long-term trend (moving average) mostly) to see where we're going
[...] Best Charts to Compare Actual Values with Targets – What is your take? (tags: excel charts) [...]
[...] Related: Charting Principles, How to compare actual values with budgets [...]
[...] Excel Charting Alternatives to compare values [...]
Jon says:
With any of the charts that have a filled component and a marker or line component, it makes more sense to use the filled component (area/ column) for target, and the lines or markers for actual.
Why does this make more sense? I like 6 the way it is, although I would use a heavy dash for the plan/target marker.
I totally agree, Bob. I would normally favour a line for the target and a column for the actual, you can see quite easily then which columns break through the line, then.
[...] best charts to compare actual values with targets — den Status mal anders zeigen, z. B. als Tacho [...]
Thermometer charts: "Not appropriate when actual values exceed targets" - this is easily solved by making the "mercury" portion a different color from the border, then you can clearly see where the expected range ends and the actual values keep going.
People seem to knock gauges quite a bit in dashboarding, but trying to show comparison of realtime data between operating sites and targets for each site can easily be done with a bank of gauges that have the optimal operating points at 12 o'clock.
The human eye is great at pattern stripping, and any deviation of a gauge from the expected 12 position will quickly register with an operator and attract his attention. Using a colour background, or meter edge, will also indicate the sensitivity of a particular site.
[…] Best charts to compare actual with target values […]
[…] Best charts to compare actual with target values […]
[…] work laptop I have a favorites folder just dedicated to Excel charts. Its got things like “Best Charts to Compare Actuals vs Targets” and “Best charts to show progress“. I love me some charts […]
I am wondering how will the plotting work, for some of the targets which may have been achieved before time. E.g. for the month of Jul the target was 226 and the actual was 219. So the chart will show a deficit in meeting the target by 7 points but what if this 7 may have been completed earlier in month of June. So ideally it not a deficit.