Contextures

Contextures News 20190402

VLOOKUP Tricks

April 2, 2019

Style problems, VLOOKUP tricks, 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.

Style Problems

Instead of using Excel's built-in styles for named Tables and Pivot Tables, you can create your own custom styles. Set up a style with your company's official colours, then use that for all your reports.

  • If you can find a built-in style that's close to what you need, right-click on it, and choose Duplicate.
  • Then, select elements in the list, and click Format, to modify the settings.
  • When you're done, click OK, then apply your new style to the table -- it's not applied automatically.

click buttons to collect data

But here's the problem with Custom Styles - if any sheet in the workbook is protected, you can't modify or delete a Custom Style. You'll need to unprotect all the sheets first - you can see a list of protected sheets if you click the File tab, then click Info. (Thanks to UniMord for that tip)

click buttons to collect data

Learn more about Custom Styles on my Contextures site. There are videos and written steps for creating and copying styles. There are tips for copying styles to a different workbook on my blog.

VLOOKUP

Were you ever afraid of VLOOKUP? In March 2012, Bill Jelen (Mr. Excel) hosted a VLOOKUP Week, which actually went on for a couple of weeks. He chose a shark as the theme, because "Excel beginners are afraid of VLOOKUP".

Several people contributed articles, and mine was about shark attacks in the USA. Choose a state from a drop down list, and VLOOKUP returns the number of shark attacks there.

One of my favourite tricks was from Charles Williams, who showed how to use wildcards in a VLOOKUP. For example, this formula finds the first name that contains an "n", and returns that person's ID number.

=VLOOKUP(“*n*”,IDNumbers,2,FALSE)

or, you could refer to a letter typed in cell B2:

=VLOOKUP("*" & B2 & "*",E1:F9,2,FALSE)

vlookup wildcard

There are more VLOOKUP examples on my Contextures website..

Product Update

There's a new bonus in my Excel UserForms for Data Entry kit -- the UserForm Builder add-in. Select a cell in an Excel Table, and the UFB add-in will help you build a simple UserForm, based on that table. Watch this 5-minute demo video, to see how it works.

To get the latest version, use the download link from your original purchase, or email me, if you need a new link.

Excel Articles

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

Functions - Chandoo shares 10 Excel formulas that you can use in any situations. Some of the functions are only available in Office 365. (Level - Intermediate)

Chart - Kaiser Fung shows how to simplify chart labels, to help get your message across. You don't need to pack each label with data! (Level - All)

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

More Snow

Well, I shouldn't have complained about the snow in last week's newsletter, because we had another snowfall on Sunday. It was only a couple of centimetres, but it coated everything, like one of those old spray-cans of fake snow. Do you remember those? Did they contain asbestos, like the fake powdered snow?

weekly photo

NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2019/20190402ctx.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
dsdalg@ gmail.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: April 1, 2019 6:51 PM