Contextures

Contextures News

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!

Make a Pop Up Slicer

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.

Pop Up Selector With Slicer

Find Last Text Item

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:

=REPT("z",255)

That's not too useful, but you can combine that with VLOOKUP to find the last text item in a column.

=VLOOKUP(REPT("z",255),D1:D7,1,TRUE)

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.

find last text item in a list

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.

Error Checking

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:

  1. online videos, with downloadable Excel workbooks
  2. online and downloadable videos, with downloadable Excel workbooks

Excel Articles

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! To learn the basics, get my UserForms for Data Entry kit. (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)

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

Something Sour

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.

weekly photo

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: http://www.contextures.com/newsletter/excelnews2017/20170725ctx.html

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.

 

Search Contextures Sites

Excel Data Entry Popup List

 

 

 

 

Last updated: July 21, 2017 2:40 PM