Contextures News

Are your Excel Slicers this awesome?

Oct 31, 2017

Awesome Excel Slicers, bad jokes, and more, in this week's Excel news. Visit my Excel website for many more tips, tutorials and videos.

Note: For some products mentioned below, I earn a commission on sales. That supports the free info on my site!

Slice and Dice

It's easy to add a Slicer that filters a pivot table (and Excel Tables too, in 2013 and later). But don't just drop the Slicer on the worksheet -- take a couple of minutes to fine-tune it.

First, change the appearance, so it looks like a pro built it. Mike Alexander has great examples and a sample file (download link at the very end of his article). The first example is my favourite -- the Slicer looks like tabs at the top of a pivot table.

Next, change the options and settings, so the Slicer is easier to use. For example, sort years Z-A, so the most recent years are at the top. Right-click on the Slicer, to see the available commands.

Read more about Slicers on my website, and download my Best Films workbook too. It has the data from my article about Halloween Slicers.

Lookup First and Last

After this month's lookup challenge, Jim S shared a handy VLOOKUP formula that he uses, to pull data from another worksheet. Sometimes you want to keep people away from the data, so they don't mess things up! I made a demo with my sample data, so you can see how it works.

On a report sheet (shown below), create 2 VLOOKUP formulas. The first one uses FALSE in the 4th argument, and it returns the First instance of the selected Customer name:

• =VLOOKUP($D$3,Table1,9,FALSE)

The second formula uses TRUE (or leave out the 4th argument) -- it finds the Last instance:

• =VLOOKUP($D$3,Table1,9,TRUE)

NOTE: These formula work correctly if the sales data is sorted by Customer name.

To see this example, download the VLOOKUP Sample workbook, and go to the FirstLast sheet. Thanks Jim!

Excel Articles

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

Excel Charts - Dan Kopf, on the Quartz site, wants you to use a simple table sometimes, instead of a chart. Do you agree that charts are overused? (Level - All)

Power BI - To get started with Power BI, or to improve your skills, download a free copy of Reza Rad's book, Power BI From Rookie to Rockstar. Or, read the book online -- scroll down about halfway, to see the table of contents. That should keep you busy for a while! (Level - Int/Advanced)

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

Bad Jokes

My 10-year-old grandson and I love to share bad jokes and goofy riddles. So, when I saw the Star Wars sticker on these bananas, I sent him a picture with this caption (hold your nose before you read it):-
"It looks like these bananas are filled with Vitamin C-3PO."

If you have any goofy jokes that are suitable for kids, please let me know. Maybe we can make an Excel file that pulls up a random kids' joke when you click a button.

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

NOTE: If you have trouble with the images or links in this email, paste this URL into your web browser, to see the online version: https://www.contextures.com/newsletter/excelnews2017/20171031ctx.html

Debra Dalgleish
ddalgleish @ 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.