power4XL Microsoft Excel Resource Center
Free Macros, Formulas, Functions, Tutorials, Downloads, Add-Ins & More!

Freezing the model by using the "Paste Values" function

ShareThis

When you need to do more advanced analytics on your Monte Carlo simulation's output data, often simply setting recalculation to "manual" is insufficient.  This may be the case when you want to do sorting of the data, use it in a pivot table, graphing the data, etc.

For this kind of analysis it is best to have a clean "hard copy" of the data, with no embedded formulas and no RAND() functions to bother with.  To achieve this, follow these steps:

  1. Create a new worksheet or workbook where you want to save the new "hard copy".

  2. In your Monte Carlo simulation worksheet, select the entire output data range.  You can also include the model calculation cells if they are relevant for your analysis.  

  3. Copy the selection to your clipboard (CTRL+C or "EDIT - COPY").

  4. Return to the new worksheet or workbook where you want the hard copy, and place the cursor in the cell where you want the upper left corner of the data range pasted.

  5. Use the "PASTE SPECIAL..." item on the EDIT menu, then select "Values and number formats" and click "OK" (see illustration below).

  6. The data will be pasted at the location of your cursor, with all the formulas now replaced by the numeric values they returned.  If you have placed the hard copy in a new workbook, rather than just a new worksheet in the active simulation workbook, you can now manipulate this data table for analysis without triggering recalculations.  In either case you can work with the data without risk of damaging your model.

 

Prev | Contents | Next

Copyright notice:  This site and all content, including computer code and spreadsheet examples, are copyright 2006 by Fritz Dooley.  License is granted for individual users to download examples and to copy code directly into user's spreadsheets and Visual Basic for Applications files.  Users may not redistribute code in any way.  Providing hyperlinks to this web site is encouraged, but posting code and examples on other web sites is expressly forbidden.  "Microsoft" is a registered trademark of Microsoft Corporation.   Neither this web site nor Fritz Dooley is affiliated with Microsoft Corporation.