In this article by Hernan D Rojas author of the book Data Analysis and Business Modeling with Excel 2013 introduces additional materials to the advanced Excel developer in the presentation stage of the data analysis life cycle. What you will learn in this article is how to leverage Excel’s new features to add interactivity to your spreadsheets.

(For more resources related to this topic, see here.)

What are slicers?

Slicers are essentially buttons that automatically filter your data. Excel has always been able to filter data, but slicers are more practical and visually appealing. Let’s compare the two in the following steps:

1. First, fire up Excel 2013, and create a new spreadsheet. Manually enter the data, as shown in the following figure:

   

2. Highlight cells A1 through E11, and press Ctrl + T to convert our data to an Excel table. Converting your data to a table is the first step that you need to take in order to introduce slicers in your spreadsheet.
3. Let’s filter our data using the default filtering capabilities that we are already familiar with. Filter the Type column and only select the rows that have the value equal to SUV, as shown in the following figure. Click on the OK button to apply the filter to the table.

   

   You will now be left with four rows that have the Type column equal to SUV.

   

Using a typical Excel filter, we were able to filter our data and only show all of the SUV cars. We can then continue to filter by other columns, such as MPG (miles per gallon) and Price. How can we accomplish the same results using slicers? Continue reading this article to find this out.

How to create slicers?

In this article, we will be going through simple but powerful steps that are required to build slicers. After we create our first slicer, make sure that you compare and contrast the old way of filtering versus the new way of filtering data.

1. Remove the filter that we just applied to our table by clicking on the option named Clear Filter From “Type”, as shown in the following figure:

   

2. With your Excel table selected, click on the TABLE TOOLS tab.

   

3. Click on the Insert Slicer button.

   

4. In the Insert Slicers dialog box, select the Type checkbox, and click on the OK button, as shown in the following screenshot:

   

   You should now have a slicer that looks similar to the one in the following figure. Notice that you can resize and move the slicer anywhere you want in the spreadsheet.

   

5. Click on the Sedan filter in the slicer that we build in the previous step. Wow! The data is filtered and only the rows where the Type column is equal to Sedan is shown in the results.
6. Click on the Sport filter and see what happens. The data is now filtered where the Type column is equal to Sport. Notice that the previous filter of Sedan was removed as soon as we clicked on the Sport filter.
7. What if we want to filter the data by both Sport and Sedan? We can just highlight both the filters with our mouse, or click on Sedan, press Ctrl, and then, click on the Sport filter. The end result will look like this:

   

8. To clear the filter, just click on the Clear Filter button.

   

Do you see the advantage of slicers over filters? Yes, of course, they are simply better. Filtering between Sedan, Sport, or SUV is very easy and convenient. It will certainly take less key strokes and the feedback is instant. Think about the end users interacting with your spreadsheet. At a touch of a button, they can answer questions that arise in their heads. This is what you call an interactive spreadsheet or an interactive dashboard.

Styling slicers

There are not many options to style slicers but Excel does give you a decent amount of color schemes that you can experiment with:

1. With the Type slicer selected, navigate to the SLICER TOOLS tab, as shown in the following figure:

   

2. Click on the various slicer styles available to get a feel of what Excel offers.

   

Adding multiple slicers

You are able to add multiple slicers and multiple charts in one spreadsheet. Why would we do this? Well, this is the beginning of a dashboard creation. Let’s expand on the example we have just been working on, and see how we can turn raw data into an interactive dashboard:

1. Let’s start with creating slicers for # of Passengers and MPG, as shown in the following figure:

   

2. Rename Sheet1 as Data, and create a new sheet called Dashboard, as shown here:

   

3. Move the three slicers by cutting and pasting them from the Data sheet to the Dashboard sheet.
4. Create a line chart using the columns Company and MPG, as shown in the following figure:

   

5. Create a bar chart using the columns Type and MPG.

   

6. Create another bar chart with the columns company and # of Passengers, as shown in the following figure. These types of charts are technically called column charts, but you can get away by calling them bar charts.

   

7. Now, move the three charts from the Data tab to the Dashboard tab. Right-click on the bar chart, and select the Move Chart… option.

   

8. In the Move Chart dialog box, change the Object in: parameter from Data to Dashboard, and then click on the OK button.

   

9. Move the other two charts to the Dashboard tab so that there are no more charts in the Data tab.
10. Rearrange the charts and slicers so that they look as closely as possible to the ones in the following figure. As you can see that this tab is starting to look like a dashboard.

    

11. The Type slicer will look better if Sedan, Sport, and SUV are laid out horizontally. Select the Type slicer, and click on the SLICER TOOLS menu option.

    

12. Change the Columns parameter from 1 to 3, as shown in the following figure. This is how we are able to change the layout or shape of the slicer.

    

13. Resize the Type slicer so that it looks like the one in the following figure:

    

Clearing filters

You can click on one or more filters in the dashboard that we just created. Very cool! Every time we select a filter, all of the three charts that we created get updated. This again is called adding interactivity to your spreadsheets. This allows the end users of your dashboard to interact with your data and perform their own analysis. If you notice, there is really a no good way of removing multiple filters at once. For example, if you select Sedans that have a MPG of greater or equal to 30, how would you remove all of the filters? You would have to clear the filters from the Type slicer and then from the MPG slicer. This can be a little tedious to your end user, and you will want to avoid this at any cost. The next steps will show you how to create a button using VBA that will filter all of our data in a flash:

1. Press Alt + F11, and create a sub procedure called Clear_Slicer, as shown in the following figure. This code will basically find all of the filters that you have selected and then manually clears them for you one at a time. The next step is to bind this code to a button:

   Sub Clear_Slicer()
   
    
   
      ' Declare Variables
   
      Dim cache As SlicerCache
   
       
   
      ' Loop through each filter
   
      For Each cache In ActiveWorkbook.SlicerCaches
   
          ' clear filter
   
          cache.ClearManualFilter
   
      Next cache
   
    
   
   End Sub

   

2. Select the DEVELOPER tab, and click on the Insert button. In the pop-up menu called Form Controls, select the Button option.

   

3. Now, click anywhere on the sheet, and you will get a dialog box that looks like the following figure. This is where we are going to assign a macro to the button. This means that whenever you click on the button we are creating, Excel will run the macro of our choice. Since we have already created a macro called Clear_Slicer, it will make sense to select this macro, and then click on the OK button.

   

4. Change the text of the button to Clear All Filters and resize it so that it looks like this:

   

5. Adjust the properties of the button by right-clicking on the button and selecting the Format Control… option. Here, you can change the font size and the color of your button label.

   

6. Now, select a bunch of filters, and click on our new shiny button. Yes, that was pretty cool. The most important part is that it is now even easier to “reset” your dashboard and start a brand new analysis. What do I mean by start a brand new analysis? In general, when a user initially starts using your dashboard, he/she will click on the filters aimlessly. The users do this just to figure out how to mechanically use the dashboard. Then, after they get the hang of it, they want to start with a clean slate and perform some data analysis. If we did not have the Clear All Filters button, the users would have to figure out how they would clear every slicer one at a time to start over. The worst case scenario is when the user does not realize when the filters are turned on and when they are turned off. Now, do not laugh at this situation, or assume that your end user is not as smart as you are. This just means that you need to lower the learning curve of your dashboard and make it easy to use. With the addition of the clear button, the end user can think of a question, use the slicers to answer it, click on the clear button, and start the process all over again. These little details are what that is going to separate you from the average Excel developer.

Summary

The aim of this article was to give you ideas and tools to present your data artistically. Whether you like it or not, sometimes, a better looking analysis will trump the better but less attractive one. Excel gives you the tools to not be on the short end of the stick but to always be able to present visually stunning analysis. You now know have your Excel slicers, and you learned how to bind them to your data. Users of your spreadsheet can now slice and dice your data to answer multiple questions. Executives like flashy visualizations, and when you combine them with a strong analysis, you have a very powerful combination. In this article, we also went through a variety of strategies to customize slicers and chart elements. These little changes made to your dashboard will make them standout and help you get your message across. Excel as always has been an invaluable tool that gives you all of the tools necessary to overcome any data challenges you might come across. As I tell all my students, the key to become better is simply to practice, practice, and practice.

Resources for Article:

Further resources on this subject:

• Introduction to Stata and Data Analytics [article]
• Getting Started with Apache Spark DataFrames [article]
• Big Data Analysis (R and Hadoop) [article]