If you have used the ROI Calculator Spreadsheet tool that Shawn came out with before, you know that it takes several steps to get to the point where you can start entering in cost data and seeing that ROI. If your need for ROI data is only occasional, then this works great. In addition, if you click on the appropriate link below, you can download the latest version of this spreadsheet, which eliminates step 4 from Shawn’s procedure.
Microsoft Excel 2007 Version
Microsoft Excel 97-2003 Version
If, however, you find yourself running through these steps again and again on a frequent basis with no shortcut in sight, there is hope!
Introducing…the ROI Calculation Macro.
Imagine…you log in to Google Analytics, download the file into Excel, click a button and you’re done. All that remains is to fill in the appropriate cost data.
Now again, since the setup of this will take some time, this will be most useful for daily or weekly use. The instructions are for MS Excel 2007. If there are enough comments expressing interest in seeing a 97-2003 version of the instructions, we will come out with this in a later post.
You’ll need to download this file (Note: You may need to right click and choose “Save link as”.):
ROI Calculation Macro for MS Excel 2007
Make sure to save it in a location where you can find it again later.
STEP #1 Open Excel.
STEP #2 Click “View” tab.
STEP #3 In the “Window” box click “Unhide”.
If you see PERSONAL in the Unhide dialog box, close Unhide dialog box and proceed to step 4. Do not unhide PERSONAL as this will cause problems later. If you don’t see PERSONAL in the Unhide dialog box or if Unhide is unclickable, most likely you do not yet have a personal workbook to store macros in. Go ahead and close the Unhide dialog box.
Follow these instructions to create a personal workbook:
STEP #3a Click the “View” tab.
STEP #3b In the “Macros” box click “Macros”, then choose “Record Macro”. (Note: if the “Macro” window pops up instead, close it, and try clicking a little lower so that it gives you the option to choose “Record Macro”.)
STEP #3c Under “Store macro in:” select “Personal Macro Workbook”.
STEP #3d Click the “OK” button.
STEP #3e In the “Macros” box click “Macros”, then choose “Stop Recording”.
STEP #4 If you have a “Developer” tab, click on it and proceed to step 5. If there is no “Developer” tab, do the following:
STEP #4a Click the Office Button.
STEP #4b Click Excel Options.
STEP #4c Check the box for “Show Developer tab in the Ribbon”.
STEP #4d Click the “OK” button.
STEP #5 In the “Code” box Click “Visual Basic”.
STEP #6 Right click VBAProject(PERSONAL.XLSB). Note: Do not click VBAProject(Book1).
STEP #7 Select Import File.
STEP #8 Find and Select Conversion.bas (It will be where you saved it in the beginning) and click “Open”.
STEP #9 Close Microsoft Visual Basic.
STEP #10 Click the Office Button.
STEP #11 Click Excel Options.
STEP #12 Click the “Customize” tab.
STEP #13 Under “Choose commands from:” select Macros.
STEP #14 Select “PERSONAL.XLSB!Conversion” and click the “Add > >” button.
STEP #15 Click “Modify”.
STEP #16 Select a symbol for the macro button and click “OK”.
STEP #17 Click “OK”.
Congratulations! You have finished setting up your ROI Calculation Macro.
To Use this macro:
1. Follow steps 2-3 in Shawn’s Procedure.
2. Click macro button located in the upper left on the quick access toolbar. It should look like the symbol that you chose in step 16.
3. Fill in the cost data. Excellent…
There you have it. If you found this macro useful or if you need any additional help implementing it, please let me know.
