Contextures News 20190910

Excel Filter Trick

September 10, 2019

New lookup function, table filter trick, 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.

New Lookup Function

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.

Filter Trick

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).

=AND(B4>=\$G\$2,B4<=\$H\$2)

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.

To download the Filter for Date Range workbook, go to the AutoFilter page on my Contextures site. There's a video there too, that shows all the steps.

Excel Articles

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)

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

Back to School

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!

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

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.

Last updated: September 6, 2019 4:20 PM