How to compare two lists in Excel and highlight repeated

How to compare two lists in Excel and highlight repeated

In this article we will teach you how to compare two Excel lists and highlight repeated, a new article about Excel. Remember that you can create and save a list of files from a folder in Excel in a professional way in a few minutes.

Step by step you will learn how to compare lists in Excel, it will help us to know if the content that we find in another list is repeated in any of these, comparing large Excel lists to see what is repeated will no longer be a headache, You can do it in a few minutes with this option.

How to compare lists in Excel?

  1. To start you must install Excel on your PC computer.
  2. First we must create a new text document within Excel that is blank.
  3. Inside it we will create two lists of what you need to do in your project.
  4. To compare these two we must first have the lists prepared with their numbers/digits already defined.
  5. We select all the cells of the first list.
  6. In the menu above we will find the “Start” tab and then in the option “Conditional formatting” that displays a new menu of tables in excel
  7. We select the “New rule” option from the menu to open a new window where we can choose settings for this new rule.
  8. The configuration that we will choose will be “Use a formula that determines the cells to apply formatting” within this we must create a formula in the section within the same window.
  9. We will write the following: =COUNT.IF, inside the parentheses we will select the next row in our list that we would compare with, then add a “;” and when we finish we return to select the first cell of our initial list.
  10. In this selection we place a $ sign twice, we will remove them from our formula.
  11. We close the parentheses and continue the formula with “>” and the value “0”.
  12. We can select the “Format” option before accepting the formula, within this we can style it.
  13. We will modify our text style to “Bold” and we will give a colored background if necessary.

When we accept we will see reflected in the first list the numbers that are repeated in the second list. In this way, it will be with the background of the color that we chose as well as in bold font.

Important: This formula is made at the time of performing the action, we cannot save the formula within Excel to be able to use it later in another project, spreadsheet in said program.

Keep in mind when comparing two lists in Excel:

This configuration can be used to search for digits/numbers within a long list of numbers, where manually it is impossible to check them one by one and it would take us many hours.

We can use this example not only with a list of numbers, we can also add to our formula a single number/digit to search among the list and in this way we focus on a single number. A very useful tool that you learned in this step by step, how to compare two lists in Excel and highlight repeated. You may also be interested in knowing more about how to insert formulas in text boxes in Excel, in case you need to do it.

We can carry out the same procedure but vice versa to achieve the same search but in the following list, it is important to note that the previous search will not be altered by this new formula to be defined between the second list compared to the first list.

select excel tables hand

Excel shows us once again that it is a powerful editor/creator of documents in spreadsheet format, with its tools it manages to generate a 100% chance for the user that their project, table or spreadsheet will be successful. Although we also have alternatives to create a spreadsheet professionally.

Remember that to access more step by step about Excel, you must enter the main menu of the page and look for the Excel label where you will find everything about this wonderful program, how to get the total and the subtotal in Excelhow to do logical functions in Excel and many more.


Please enter your comment!
Please enter your name here