Comparing data across multiple columns in Excel is a common task. This tutorial provides a step-by-step guide on How To Compare Four Columns In Excel using a simple formula and conditional formatting.
This formula compares the values in cells B2, C2, D2, and E2. If all values are identical, it returns “Equal”; otherwise, it returns “Not Equal”.
=IF(AND(B2=C2,C2=D2,D2=E2),"Equal","Not Equal")Applying the Formula: A Practical Example
Let’s say you have a dataset of the highest-scoring players from different basketball teams across four games. You can use the formula to see if the highest scorer was the same across all four games for each team.
-
Enter the formula: In cell F2, enter the formula:
=IF(AND(B2=C2,C2=D2,D2=E2),"Equal","Not Equal"). -
Apply the formula to all rows: Drag the fill handle (the small square at the bottom right of cell F2) down to the last row of your data. This will apply the formula to each row, comparing the four columns in each respective row.
Using Conditional Formatting for Visual Comparison
Conditional formatting helps visually highlight rows where all four columns have matching values.
-
Select the results column: Highlight the cells containing the formula results (e.g., F2:F11).
-
Apply conditional formatting: Go to the “Home” tab, click “Conditional Formatting”, then “Highlight Cells Rules”, and select “Equal To”.
-
Set the condition: In the dialog box, enter “Equal” and choose a formatting style (e.g., green fill). Click “OK”.
Now, rows with matching values across all four columns will be highlighted in green.
This visual representation quickly identifies rows with identical values, making data analysis easier. By combining a simple formula with conditional formatting, you can efficiently compare four columns in Excel and extract meaningful insights from your data.
