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.
23 Responses to “Displaying Text Values in Pivot Tables without VBA”
Its possible to display up to 4 text values.
Have a look at the screen shot of an example that I had posted way back at the EHA and figure out how its done !
http://tinypic.com/r/muzywk/6
With Excel 2010 you can use Conditional Formatting to apply custom number formats which can display text. (In older versions you can only modify text color and cell background color, but not number formats.) Using CF allows for an even larger number of different display values.
[...] Display text values in Pivot Tables without VBA [...]
Hey,
Thanks, this helps. But how do you do it for multiple values where there is a huge amount of non repeating text?
@Soumya
The only way to do more than 4 values is to make the Pivot Table manually with formulas, of course then it isn't a Pivot table
You can of course do it with VBA
You may want to have a look at this description of how to do it here: http://www.clearlyandsimply.com/clearly_and_simply/2011/06/emulate-excel-pivot-tables-with-texts-in-the-value-area-using-vba.html
@Soumya
The only way to do more than 4 values is to make the Pivot Table manually with formulas, of course then it isn’t a Pivot table
You can of course do it with VBA
You may want to have a look at this description of how to do it here: http://www.clearlyandsimply.com/clearly_and_simply/2011/06/emulate-excel-pivot-tables-with-texts-in-the-value-area-using-vba.html
[...] Pivot Tables take tables of data and allow the user to summarise and consolidate the data at the same time. This is a great and very fast method of analysis but is restricted to handling mathematical functions on the value field resulting in numerical summaries. – read more [...]
[…] Read more here: Displaying Text Values in Pivot Tables without VBA […]
There is a very good way actually for handling text inside values area.
First you create a special column on the very left side and call it ID, and put unique ID (numbers only), and then create a pivot table with:
Row Labels and Column labels as you like, and in the Values labels use the unique ID number.
Move the unique ID number (copy paste) somewhere to the right and use vlookup to load the data you need using the ID as reference.
It is a bit longer way but for me it works perfectly to combine values as you like in any moment.
hope helps.
Regards,
Jon
Thank you! I finally understand pivot tables thanks to your clear, concise explanations and examples.
Good Day. This is exactly what i have been looking for. However when i try it on my pivot table or even when i try to recreate this exercise using the sample worksheet, i get this error:
"Microsoft Excel cannot use the number format you typed. Try using one of the built-in number formats."
Same thing here, Excel quite did not like the format in my PowerPivot. Any clues as to what may be going on? Thanks.
I have the same thing happening on my end. I'm running a normal pivot table on a .xlsm file.
@Danzi
What format did you use?
can you post the file ?
pls. help in table there is name, pan. amount. i have to make pivot table for example
NAME PAN AMOUNT
MR.X AAAAC1254T 500.00
MR.Y AAABR1258C
MR.A CFVDE2458T
MR.Z AAVCR12548C
MR.X AAAAC1254T
MR.Z AADCD245T
pls. help in table there is name, pan. amount. i have to make pivot table for example
NAME PAN AMOUNT
MR.X AAAAC1254T 500.00
MR.Y AAABR1258C 1000
MR.A CFVDE2458T 2000
MR.Z AAVCR12548C 5451
MR.X AAAAC1254T 45564
MR.Z AADCD245T 4500
how to get pivot tabe so i get PAN no. against Name.
I found an easy way to get text values in pivot table.
I create an other worksheet in wich each cell has a formula that copy the pivot table. The trick is that the formula does a lookup for the numbers in the pivot table.
The formula looks like that:
=IF(ISNUMBER(table!A1);VLOOKUP(table!A1;Code!$A$1:$B$65;2);IF(ISBLANK(table!A1);" ";table!A1))
Code is a worksheet where there is a liste of text /numbers correspondance.
As a bonus The new sheet is easier to format
Additional trick:
In my case, i encoded differents codeid with a power(2, codeId-1) so that summing then is equivalent to concatenate them.
1-A
2-B
4-C
8-D
yields :
5 - AC
14 - BCD
Hi
I want to ask if pivot can display dates in pivot field. As in a column i have customers and in row different items i want to know there last purchase date. anyone help in this??
Hello Guys, Need your help
I am doing some analysis of the cycle time of the product i.e how much time a product takes from manufacturing to the central warehouse.
I have batch numbers for the product and against them i have to pull out the diff. dates
Like the base date is from where the manufacturing start. So i have the batch number,against it's manuf. date. Now i have to pull out the date when it was quality released.
I have the quality released data but the data have duplicates, like i will have two dates or may be three for the same batch. So my main objective is to pull out the date which is latest among them.
BATCH NO. DATE of Mfg. DATE of Quality release
A1 12/4/2014 (HERE I HAVE TO PULL value)
Next Sheet
BATCH NO. DATE of Quality Release
A1 14/5/2014
a2 23/5/2016
A1 12/5/2014
A1 13/6/2014
From this sheet i have to pull up the latest date format of date here is dd/mm/yyy
TIA
[…] needed to present text instead of counts in a pivot table value column. Here is an excellent resource for Excel manipulation, in addition to an overview of pivot […]
This is great thank you.
Wow!!! Excellent!! It helped me a lot.
I am developing training tracking sheet for 200 employees with training completed date. Each employee will be attending 25 courses. How to indicate actual dates in pivot table value field.