Yearning for Word-like footnotes in your spreadsheet? Here’s the solution

Yearning for Word-like footnotes in your spreadsheet? Here’s the solution

by Kara Soos

Application:

Microsoft Excel 2000/2002/2003/2004/2007

Operating Systems:

Microsoft Windows, Macintosh

Excel doesn’t offer an intuitive way to include footnotes in your spreadsheets. While you can create footers for your printouts, they don’t have the flexibility of Microsoft Word’s built-in Footnotes features. Our macro helps you emulate Word’s professional-looking footnotes without much legwork.

To add a footnote to our spreadsheet that imitate a word processor’s footnotes, we’ll:

• Build a UserForm in which we’ll type our footnote’s text.

• Insert code that formats our footnote correctly and adds our custom text to the worksheet’s footer.

• Create another brief macro that clears your worksheet’s footer in case you need to start fresh.

Excel is primarily a data analysis program, so there are some word-processing features that it just doesn’t offer. For instance, if you want to annotate your data, you won’t find an easy way to add a numbered footnote. But with our VBA macro, you can create footnotes that look just like the ones you love in Word.

Take note of this example

In our example, we want colleagues and clients to know that the income listed in our data table doesn’t include income from a 2006 acquisition. Here’s where a traditional Word footnote would come in handy. Lucky for you, you can emulate a word-processor footnote with our technique, as shown in Figure A.

Our footnote looks just like the ones you’re used to seeing in word-processing documents — and it took seconds to create with our macro.

Base the technique on a UserForm

You’ll need a way to tell Excel what text you want to include in the footnote. Our UserForm will fit the bill — and it will give us an opportunity to trigger the rest of our macro procedure. Let’s design the UserForm.

Download: You can follow along with our technique by downloading and extracting the file footnotes.xls from the URL given at the beginning of this article. The code listed in this article is also available in a text file at the same location.

To create a UserForm:

1. Launch Excel and open our sample file, or any spreadsheet to which you want to add footnotes.

2. Press [Alt][F11] to display the Visual Basic Editor (VBE).

3. Choose Insert | UserForm from the VBE’s menu bar. The Toolbox should display. If not, you can view it by choosing View | Toolbox from the menu bar.

4. Change the Caption property in the UserForm’s Properties window to Add Footnote Text. (If you don’t see the Properties window, press [F4] to view it.)

5. Click the Label button label in the Toolbox and click and drag on the blank UserForm to create a label. Add the text Please add your footnote text and then click OK.

6. Click the TextBox button textbox in the Toolbox. Click and drag a large text box to hold the footnote text.

7. Click the CommandButton button commandbutton in the Toolbox and create a button on your UserForm. Enter the text OK.

Your UserForm should