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.
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.
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!
If you plan to protect the worksheet, follow these steps too:
Read more about Slicers, and watch a few videos, on my Excel Slicers page, or get some Slicer Macros.
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
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.
Last updated: July 21, 2017 9:40 AM