Software

How to create an effective, user-friendly slicer in Excel

For dashboards and quick filtering, you can't beat Excel slicers. They're easy to implement and even easier to use. Here are the basics--plus a few power tips.

hero
Image: iStockphoto.com/PavelRodimov
Slicers are graphic filters. You click a button and the linked data automatically adjusts. For example, you might want to see transactions for a particular region. To do so, you could sort the data and implement Excel's built-in filter feature... or click a slicer button. Which would you prefer? Slicers lend well to a dashboard environment because of their visual qualities, but you can use them anywhere to make filtering data easier for your users. In this article, I'll show you how to add a basic slicer to a Table and more. Over the next few months, we'll explore more complex uses for slicers.

I'm using Excel 2016 on a Windows 64-bit system, but the feature is available in Excel 2010 and 2013. In Excel 2010, slicers work only with PivotTables. Beginning with Excel 2013, you can add a slicer to a Table. They even work in a browser using Excel Online. For your convenience, you can download the .xlsx demonstration file.

The Table

In this first example, we'll add the region slicer shown in Figure A. But before you can add the slicer, you need a Table.

  1. Click anywhere inside the data range.
  2. Click the Insert tab and the click Table in the Tables group.
  3. In the resulting dialog (Figure B), select the My Table Has Headers check box. (If you're working with your own data, you might need to uncheck this option—the example data has a header row.)
  4. Click OK.

Figure A

Figure A

Adding a region slicer to the Table is a quick and easy process.

Figure B

Figure B

Check the appropriate header option.

If you're using Excel 2010, you'll need a PivotTable to add a slicer. You can continue, but create a PivotTable instead of a Table.

SEE: Track stocks like a pro with a free Excel add-in

The slicer

Now that you've converted the data into a Table, you can generate the slicer almost as easily:

  1. Click the Insert tab and then click Slicer in the Filters group (in 2013 and 2016 only—you can't do this in Excel 2010).
  2. In the resulting dialog, click Region (Figure C) and then click OK to add the slicer shown earlier in Figure A.

Figure C

Figure C

Choose the Region column for the slicer.

To filter the Table using the slicer, click any of its buttons. To clear a filter, click the Clear Filter icon in the top-right corner (or press [Alt]+C). To evaluate more than one region at a time, click the Multi-Select to the left of the clear icon (or press [Alt]+S).

Implementing a slicer isn't easier than implementing Excel's built-in filtering feature. The gain is the visual slicer itself. Your users will find it much more intuitive than a filtering dropdown. That's why slicers are such a great dashboard tool; users don't have to know how to use Excel to arrange the data in a way that's meaningful to them.

Customizing the slicer

You can add as many slicers to a Table as you have columns. Figure D shows the result of adding a personnel filter and using both slicers. With only two clicks, you can find Dorothy's Southeast transaction(s). You can see that the buttons update in real time. Specifically, Dorothy has transactions only in the Northeast and Southeast regions, so the other regional buttons are dimmed. In addition, only the personnel with transactions in the Southeast are clickable once you click Southeast.

Figure D

Figure D

You can use multiple slicers.

The default slicer might be adequate, but there are other things to consider. First, you can customize the slicers using the contextual Options tab. You can change the slicer's style to match the Table (which is a good idea). You can change the one-column option default to arrange filtering buttons in multiple columns. You can change the size of the buttons or the slicer itself. To remove the header or change the caption, click Slicer Settings in the Slicer group (far left). Spend some time familiarizing yourself with these options for the best results.

Once you have a slicer, users probably won't need to see those values repeated in the actual data. When this is the case, you can hide those columns as follows and not lose a thing:

  1. Select columns D and E, the Personnel and Region columns, respectively.
  2. From the Format dropdown (in the Cells group) choose Hide & Unhide.
  3. In the resulting submenu, choose Hide Columns (Figure E).

Figure E

Figure E

Hide the columns.

Hiding the columns removes redundant values reducing the amount of data the user needs to pursue (Figure F). The user already knows the person and region—the user made that choice via the clicker.

Figure F

Figure F

Hiding columns reduces clutter.

In addition, you can disable the Table's filters, which will inhibit the filtering dropdowns in the header cells. Doing so will give your Table a cleaner look but more important, it will prevent users from inadvertently adding a filter that most likely will change the viewed results if users don't realize that filter is engaged. To inhibit this feature, click the Table's contextual Design tab and uncheck the Filter Button option in the Table Style Options group (Figure G).

Figure G

Figure G

Disable the built-in filtering dropdowns.

One more quick addition. By clicking the Total row in the Table's contextual Design tab, users can glean even more information as they slice through the data. Figure H shows the result of adding the Total row and then using the slicers. As you can see, the totaling function in the Total row updates as you click buttons. It isn't magic, but it's almost as much fun.

Figure H

Figure H

A totaling row updates with the slicer.

You don't have to hide the columns, but you should know that you can, without limiting the slicers' functionality. You probably should disable the filtering dropdowns. Adding a totals row will depend on your needs.

SEE:

Any way you slice it

I know,,, what a cliché. But I couldn't help myself! Slicers are powerful yet amazingly simple to implement. You can customize the way they look with little effort. Earlier, I suggested that slicers are the same as filters in terms of functionality, but that's not entirely true. Using slicers, you can do some things you can't do with the built-in filters. You can:

  • Hide values users don't need to see.
  • Disable the built-in filtering feature.
  • Make filtering more intuitive and easy for users unfamiliar with Excel.
  • Use them with PivotTables and other charts and in Access Web Apps (which we didn't cover in this article).

Over the next few months, watch for articles on how to implement more complex slicers.

Send me your question about Office

I answer readers' questions when I can, but there's no guarantee. Don't send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. I'm not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at susansalesharkins@gmail.com.

Also read...

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox