Comparison functions in Excel - How to use comparison functions and examples using comparison functions

With a large amount of data, you want to check for duplicates by checking normally, it is really hard. In this article, I introduce you to the Comparison Functions in Excel, with practical examples to help you easily visualize.

Comparison functions in Excel - How to use comparison functions and examples using comparison functions Picture 1

1. Use the Exact function to compare data

Description

The Exact function compares two text strings, returning True if the two strings match, and returning False if the two data strings are different. Note The Exact function is case-sensitive.

Syntax

EXACT (Text1, Text2)

Where: Text1, Text2 are two text strings to be compared, are two required parameters.

Attention

- The Exact function is case sensitive, but it does not distinguish format.

For example

Compare the corresponding data in rows between column 1 and column 2. In the cell to compare enter the formula: EXACT (D4, E4)

Comparison functions in Excel - How to use comparison functions and examples using comparison functions Picture 2

Pressing Enter results returns a False value, which means the two values ​​do not match because of the first and lowercase letters:

Comparison functions in Excel - How to use comparison functions and examples using comparison functions Picture 3

Similarly copying the formula for the remaining values ​​results:

Comparison functions in Excel - How to use comparison functions and examples using comparison functions Picture 4

However, with this function, you cannot compare a value in column 1 with all the data in column 2 to find a duplicate. With this function you can only compare rows between themselves.

2. Use the Countif intermediate function to compare data

To overcome the situation in example 1, you can use the Countif function as an intermediary to compare the values ​​in column 1 against the values ​​in column 2.

Description of the Countif function

- Countif function counts the number of cells that meet certain conditions in the selected data range.

Syntax

COUNTIF (Range, Criteria)

Inside:

- Range: The data area containing the data to be counted, is a required parameter.

- Criteria: The condition used to count data, is a required parameter

For example

Compare data between 2 columns, highlight values ​​not in column 1 but in column 2 and vice versa.

Step 1: Name the data columns:

Select the entire column 1 data into the address bar enter listname1 -> press Enter.

Comparison functions in Excel - How to use comparison functions and examples using comparison functions Picture 5

Step 2: Similar to the name of column 2, then the names of 2 data columns are displayed:

Comparison functions in Excel - How to use comparison functions and examples using comparison functions Picture 6

Step 3: Select the whole list 1 -> go to Home tab -> Conditional Formatting -> New Rule .

Comparison functions in Excel - How to use comparison functions and examples using comparison functions Picture 7

Step 4: A dialog box appears, select Use a formula to determine which cells to format:

Comparison functions in Excel - How to use comparison functions and examples using comparison functions Picture 8

Step 5: Enter the formula = COUNTIF (list2, C3) = 0 then click Format:

Comparison functions in Excel - How to use comparison functions and examples using comparison functions Picture 9

Step 6: The Format Cells dialog box appears, click the Fill tab -> select the color marking the fruit is not in the list 2 -> click OK:

Comparison functions in Excel - How to use comparison functions and examples using comparison functions Picture 10

Step 7: Next, click OK to close the resulting fruits dialog box in List 1, which is not in List 2, which is colored to distinguish:

Comparison functions in Excel - How to use comparison functions and examples using comparison functions Picture 11

Step 8: Similar to the list 2 you do the same, but only differ from the formula: = COUNTIF (list2, F3) = 0 , it will color the cell with a value of 0 (not the same)

Comparison functions in Excel - How to use comparison functions and examples using comparison functions Picture 12

As a result, you have compared 2 columns of data and can add notes for readers to easily visualize:

Comparison functions in Excel - How to use comparison functions and examples using comparison functions Picture 13

Above is an instruction on how to use EXACT function , application COUNTIF function  to compare data in Excel. Good luck!

5 ★ | 1 Vote

May be interested

  • IRR function in Excel - Usage and examplesIRR function in Excel - Usage and examples
    in excel financial functions, users cannot ignore irr - the function that returns the internal rate of return for a series of cash flows. so how does the function structure and usage of the irr function? please read the article below.
  • PMT function in Excel - Usage and examplesPMT function in Excel - Usage and examples
    the pmt function is one of the built-in financial functions of the excel software used to calculate the payment for a loan based on regular payments and a constant interest rate. the pmt function is not only useful for businesses, but also very practical for users if you want to calculate a loan.
  • Complete financial functions in Excel you should knowComplete financial functions in Excel you should know
    fv, date, time, hour, workday, ... functions are the basic financial functions with the same way of getting color codes in excel. show you how to use these financial functions. click view now!
  • VLOOKUP function - Usage and detailed examplesVLOOKUP function - Usage and detailed examples
    vlookup is one of the most used excel functions. the following article details how to use and detailed examples when using the vlookup function.
  • DCOUNT function in Excel - Usage and practical examplesDCOUNT function in Excel - Usage and practical examples
    the dcounta function is one of many frequently used math functions in excel. the dcount function helps you count non-blank data cells in list columns or data arrays with defined conditions.
  • VLOOKUP function to use and specific examplesVLOOKUP function to use and specific examples
    vlookup is one of the most useful excel functions but few understand it. in this article, we will help you understand vlookup with practical examples, such as grading students and staff using the vlookup function.
  • How to use the IF function in ExcelHow to use the IF function in Excel
    the if function in excel helps you easily check conditions and return corresponding results, providing optimal support in calculating and processing data. this is one of the important functions, widely used to automate the comparison and classification of information.
  • MS Excel - Lesson 5: Excel formulas and functionsMS Excel - Lesson 5: Excel formulas and functions
    the formula in excel is a program that performs calculations on data tables. these formulas perform very precise operations such as adding, multiplying, or comparing values ​​in worksheets.
  • 8 little-known Excel functions that can save you a lot of work8 little-known Excel functions that can save you a lot of work
    even seasoned excel users often find themselves stuck performing tasks manually that could be automated with a few clever functions.
  • Summary of information functions in ExcelSummary of information functions in Excel
    to facilitate the selection of functions to suit the requirements when you need to process information in excel spreadsheets. the following article summarizes the functions along with the functions of each function group in the excel spreadsheet.