Saturday, June 4, 2011

Switch "dynamically scenarios using wemshrohat

Posted on 1 June 2011 in Pivot Tables & charts-3 comments

Wemshrohat is the new favorite in Excel attributes. And in 2010 in Excel, such as optical filters are wemshrohat.

Let us be your sales report (link) to multiple vendors. Because you want to show the report to one person at a time, you can use report filters in the pivot table for this view. But find that switching between areas of pain using report filter.

Enter wemshrohat.

Now, just click the name of the region to show report for that region, such as this:

Using Slicers to dynamically show sales report by person

Now, we use wemshrohat Creative Director to make an interactive scenario in Excel, some thing like this:

Using Slicers to Switch Scenarios in Excel

This method gives the same result as displayed and select scenarios using VBA article, but easier to implement

You need to specify different scenarios in table, like this:

Scenario-wise data - setup

Select the table that you created in step 1, then insert a PivotTable. Use a variable name and variable value row label in the value field.

Select anywhere inside the axis. And now, on the Options tab, click the button "insert morgue". Click to insert a slicer field scenarios.

Add a slicer to select scenario

I'm beyond interpretation creates a form that is not relevant here.

Once you set up the form, simply refer to the pivot table for all values of variables.

Go to PivotTable worksheet and select in the morgue, click CTRL + X to cut it.

Return to the worksheet form and paste it into a morgue.
Disabling Slicer Heading and Clear Filter Button

Wemshrohat Excel by default shows the option to remove the filtered morgue. You can get rid of this button,

1) right click on the slicer
2) go to the slicer settings
3) UN option "show header check

See aside.

That's it, we transform smart scenario morgue ready. Now, you can extend this in several ways. For example, you can write some clever formulas of handling multiple selection wemshrohat. You can compare one scenario and another when you choose one or more of the morgue. Be much more. But let your imagination run wild.

I have made a simple example to demonstrate this technique.

Please download and open it in Excel 2010.

Screening action "scenario" and "axis" model for understanding how morgue setting, and how to do this.

As I said, is the favorite new attributes wemshrohat in Excel. And may use them as much as possible because it is easy to use and very powerful.

What about you? Do you frequently slide? What was your experience like? Please share ideas and tips with your comments.

1) create a "dashboard" in Excel using wemshrohat
2) create a dynamic chart using PivotTable report filters "
3) remove duplicates and sort the list by using the pivot table
4) more on modeling & pivot tables

RSS feed for comments on this post. TrackBack URI

View the original article here

No comments: