Contextures News

Slicers Keep Excel Data Safe

Apr 5, 2016

Use Slicers to keep people away from your data, and more, in this week's Excel news. If there are topics that you'd like to see covered in future newsletters, please let me know.

 -  Debra -  ddalgleish @
     Visit my Excel website for many more tutorials and videos.

Slicers to Filter Data

If you have your data in an Excel table, there are filters in the headings, and you can use those to see specific data. If other people are using the file too, you might want to keep them away from the data, so they don't mess up the data or formulas that you worked so hard on!

To help keep your data safe, put Slicers on a separate sheet, and use those to filter the data. Then, click a button, to dump the matching records onto that Slicer sheet. In this example, there are only 5 columns for the output, so nobody is overwhelmed by too much information. Want to see different fields? Choose them from the drop down lists. Go to my site to download the sample file, and see how it works.

Do you have any workbooks where you could use this technique? Any questions about how it works?

Find the Lowest Amount MIN IF

Last week, someone asked me how to show the lowest amount for different categories, and details for that low record. For example, show the lowest order in each region, and the product sold in that order.

To get the lowest value, use a MIN IF formula, or a pivot table. Then, to see the connected data, use an INDEX/MATCH formula in the next column.

In the screen shot below, the low amount is in cell G2. The formula in H2 finds that amount in the Amt column, and returns the product name from that row:

NOTE: If there are duplicate amounts, this won't work reliably -- it returns the product name for the first matching amount.

Excel Articles

Here are a couple of Excel articles I read recently, that you might find useful.

Excel Shortcuts -- On the LifeHacker blog, there's a list of 8 useful Excel shortcuts. Which ones do you use? If you use tip #5, remember to ungroup the sheets when you're done! And for tip #7, a little popup appears while you drag down, and it shows the number of rows selected, so you don't have to keep track. (Level - Basic)

Power Query -- If you're still confused about Power Query (like most people!), Matt Allington clears things up in his post, "What is Power Query?" The article is clearly written, and describes what Power Query can do, and how it can help you. Matt also explains how you get it in Excel 2010/2013/2016.
If you're using Power Query, and need help, Ken Puls just started a forum where you can ask questions. (Level - Intermediate)

More Excel Articles -- Visit my Excel website for many more tutorials and videos. Or watch my very short video on inserting rows.

In the Garden

It was a bit warner on the weekend, so we checked out the garden, to see if there were any signs of life. The first thing we saw was a bunny, but it dashed off very quickly. The rhubarb is sprouting, along with a few of the hardier plants, so we're off to a good start. In the photo, my grandson is looking at the little dish that we leave water in, for the butterflies and bees. It's still frozen, so I hope they don't need it too soon!

NOTE: If you have any problems with the links in the email, you can see this newsletter on my website -- copy this link and paste it into your browser:

Recommended Excel Products


Note: I am an affiliate for some of the products mentioned in this newsletter, and earn a commission on the sales.






Get weekly Excel tips from Debra






Last updated: February 4, 2021 3:10 PM