Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to compare if multiple cells are equal in Excel?

As we all know, to compare if two cells are equal, we can use the formula A1=B1. But, if you want to check if multiple cells have the same value, this formula will not work. Today, I will talk about some formulas to compare if multiple cells are equal in Excel.

Compare if multiple cells are equal with formulas


Compare two ranges and find if the cells are equal or not:

With Kutools for Excel’s Compare Cells utility, you can quickly find the same or different values between two cells.

doc-check-if-equal-cells-6

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


arrow blue right bubble Compare if multiple cells are equal with formulas


Supposing I have the following data range, now I need to know if the values in A1:D1 are equal, to solve this task, the following formulas will help you.

doc-check-if-equal-cells-1

1. In a blank cell besides your data, please enter this formula: =AND(EXACT(A1:D1,A1)), (A1:D1 indicates the cells that you want to compare, and A1 is the first value in your data range)see screenshot:

doc-check-if-equal-cells-2

2. Then press Ctrl + Shift + Enter keys together to get the result, if the cell values are equal, it will display TRUE, otherwise, it will display FALSE, see screenshot:

doc-check-if-equal-cells-3

3. And select the cell then drag the fill handle to the range that you want to apply this formula, you will get the result as follows:

doc-check-if-equal-cells-4

Notes:

1. The above formula is case sensitive.

2. If you need to compare the values without case sensitive, you can apply this formula: =COUNTIF(A1:D1,A1)=4, (A1:D1 indicates the cells that you want to compare, A1 is the first value in your data range, and the number 4 refers to the number of cells that you want to check if), then press Enter key, and you will get the following result:

doc-check-if-equal-cells-5


Related article:

How to check if the number is integer in Excel?


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

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!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Suresh badhani · 2 months ago
    sO GRAET HELPFUL
  • To post as a guest, your comment is unpublished.
    AG · 11 months ago
    =COUNTIF(A1:D1,A1)=4 ---> mind = blown
  • To post as a guest, your comment is unpublished.
    M.Irfan · 1 years ago
    how can i find the matching numbers in a column
  • To post as a guest, your comment is unpublished.
    ar · 1 years ago
    hi, i want to display the value / text of a table if a particular value is below 100. is there any option
  • To post as a guest, your comment is unpublished.
    Dal · 1 years ago
    Hi
    I need to highlight multiple numbers that appear three times or more in one column.
    Can anyone assist?
    Thanks
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello, Dal,
      To highlight the cell values that appear three times or more, the Conditional Formatting can help you. Please enter this formula into the Conditional Formatting: =COUNTIF($F$2:$F$500,F2)>2 (Note: Change the cell references to your need),and then choose one color for highlighting the cells.
  • To post as a guest, your comment is unpublished.
    Bob Olanski · 1 years ago
    The "And(Exact...." works great if you have a set range. In my case, It is multiple cells within a range of columns.

    Basically need the comparison to evaluate A7, B7, M7, AD7, AE7, AX7, BC7, and BQ7, and return "TRUE" if they are identical text.
  • To post as a guest, your comment is unpublished.
    sonia · 1 years ago
    i want to know part number from in one column is duplicated in any other cells of adjacent column
  • To post as a guest, your comment is unpublished.
    Paul · 1 years ago
    I have Cells A1=35, A2-45, A3=50, A4=75, A5=80, B1=40, B2=19, B3=55, B4=31, B5=22. In this, A1 IS 35 AND B1 IS 40. What I want to find out is find out whether anywhere else in the range the this combination is appear or repeated and how many times it is repeated i need to know. Can anyone help me out in this. If there is no formula, a vba would be helpful, thanks in advance.
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hi, Paul,
      Can you give your problem more clearly and you can upload an attachment screenshot here.
      Thank you!
  • To post as a guest, your comment is unpublished.
    Jim T · 2 years ago
    Interesting, this fails for me in 2016 version
    • To post as a guest, your comment is unpublished.
      skyyang · 2 years ago
      Hello, Jim,
      The above formulas work well in Excel 2016, when you apply the first formula, please remeber to press Shift + Ctrl + Enter keys together to get the correct result.
      Please try again, thak you !
  • To post as a guest, your comment is unpublished.
    jignesh · 2 years ago
    i have six column a b c d e f, in column e i want to put higher value among a & c, and in column f it is dependent on e, like if e is a than b, if e is c than d. how could i solve this???
  • To post as a guest, your comment is unpublished.
    Holly · 2 years ago
    This worked great for me I did =And(exact(A2:C2,D2:F2)) to validate that cells A2-C2 exactly matched D2-F2.

    Thanks so much!
  • To post as a guest, your comment is unpublished.
    Shan · 2 years ago
    I wanted to compare the alphabets with 4 columns for every 4 rows. For example,
    A = [aabc;dbba;a] B = [aabc;baba;c] C = [aabc;dbba;b] D = [bcac;bcbc;a]

    If we look at the table above shows first four rows, "aabc" repeated in 3 columns. So, out of 4, 3 gets correct matching.it is 3/4. for the second cycle, we can see "dbba" repeated so it 2/4.This is for 4 rows vs 4 rows, now the same matching method for taking one line down, two line down and three line down comparing with other string. Means, the rows will be constant and just the alphabets changing. The formula can be in the new column say E and results will be there. Any idea?
  • To post as a guest, your comment is unpublished.
    Shan · 2 years ago
    How about comparing multiple rows with certain columns?
    I wanted to compare the alphabets with 4 columns for every 4 rows.For Example,

    A = [aabc;dbba;a] B = [aabc;baba;c] C = [aabc;dbba;b] D = [bcac;bcbc;a]

    If we look at the table above shows first four rows, "aabc" repeated in 3 columns. So, out of 4, 3 gets correct matching.it is 3/4. for the second cycle, we can see "dbba" repeated so it 2/4.This is for 4 rows vs 4 rows, now the same matching method for taking one line down, two line down and three line down comparing with other string. Means, the rows will be constant and just the alphabets changing. The formula can be written in a new cell may be the next column E. Any Idea?
  • To post as a guest, your comment is unpublished.
    Frances H · 2 years ago
    Useful formula - works great thanks. I'm curious why do you have to use Ctrl + Shift + Enter ?
  • To post as a guest, your comment is unpublished.
    Archana Maru · 2 years ago
    Hi there!! I am very new to this excel validation and hence do not know much. I am trying to write a function in which if the values in A1 to A10 is True then the result in A11 should show Accepted and if they are not same then the result should prompt Rejected. Can you please help me write a forumula for the same.
    Thanks
  • To post as a guest, your comment is unpublished.
    Mimi · 2 years ago
    The cells that i want to compare value are not next to each other. So when i separate them in the formula with a "," it doesn't work.
  • To post as a guest, your comment is unpublished.
    EricMartinson · 2 years ago
    This is a great tip and did exactly what I needed. Very helpful! Thanks!
  • To post as a guest, your comment is unpublished.
    Sergey · 3 years ago
    This doesn't work for me, the result is returned as #VALUE!
    • To post as a guest, your comment is unpublished.
      Damo · 2 years ago
      You are not doing the "Ctrl + Shift + Enter keys together to get the result" part... I had the same issue, then read further in the article
  • To post as a guest, your comment is unpublished.
    imran · 3 years ago
    What about the empty cells?
    I have data on multiple columns and the column count is not same
    data is spread across columns upto 17
    some have data up to 4 columns