April 2, 2019
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
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.
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)
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.
or, you could refer to a letter typed in cell B2:
=VLOOKUP("*" & B2 & "*",E1:F9,2,FALSE)
There are more VLOOKUP examples on my Contextures website..
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.
To get the latest version, use the download link from your original purchase, or email me, if you need a new link.
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)
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?
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.
Last updated: February 4, 2021 2:22 PM