Use Conditional Formatting in Excel to compare two columns

=IF(COUNTIF('Ordered books'!D:D,C2)>0,'Ordered','')įill the formula down, and you can quickly see the results of comparing two columns for duplicates. Otherwise we can leave the cell blank, so we enter quotes without anything inside and close parenthesis: Now we say that if the number of matches is over zero, we want to see 'Ordered' in the cell. Now we can use this as a condition to mark the ordered books: we add the IF function, open parenthesis, use our COUNTIF formula, and add '>0' condition to it. Once you fill this formula down, you will get each cell in column C checked in the range you specified. Once we enter the formula, we need to apply it to every cell in our column C, so we can point to the cell with the formula and double-click on the black plus sign. You can read the formula easily: search for this requested book in the list of ordered books, and give me a count of the found matches. Enter a comma, and add the first value to check in our case it is the first requested book: Let's start with COUNTIF and see what it does:Įnter =COUNTIF, open parenthesis and point to the range where it will search. The combination of IF and COUNTIF functions is one of the most convenient options. There are several functions that can help us.

Excel formulas for comparing two columnsįirst let's consider a native way: compare two columns in Excel using formulas. I'd like to see those of the requested books that were already ordered. Here I have a table with the requested books and a list of ordered books.

In this video we will look at the easiest ways: using formulas, conditional formatting, or a special add-in. Microsoft Excel offers a variety of options for comparing two columns of data. How to compare two columns in Excel: video transcript

How to get two columns in powerpoint