Advanced Excel Modeling - Slicers

Post date: Aug 10, 2018 10:11:49 PM

For the professional modeler Excel's advanced features improve transparency and efficiency while reducing complexity and risk. They also provide greater flexibility, scalability and connectivity. These benefits grow in value as models become more complex and connected to other models, data sources and people.

In the previous section we discussed creating PivotTables with Power Pivot and the Data Model. In this section we add a slicer to that PivotTable. Slicers are useful in modeling to focus on a various scenarios that our model may cover, or a product line, or sales channel, or several combinations of many factors to help us understand how the various elements of what we are modeling impact results.

Input Tables

Process Tables

Data Model

Slicers

PivotTables

Pivot Charts

Lesson Objective

At the end of this lesson you will be able to:

    • Describe what slicers are

    • How slicers differ from PivotTable filters

    • How to create a slicer

    • How to set slicer properties

This is under construction.

Click this link for the course's start

What are Slicers?

Slicers provide buttons users can click to filter table rows or PivotTable Fields. This helps users focus on one or more items of interest.

One might think they are the same as PivotTable filters but slicers can do more.

Slicers display the connected Table's or PivotTable's current filtering state. By looking at the slicer we can see what data is included in the Table, PivotTable, or PivotChart and what it is not.

Slicers can be linked to multiple PivotTables and their associated Pivot Charts so we can see things like a specific product's sales volume by region, sales volume over time, and profit margin simultaneously. One slicer can control all three charts giving us a much richer picture of one or more aspects of our model.

In the Output Chain diagram we show Slicers before PivotTables. From the perspective of how data conceptually flows between these Excel features that is correct; however, Slicers can be applied to either Tables or PivotTables. We typically apply them over PivotTables.

How To:

Create a Slicer

There are multiple ways to create slicers. One way is to select any cell in our PivotTable. This automatically opens the Ribbon's contextual tab set PivotTable Tools. From there we can select Analyze > Insert Slicer. (In older versions of Excel click tab Options then click Insert Slicer.)

This displays the Insert Slicers dialog (right). If the PivotTable uses the data model we will see two tabs: Active and All. All lists the data model's tables allowing us to select any and all of their fields from which to create slicers (one per field).

If we needed more slicers just check each field we want to slice with. Excel would creates one slicer for each. This is the easiest way to create multiple slicers at one time but may not guarantee that all slicers share the same PivotCache (See Next)

Slicers can be applied to multiple PivotTables ONLY if the PivotTables share the same PivotCache. One way to insure multiple PivotTables share the same PivotCache is to create subsequent PivotTables by copying the first PivotTable and then modifying the copy as needed.

When it comes to a PivotTable's drilldown feature we might be mislead by PivotTables filtered by Slicers. Slicers do not impact drilldown. To make drilldown display only records represented by PivotTables filtered by Slicers we must add the Slicer fields to the PivotTable's Page Fields. The Page Fields will follow the Slicers and they will then filter drilldown results appropriately.

Link a Slicer to Multiple Pivots

Slicers can filter multiple PivotTables and their associated Pivot Charts simultaneously as long as the PivotTables share the same data source. With the slicer selected we can connect it to multiple PivotTables using Options > Report Connections

This displays the Report Connections dialog. We can check all PivotTables we want the slicer to filter. This flows to the PivotTables' associated Pivot Charts

Set Slicer Properties

With the slicer selected we can adjust it to our taste using Tab: Slicer Tools > Tab: Options > Group: Slicer > Icon: Slicer Settings

Lab

    1. Create a slicer to filter by Loan Term

      1. Click on any cell in pvtFI (Fees and Interest PivotTable)

      2. Use menu path (PivotTable Tools) Analyze > Insert Slicer

      3. Check Term under tblLP

      4. Use menu path (Slicer Tools) Options > Slicer Settings

      5. Change name to slcTerm

      6. Check Display header.

      7. Enter Term in Caption

      8. Select Sort ascending (A to Z)

      9. Check Hide Items with no data

      10. Click slcTerm then select a Slicer Style from (Slicer Tools) Options > Slicer Styles.

      11. Click and drag (or Copy Paste) slcTerm to where you want it.

      12. Right click slcTerm and select Size and Properties.

      13. Check Disable resizing and moving

      14. Enter 2 in Number of columns

      15. Set Width to 1.75" and Height to .75"

      16. Check Lock aspect ratio.

    2. Create a slicer to filter by Loan Description

      1. Click on any cell in pvtFI (Fees and Interest PivotTable)

      2. Use menu path (PivotTable Tools) Analyze > Insert Slicer

      3. Check Description under tblLP

      4. Use menu path (Slicer Tools) Options > Slicer Settings

      5. Change name to slcDescription

      6. Check Display header.

      7. Enter Term in Caption

      8. Select Sort ascending (A to Z)

      9. Check Hide Items with no data

      10. Click slcDescription then select a Slicer Style from (Slicer Tools) Options > Slicer Styles.

      11. Click and drag (or Copy Paste) slcDescription to where you want it.

      12. Right click slcDescription and select Size and Properties.

      13. Check Disable resizing and moving

      14. Set Width to 1.75"

      15. Check Lock aspect ratio.

    1. Play with the slicers

Assignments

Quiz

    1. Slicers can be applied to:

      1. PivotTables

      2. Excel Tables

      3. All of the above

      4. None of the above

    2. Slicers can have custom formats:

      1. TRUE

      2. FALSE

    1. Slicers can filter one and only one Table or PIvotTable:

      1. TRUE

      2. FALSE

    1. A PivotTable or Table can have only one slicer:

      1. TRUE

      2. FALSE

    1. For a Slicer to be connected to multiple PivotTables each PivotTable must:

      1. Have the same field

      2. Display the same field in the same position

      3. Be over the same source data

      4. Use the same PivotCache

      5. It can't be done. A slicer can only connect to one PivotTable or Table.

    1. Slicers determine PivotTable drilldown results.

      1. TRUE

      2. FALSE

    1. Slicers can have multiple columns.

      1. TRUE

      2. FALSE

    1. Slicers determine PivotTable drilldown results.

      1. TRUE

      2. FALSE

    1. Slicers can provide a visual way to know which items a PivotTable or Pivot Chart is showing..

      1. TRUE

      2. FALSE

Summary

Slicers are fun and when linked to multiple PivotTables and Pivot Charts can help reveal several aspects of our model simultaneously.

This brings up the Slicer Settings dialog.

Source Name: I like to name slicers with the prefix slc followed by the header's caption.

Header: I prefer the header's caption be similar to the field name upon which the slicer is based.

Item Sorting and Filtering: I usually sort my slicers alphabetically in ascending order.

Hide Items with no data: I prefer that any items that have no data remain out of sight.

Position Slicers

There are two more things I like to do with slicers just before giving it to the user: Disable moving and resizing by the user and by Excel when cells change size.

To make these changes right click on the slicer and at the bottom of the pop-up menu select Size and Properties. That displays the Format Slicer dialog.

Check Disable resizing and moving to prevent users from moving the slicer

Click Don't move or size with cells to prevent Excel from changing it.

Next