Slicers in Microsoft Excel

Intro:
Hello, and welcome to the CPA Australia podcast, your weekly source for accounting, education, career, and leadership discussion.

Neale Blackwood CPA:
In the December issue of in the black, I covered how we can create a dynamic chart using, or combining, two of Excel's features. So, what we're looking at combining are the slices, which is a filter interface, typically used with pivot tables, but from Excel 2013 onwards, slices is can also be used with formatted tables. That's the other feature.

Neale Blackwood CPA:
Now I have written an article and also, I think I've done a podcast on formatted tables. If you're not already using formatted tables, I highly recommend that you have a look at them. For small data sets they work brilliantly, because they basically tell to treat the table like a little database. Slices are a filter interface that are really easy to use, you just basically click the items on the slicer and you can filter by those. You can hold the control key down to select multiple items as well.

Neale Blackwood CPA:
The table that is in the article has the states listed going down the left hand side. Across the top are the months of the year, so we've got from July through to November, and we're plotting the margin percentage. So that's what the table looks like that we're going to work on.

Neale Blackwood CPA:
Let's go through how you create this dynamic chart. So basically, you have a table. You need to make sure that table is a formatted table. So, there's an icon on the home ribbon called formatter's table. You can also use a keyboard shortcut, which is control and the letter T, so hold control down and then press the T, and that will take you to directly to the selection of the range that you want to include in the formatted table. In general, when you're creating a table, don't have blank rows or blank columns in that table. If you do, Excel won't be able to guess the table range correctly. Blank rows and blank columns affect Excel selects ranges, so always make sure that you, you avoid them when you are using formatted tables and tables in general.

Neale Blackwood CPA:
So, if you've created the formatted table, you can click inside the formatted table and then click the insert ribbon and then select the type of chart in the... We've done a video for this article as well, and that used the line chart that we created. Now, when you create the line chart, it won't actually look as you'd expect. Instead of having the months of the year going across, which is what the table had, it actually has the states going across the bottom.

Neale Blackwood CPA:
So, there is an icon in the... It's called chart design, or it might just be design, and the icon is switch row columns. Now, this is actually a very useful icon even if the chart you create works out, because what it does is it shows you a different way of looking at the data.

Neale Blackwood CPA:
So, even if you think your chart is okay, just try using that icon and you might find that you can get a better outcome. Now, when you plot too many lines on a line chart, it is hard to read, and that's where the filtering comes in. So, you can filter the table on the column, but that's a little bit clunky. So basically when you filter it, that filter flows through into the chart. So anything hidden in the data will be hidden on the chart. That's the default setting for charts. And so, you can use the filter on the column, and that will basically allow you to select what you want to pull lot on the charts.

Neale Blackwood CPA:
But as I mentioned, it's a little bit clunky to use the column filter. So, it's a lot easier, and especially for the user, to use the slicer. So the slicer interface was added for formatted tables in Excel 2013. So, you click inside the table, you click the design tab, or it might be called table design in the later versions, and the slicer is about a third of the way across the ribbon at the top and it's just called insert slicer.

Neale Blackwood CPA:
When you select it, you have to choose the column that you want to slice by. So in our example, it's the first column, which has the eights listed. That's it. That inserts the slicer. You can change the heading, it's called the caption on the slicer, by right-clicking it. And there's an option for slicer settings. And it's the caption that you need to change, and that will change the heading that's on the slicer.If you want to select multiple items, you just hold the control key to on while you're clicking on the slicer.

Neale Blackwood CPA:
In the later versions of Excel, there's also a little icon there that is called multiselect. It's in the top right hand corner of the slicer, and that also allows you to select multiple items, in that case, without holding the control key down.

Neale Blackwood CPA:
Now, a little trick. If you want to display the slicer sideway. So in general, the slicer goes top to bottom, but if you want to put the slicer across the bottom of, say a sheet or a underneath the chart, you need to change one of the properties of it. So what you can do is right-click the slicer and you can choose size and properties. And under the layout section, there is a number of columns. Now, the default is one. So if you change that to say five or six, then that will allow you to show the states going across the bottom. You need to hit enter after you've done that. And that allows you to have, if you like, a horizontal slicer, which in some cases can look a little bit better and can be a little bit easier to fit, say on a dashboard or something like that.

Neale Blackwood CPA:
Using slices and formatted tables together gives you the option of creating a dynamic chart. Again, formatted tables have got a lot of advantages. So for example, in the video, I added Tasmania to our table and it automatically appeared on the chart. I also added an extra month on the end, and that automatically appeared. One thing about formatted tables that's worth mentioning is that you can't have a date as a column heading.

Neale Blackwood CPA:
In our example, all of the dates that were the column headings were all left-aligned after I changed it to a formatted table. So, it's treating it like text. So, just a heads up on that. I'm not sure why, but you can't have a date as the column heading in a formatted table. It doesn't affect the chart that we created, but it's just something to keep in mind. Hope you've found that useful. Thanks for listening.

Outro:
Thanks for listening to the CPA Australia podcast. For more information on today's episode, please visit the show notes at www.CPAAustralia.com.au/podcast. Never miss an episode by subscribing to our podcast on Apple Podcasts, Spotify, or Stitcher.