📊Pimp my Excel Chart and Slicer – Supercharge your Chart Skills

Have you ever created a pivot chart and added a slicer and thought to yourself this slicer looks ugly? Because I basically think that every single time I add a slicer they look very boring, they look very standard and they don't really look like they're part of the actual chart. So we're going to solve this little issue and start making our line charts look freaking amazing by formatting a slicer correctly and really integrating it with the chart that that slicer controls. Now just as a side note I just want to show you all my new jumper. I got this jumper today and it's got cool lettering on it and I'm completely in love with it. Blakely.com everybody. Anyway enough about me let's go. Now I've already gone in and created the pivot table based off of this table of data just here and as usual we just have some very generic sales figures for different months, units sold, price per unit, total sales, the usual stuff. And on the next worksheet I've created a pivot table and let's just drag the pivot table fields over here so you can see them. So all we're basically representing in this pivot table is we're showing the months and we're showing the sum of the total sales. So very simple pivot table. Let's put him back over there. So what I'm going to do now is just insert a quick pivot chart. I'm just going to do a really basic column chart because the main point of this demonstration is the slicer. So let's click in the pivot table, pivot table analyze tab, pivot chart and we're going for a column chart. Let's click on OK. Now I'm going to do a little bit of formatting to this chart just to tidy it up. The first thing I always do is remove these grey buttons. These are basically filters. I could filter by month using this particular one but we're going to be adding a slicer to do some filtering anyway so these are kind of pointless. And it's always worth making your charts look as clean as possible and only including the information that is needed. So if we right click on any of these grey buttons we can say hide or field buttons on chart just to get rid of those. What else don't I need on here? I don't really need the legend because that's really being represented in the chart anyway. So I'm just going to click, it is just a text box, press the delete key to get rid of that. Now we're going to add a title at the end so we won't worry about that for now. One thing I am going to do is I'm just going to format these bars a little bit. They're a little bit too skinny for me. I like them to be nice and fat. So let's right click format data series. I've had too much coffee this morning. And notice when you go into format data series you have this little gap width slider. So we can drag this down to decrease the gap width in between the bars and make them nice and chubby. So there we go. Let's add a little bit of colour because this green is boring the hell out of me. So let's go to the fill option and I'm going to choose my new favourite little option, vary colours by point. And that's basically going to make sure that every single bar is a different colour based on the theme that I'm using. So I think that looks pretty nice for a very basic chart. Now let's go in and add our title. Double click and we're going to call this sales by month. Let's apply a little bit of formatting. Let's go to home. I'm going to change this title to Roboto Black and I'm going to make it a slightly lighter grey I think. And let's make it a bit smaller. Now another little trick here, if you want to move this title around you can just pick it up and drag and drop it wherever you like. But if you want to keep it on like one line as you move it, if you hold down shift that's going to allow you to move it horizontally with a little bit more control. So I'm going to drag this over to the side because we're going to put our slicer over on the other side and really make it look like it's integrated beautifully into the chart. Woo! Right let's go. Now if you're not sure what a slicer is, it's basically just a really nice interactive way that we can filter the data that we're seeing in the chart. So maybe I want to have a little filter that allows me to view these sales not only by the month but also by the year. So I want to be able to select the year in the filter and all of the figures below update. So let's click on the chart, pivot table analyze and we're going to insert a slicer. Now you're going to get a choice to add slicers for every single column heading that you have in your data source. Now I only want to filter by the year so we're going to select the year and click on OK. So by default this is what you get and this is what I mean. Slicers look kind of ugly. So instead of just keeping this as it is and putting it somewhere on the worksheet which doesn't look particularly great, we want to make this look as if it's part of the chart. So we need to apply a little bit of formatting just here. Now the first thing I'm going to do is instead of having the years listed vertically I'm going to list them horizontally. So we click on the slicer up to the slicer tab and in the buttons group where we have columns, currently says one, I'm going to take this up to three to arrange that horizontally. I can then drag the bottom up and we can sort of move it over here. Now it's still not looking particularly great, we need to make a couple more changes. Something that's sort of annoying me here which doesn't necessarily need to be there is this header area where it says year and clear filter so on and so forth. So if we click on the slicer up to the slicer ribbon and in the first group we can jump into our slicer settings and from here we can choose if we want to display that header or not. So I'm going to deselect this box, click on okay and now I have a much cleaner looking slicer. Now what else do I want to do here to make this look slightly nicer? Well I definitely want to remove that border around the outside. So let's click on the slicer and if you want to make any formatting changes to your slicer you basically need to duplicate the slicer style and modify it. With the slicer selected, take a look in the slicer styles group. We can see the style we currently have applied so I have this kind of light greeny colour. So what I'm going to do is right click and I'm going to duplicate the style and I'm just going to give it a name. I'm going to call it my initials and custom and then we can start to make some changes to every single part of this slicer. So I'm going to choose whole slicer, I'm going to go into format and we're going to remove the border. So let's go to the border tab, I'm going to say none and click on okay. Now I could go through each part of this slicer making formatting changes. For example, if I want the little slicer to change colour when I hover over one of the years, I could change the formatting for hovered selected item with data. So let's click on this, let's click on format. I'm going to go to fill and let's fill that with a blue colour and the font is going to change to white and click on okay and I could carry on going. I'm not going to bother due to time but you can go through all of these and really customise the way this slicer looks. Let's click on okay and then in the slicer styles drop down we have a custom group. We want to make sure we apply the new style that we just created and check it out. If I click away, I'm going to need to move this down a little bit, you can see the difference that's made. So now this looks like it's completely part of this chart and we can click on these and we can see our data displayed by the year. So this is a technique I love to use and I generally use this whenever I'm using slicers in a chart or if I'm building a dashboard or something like that. Just makes it look a lot more integrated and a lot nicer than just that standard ugly slicer. That's it guys, if you like this video smash that like button, give me a cheeky follow and if you've got anything to say put it down in the comments section and I will reply to you when I've had my cup of tea. See you next time guys, bye!