Switch Scenarios Dynamically using Slicers

Slicers are my new favorite feature in Excel. Introduced in Excel 2010, Slicers are like visual filters.

A simple example of slicers:

Let us say you have a sales report (pivot) for multiple salespersons. Since you want to show the report by one person at a time, you used report filters in pivot tables to display this. But you find that switching between regions is a pain using the report filter.

Enter slicers.

Now, you can just click the region name to show the report for that region, like this:

Using Slicers to Switch between Scenarios Dynamically:

Now, we can use slicers creatively to make an interactive scenario manager in Excel, some thing like this:

This technique gives the same outcome as the Display and Select Scenarios using VBA article, but easier to implement

How to use slicers to switch between scenarios?

Step 1: Set up various scenarios in a table

You need to define various scenarios in a table, like this:

Step 2: Create a pivot table from your scenario data

Select the table you created in step 1 and insert a pivot table. Use variable name as row label and variable value in value field area.

Step 3: Insert a slicer for the scenarios

Select anywhere inside the pivot. Now, from options tab, click on Insert Slicer button. Click on Scenarios field to insert a slicer.

Step 4: Create your model, in our case a break-even model

I will skip the explanation of model creation as that is not relevant here.

Once the model is set up, just refer to the pivot table for each of the variable values.

Step 5: Move slicer to Model

Go to the pivot table worksheet and Select the slicer, click CTRL+X to cut it.

Go back to your model worksheet and paste the slicer.

Step 6: Format the slicer

Excel slicers by default show an option to remove the filtered slicer. You can get rid of this button by,

1) Right click on the slicer

2) Go to slicer settings

3) Un-check Display Header option

See aside.

Step 7: Use the slicer to interactively switch scenarios

That is all, our smart scenario switching slicer is ready. Now, you can extend this in many ways. For example, you can write some clever formulas to handle selection of multiple slicers. You can compare between one scenario and another when more than one option is chosen from the slicer. So much more is possible. But I will let your imagination run wild.

Download Example Excel File:

I have made a simple example to demonstrate this technique.

Please download the file and open it in Excel 2010.

Examine the worksheets “Scenario Pivot” and “Model” to understand how the slicer is setup and how this works.

Do you slice?

As I said, Slicers are my new favorite feature in Excel. I have been using them as much as possible because they are simple to use and very powerful.

What about you? Do you slice often? What is your experience like? Please share your ideas and tips using comments.

More examples on Slicers & Pivot Tables:

1) Creating a Dynamic Dashboard in Excel using Slicers

2) Creating a Dynamic Chart using Pivot Table Report Filters

3) Remove Duplicates and Sort a list using Pivot Tables

4) More on Pivot Tables & Modeling

Generated by BlogIt

BlogIt - Auto Blogging Software for YOU!