#COMPARE TWO COLUMNS IN EXCEL DIFFERENT SHEETS HOW TO#

For the previous articles, see How to use Excel’s conditional formatting to compare lists. Note: this is the second of three articles in a series.

#COMPARE TWO COLUMNS IN EXCEL DIFFERENT SHEETS DOWNLOAD#

You can work with your own data or download the demonstration.

#COMPARE TWO COLUMNS IN EXCEL DIFFERENT SHEETS WINDOWS 10#

I’m using Excel 2016 (desktop) on a Windows 10 system, but this solution will work in older versions. How to return first and last times from timestamps in Microsoft ExcelĬhecklist: Microsoft 365 app and services deployments on Macs Master Microsoft Office with this accredited training If you want a solution that screams out, “Here I am! I’m a duplicate!” why not do just that? In this article, I’ll show you a simple expression solution that might be easier to implement and more effective than conditional formatting. Conditional formatting might be an option, but if you already have several formats, it too might not be the best solution. So, while it’s better than no solution at all, it’s not a great solution. A sort can work, but then you must spot the duplicates. Finding duplicates that span multiple columns is a tad more difficult. For more info, visit our Terms of Use page.įinding duplicate values in the same column is easy you can sort or apply a filter depending on the circumstances. This may influence how and where their products appear on our site, but vendors cannot pay to influence the content of our reviews. We may be compensated by vendors who appear on this page through methods such as affiliate links or sponsored partnerships. When none of those are an option, try this simple formula solution. For example, for comparing two prices in Excel and even on different worksheets.How to use Excel formulas to compare multi-column listsĭuplicates in the same column are easy to find by sorting, filtering, and using conditional formatting. The same principle can be applied to various similar tasks. The reference in the second argument is relative, then all the cells of the selected range will be checked in turn (for example, A2: A15).ĭownload example comparison 2 tables in Excel

In this case, the cell is assigned a custom format specified in the conditional formatting options. If the number of times is 0, then the formula returns TRUE. In this example, this function checks how many times the value of the second argument (for example, A2) occurs in the list of the first argument (for example, Table_2). We used the COUNTIF function when defining conditions for formatting column cells. Principle of comparing the data of two columns in Excel

Click on the 'Format' button and specify the green color on the 'Fill' tab.

Select the range of the first list C2:C15 and select the tool again – 'HOME'-'Conditional Formatting'-'New Rule'-'Use a formula to determine which cells to format:'.

Click on the 'Format' button and specify a blue color on the 'Fill' tab.

Select the range of the first table A2:A15 and select the tool: 'HOME'-'Conditional Formatting'-'New Rule'-'Use a formula to determine which cells to format:'.

At the same time, the items in Table 2 which don’t present in Table 1 will be highlighted in blue. Positions that are in Table_1, but not in Table_2 will be displayed in green. Now let's use conditional formatting to compare two lists in Excel. Just select the ranges of cells, and in the name field enter the appropriate name for the range and press Enter.

It is located on the left of the formula row. Helpful advice! Range names can be assigned more quickly by using the name field. And choose the range C2:C15 respectively. Then click OK.įor the second list follow the same steps and just use another name - Table_2.

With the left mouse button click on the input field 'Refers to:' and select the range: A2:A15.

Enter the value - Table_1 in the appeared window in the field 'Name:'.

Select the 'FORMULAS' tool - 'Defined Names' - 'Define Name'.

This makes it easier to understand which cell ranges are compared: Worksheet with tables:įirst, you need to name both tables. How can we compare values for two columns in Excel? To solve this task, we recommend using conditional formatting which quickly selects the color of positions that are only in one column. Comparing two columns for finding matches in Excel

Compare two columns in excel different sheets