I have been using them as much as possible because they are simple to use and very powerful. Do you slice?Īs I said, Slicers are my new favorite feature in Excel. Please download the file and open it in Excel 2010.Įxamine the worksheets “Scenario Pivot” and “Model” to understand how the slicer is setup and how this works. I have made a simple example to demonstrate this technique. But I will let your imagination run wild. You can compare between one scenario and another when more than one option is chosen from the slicer. For example, you can write some clever formulas to handle selection of multiple slicers. That is all, our smart scenario switching slicer is ready. Step 7: Use the slicer to interactively switch scenarios Go back to your model worksheet and paste the slicer.Įxcel slicers by default show an option to remove the filtered slicer. Go to the pivot table worksheet and Select the slicer, click CTRL+X to cut it. Once the model is set up, just refer to the pivot table for each of the variable values. I will skip the explanation of model creation as that is not relevant here. Step 4: Create your model, in our case a break-even model Click on Scenarios field to insert a slicer. Now, from options tab, click on Insert Slicer button. Step 3: Insert a slicer for the scenarios Use variable name as row label and variable value in value field area. Select the table you created in step 1 and insert a pivot table. Step 2: Create a pivot table from your scenario data You need to define various scenarios in a table, 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 ![]() Now, we can use slicers creatively to make an interactive scenario manager in Excel, some thing like this: Using Slicers to Switch between Scenarios Dynamically: Now, you can just click the region name to show the report for that region, like this: But you find that switching between regions is a pain using the report filter. ![]() Since you want to show the report by one person at a time, you used report filters in pivot tables to display this. Let us say you have a sales report (pivot) for multiple salespersons. Introduced in Excel 2010, Slicers are like visual filters. Slicers are my new favorite feature in Excel.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |