# Comparing Formulas on Two Worksheets

by Allen Wyatt
(last updated February 17, 2020)

Rick has two worksheets that he needs to compare to each other to highlight differences. The comparison needs to not compare what is displayed, but the formulas in each of the cells. In this way Rick hopes to discover where the formulas differ on each worksheet.

There are several ways you can go about comparing formulas. In certain versions of Excel you have access to a comparison add-in that can handle the task for you. If you are using Office Professional Plus 2013 or Office 365 ProPlus, you can use the Spreadsheet Compare add-in. Information on this add-in can be found here:

```https://support.office.com/en-us/article/Overview-of-Spreadsheet-Compare-13fafa61-62aa-451b-8674-242ce5f2c986
```

If you are using Office Professional Plus 2013 or Office 365 you can also use the Spreadsheet Inquire add-in. Information on this add-in is located here:

```https://support.office.com/en-nz/article/What-you-can-do-with-Spreadsheet-Inquire-ebaf3d62-2af5-4cb1-af7d-e958cc5fad42
```

Recognizing that not everyone is using one of these Excel versions or may not want to use an add-in, there are other things you can do. In Excel 2013 and later versions there is a handy worksheet function called FORMULATEXT. You can use this function to retrieve the formula stored in a cell, in this manner:

```=FORMULATEXT(A7)
```

This returns the formula contained in cell A7 (in this case). If the cell doesn't contain a formula, then it returns an #N/A error. You could use this behavior to create in a "comparison worksheet" an indicator as to whether the formulas are equal or not. Just create the new worksheet and place this in cell A1:

```=IF(FORMULATEXT(Sheet1!A1)=FORMULATEXT(Sheet2!A1),"","Different")
```

Copy the formula as far down and as far right as desired. It marks differences between the corresponding cells on Sheet1 and Sheet2.

Remember that FORMULATEXT was introduced in Excel 2013, so this approach won't work in older versions of Excel. If you are using another version (or, even, if you are using Excel 2013) you could use a macro to mark the differences between worksheets. There are many macro approaches you could use; the following is a short way to do the comparison.

```Sub ComparaFormulas1()
Dim Check As Worksheet
Dim Master As Worksheet
Dim c As Range

Set Check = ActiveSheet
Set Master = Worksheets("Master")

For Each c In Check.UsedRange
If c.HasFormula Then
c.Interior.Color = RGB(255, 0, 0)
End If
End If
Next c
End Sub
```

To use this macro, display the workbook you want to compare. It assumes you want to compare to the same cells in a worksheet called "Master." (You can obviously change that in the macro if your "standard" worksheet has a different name.) Every cell on the current worksheet is compared to the corresponding cell on the "master" worksheet. If the cells contain formulas and those formulas are different, then the background color of the cell is changed to red in the current worksheet.

Such an approach obviously changes the formatting of the worksheet being compared. If you prefer to not change the formatting, but instead simply want a list of cells with differences, you could use the following variation on the macro:

```Sub ComparaFormulas2()
Dim Check As Worksheet
Dim Master As Worksheet
Dim c As Range
Dim sTemp As String
Dim lDif As Long

Set Check = ActiveSheet
Set Master = Worksheets("Master")
sTemp = ""
lDif = 0

For Each c In Check.UsedRange
If c.HasFormula Then
lDif = lDif + 1
sTemp = sTemp & vbCrLf & lDif & ": " & c.Address
End If
End If
Next c
If lDif > 0 Then
sTemp = "These were the differences" & vbCrLf & sTemp
Else
sTemp = "There were no differences"
End If
MsgBox sTemp
End Sub
```

You could also create a user-defined function (UDF) that accepts ranges for the comparisons. That way you could use it in a variety of ways.

```Function CompareFormulas3(rng1 As Range, rng2 As Range)
Dim x As Long

If rng1.Count <> rng2.Count Then
'Range sizes do not match
CompareFormulas = CVErr(xlValue)
Else
CompareFormulas = True    ' Assume all the same
For x = 1 To rng1.Count
If rng1(x).Formula <> rng2(x).Formula Then
'Formulas do not match
CompareFormulas = False
x = rng1.Count    ' No need to keep comparing
End If
Next x
End If
End Function
```

If you just want to confirm that a range of cells in both worksheets have identical formulas you can just use something like:

```=CompareFormulas3(Sheet1!A1:Z1000,Sheet2!A1:Z1000)
```

The function returns TRUE if all the cells have identical formulas, FALSE if any of the cells have different formulas, or #Value error if the 2 ranges are not the same size.

If you want to highlight differences, you can use the UDF within a conditional formatting rule. Assuming you want to apply the conditional format to the cells in Sheet1, just specify that the rule should use a formula and then use this as the formula:

```=NOT(CompareFormulas3(Sheet2!A1,A1))
```

If any of the cells in Sheet1 do not match the corresponding cells in Sheet2, they are formatted according to whatever formatting you defined in the conditional formatting rule.

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 (13400) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.

##### 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

Letters Turn into Squares

Imagine that you are typing away, and all of a sudden your beautiful prose turns into a series of small rectangles that ...

Discover More

Creating and Naming a Worksheet Using a Macro

You can use macros to make your common Excel tasks easier and faster. For instance, if you routinely need to create new ...

Discover More

Disabling Track Changes Completely

Have you ever had problems with a document because you accidentally pressed a keyboard shortcut? The good news is that ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

##### More ExcelTips (ribbon)

Determining the Complexity of a Worksheet

If you have multiple worksheets that each provide different ways to arrive at the same results, you may be wondering how ...

Discover More

Automatically Renaming Worksheets

Excel allows you to easily add and remove worksheets from a workbook. You may want a way to automatically rename all of ...

Discover More

Lotus Grouped Worksheets

Not all spreadsheet programs are created equal; there are some things that can be done in others that can't be done in ...

Discover More
##### Subscribe

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

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}] 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 four more than 7?

2019-05-23 16:44:56

Yvan Loranger

Workaround if you don' t have the FORMULATEXT function:
highlight the cell of interest then select all [except the 1st =] in the formula bar and Copy-Paste in another cell.

p.s. LibreOffice [competitor to MS-Office, successor to OpenOffice] has FORMULA() instead of FORMULATEXT(), works the same way.

2019-05-21 16:26:11

James Sweatt

I may be missing something, but it seems the macros presented could miss something:

Suppose the "Master" sheet had a formula in a certain cell, but the "Check" sheet did NOT have a formula in that cell;
No comparison would be made? And no difference would be highlighted?

A possible way around it, without changing the essence of the macro, would be to run it a second time with the "Master" and "Check" sheets switched.

2018-08-24 07:37:19

Thierry BUR

Hi,

It seems that the CompareFormulas3() function does'nt make any difference between a true formula and a constant. So if you have in a range a cell constant that differs from its counterpart it detects a difference which is a false positive, IMHO.

Maybe you should try to test "hasformula" to distinguish between constant only and true formula.

Thierry

2018-04-15 19:24:42

king_excel

You can compare 2 Excel sheets with highlighting the differences (update, new, removed) cells and rows without using any formula using Dose for Excel Add-In:https://www.zbrainsoft.com/Compare-Sheets.html

2015-09-05 11:58:07

Kieranz

Hi Joe
It would be educative for newbies if you could upload or send me a copy
Many thks. Rgds KNZ

2015-08-23 09:07:20

Joe Labellarte

Hi Allen,

I have a macro that I have developed to compare cell values on 2 worksheets (created initially with XL version 2003 and updated/used thru the years) - I'd be glad to send it to you and can take a look at it and if of any value feel free to share it with your audience.

Joe Labellarte
224-279-9089
RedBrickMatrix.com

##### 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.