A brief reminder
In this Tip, we first remind ourselves some of the basic analysis tools for tables before moving on to discuss Slicers.
Tables, you’ll hopefully recall, are Excel’s way of holding data in a clear & structured manner; If you need a “refresher”, here’s a link to November’s tip when we discussed Tables in Excel
The basic analysis options
Because they have a clear structure, tables automatically include a number of analysis features…
At the top of the table, within the header row…
- Each column in the table has an Auto filter arrow from which a drop down provides a number of options
- It allows quick sorting of the table in either ascending or descending order. It’s not necessary to select the whole table before applying the sort, Excel will select it all for you
- Depending on whether your data is text, numbers or dates, different filters will be available from a side menu that appears when you select the relevant filter
- Unique items are listed in the bottom window of the dropdown – if there’s a tick in the box, you can see the data. To only see a few items, it’s quicker to untick the Select All box & then pick the ones that you want. If you have a column of dates, you may find (as in this example) that they’ve been grouped into years (which will be calendar years rather than tax or accounting ones – you’ll need to use a lookup to create these)
At the end of the table in the Total row
By default, the Total Row is turned off – to turn it on, go to the Table Tools>Design Tab & tick the box
Doing this brings a Total Row to the foot of your table…
- By selecting Total Row on the Table Tools>Design Tab…
- A new Total Row is added…
- With a total in the end column of your table already selected (whether you actually want one there, or not!)
- This total uses the SUBTOTAL() Function (more on that later)
Adding more totals
To the right of each cell in the Total Row, there is a drop-down arrow that allows you to select a totalling method
Excel uses the SUBTOTAL() Function to provide the “total” selected as this automatically excludes any data that you have filtered out so the total will always be based on the values that you can see. However, as you can see, it’s not just “adding up” that’s possible, maximum & minimum values as well as variances & standard deviations can also be calculated.
A point to note with the Total Row is that when you add more data into your table, the TotalRow moves down automatically – you don’t have to insert any rows first and, because it’s using the Field Names in it’s calculations, you don’t need to adjust any total formulas, either!
Taking it further: Slicers
One of the problems with the filters that can be applied to an Excel table is that it can be difficult to spot which field is being filtered on; OK, you know that the table is filtered as the row numbers change to blue, but the filter buttons hardly seem to change…
Slicers have been available to use with Pivot Tables since the 2010 release; in the 2013 release of Excel, they were extended to Tables, too.
So, what do Slicers offer?
In simple terms, a quick & visual way of filtering data which allows the user to clearly see where a filter has been applied
This is the same filter as above, but this time using a Slicer. It clearly shows the Field Name, the options that can be selected & the filter that’s applied (the coloured “lozenge”)
Adding a slicer
From the Table Tools>Design Tab select Insert Slicer. If you’re unable to see these tabs, just check that you’ve actually selected a cell within the table.
This will then open the Insert Slicers dialog box which lists each of the headings from your table.
Clicking on the box next to the field name(s) and then clicking on the OK button will insert the slicer(s) for you and, at the same time, open the Slicer Tools>Options Tab
Here you can select different colours for your slicers, as well as increasing the number of columns of data per slicer & changing their height & width