Make a Pop Up Excel Slicer
July 25, 2017
Slicers take up lots of room, so make a pop up, and more, in this week's Excel news. Visit my Excel website for many more tips, tutorials and videos. We're on our summer schedule now, so the next newsletter will arrive on August 8th.
Note: For some products mentioned below, I earn a commission on sales. That supports the free info on my site!
Slicers make it easy to select items from a list, but they take up lots of room on a worksheet. It's hard to find a spot to put them, without covering up your data.
AlexJ found a way to make Slicers pop up, only when you need them. Just click a small button on the worksheet, select what you need from the Slicer, and then the Slicer disappears again.
You can get all the details on the Pop Up Slicer Selector page of my website. There are written steps, videos to watch, and a workbook to download too.
Do you ever use the REPT function in Excel? It repeats a text string a specific number of times. For example, use this formula to get 255 z characters in a cell:
That's not too useful, but you can combine that with VLOOKUP to find the last text item in a column.
The VLOOKUP function looks down column D for the long "z" string, but doesn't find it. Because we asked for an approximate match (TRUE), it returns the last text item in the list instead.
Go to my Contextures blog, to see more examples of using the REPT function.
Mini-Challenge: What formula would return the last item in the list (text or number)? My formula is below the screen shot.
Use this formula to find the last item (text or number): =LOOKUP(2,1/(D1:D7<>""),D1:D7)
There are more LOOKUP examples on my blog.
I've wasted countless hours looking for the errors in Excel files, and maybe you have too. Sometimes, everything is working smoothly, then somebody makes a little change, and "Boom!"
In his new course, Ken Puls shows how to build an error checking system in your workbooks. See how to create an error checking section on each worksheet, and a global check point for the entire workbook. The system even checks the pivot tables, to see if they've been updated -- I've been hit by that problem!
I was surprised by the low cost of Ken's course -- it's a small investment that could save you hours of headaches! There are two versions of the course:
Here are a couple of recent Excel articles that you might find useful or interesting.
Excel UserForms - Jon Peltier shares some fancy tricks for drop down buttons on a UserForm, like the features he has in his Excel Charting Utility. You can even put a button on a Text Box! (Level - Programming)
Charts - Ann K. Emery shows how she transformed a simple table into 4 charts that each tell a different story about the data. She also explains why she made the changes, which is very helpful. (Level - All)
We've had a clump of rhubarb in our garden for many years, and this spring I moved it to a different spot. The stalks didn't get as tall as usual, but I picked enough to make some stewed rhubarb. And don't worry -- the tomatoes didn't go into the cooking pot! Not too many people like this sour fruit, but I love it.
That's it for this week! Watch for the next newsletter in 2 weeks.
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/20170725ctx.html
ddalgleish @ contextures.com
Last updated: February 4, 2021 2:22 PM