## Compare if multiple cells are equal – Easy tutorial

As we all know, comparing two cells for equality in Excel is straightforward using the formula =A1=B1. However, this method falls short when you need to check if multiple cells have the same value. Today, I will discuss several formulas that can be used to compare multiple cells for equality in Excel.

#### Compare if multiple cells are equal （Not case sensitive）

To compare multiple cells without considering the case sensitivity, you can use the AND or COUNTIF formula. These methods are helpful when the text case might vary but you still want to treat the contents as equal.

#### Using the AND Formula

1. Please enter or copy the following formula into a cell where you want to get the result, and then press Ctrl + Shift + Enter keys to get the result:
2. `` =AND(A2=B2:D2)``
3. And then, select the formula cell, drag the fill handle to the range that you want to apply this formula. If the cell values are equal, it will display TRUE, otherwise, it will display FALSE, see screenshot:
Tip:

If you want to return a result other than TRUE or FALSE, please wrap AND in the IF function like this:

`` =IF(AND(A2=B2:D2), "Yes", "No")``

#### Using the COUNTIF Formula

The COUNTIF formula can also be used for a case-insensitive comparison. This method counts how many times a specific value appears in a range and compares it to the number of cells in that range.

Please enter or copy the following formula into a cell where you want to get the result, and then drag the fill handle down to fill other cells. The formula checks if the value in A2 appears exactly 4 times in the range A2:D2. see screenshot:

Tip:

To return a specific result other than TRUE or FALSE, please wrap COUNTIF in the IF function like this:

`` =IF(COUNTIF(A2:D2, A2)=4, "All match", "Not match")``

#### Compare if multiple cells are equal with formals (Case sensitive)

When comparing multiple cells, we can nest the EXACT function within the AND function to check if all cells match exactly. This means the formula will return TRUE only if every cell in the comparison has the exact same value, including the letter case.

Please enter or copy the following formula into a cell where you want to get the result, and then drag the fill handle down to fill other cells. See screenshot:

`` =IF(AND(EXACT(A2:D2, A2)), "Yes", "No")``

#### Compare if multiple cells are equal and then highlight them

With Kutools for Excel’s AI Aide, you can compare multiple cells for equality with just a few clicks. No need to remember complex formulas or spend time setting up conditional formatting. Our intuitive tool does it all for you!

1. Select the data range where you want to highlight rows if all cells in a row are equal.
2. Then, click Kutools AI > AI Aide to open the Kutools AI Aide pane.
3. In the Kutools AI Aide pane, type the following requirement into the chat box, and click Send button or press Enter key to send the question.
• Case-Insensitive Comparison:
• “Check if multiple cells are equal in each row of the selection (not case sensitive), and then highlight them with light red color”
• Case-Sensitive Comparison:
• “Check if multiple cells are equal in each row of the selection (case sensitive), and then highlight them with light red color”
4. After analyzing, click Execute button to run. Kutools AI Aide will process your request using AI and return the results directly in Excel.

In summary, Excel offers several robust methods for comparing multiple cells to determine if they hold equal values, addressing both case-sensitive and case-insensitive scenarios. You can choose the one that best suits your needs. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials. Please click here to access them. Thank you for reading, and we look forward to providing you with more helpful information in the future!

#### Related Articles:

• Compare two columns to find duplicates in Excel
• Identifying duplicate values across two columns in Excel is a common task in data analysis. This helps in pinpointing data entry errors, duplicate records, or for data cleansing purposes. This article will guide you on how to efficiently and accurately identify duplicate or matches in two columns.
• Compare two cells and return Yes if they are matched
• Supposing you have two columns as the following screenshot shown, and now, you need to compare the cells in the same row of these two columns then return Yes if they are exactly matched, how can you do to deal with it? This article will show you a method to quickly get it done in Excel.
• Compare two columns and return values from the third column
• For example, I have the following two columns, column A is some projects, and column B is the corresponding names. And here, I have some random projects in column D, now, I want to return the corresponding names from column B based on the projects in column D. How could you compare the two columns A and D and return the relative values from column B in Excel?

### 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 Toolsets:  12 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

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!
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
hi ... i have a five letter word ... the five letters are in five cells in a row ... i want to put the letters of the word ... into alphabetical order ... into 26 columns ... A to Z
This comment was minimized by the moderator on the site
Hi.. I have a range of cells (say - a1:h1). this range can contain empty cells as well as numbers (at start, middle or end of range). These numbers can be similar or different, at any situation. I need a formula to see if all the 7 cells (a1:h1) have similar value then the similar value should be displayed in cell i1 or an error. Can someone help please.
This comment was minimized by the moderator on the site
I want to compare 3 cells and filter out the rows which doesn’t fulfil the criteria, for example Like 3 columns A,B,C have different values and from these columns if anyone cell doesn’t cross   Like for example 100 that entire row must be deleted
This comment was minimized by the moderator on the site
Hi，Kishan,Do you mean to delete the rows that the cells are not equal?If so, please apply the formula in this article to test if the cells are equal or not, and then filter based on the formula cells to display all FALSE rows, and last, select and delete the filtered rows as you need.Please try, hope it can help you!
This comment was minimized by the moderator on the site
I am trying to compare the same cell in multiple tabs to see whether the contents are identical.  If so, then return that identical value; and if not, then return "NO".  How am I able to accomplish this?  It appears that the AND(EXACT function doesn't work across multiple sheets.
This comment was minimized by the moderator on the site
hello sir, i have 6 columns of 2 tables in different sheets, but i want to match 2nd tables data with 1st tables data.
There are no comments posted here yet