Excel Filter Trick
September 10, 2019
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
Did you know that VLOOKUP is Excel's 3rd most-used function? Do you use it in your daily Excel work? Well, there's a new lookup function in Excel - XLOOKUP - and it's designed to replace both VLOOKUP and HLOOKUP.
The old functions need 4 arguments, but you'll only need 3 in XLOOKUP - what to look for, where to look, what to return. It's similar to SUMIF, where you select a range to check for criteria, the criteria, and the range to sum. (There are also 2 optional arguments in XLOOKUP - match mode, and search mode.)
For now, XLOOKUP is only available if you're in the Office 365 Insiders Program. To learn more, read Microsoft's official announcement. Or, watch Jon Acampora's video that shows how XLOOKUP works, and how it compares to VLOOKUP and INDEX/MATCH.
How can you filter for a date range, in a named Excel table? The table heading arrows have built-in date filters, but those don't let you refer to start and end date cells on the worksheet.
However, there's another type of filter in those headings - Filter by Color. Thanks to a tip from UniMord, I learned a cool trick for using that option!
First, I set up a conditional formatting rule that compares the date in the table's first row (B2), to the start date (G2) and the end date (H2).
Instead of using a fill colour, the Conditional Formatting rule will change the font colour slightly, if the date is within the set date range. (I used Black, 5% Lighter))
The formatted dates won't look different on the worksheet (not to me anyway!), but Excel recognizes the difference, and will show that colour in the Filter by Color options.
Here are a couple of Excel articles that you might find useful or interesting.
Baseball: Do you like baseball? And statistics? A Cleveland fan analyzed the team's performance, and you can download an Excel version of the file, to see how it works. (Level - Int)
Errors: In a short video, Ben Zorn, from Microsoft Research, shows a couple of tools that help find errors or structures in a spreadsheet. There's more information about ExceLint on GitHub. (Level - Int/Adv)
School started last week, and I helped my granddaughter shop for back to school supplies. When I asked her what we should check for, in a new backpack, she said it should be big, have lots of pockets, good straps, and be cute. (That last criterion was heavily weighted!)
She made excellent choices for her new backpack and lunch bag, and I got a few things too. You're never too old for new pencils in September!
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2019/20190910ctx.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 6, 2019 4:20 PM