Contextures

Contextures News 20190924

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.

Pivot Field List

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

sort order for pivot table fields

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.

arrange pivot table field list

Go to my Contextures site to see more Pivot Table Field List tips, and to watch a short video.

Multi-Item Filter

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.

slicer for multi-select cells

The Filter column has a formulas that uses 3 functions - SEARCH, ISNUMBER and OR.

  • =OR(Lists!$H$1="(All)", ISNUMBER(SEARCH(Lists!$H$1, [@WorkDays])))

In the formula:

  • SEARCH function looks for the selected weekday name, and if found, returns a number - the starting position in the cell's text. If the text isn't in the cell, an error is returned.
  • ISNUMBER returns TRUE if the search result was a number
  • OR function returns TRUE if the pivot table is showing "(All)" OR the search result was a number

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.

pivot table for slicer

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!

Excel Articles

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)

Also see: My Excel Products || Previous Issues || Excel Twitter

Muggles

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!

weekly photo

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.

Debra Dalgleish
dsd@ contextures.com

Debra Dalgleish

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.

 

 

Get weekly Excel tips from Debra

 

pivot power free

 

 

 

 

 

Last updated: September 23, 2019 3:49 PM