Contextures

Contextures News 20180213

Excel Formula Mystery

February 13, 2018

Solve the lookup formula mystery, 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 supports the free info on my site!

Quick Filters

In a pivot table, you can use the heading drop downs, to show or hide items. For example, select a couple of products to show, and leave the rest hidden.

pivot filter heading drop down

Instead of using that drop down list, it's sometimes quicker to use right-click menu:

  • In the pivot table, select one or more of the row items
  • Right-click on one of the selected items
  • In the popup menu, click Filter
  • Click one of the options to Keep or Hide the selected cells

pivot filter pop up menu

See more pivot table filter tips on my website. And if pivot tables are a big part of your job, my Pivot Power Premium add-in can help you save time.

Lookup Mystery

Someone sent me a workbook in which a simple VLOOKUP formula was returning #N/A errors, instead of the correct results. The product numbers looked the same, but Excel didn't match them in the lookup.

If you love an Excel challenge, download the sample file, and see if you can fix the lookup formula - it's a tricky one! There are VLOOKUP troubleshooting tips on my site.

Or, scroll down, to see how I fixed the formula.

vlookup price

The problem: After checking all the usual suspects, I found that the codes in the lookup table had hidden characters at the start and end. Maybe the data was copied from a file in a different language.

The fix: In cells E6 and F6, the LEFT and RIGHT functions return those hidden characters. Then, combine those with the product number in the VLOOKUP formula in cell E2, to get the product name:
=VLOOKUP(E6 & D2 & F6, $A$2:$B$6,2,0)

To see more detail on the problem and fix, download the sample file.

vlookup price

Thanks to Mohit Kejriwal for sending this question.

Excel Articles

Here are a couple of Excel articles that you might find useful or interesting.

Charts - Jon Peltier shows how to work with blank cells, or #N/A cells in Excel charts. There are details for older versions of Excel, and the new setting in Excel 2016. (Level - Intermediate)

Dashboards - Closing Thursday, Feb. 15th - There's only a little time left to sign up for Mynda Treacy's highly-rated dashboard course. Click here to see details for the Excel Dashboard Course or the Power BI Course. (Level - Intermediate/Advanced)

Odd Facts - On the Data Rails blog, they posted "6 Cool Facts About Excel", but I can't find any proof of the 2nd (names) or 3rd (toolbars) items. Mike Alexander also mentioned those facts in an old post, and there's some discussion in the comments, but no links to a source. (Level - All)

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

I See Faces

It's a sure sign that winter has gone on too long, when you start to see faces in a bowl of Habitant Pea Soup! I put in a few dashes of hot sauce, and suddenly it looked like a Picasso painting. We enjoy the canned version of this French-Canadian soup, but Aube Giroux shares her mother's recipe -- she even grew special peas for it!

weekly photo

That's it for this week! If you have any comments or questions, send me an email.

NOTE: For the online version, paste this URL into your web browser: http://www.contextures.com/newsletter/excelnews2018/20180213ctx.html
I'll also post any article updates or corrections there.

Debra Dalgleish
ddalgleish @ 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: May 4, 2018 3:29 PM