Time for Conditional Formatting to step in again.įor example, we will compare two lists of fruits from different vendors and highlight matching cells with Conditional Formatting. What would be further helpful is if these matching values were highlighted. Different types of data are put together differently and you may need to find if there are any matching items in two lists. Up until now, you have seen comparing two columns for row by row. Compare Two Columns & Highlight Matching Data This formula will highlight the cells if a value in column B doesn't equal the adjacent value in column C. Next, enter the following formula in the provided field:.First, select the rule type Use a formula to determine which cells to format.This will open a New Formatting Rule window. While in the Home tab, click on the Styles group's Conditional Formatting button and select the New Rule… option from the menu.Select the cells in the dataset for comparison.Here are the steps to highlight cells with matching data with Conditional Formatting: This is easily achievable using the very handy Conditional Formatting feature in Excel.Ĭonditional Formatting changes the appearance of a cell to show if a certain condition is met. For a quick look, it'd immediately be more noticeable to have the matching cells highlighted. Perhaps you don't want to have a resulting column with TRUEs and FALSEs or their equivalents. Compare & Highlight Cells with Matching Data (side by side) The IF function takes the resulting TRUE from the EXACT function and returns "Match". In the second row of the dataset, B4 and C4 are the same case type. The EXACT function results in FALSE and the IF function returns FALSE as our supplied text string "Check source 2". We find C3 to contain the surname in uppercase which is differing from the name in B3 which is in capitalized initials. The EXACT function checks the text in B3 and C3 to be exact case-sensitive matches. Here is the formula to compare the value of two cells using the equals operator: As an example, we will be working on comparing shipping and billing addresses to see if they match each other. Using the equals operator "=" we can compare the values in two columns for equalness. Let's begin with the methods of comparing cells in the same row. The methods below are relevant to exact matches between the two columns and will not overlook even a single space character's difference between the columns. Our first test is checking whether the values in one column match the values in the adjacent column. The very first comparison of two columns is going to be a simple row by row, line by line comparison. Compare Two Columns & Pull Matches (Partial Match)Ĭompare Cells in the Same Row (side by side).Compare Two Columns & Pull Matches (Exact Match).Compare Two Columns & Highlight Mismatching Data.Compare Two Columns & Highlight Matching Data.Compare & Highlight Cells with Matching Data (side by side).Compare Cells in the Same Row (side by side).
0 Comments
Leave a Reply. |