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:
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:
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:
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:
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 If c.Formula <> Master.Range(c.Address).Formula 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 If c.Formula <> Master.Range(c.Address).Formula 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:
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:
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.
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.
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!
Your workbooks can contain many, many worksheets. Which of those worksheets are the largest, however? Here's some ideas ...Discover More
Excel allows you, in your formulas, to include references to cells on other worksheets. Those references include the name ...Discover More
Excel keeps a full set of properties related to workbooks. When it comes to worksheets, however, there is very little ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.