Contextures News

Excel Slicer Trick

May 23, 2017

Sneaky Slicer trick, and more, in this week's Excel news. Visit my Excel website for many more tips, tutorials and videos -- let me know if you're looking for something and can't find it there.

Hide Used Items

You can add a drop down list in a cell, to make it easy for people to enter data. It's really easy to do that with Data Validation. Then, after you've made a basic list, you can experiment with fancier features, like hiding items that have been previously selected.

That can prevent problems if you're creating a schedule, for example, and shouldn't be selecting any employee twice for the same workday. There are other examples, and details for setting this up, on the Hide Used Items page of my Contextures site.

The last example -- choose printers -- is the simplest one. After you select a primary printer, you can't choose that as one of the backup printers -- it's not in the Backup list.

Slicer Trick

Slicers were added to Excel in 2010, and they're a great way to filter a pivot table. If you have Excel 2013 or later, you can also use Slicers to filter a named Excel table.

After you've set up your Slicers, and carefully positioned them, you might want to change a couple of settings on your Slicers, to keep things running smoothly.

Try this trick, to prevent people from moving the Slicer (even if the sheet is not protected). Most people won't know why they can't move it!

1. Right-click on a Slicer, and click Size and Properties
2. In the Format Slicer pane, click the triangle at the left of Position and Layout, to see all the options
3. Add a check mark to "Disable resizing and moving"

If you plan to protect the worksheet, follow these steps too:

1. In the Format Slicer Pane, go down to the Properties section
2. Remove the check mark from "Locked"
3. Then, when you protect the sheet, be sure to add a check mark to "Use PivotTable & PivotChart"

Read more about Slicers, and watch a few videos, on my Excel Slicers page, or get some Slicer Macros.

Excel Articles

Here are a couple of recent Excel articles that you might find useful.

Functions - Jon Wittwer, from the Vertex42 site, asked several Excel experts for a list of their favourite functions. There are a few top choices, and a wide range of other functions mentioned. Are your favourites in the lists? Just for fun, I made a workbook with a list of all the votes, and a pivot table summary. (Level - All)

Excel Song -- If you want to entertain (or annoy) your co-workers, blast the latest Excel song from professor Clint Tuttle. He does this at the end of each school year, and you can hear more on his YouTuble playlist. My favourite is "Excel Funcs" (Level - All)

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

Spring Blossoms

We've had great spring weather recently, and the trees are covered in beautiful blossoms, like these lilacs (sorry you can't smell them!) It's officially our planting season now too, so I'm heading to the garden centre today to get some annual flowers. There might not be much left, after all the shoppers on the long weekend (Victoria Day in Canada).

That's it for this week! If there are topics that you'd like to see covered in future emails, please let me know.

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/20170523ctx.html

Debra Dalgleish
ddalgleish @ contextures.com

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.

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