If you are building financial models or any other type of excel based decision models, chances are, there will be multiple scenarios in your model. Whenever you have multiple scenarios, you may want an easy, intuitive way to select one of them. In this post, I will present an interesting scenario display & selection technique that I received by email from our reader Itay Maor.
First see the scenario selection in action:
Download the sample workbook with scenario selection macro
Click here to download the workbook (.xlsm file) that Itay emailed me.
How does this work?
In order to understand how this works, first you must know the limitations of this file. It can only support up to 5 scenarios.
The workbook has a bunch of macros – ChangeScenario, AddTab, RemoveTab, RenameTab etc.
Here is how the magic behind this macro is cast:
- When you click any tab, that particular scenario’s input values are loaded by running ChangeScenario macro
- When you click the ‘x’ button, that particular scenario’s tab is hidden and other tabs are moved accordingly by running the macro RemoveTab.
- When you click the ‘+’ button, a new scenario tab is displayed by un-hiding one of the remaining tabs. This uses the macro AddTab.
- When you close the workbook, the tab order, scenario values are all preserved automatically.
The workbook uses very simple but clever macros to hide / un-hide tabs and display and select scenarios. I encourage you to dissect the macros and play with the file to understand it better. Go here to download the file.
Thank you Itay,
Thank you so much for sharing your work with us Itay. I have learned some valuable macro tricks exploring your code. I am sure our readers will be able to learn something from it. Thank you.
How do you handle multiple scenarios?
I never used a technique like Itay’s. Usually I prefer a scenario selection sheet with data validation and conditional formatting (more on this later in a post). I would like to know how you handle multiple scenarios. Please share using comments.
Share your workbooks, example files with us
I am always looking for new and interesting ways to solve problems using Excel. If you have something fun, exciting or useful to share, please email me your workbook / tip / article to at chandoo.d @ gmail.com. I would love to learn from you and share your ideas with others here.
11 Responses to “Fix Incorrect Percentages with this Paste-Special Trick”
I've just taught yesterday to a colleague of mine how to convert amounts in local currency into another by pasting special the ROE.
great thing to know !!!
Chandoo - this is such a great trick and helps save time. If you don't use this shortcut, you have to take can create a formula where =(ref cell /100), copy that all the way down, covert it to a percentage and then copy/paste values to the original column. This does it all much faster. Nice job!
I was just asking peers yesterday if anyone know if an easy way to do this, I've been editing each cell and adding a % manually vs setting the cell to Percentage for months and just finally reached my wits end. What perfect timing! Thanks, great tip!
If it's just appearance you care about, another alternative is to use this custom number format:
0"%"
By adding the percent sign in quotes, it gets treated as text and won't do what you warned about here: "You can not just format the cells to % format either, excel shows 23 as 2300% then."
Dear Jon S. You are the reason I love the internet. 3 year old comments making my life easier.
Thank you.
Here is a quicker protocol.
Enter 10000% into the extra cell, copy this cell, select the range you need to convert to percentages, and use paste special > divide. Since the Paste > All option is selected, it not only divides by 10000% (i.e. 100), it also applies the % format to the cells being pasted on.
@Martin: That is another very good use of Divide / Multiply operations.
@Tony, @Jody: Thank you 🙂
@Jon S: Good one...
@Jon... now why didnt I think of that.. Excellent
Thank You so much. it is really helped me.
Big help...Thanks
Thanks. That really saved me a lot of time!
Is Show Formulas is turned on in the Formula Ribbon, it will stay in decimal form until that is turned off. Drove me batty for an hour until I just figured it out.