AutoFormulaBox Macro

The AutoFormulaBox macro was created by Ola Holmstrom in 2002 and improved by Timir Karia in 2004. The AutoFormulaBox macro greatly simplifies the task of entering comments into cells in an Excel spreadsheet to show formulas in the cells.

Specific instructions for Excel 2007 appear at the bottom of this page.

First, search your computer for files that end in “.xla”. These files are add-ins for Microsoft Excel. We are going to be installing the AutoFormulaBox add-in, and we want to put it in the same place as the other add-ins on your computer.

To search your computer using Windows Explorer, select the “Search” button and fill in the boxes like this:

Click on “Search” at the bottom of the box, and your computer will search for “.xla” files. When it is done, you’ll get a window like this:

The specific appearance of your computer will be a little different, but the main thing here is to get an idea of where the add-in files are stored. Here, we can see that there are a lot of ".xla" files in the folder C:\Program Files\Microsoft Office\OFFICE11\Library. So it seems logical to save the new add-in there.

(This is not critical. The macro will still work if you save it somewhere else. This step is simply intended to help keep your computer well-organized.)

Now right-click here to download the add-in file. Save it in the folder that contains your other add-in files.

If you're using Excel 2007, skip this section and look below, where it says "Excel 2007".

Excel 2003

Now start Excel and select Tools – Add-ins. You might see a little box for “Auto Formula Comment”; if so just check the box. If not, click “browse” and go to the place where you saved the file.

Excel 2007

Click the Microsoft Office Button (this is the button in the upper left corner) , and then click Excel Options.

Click the Add-Ins category.

In the Manage box, click Excel Add-ins, and then click Go.

To load an Excel add-in, do the following:

In the Add-Ins available box, select the check box next to the add-in that you want to load, and then click OK.
Tip: If the add-in that you want to use is not listed in the Add-Ins available box, click Browse, and then locate the add-in.

If the add-in is not currently installed on your computer, click Yes to install it.

Running the Macro (both Excel 2003 and 2007)

Once it’s installed, the macro will automatically create a cell comment showing your formula. All you need to do is select a cell (or cells) with a formula in it and hit Shift+Ctrl+f.

-- Thanks to Sharon Sarosky '09 and Noel Fairbanks '10 for their help keeping these notes up to date. Please e-mail dj114@columbia.edu with proposed changes/additions.