Skip to main content

Kutools for Office — One Suite. Five Tools. Get More Done.

How to check if two ranges are equal in Excel?

Author Zhoumandy Last modified

In Excel, we may run into the situation that we need to check whether two ranges are equal cell-by-cell and return the logical value TRUE or result YES when all the cells in the corresponding positions match. In this tutorial, we will talk about two quick ways to compare two ranges.

A screenshot showing two data ranges for comparison

Check if two ranges are equal with formula

Compare if two ranges are equal with a handy tool


In this case, we have two ranges of data in the table below. It is hard to determine whether the two ranges are equal by checking the multiple corresponding cells one by one. With the formulas below, things will be much easier. Please do as follows.

1. To get the TRUE or FALSE result, please copy the array formula below into cell I2 and press the Ctrl + Shift + Enter keys to get the result.

=IF(MAX(IF(A2:C8<>E2:G8,1,0))=1,"FALSE","TRUE")

A screenshot showing the Excel formula result for comparing two ranges

Notes

  1. To get the YES or NO result, please change FALSE and TRUE to No and Yes respectively in the formula.

    =IF(MAX(IF(A2:C8<>E2:G8,1,0))=1,"No","Yes")

    A screenshot showing the Excel formula result with Yes/No for comparing two ranges

  2. If you are using Excel for Microsoft 365, the following formulas can also help.

    =AND(A2:C8=E2:G8)

    =IF(AND(A2:C8=E2:G8), "YES","NO")

  3. The two ranges must have the same cell dimensions, otherwise the #N/A error value will be returned.

When we not only want to know whether two ranges are equal, but also the differences between the two ranges, the Select Same & Different Cells feature of Kutools for Excel can solve the problem easily and quickly.

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Itegarate with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...

1. Go to Kutools tab, and click Select > Select Same & Different Cells.
A screenshot showing the Kutools tab in Excel with the Select Same & Different Cells option selected

2. The Select Same & Different Cells dialog box pops up. Follow the steps below.

  • Select the two ranges without headers respectively in the Find values in and According to textboxes.
  • Choose the Single cell option in the Based on section.
  • Check the Different Values option in the Find section.
  • Specify the type of marking the differences in the Processing of results section. Choose between the Fill backcolor option and Fill font color option, or pick them both. Here I choose the Fill backcolor option.

A screenshot showing the Kutools Select Same & Different Cells dialog with options set for comparison

3. Click OK. The Kutools for Excel dialog pops up, showing 1 cell(s) have been selected. It means one difference between the two ranges.
A screenshot showing the Kutools result showing 1 difference between the two ranges

4. Click OK to close the dialog.

Note: If the two ranges are equal, Kutools for Excel dialog box will show No match was found.
A screenshot showing the Kutools dialog box with 'No match was found' message indicating no differences

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now


Other Operations (Articles)

How To Quickly Insert A Line Based On Current Date In Excel?
Supposing there is a sheet containing a row with dates, and now I want to insert a line right to the current date which will auto change while opening the workbook every day. Is there a trick can solve it in Excel?

How To Always Insert A Blank Row Below Instead Of Above In Excel?
As we all known, when inserting blank row in a worksheet, the blank row will always be inserted above the selected row or cell. But, sometimes, you may need to insert the row below the selected cell or row. How could you solve this job in Excel?

How To Insert A Blank New Row Automatically By Command Button In Excel?
In many cases, you may need to insert a blank new row into a specified position of your worksheet. In this article, we will show you how to insert a blank new row automatically by clicking on a Command Button in Excel.

How To Insert A Blank Row After Specific Text In Excel?
If you want to insert blank rows after a specific text as following screenshot shown, how to deal with it quickly and easily without inserting them manually one by one?

Best Office Productivity Tools

šŸ¤– Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

All Kutools add-ins. One installer

Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.

Excel Word Outlook Tabs PowerPoint
  • All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
  • One installer, one license — set up in minutes (MSI-ready)
  • Works better together — streamlined productivity across Office apps
  • 30-day full-featured trial — no registration, no credit card
  • Best value — save vs buying individual add-in