Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Replacing Some Formulas with the Formula Results.

Replacing Some Formulas with the Formula Results

Written by Allen Wyatt (last updated July 18, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


8

Brian has a need to process a worksheet before it can be handed out to other people. What he needs is to eliminate most, but not all, of the formulas in the worksheet. He wants to step through all the cells in a selected range of cells and, if the cell contains a formula, check that formula. If the formula contains a reference (any reference) to a different worksheet in the current workbook, then the formula is ignored. If the formula does not contain such a reference, then the macro needs to replace the formula with the result of the formula.

This is a relatively straightforward task; all you need to do is have your macro step thorough the cells and find out if the cell contains a formula. If it does, then check to see if the formula contains an exclamation point. Exclamation points are used in formula references, such as the following:

=Sheet2!A1

So, if the formula contains an exclamation point, you can ignore it. If it doesn't contain an exclamation point then you can replace it with its value.

Sub ConvertFormulas1()
    Dim c As Variant
    Dim frm As String

    On Error Resume Next

    For Each c In Selection
        If c.HasFormula Then
            frm = c.Formula
            If InStr(1, frm, "!") = 0 Then
                c.Value = c.Value
            End If
        End If
    Next c
End Sub

There is one drawback to this approach: the exclamation point will appear in all formulas external to the current worksheet, including those that are in other workbooks. If you truly want to only replace formulas to other worksheets in the current workbook but ignore formulas that reference sheets on other workbooks, then you need to add some additional logic. The logic makes itself apparent when you look at how Excel references those other workbooks:

=[OtherWorksheet.xls]Sheet1'!$C$9

Note that the name of the other workbook is contained within brackets. Thus, after testing for the exclamation point (which informs you that the reference is to another worksheet, you need to check for the presence of a left bracket. If it is there, then the reference is not to a cell within the current workbook.

Sub ConvertFormulas2()
    Dim c As Variant
    Dim OtherSheet As Boolean
    Dim frm As String

    On Error Resume Next

    For Each c In Selection
        If c.HasFormula Then
            frm = c.Formula
            OtherSheet = False
            If InStr(1, frm, "!") Then
                OtherSheet = True
                If InStr(1, frm, "[") Then
                    OtherSheet = False
                End If
            End If
            If Not OtherSheet Then
                c.Value = c.Value
            End If
        End If
    Next c
End Sub

It should be pointed out that it would be relatively easy to modify the formula used in this macro so that it got rid of all external references while leaving the references to the current worksheet intact. In fact, all you need to do is get rid of the checking for the bracket and then get rid of the "Not" keyword in the structure that checks the OtherSheet variable.

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 (12158) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. You can find a version of this tip for the older menu interface of Excel here: Replacing Some Formulas with the Formula Results.

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

Calculating an IRR with Varying Interest Rates

You might wonder how you can calculate an IRR (internal rate of return) when the person repaying the loan pays different ...

Discover More

Using Fractional Number Formats

If you want information to display on the screen using fractions instead of decimals, you're in luck. Excel provides ...

Discover More

Sorting by Highlighting

The sorting capabilities of Word are quite handy and easy to use. What if you want to sort by something Word doesn't sort ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Specifying a Delimiter when Saving a CSV File in a Macro

You can, within a macro, save a workbook in several different file formats that are understood by Excel. However, you may ...

Discover More

Stopping a Checked Box from being Unchecked

When creating user forms for use in Excel, you are provided with a range of controls you can add, including check boxes. ...

Discover More

Determining If a Number is Odd or Even

If you need to know whether a particular value is odd or even, you can use this simple formula. Designed to be used in a ...

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 three less than 3?

2023-07-18 14:52:05

J. Woolley

Brian's requirement described in this Tip is to replace a formula like =A1 but not one like =Sheet1!A1 in "a worksheet before it can be handed out to other people." But what about a formula like =A1+Sheet1!A1? And what about the other issues discussed in my most recent comment below? Why not replace all of the formulas with their values?
At least this Tip provokes consideration of two useful points:
1. Be careful with your requirment specification; consider all possibilities.
2. Factoring an arbitrary Excel formula for analysis of its parts is difficult.


2023-07-17 18:10:24

J. Woolley

Brian wants to replace a formula with its value except a formula like
=...Sheet1!A1...
unless that formula is like
=...[Book1.xlsx]Sheet1!A1...
The Tip's macros ignore the fact that a formula might include an exclamation point or bracket as part of a text string like "Hello World!" or "[a-z]". Here are three examples:
=SEARCH("[*]",$A$1)
=SUBSTITUTE($A$1,"!","?")
=SUBSTITUTE(SUBSTITUTE($A$1,"[","("),"]",")")
If $A$1 contains the following text
[...]...!
the 1st example returns 1
the 2nd returns [...]...?
the 3rd returns (...)...!
Besides the issues raised by Philip and Willy, the Tip does not address these questions:
1. What if a formula's value is text that begins with an equal sign (=)?
2. What if a cell includes a legacy array formula like {=B2:B6*C2:C6} which was entered into D2:D6 using Ctrl+Shift+Enter (CSE)?
3. What if a cell is part of a dynamic array formula like =B2:B6*C2:C6 which was entered into D2 using Excel 2021+?
Here's my version to address all these points:

Sub ConvertFormulas3() 'consider only the currently selected range
'replace formula with value unless it refers to a sheet in this workbook
    Dim rSele As Range, rCell As Range
    Dim sForm As String, sTemp As String
    Dim bEnEv As Boolean, bKeep As Boolean
    Dim nCalc As Integer, nEx As Integer, nR As Long, nC As Long
    Dim WS As Worksheet, A As Variant, v As Variant
    Set rSele = Selection.SpecialCells(xlCellTypeFormulas)
    'for single cell Selection, confirm it is Special
    Set rSele = Application.Intersect(Selection, rSele)
    If rSele Is Nothing Then MsgBox "No formulas in Selection": Exit Sub
    With Application
        bEnEv = .EnableEvents
        .EnableEvents = False
        nCalc = .Calculation
        .Calculation = xlCalculationManual
    End With
    On Error Resume Next 'to insure events and calculations are restored
    rSele.Calculate 'update
    For Each rCell In rSele 'cells that had formulas
        If rCell.HasFormula Then 'might have been replaced already
            sForm = rCell.Formula
            bKeep = False
            nEx = 0
            Do While True 'there might be several "!"
                nEx = InStr((nEx + 1), sForm, "!") 'proceed left-to-right
                If nEx = 0 Then Exit Do
                sTemp = Replace(Left(sForm, nEx), "'", "") 'remove any "'"
                For Each WS In Worksheets
                    bKeep = (InStr(1, sTemp, (WS.Name & "!")) > 0) And _
                        (InStr(1, sTemp, ("]" & WS.Name & "!")) = 0)
                    If bKeep Then Exit For
                Next WS
                If bKeep Then Exit Do 'found ...Sheet!... not ...]Sheet!...
            Loop
            If Not bKeep Then 'replace formula with value
                If rCell.HasSpill Then 'dynamic array (Excel 2021+)
                    A = rCell.SpillingToRange.Value
                ElseIf rCell.HasArray Then 'legacy CSE array
                    A = rCell.CurrentArray.Value
                Else
                    A = rCell.Value
                End If
                If IsArray(A) Then 'array-from-range is base-1 2D
                    For nR = 1 To UBound(A, 1)
                        For nC = 1 To UBound(A, 2)
                            v = A(nR, nC)
                            If Left(v, 1) = "=" Then A(nR, nC) = "'" & v
                        Next nC
                    Next nR
                    rCell.Resize(UBound(A, 1), UBound(A, 2)) = A
                ElseIf Left(A, 1) = "=" Then
                    rCell = "'" & A
                Else
                    rCell = A
                End If
            End If
        End If
    Next rCell
    On Error GoTo 0 'not necessary
    With Application
        .EnableEvents = bEnEv
        .Calculation = nCalc
    End With
    Beep
End Sub

This macro uses the currently undocumented Range.SpillingToRange property.
Notice this macro does not replace a formula like
=...Sheet1!A1...[Book1.xlsx]Sheet1!A1...
nor a formula like
=...[Book1.xlsx]Sheet1!A1...Sheet1!A1...
Perhaps these should be highlighted for user consideration.
There might be other issues this macro does not address.


2023-07-17 08:06:17

Willy Vanhaelen

@J. Woolley
My comment is indeed meant for when in a workbook event handler macros are used (I use them a lot).


2023-07-17 03:58:01

Philip

@J. Woolley, I think the crux is in “unless the workbook contains other event handlers”. That’s something that may not be known at time of inserting the code from this tip. Or event handlers may even be added later. Replacing cell contents would (I believe) at least trigger recalculation of the workbook, so interrupting events and calculations is in my opinion “best practice” in cases as these (of course including re-enabling them at the end)


2023-07-16 12:12:58

J. Woolley

The Tip includes the following sentence: "If you truly want to only replace formulas to other worksheets in the current workbook but ignore formulas that reference sheets on other workbooks, then you need to add some additional logic."
I believe the Tip has reversed the logic in that sentence and it should be: "If you truly want to IGNORE formulas to other worksheets in the current workbook but REPLACE formulas that reference sheets on other workbooks, then you need to add some additional logic."


2023-07-16 10:17:29

J. Woolley

@Willy Vanhaelen, Philip
Unless your workbook has event handler macros, I don't see why it is necessary to disable events. And since the Tip's macros replace a formula with its current value, I don't see why automatic calculation matters. Please explain.


2023-07-16 10:05:40

Willy Vanhaelen

@Philip
Exactly, you got that right ! especially disabling application events. But don't forget to enable them again when finished .


2023-07-15 16:17:23

Philip

Haven’t tested this, but it would seem to me that the proposed macro is missing a step or two: disabling automatic calculation and disabling application events before replacing formulas with values … ?


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu 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.