Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Pulling Formulas from a Worksheet.

Pulling Formulas from a Worksheet

Written by Allen Wyatt (last updated June 11, 2021)
This tip applies to Excel 97, 2000, 2002, and 2003


3

Excel allows you to display the formulas in a worksheet simply by making sure the Formulas check box is selected on the View tab of the Options dialog box. This can be a handy way to print a worksheet that shows the formulas used to create the worksheet.

However, this approach only works well if the formulas used in the worksheet are rather short. If the formulas are longer, then understanding a worksheet with formulas displayed can quickly become a bothersome chore.

One solution is to pull the formulas from Excel and place them in a program such as Word. Why Word? Because you can easily format text attributes (such as typeface and point size) to best display your formulas. You can also add additional text to explain the formulas, if desired.

The simplest way to get formulas from Excel into Word is to follow these steps:

  1. In Excel, choose Options from the Tools menu. Excel displays the Options dialog box.
  2. Make sure the View tab is selected. (See Figure 1.)
  3. Figure 1. The View tab of the Options dialog box.

  4. Ensure that the Formulas check box is selected.
  5. Click on OK. Excel should now be displaying formulas.
  6. Select the cells whose formulas you want to copy to Word.
  7. Press Ctrl+C to copy the cells to the Clipboard.
  8. Switch to Word.
  9. Position the insertion point where you want the information added.
  10. Choose Paste Special from the Edit menu. Word displays the Paste Special dialog box. (See Figure 2.)
  11. Figure 2. The Paste Special dialog box in Word.

  12. Choose the Unformatted Text option.
  13. Click on OK.

At this point your formulas are in Word, and you can do with them as you see fit. While this approach works well, it can become bothersome to do this over and over again if you have a large number of formulas to copy. If you are in such a situation, you would benefit from having a macro that actually pulled the formulas and placed them in a Word document for you. The following Excel macro will do just that:

Sub WriteFormulasToWord()
    Dim Wrd As New Word.Application
    Dim CellTxt As String
    Dim CellAddr As String
    Dim SRow As Long
    Dim SCol As Long

    Wrd.Visible = True
    Wrd.Documents.Add

    Wrd.Selection.TypeText Text:="List of the Formulas of Sheet """ _
      & ActiveSheet.Name & """ in Workbook """ _
      & ActiveWorkbook.Name & """."
    Wrd.Selection.TypeText Text:=vbCrLf & vbCrLf

    'Change the following line to pick the number of columns
    For SCol = 1 To 5
        'Change the following line to pick the number of rows
        For SRow = 1 To 10
            If Cells(SRow, SCol).HasFormula Then
                CellAddr = Chr(64 + SCol) & Trim(Str(SRow)) & vbTab
                CellTxt = ActiveSheet.Cells(SRow, SCol).Formula
                Wrd.Selection.TypeText Text:=CellAddr & CellTxt
                Wrd.Selection.TypeText Text:=vbCrLf
            End If
        Next SRow
        Wrd.Selection.TypeText Text:=vbCrLf
    Next SCol
End Sub

There are a couple of things to note in this macro. First of all, you can change the range of rows and columns over which the macro works by changing the noted For statements that use both SCol and SRow. In the example shown above, the macro pulls formulas from columns 1 through 5 (A through E) and rows 1 through 10.

In addition, this macro will not work properly unless you set up Excel macros to handle references to Word objects. You do that by following these steps within the VBA Editor:

  1. Choose References from the Tools menu. VBA displays the References dialog box. (See Figure 3.)
  2. Figure 3. The References dialog box.

  3. Scroll through the list of references until you see one called Microsoft Word Object Library. (There may be a version number included in the reference name, such as Microsoft Word 11.0 Object Library.)
  4. Make sure the check box to the left of the object library is selected.
  5. Click on OK.

An advantage to using a macro to actually pull your formulas is that you can customize exactly what is placed in the Word document. In the case of this macro, the address of the cell is inserted, followed by a tab character, and then the actual formula. You could just as easily change the information inserted to be anything you need in your particular instance.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3190) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Pulling Formulas from a Worksheet.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Month for the Nth Sunday

Doing math with dates is quite easy in Excel. As this tip illustrates, this fact makes it easy to figure out the Nth ...

Discover More

Summing Absolute Values

You can easily sum a series of values in Excel, but it is not so easy to sum the absolute values of each value in a ...

Discover More

Adding Automatic Time Stamps

Your computer knows the current date and time, and Word provides ways you can get that date and time into your document. ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (menu)

Counting Only Money Winners

If a series of cells contain the amount of money won by individuals, you may want to count the number of individuals who ...

Discover More

Matching Formatting when Concatenating

Convert a numeric value to text and you may be surprised by how Excel displays the value. Here's a run-down on exactly ...

Discover More

Figuring Out the Low-Score Winner

Need to figure out the lowest score in a range of scores? Here's the formulas to get the information you need.

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is five more than 0?

2019-09-12 22:22:14

Tlaloc Rodriguez

I got an error at the 2nd line [i.e. 'Dim Wrd As New Word.Application'], even with both MS Office & MS word references added in Tools.
It turns out I just needed MS Office library references and not for Word, then it worked with a small formula edit.

The following code worked in Excel 2016:
======

Sub WriteFormulasToWord()
Dim Wrd As Object
Dim CellTxt As String
Dim CellAddr As String
Dim SRow As Long
Dim SCol As Long

Set Wrd = CreateObject("Word.Application")

Wrd.Visible = True
Wrd.Documents.Add

Wrd.Selection.TypeText Text:="List of the Formulas of Sheet """ _
& ActiveSheet.Name & """ in Workbook """ _
& ActiveWorkbook.Name & """."
Wrd.Selection.TypeText Text:=vbCrLf & vbCrLf

'Change the following line to pick the number of columns
For SCol = 1 To 5
'Change the following line to pick the number of rows
For SRow = 1 To 10
If Cells(SRow, SCol).HasFormula Then
CellAddr = Chr(64 + SCol) & Trim(Str(SRow)) & vbTab
CellTxt = ActiveSheet.Cells(SRow, SCol).Formula
Wrd.Selection.TypeText Text:=CellAddr & CellTxt
Wrd.Selection.TypeText Text:=vbCrLf
End If
Next SRow
Wrd.Selection.TypeText Text:=vbCrLf
Next SCol
End Sub


2017-02-01 19:16:16

Kat

Macro is not naming the columns correctly once it exceeds col Z. Eg col BA is showing as { which defeats the purpose of the macro.
PS: I am running this macro in Office 2016.


2016-12-10 09:49:43

Dave Onorato

The easy quick way to switch to "display formulas" is this...
<ctrl> + `

Hold the Ctrl key and hit the key with the tilde(~) and the other accent(`). It's to the left of the numbers row on my keyboard, above the tab.

And do it again, and the display is normal, showing calculation results.

I remember it as "Control Tilda"


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.