# How to compare two columns in Excel on Mac: step by step for Microsoft 365

> How to compare two columns in Excel on Mac with Conditional Formatting and a MATCH formula. Steps for Microsoft 365, Excel 2024 and 2021.

URL: https://www.lazur.app/how-to/compare-two-columns-in-excel
Updated: 2026-11-09

To compare two columns in Excel on Mac, select both columns, then go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values to color values that repeat. To check which values in one column are missing from the other, use a MATCH formula in a helper column.

## Quick answer

- Select both columns, then use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values to color repeated values.
- For a list-to-list check, enter =IF(ISERROR(MATCH(A1,$C$1:$C$3,0)),"",A1) in cell B1, then fill it down.
- Duplicate Values does not compare rows. Use =A2=B2 when the row position matters.
- Fix numbers stored as text first, or a correct match can look like a blank.
- Copy your sheet before you run Remove Duplicates, because that command permanently deletes data.

Applies to: Microsoft 365, Excel 2024 and Excel 2021 for Mac. Time: 10 min. Difficulty: Easy.

## What you need

- Microsoft 365, Excel 2024 or Excel 2021 for Mac
- Two columns of data on the same sheet
- A copy of the sheet before you run Remove Duplicates

## How to Compare two columns in Excel on Mac in Excel on a Mac

1. **Select both columns.** Click the letter above the first column, then hold Command and click the letter above the second column. Both columns stay selected at once. For long lists, click the first cell and Shift-click the last one instead. Tip: Leave out the header row. Otherwise, matching headers such as 'Order ID' will light up too.
2. **Open Conditional Formatting.** (Home > Conditional Formatting) Click the Home tab on the ribbon. Then click Conditional Formatting. A list of rule types opens.
3. **Open Highlight Cells Rules.** (Home > Conditional Formatting > Highlight Cells Rules) Point to Highlight Cells Rules. A submenu opens with the rule types. Move across slowly so the menu stays open.
4. **Choose Duplicate Values.** (Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values) Click Duplicate Values. A rule dialog opens for the range you selected.
5. **Pick duplicate or unique.** Next to 'values in the selected range,' choose duplicate to flag repeats. Choose unique to flag values that appear only once. Tip: Start with duplicate when you want the overlap between the two columns.
6. **Confirm the rule.** Set the highlight style in the dialog, then confirm the rule. Dialog wording can change between builds, so read each option before you confirm. Tip: The rule stays on the cells until you clear it. Step 13 shows how.
7. **Set up the list-to-list layout.** Put your first list in column A starting at A1. Leave column B for the results. Put your second list in column C starting at C1. This layout matches Microsoft's worksheet example.
8. **Enter the MATCH formula.** Click cell B1, type =IF(ISERROR(MATCH(A1,$C$1:$C$3,0)),"",A1), then press Return. Change $C$1:$C$3 so it covers your whole second list. Tip: Keep the dollar signs. They lock the lookup range while the rest of the formula moves.
9. **Fill the formula down.** Drag the small square at the bottom-right corner of B1 down to row 5. Excel copies the formula into each row next to column A. Tip: Microsoft's worksheet page names Editing > Fill > Down with Windows labels. Use the fill handle on Mac instead.
10. **Read the results.** Each cell in column B shows the value from column A when that value also appears in column C. A blank cell means no match. Count the blanks to see how many items are still missing.
11. **Compare rows one by one.** Type =A2=B2 in a free column such as D2, press Return, then fill the formula down. TRUE means the two cells match. FALSE means they do not. Tip: Use this when row position matters. Duplicate Values cannot tell you that.
12. **Find the highlighted cells.** (Edit > Find > Go To) Click Edit > Find > Go To. In the Go To window, click Special, then choose Conditional formats. Excel selects the cells that carry a conditional format.
13. **Clear the highlights.** (Edit > Clear > Formats) Select the cells you want to clean, then click Edit > Clear > Formats. This removes all formatting from the selection, not only the highlight, so use it where that is fine. Tip: Check Home > Conditional Formatting for a Clear Rules option. I did not verify that option on a Mac.

Most people who search this phrase need one of two answers. Some want to see which values repeat across two columns. Others want to know which item in column A is missing from column B. Those jobs look alike, but Excel handles them in different ways. This guide covers both, plus row-by-row checks, a formatting trap that breaks matches, and the mistakes that cost people the most time.

![Person at a laptop comparing two columns of data in a spreadsheet on a Mac](https://images.lazur.app/content-images/howto/compare-two-columns-in-excel/how-to-compare-two-columns-in-excel-1-e7ca3f.jpg)

## Check your Excel version before you start

Open Excel and go to Excel > About Excel. The build number tells you which release you run. Microsoft's release notes list 16.114.3 (26102720) as the latest Excel for Mac build. That entry is dated October 27, 2026, and it mentions only quality and performance improvements. The steps below apply to Microsoft 365 for Mac, Excel 2024 for Mac and Excel 2021 for Mac.

The release notes also show what did change. Build 16.113, released September 16, 2026, added Copilot in Excel features such as a change history skill and chart and PivotTable skills. The notes I read show no Excel menu changes and no mention of Conditional Formatting or Duplicate Values. A third-party encyclopedia page listed 16.114.1 as the latest build, so treat Microsoft's page as the reference.

- **16.114.3** Latest Excel for Mac build in Microsoft's release notes, dated October 27, 2026 ([source](https://learn.microsoft.com/en-us/officeupdates/release-notes-office-for-mac))
- **About 94%** Share of operational spreadsheets with at least one error, per a vendor summary of Panko's research ([source](https://www.datahubpro.co.uk/what-percentage-of-spreadsheets-have-errors))

## Highlight values that repeat across two columns

Conditional formatting is the fastest route, and it leaves your data alone. Select both columns and apply the Duplicate Values rule. Excel colors every cell whose value appears more than once in that selection. That includes matches between the two columns. It also includes repeats inside one column, so a column can highlight its own repeats. Expect that, and do not treat it as a bug.

Choose the option that matches your question. Duplicate flags any value that appears more than once. Unique flags values that appear only once in the selection. Start with duplicate when you want to find overlap between the two lists. Switch to unique when you want to find what stands alone.

Duplicate Values does not compare rows. A value in A2 can match a value in B9, and the rule still colors both. Microsoft's Mac page describes the rule in four steps. I did not find any mention of row alignment there. Treat the highlight as a value check, not a row check.

| Task | Menu path |
| --- | --- |
| Highlight repeated values | Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values |
| Select the highlighted cells | Edit > Find > Go To, then Special > Conditional formats |
| Remove formats from cells | Edit > Clear > Formats |
| Highlight in Excel for the web | Home > Styles > Conditional Formatting > Highlight Cell Rules > Duplicate Values |

_Mac menu paths used on this page, from Microsoft's Mac and web pages._

## Find which values in column A are missing from column B

This is the question most people actually have. Say column A holds order IDs, and column C holds the IDs your team approved. You want to know which orders are approved. Conditional formatting can flag overlaps, but it cannot pull out a clean list. A lookup formula can. Microsoft's method puts a MATCH inside an IF, in a helper column next to your list.

Break the formula into parts. MATCH looks up the value in A1 inside $C$1:$C$3. It returns the position of the match, or an error when the value is missing. The 0 at the end asks for an exact match. ISERROR turns that error into TRUE. IF then returns a blank, and otherwise it returns the value from A1. The dollar signs lock the lookup range, so it stays fixed as you copy the formula down.

![Office worker reviewing matched data on a computer screen before comparing columns in Excel](https://images.lazur.app/content-images/howto/compare-two-columns-in-excel/how-to-compare-two-columns-in-excel-2-279bcd.jpg)

Here is a small example. Column A holds five order IDs: 1001, 1002, 1003, 1004 and 1005. Column C holds three approved IDs: 1002, 1004 and 1006. After you fill the formula down to row 5, column B shows 1002 and 1004. The cells in rows 1, 3 and 5 stay blank. The 1006 in column C never appears, because the formula only checks column A.

## Check rows one by one

Sometimes row position matters. Say column A holds a customer name and column B holds the name from a new export. You want to know whether each row still matches. Duplicate Values cannot answer that. A simple comparison can. Type =A2=B2 in a free column such as D2 and fill it down. Excel returns TRUE when the two cells match and FALSE when they do not. Filter for FALSE to see only the rows that differ.

## Fix numbers stored as text before you compare

Many false mismatches come from numbers stored as text. Excel marks these cells with a small green triangle in the upper-left corner. Microsoft's guide says to click the error button and choose Convert to Number. You can also use Paste Special with Multiply by 1, which sits in the Clipboard group on the Home tab. On Mac, Paste Special is ⌘⌃V, but Microsoft marks that shortcut as not applying to every product. If it does nothing, use the Home tab. For a longer walkthrough, read [how to convert text to numbers in Excel on Mac](https://www.lazur.app/how-to/convert-text-to-numbers-in-excel).

The MATCH formula can hide this problem. A text 1002 does not match a number 1002, so the lookup fails. ISERROR catches the failure, and the cell goes blank. You get no error message to warn you. Third-party guides describe this mismatch, and I did not test it on a real Mac file. Check the formats before you trust a blank.

## Mistakes that cost people the most time

- Selecting only one column with Duplicate Values, then expecting matches across two columns. Select both columns to see the overlap.
- Forgetting the dollar signs in the lookup range. The range shifts as you fill down, and the results go wrong without a warning.
- Running Remove Duplicates on your only copy of the data. Microsoft says it permanently deletes the duplicate data.
- Leaving highlights on the sheet. The formatting stays until you clear it, so later checks look busier than they are.
- Trusting a blank without checking formats. A text number can look like a missing value.
- Ignoring wildcards. Microsoft's Duplicate Values rule treats * and ? as wildcards, even inside a formula.

> **Copy before you remove:** Remove Duplicates permanently deletes data. Copy the original data to another worksheet first, and remove any outlines or subtotals before you run it.

Remove Duplicates lives on the Data tab. It deletes repeats from the data itself, not from the view. That is different from highlighting, which changes only the color of the cells. If you only need to see repeats, stick with conditional formatting. Save the destructive step for a copy you can throw away.

## Using Excel for the web on a Mac

Excel for the web uses a different ribbon path. Microsoft lists Home > Styles > Conditional Formatting > Highlight Cell Rules > Duplicate Values. You enter the values, then choose a fill, text or border color. Microsoft's page spells the menu item in the singular, so match the name you see on screen. Excel for the web also cannot apply conditional formatting to references that point to another workbook. Copy those values into the same workbook first.

## How much to trust the matches

Spreadsheet errors are common, and a comparison is only as good as its inputs. A DataHub Pro summary of Raymond Panko's spreadsheet research says about 94% of operational spreadsheets contain at least one error. DataHub Pro sells spreadsheet software. I could not open Panko's primary page, so treat that figure as a rough signal, not a precise measurement.

A small test helps more than any percentage. Copy ten or twenty rows to a new sheet. Run the steps there. Count the matches by hand, then compare that count with the formula result. If the two counts agree, run the same steps on the full sheet.

![Laptop on a desk with a spreadsheet open, ready to compare two columns in Excel on Mac](https://images.lazur.app/content-images/howto/compare-two-columns-in-excel/how-to-compare-two-columns-in-excel-3-473639.jpg)

## Where to go next

If the matches feed a summary, a pivot table can count them by group. Read [how to make a pivot table in Excel on Mac](https://www.lazur.app/how-to/make-a-pivot-table-in-excel) for that part. If your lists start in Google Sheets, read [how to download Google Sheets as Excel on Mac](https://www.lazur.app/how-to/download-as-excel-in-google-sheets) first, then run the steps here.

## Shortcuts

- Find (search cells for a value): ⌘F
- Paste Special (for the Multiply by 1 fix): ⌘⌃V

## If it does not work

- **Duplicate Values highlights repeats inside one column.** The rule checks the whole selection, so it cannot separate one column from the other. Use the MATCH formula for a strict list-to-list check.
- **MATCH shows blank for a value you know is in the list.** Check formats and stray spaces first. A text number does not match a real number. Convert both columns to the same type, then fill the formula down again.
- **The results shift when I fill the formula down.** The dollar signs are missing from the lookup range. Retype $C$1:$C$3 with the dollar signs, then fill down again.
- **Remove Duplicates deleted rows I still need.** If you catch it right away, use Edit > Undo. Otherwise, restore the rows from your copy. Copy the sheet before you run the command next time.
- **Highlights catch values with asterisks or question marks.** Duplicate Values treats * and ? as wildcards. Test a few rows that contain those characters before you trust the highlight.
- **I cannot find Duplicate Values in Excel for the web.** Use Home > Styles > Conditional Formatting > Highlight Cell Rules > Duplicate Values. Conditional formatting cannot use references to another workbook, so copy those values in first.

## With Lazur

Lazur is a voice assistant for the Mac that lives in the notch. Hold ⌥ ⌘ and ask; it sees your screen, answers out loud, points at buttons and walks you through apps. Hold fn to dictate. Free for 3 days, then $18/month: https://www.lazur.app/download

- “Where is Conditional Formatting in this sheet?”: Lazur circles Conditional Formatting on the Home tab and labels it. You still click it and choose the rule yourself. (Points at it)

## FAQ

### Does Duplicate Values compare two columns row by row?

No. Duplicate Values checks whether a value appears more than once in the selected range. It does not check whether A2 matches B2. For row-by-row checks, type =A2=B2 in a free column and fill it down.

### Why does my MATCH formula show blank cells for values I know are in the list?

Check the formats first. A number stored as text does not match the same number stored as a number. The formula returns a blank rather than an error, so the mismatch looks like no match. Convert both columns to the same type, then fill the formula down again.

### Will Remove Duplicates delete data on my Mac?

Yes. Microsoft says Remove Duplicates permanently deletes the duplicate data. Copy the original data to another worksheet before you run it, and remove any outlines or subtotals first.

### Which Excel version do I need for this?

The steps on this page apply to Microsoft 365 for Mac, Excel 2024 for Mac and Excel 2021 for Mac. Microsoft's release notes show 16.114.3 as the latest build, and the notes I read show no change to these menus.

### Can I do this in Excel for the web on a Mac?

Yes, with a different menu path. Use Home > Styles > Conditional Formatting > Highlight Cell Rules > Duplicate Values. Excel for the web cannot apply conditional formatting to references that point to another workbook.

### Why do my highlights catch asterisks and question marks?

Microsoft's Duplicate Values rule treats * and ? as wildcards, even inside a formula. Test a few rows with those characters before you trust the highlight. A DBCS language setting can also make halfwidth and fullwidth characters match.

## Sources

1. [Highlight patterns and trends with conditional formatting in Excel for Mac](https://support.microsoft.com/en-us/excel/highlight-patterns-and-trends-with-conditional-formatting-in-excel-for-mac), Microsoft Support, checked 2026-11-09
2. [How to compare data in two columns to find duplicates in Excel](https://support.microsoft.com/en-us/office/how-to-compare-data-in-two-columns-to-find-duplicates-in-excel-fbeab47c-dd7a-4cf2-8aaf-50fc19d85dcc), Microsoft Support, checked 2026-11-09
3. [Common Office for Mac keyboard shortcuts](https://support.microsoft.com/en-us/office/common-office-for-mac-keyboard-shortcuts-307f469a-1a05-4263-9b55-49df19a8b8ff), Microsoft Support, checked 2026-11-09
4. [Use conditional formatting to highlight information in Excel](https://support.microsoft.com/en-us/excel/use-conditional-formatting-to-highlight-information-in-excel), Microsoft Support, checked 2026-11-09
5. [Find and remove duplicates](https://support.microsoft.com/en-us/excel/find-and-remove-duplicates), Microsoft Support, checked 2026-11-09
6. [Fix text-formatted numbers by applying a number format](https://support.microsoft.com/en-us/excel/fix-text-formatted-numbers-by-applying-a-number-format), Microsoft Support, checked 2026-11-09
7. [Release notes for Office for Mac](https://learn.microsoft.com/en-us/officeupdates/release-notes-office-for-mac), Microsoft Learn, checked 2026-11-09
8. [What % of spreadsheets have errors?](https://www.datahubpro.co.uk/what-percentage-of-spreadsheets-have-errors), DataHub Pro, checked 2026-11-09