Microsoft Excel macro's for results and formulas on the same page
Ever want to display both results and cell formulas on the same printed page in Microsoft Excel? Then this set of macr'so might do the trick.
The following set of macro's was created out of the pure frustration of Excel's limitation of either only showing formula results, or the formulas themselves. This grew out of the need to print both in an MBA class I'm taking at the University of San Francisco (MBA 612 with Professor Grossman). Hating the fact of having to print two pages for every model, and after hearing much of the same frustration in class, I decided to do what I do best. Hack the program to make it do what our class needed.
Now, before you go off and claim this is the greatest thing in the world, let me stop you. It is not. It is a simple macro hack utilizing the fact that Excel uses the same comment operator as Visual Basic for Applications, and won't print the comment operator. Yes, I know that VBA has the cell.Formula reference, and I'm aware that if I wanted to be fancy I could have written a wizbang copy function that would copy the model, convert to true formula reference, and then resize to fit both on the same page. I still might do this in the future, but right now I needed something that would work today so I could do work. So that's all.
If you are just here for the macro's, they are available here: | Macro's for Cell Formulas
I'm not going to explain why they work the way they work (if you read the code, it's commented and programming 101 stuff). Instead, lets get down to the how to use these macro's.
The Tutorial
I've created a test excel spreadsheet for download that has the macro's already included, as well as a model setup for testing. This will be the file I use in the following tutorial. Download: | Macro/Model Test File
First, let's look at our model in the example file:
Wait a minute you say, what's this warning? Depending on your macro security settings, you may have to enable macro's for the file to be able to use them. Click "Enable Macro's" to continue.
Okay finally we have our example model on screen, which looks like this:
What we're going to do is simply duplicate the model next to itself by copy and paste:
Make sure that you are not just pasting values; you want to paste the formulas, otherwise the whole exercise is for naught.
Now, lets view the formulas for our copied model using the macro's. Select all the cells in the second model as below:
Now, access the macro menu by either clicking ALT+F8 or by going to Tools->Macro->Macro's. The screen below should appear:
You will should see two macro's (you may see more depending on if you have other macro's installed). The two macro's are fairly self explantory:
- ShowCellFormulas displays the cell formulas for the cells you just selected.
- RemoveCellFormulas can be run after ShowCellFormulas if you wish to return the cells to normal working order.
Run ShowCellFormulas by selecting "ShowCellFormulas" and clicking "Run". If done correctly, you should see the result below:
After changing the print settings (margins and landscape mode), you can see that you can print both models on the same page, one with results, one with formulas:
That's it! Select the cells you want to view formulas for, run the macro, and wiz-bang, they appear. If you want to use the same macro's over and over without having to copy and paste them into the VBA editor each time, you'll need to create or edit your personal.xls file which loads macro's each time Excel loads. For more information on this feature, please visit Microsoft step-by-step located at http://office.microsoft.com/en-us/assistance/HA010872961033.aspx.
