Excel Slicer Trick - Use Symbols instead of Text in Pivot Slicers

in this video I'll show you how you can use symbols for your slicers to create custom looks for your dashboard reports this is our sample dataset it's some HR information with cost center name sex full-time equivalent information and the base salary of each person what we want to do is to create a pivot table that shows the cost center with the average salary that's associated with each cost Center and we wanted by sex and FTE so first of all let's just create our pivot table first let's create our standard slicer just to see how it looks and then let's see how we can replace them with symbols this is just a normal Excel table now what I'm going to do is to transform it into an official excel table by pressing ctrl T get because that way when I create a pivot table based on this and I have new data that comes in my pivot table is automatically going to accept a new data when i refresh it I don't have to expand my source first of all I don't like this designs I'm just going to go and clear it to get back to my old design let's give this table a name we can summarize it with a pivot table I wanted on the existing spreadsheet let's just put it here and press ok I'm gonna bring costs enter to the row so I'll just put a tick mark let's see if it figures it out sometimes it does and this time it didn't because cuz Center is numbers so it's thinking that it should add them up but I don't want it there I want it here I want my base salary in the values okay but I don't want to show the fool the sum of the base salary I'm gonna either click here and go to value field settings or right mouse click here doesn't matter where we do it so I'll just go directly from here and say I want the average while I'm here let's go and make sure that we use thousand separator type of formatting now I came to custom but I don't have to I can also take a number formatting from here and activate the thousand separator without decimal places so now I'm gonna say okay so that looks much better let's go and change the layout of this report to show it in tabular format so that we can see our headers here we can change this to to just total okay let's change the formatting of this to this color the gray my favorite color and I'm just gonna bring it a bit down because we want to add the slicers on top and let me just put this background white that's basically all the cost centers and that's the average of base salary which doesn't look nice so we want to call it average base salary now I want to create a slicer let's say for the sex I'm gonna right mouse click and say add as slicer let's go to options as well and change it to the format that fits here better I have F and M so I can click on these and the pivot table is restricted to show this selection I can obviously update this to fit better but what I want to do is to show symbols so the female symbol here and the male symbol here also for FTE if I right mouse click and say add a slicer you can see the different if two E's I have but I don't want to see them like this I just want to distinguish between full-time and part-time using two symbols okay so anything that's less than one I want to show as part-time and anything that equals one I want to show with a separate symbol okay so I want to have my symbols that I put on top of my pivot report and I can switch between full-time part-time and female male what I need to do is to bring in those symbols in my original data source especially for these cases where I have different numbers that I want grouped together so anything less than one should be this symbol I can bring it into the data source and here because I have text I can also bring in a separate column that puts in the symbol in my data source so what I'm going to do here is insert a new column and that's going to be the symbol for sex so sex symbol shortcuts for mail symbol is if you hold down the Alt key and you go to your number side of your keyboard and you press 11 you get the mail sign and if you hold down alt and press 12 you get the female sign guess I can use these in my if formula here are some of the unique character codes that I figure it out I already know that what these symbols are but I'm just going to show you the formula for it so that's the unique hair open bracket that's the number close bracket enter so that's the female symbol the next one should be the male symbol and this is the symbol I want to use for part-time workers and that's the symbol I want to use for full-time workers okay so these unique character codes they range really they have a huge huge range of numbers you can google it and come up with the character that's associated to each of these numbers or you can also play with it yourself so just add a bunch of numbers to Excel and then pull down these formulas and you get all these different characters and you like some of them copy and paste the ones that you think you might use in the future in kind of like a template or a simple template that you can use whenever the time comes yes in this case I'm just interested in the first four I'm gonna control C it and then I'm gonna right mouse click and paste them as values because I want to use them in my formula okay so going back here I already have these two symbols with the shortcut keys are also 11 and alt 12 I'm gonna use them in the a function just make a simple one if this equals M then the symbol otherwise the female symbol that looks good press ENTER again so they're all automatically populated because this is an Excel table so that's the advantage of having excel tables now for FTE I'm gonna have FTE symbol and say if this equals one then and now this is where I need to copy and paste those so let me just put the quotation works here otherwise this enter lets me go here and copy these so ctrl C put them together so I'm gonna control V here highlight both control C and press escape I have them together let's go and paste them in so if it's one it's a full-time otherwise if it's not one it's gonna be or that person is going to be part-time so I have them in there this is an Excel table so I just have to come here and refresh and they are already in my pivot table I no longer want to use these slicers I'm just going to delete and delete what I do want to use are my new slicers so I'm gonna right mouse click on this one added a slicer and I get my symbols okay so optimally I want to place this on top now let's go to options you don't need them to be that big and we don't need them to have a header so I'm gonna right mouse click and go to slicer settings and do not display that header also let's pick a very similar slicer design like that okay but you see that border here I don't want the border and all of these have borders unfortunately so what we need to do is right mouse click it is and duplicate it and I'm just gonna put slicer no border so for the whole slicer here I'm gonna go to format border none' again you can make a lot of changes to the slicers you have really full control over the fonts that color everything but in this case I just want to take that border away looks like it's there because it's still sitting on the original ones I have to go here and click on the one that I did so now I can switch male and female what about the FTE that's the FTE symbol I'm gonna right mouse click and add a slicer so now I just have full-time and part-time workers let's go and apply the new slicer design that we just created and to this I'm gonna add two columns I can leave the heading if I want but in this case I'm gonna go and also take it away okay so I'm gonna put that here now I can take a look at females working full-time that's the average salary females working part-time that's the every salary what about males working part-time well much higher salary here and male working full-time this is how you can use simples in your slicers if you liked this video don't forget to leave a thumbs up and for more of these videos why not subscribe to my channel for updates when new videos like this one come out

Channel: Leila Gharani

Views: 83,492

Rating: 4.9735651 out of 5

Keywords: XelplusVis, Advanced Excel Tutorials, Leila Gharani, Excel 2016, Excel 2013, Excel 2010, Advanced Excel tricks, Excel online course, Excel tips and tricks, Excel for analysts, excel pivot slicer, excel pivot table slicer, symbols in slicers, grouping pivot slicers, pivot slicer horizontal, pivot slicer options, customize pivot slicers, custom slicer, excel slicer dashboard, excel slicer tricks, excel slicer formatting, excel slicer 2016

Id: sGgjufTM0VA

Length: 11min 21sec (681 seconds)

Published: Wed Oct 04 2017