Excel Slicers, Inside Out - includes workbook with step by step instructions

hi I'm Linda Tracy from my online training hub in this video we're going to get you up and running with Excel slices and once you see them in action you'll wonder how you lived without them now I've created an excel file that you can download and keep and that's got step-by-step instructions for everything covered here so don't worry if I cover things quickly because I've taken notes for you and you're welcome to share that file with your colleagues too now there's a link under the video where you can download the workbook and I'll put it up at the end of the video as well okay so slicers are available for pivot tables in Excel 2010 onwards and they also work with tables from Excel 2013 onwards now slices allow you to quickly and easily filter tables and pivot tables with just a click or click and drag of buttons and they float above the grid they're objects so they're easily movable and aligned and they're not tied to the cells on your worksheet they visually indicate items that are selected as you can see here and if I select bikes you can see the subcategory now has these grayed out buttons which indicates data that's not available for that particular category we've got scroll bars that enable you to scroll through the list and they're inserted by default based on the size of the slicer slices the pivot tables can filter more than one pivot table as you can see here I've got a pivot table here and one right beside let me zoom out a little bit so you can see it easier so as I select States you can see both pivot tables respond based on the selections that I've made in the slices and also notice that as I continue to filter the slice of selections are additive so I can drill down into my data and filter out all the noise that I don't want to see by continually selecting more and more buttons in my slices now it slices the tables first of all you need to format your data in a table so if it's not formatted in a table select a cell in the table range or all of the data and then control-t will insert the table formatting and now you can apply a slicer to that table so inserting slices is pretty quick and easy and as with most things in Excel there are lots of ways to approach this one option is with the table or pivot table selected on the contextual tab you can see here I've got insert slicer if I'm on a pivot table I also have the contextual tab and you'll see insert slicer but you'll also find the insert slicer on the insert tab first of all make sure you've got a pivot table or table selected that you want to insert a slicer for click on the slicer button you get the list of all of the fields and from here we can select multiple slices to insert and then click ok I can move them around I can click and drag the outer edge to resize them and if I choose not to use that slicer with it selected on the outer edge I can press the Delete key and get rid of them now the other way to insert a slicer that is available in Excel 2013 onwards is in your field list you can right-click a field and then add a slicer and that will give you one slicer at a time you can't insert multiple slices with that method I'll delete that one now the great thing about using slices is they're quite intuitive so as you saw we can left-click a button we can left-click and drag to select multiple contiguous buttons or items we can left-click and then hold down control to select non-contiguous items or we can left click hold down shift and then click the last to select a range of items we have this clear filters button at the top in Excel 2016 we have this multi select option here and this changes the behavior of the single button click so that each click ads or D selects an item and this is really ideal for touchscreen use in Excel when you're using it on your desktop it's more intuitive to leave multi-select off but that is an option for Excel 2016 so let's look at our formatting options for slices with a slice of selectors we get the contextual slicer tools tab and here we can change the caption on the slicer we can access the slicer settings which I'll cover in a moment and report connections also in a moment we can set our slices style so maybe we want them to be a different color and as a whole gallery that we can choose from we can even create our own slice of style because these are objects we have the arrange tools so we can align slices if you hold down shift and select multiple slices then you can use the align tools you can also group them so they're easy to move around as though they're one object I'll undo that so I can make some more changes and show you some more of the settings we can change the number of columns so you can see my year slicer has two columns and I can increase that as I want we can also alter the bottom height and the width of the slicer buttons we can alter the overall height using the height adjuster and the width or we can just use up pull handles to roughly size them how we want we can cut them and move them so the slicer doesn't have to be on the sheet set your pivot tables on so with the slicer selected I can control X to cut it out and I could pop it on a completely different sheet I could still use it here if I select 2015 and go back to my pivot table you can see my data is now filtered for 2015 so slices are independent of the sheets that the pivot table is actually on let me pop it back in here we can also hold down alt when we're moving it around and that will snap the slicer to the grid and align it to the cells underneath or if we hold down shift when we move the slicer then it will stay aligned vertically or horizontally depending on the direction you're moving it in another great shortcut is if you want to select all slices with one selected control a will select them all now the control a will select all objects on your sheet so if you have other charts or images then control they will select them as well so may not always be suitable but it is a shortcut if you're just working with slices and you might want to select them all and change them all to a particular color in one go now another thing I like to do when I'm producing a report for other users is to set the properties of the slicer so that when you click on it it doesn't activate the pull handles if you click the buttons it's okay but if you miss that button the pull handles on the slicer become activated and it looks like it's in edit mode so to prevent that from happening you can right click and then go into siphon properties and then under position and layout select disable resizing and moving you can see the pull handles are now gone I can still work with the slicer but now if I click the outer edge I can't move it doesn't look like it's in edit mode so let me undo that because I want to show you how to manage the slicer connections so as I said slicers can connect to more than one pivot table and that means you can control the filtering on multiple pivot tables with a single slicer to control the pivot tables that that slicer is connected to we can right click on the slicer and then go into report connections if you're using Excel 2010 then this will say pivot table connections but it's in the same position in this list so in here I have a list of all the pivot tables in my file and the sheets that they're on and I can check the boxes for the pivot tables that I want this particular slicer to filter so if I wanted it to also filter the slices on the insert slicer sheet then I can check those boxes now it's always a good idea to give your pivot tables recognizable names and that will help you identify them when you're working with the report connections so try and avoid the generic pivot table default names if you can an easy way to rename your pivot table is to right-click on any cell and then going to pivot table options and up here you can pop in a new name you'll also find that on the pivot table tools tab in the pivot table name box up here now if a pivot table isn't in your report connections list and you'd expect it to be then it will suggest that the problem with that pivot table is it doesn't share the same pivot cache as the pivot table that this slicer is connected to so a PivotTable cache is created when you insert a pivot table Excel takes a copy of the data and holds it in memory and that's called the pivot cache in order for a pivot table to share the pivot cache of another pivot table it has to use that same source data so if you have two different sheets with different data on it and you've created two pivot tables from each of those sheets then those pivot tables won't share the same pivot cache and you can't use one slicer to control them the only way to do that is to use PowerPivot or the data model to create one cache so if your pivot tables not in this list and you'd expect it to be because it shares the same source data then you'll need to recreate that pivot table that's missing from the list and the way I suggest to do that is to copy an existing pivot table that is in your list of connections and then modify that that will ensure that they share the same pivot cache now in the file that you can download with this video I've got some more information on the pivot cache you can learn about that now last thing I want to cover are the slicer settings so let's right-click the slicer and go into slicer settings here we can change the name for the slicer although it doesn't really have much effect it doesn't it's not visible in the slicer itself the name up here on the slicer header is altered here so we can change the caption or we can remove their header altogether and if I remove that header then the caption the multi select tool and the clear filters will be removed so you might want to consider that carefully because not being able to remove the filters in one go may be inconvenient if you have lots of items in your slicer the other thing we can do is alter the sort order and if you want to sort them in a particular order other than alphabetically then you can use a custom list to sort them here we can hide items with no data so let me click OK there you can see my head is gone but if I select an item in this slicer you can see my items have no data are grayed out so if I edit my slicer settings for this slicer and choose to hide items with no data then these items will not be in the list let's click OK so now you can see it appears like I only have three sub categories and that's because I've filtered bikes if I choose clothing I get a different list back in the slicer settings is one more thing instead of hiding items with no data then the default is to visually indicate items with no data by graying out the buttons and it will show items with no data last this last one here show items deleted from the data source if your source data changes and say you no longer have a category for bikes by default bikes will still be included in the slicer but it will be greyed out because we've got visually indicate items with no data now I personally don't think there's any point in showing items that are deleted from the data source still in the slicer I think that's a bit confusing so I like to deselect that and click OK so that is slices in a nutshell have some fun with them I'm sure your users will love how intuitive they are and don't forget to download the workbook it's got the step-by-step instructions in there for you share it with your colleagues and share the slice of love if you enjoyed this video please click the like button and thanks for watching

Channel: MyOnlineTrainingHub

Views: 79,477

Rating: 4.9803247 out of 5

Keywords: Excel Slicers, slicers

Id: 2H7aOHKZ6PY

Length: 13min 4sec (784 seconds)

Published: Mon Aug 14 2017