Excel Table Slicer Trick
September 24, 2019
Pivot field list tips, Slicer trick for partial matches and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
When a pivot cell is selected, you'll usually see pivot table field list, at the right of the Excel window. Instead of leaving the default settings, you can select a different field list layout, move it to a new position, and change the list order.
Unless the field names are really long, I find that the 2nd layout option works best, with the fields and areas side by side
You can also change the order of the field names. By default, they're in the same order as the columns in the source data. If there are lots of fields, it might be easier to find them if you sort A-Z.
Go to my Contextures site to see more Pivot Table Field List tips, and to watch a short video.
It's easy to filter a column with a Slicer, if there's only one item in each cell. But if there are multiple items in the cells, the Slicer can only find exact matches.
I've uploaded a new sample file that shows how to filter with a Slicer, and match a specific item, anywhere in a cell. In the animated screen shot below, any cells that contain the selected weekday name are filtered.
The Filter column has a formulas that uses 3 functions - SEARCH, ISNUMBER and OR.
In the formula:
The Slicer is connected to a small pivot table, which is based on a weekdays table. When the pivot table is updated by the Slicer, a bit of code runs, to filter the main table, and only shows the TRUE rows.
Go to my Contextures site, to get the sample file, and to see how to set up drop down lists that allow multiple selections. Or, get my Data Entry Popup kit. Someone who bought it last week said it was the best $20 they ever spent!
Here are a couple of Excel articles that you might find useful or interesting.
Excel Tips: Here are 5 Excel tips and tricks that might help you. Do you use the IFS function in your work yet? (Level - All)
New Features: If you have Excel in an Office 365 subscription, new features are added occasionally, based on which version you selected. At work, you might get a semi-annual update, but if you want all the latest features quickly, you'll need the Insider version. Chris Newman explains how to switch. (Level - All)
Power BI: Matt Allington is hosting a free webinar on Oct. 2nd- Introduction to DAX for Power BI, targeted to DAX beginners. (Level - Int/Adv)
Our grandson came to visit on the weekend, and on our shopping trip, he bought this coffee mug for his mother -- she's a big Harry Potter fan! The mug has good advice - "Don't let the Muggles get you down". In Harry Potter books, Muggles are non-magical people. I think that means people who don't know how to use Excel!
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2019/20190924ctx.html
I'll also post any article updates or corrections there.
That's it for this week! If you have any comments or questions, send me an email.
Last updated: September 23, 2019 3:49 PM