Contextures

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 @ contextures.com
     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:
=INDEX(Table1[Product],MATCH(G2,Table1[Amt],0))

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

Your Feedback

Thanks for your emails about last week's article on my new add-in - Pivot Table Builder (PTB). Someone asked if has features similar to my Pivot Power Premium (PPP) add-in.

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.)

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:

www.contextures.com/newsletter/excelnews2016/20160405ctx.html

Recommended Excel Products

  • Pivot Table Builder: Use the Pivot Table Builder add-in to quickly create or update pivot tables. You can store multiple lists with pivot table layout settings in your workbook. Quickly create and store those lists, then apply them to any existing pivot table in the workbook, or create a new pivot table.

________________________

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

 

 

 

Search Contextures Sites

Excel Data Entry Popup List

 

 

 

 

Last updated: June 14, 2017 11:47 PM