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 @ contextures.com
Visit my Excel website for many more tutorials and videos.
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?
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.
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
NOTE: If there are duplicate amounts, this won't work reliably -- it returns the product name for the first matching amount.
The answer is no, the PTB tools create lists with field names, and let you use those lists to create and update pivot tables quickly. It doesn't have any of the pivot table management features that are in the PPP add-in. (Note: The next version of PPP will have pivot table building tools too.)
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)
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:
Note: I am an affiliate for some of the products mentioned in this newsletter, and earn a commission on the sales.
Last updated: June 14, 2017 11:47 PM