![highlight duplicates in two columns in excel for mac highlight duplicates in two columns in excel for mac](https://blog.coupler.io/wp-content/uploads/2021/07/2-duplicate-results.png)
Holding the SHIFT key, press "end" and "up arrow" to jump back to the top of column C since you held SHIFT the cells are all selected.
![highlight duplicates in two columns in excel for mac highlight duplicates in two columns in excel for mac](https://cdn.ablebits.com/_img-lp18/excel-compare-tables-lp18/highlight-dupes.png)
You can easily carry on and select more columns if needed. Select the first column by clicking on it, then press and hold the Ctrl key and, while holding it, click on any other column you need to select.
#Highlight duplicates in two columns in excel for mac how to
![highlight duplicates in two columns in excel for mac highlight duplicates in two columns in excel for mac](https://www.lifewire.com/thmb/ck6SIdyIYbOrh3j3lmavw1PRo_0=/939x528/smart/filters:no_upscale()/Excel_01-60e150b9f37a4835862036139397bc3f.jpg)
This assumes that you don't have any blank values in the middle of column B's data. This will jump you to the last non-blank value in column B (i.e., the "end" of the column). Press the "end" key, and the the "down arrow" key.Using the arrow keys, move left to cell B1.select cell C1, press Ctrl-C (Command-C on Mac) to copy.TIP: to quickly copy C1 to over 12000 rows, try this: To get all of the values in col C together, you can select columns B and C together, and sort on column C. Now each cell in column C contains the value next to it from Column B if that value doesn't occur in Column A, or a blank (an empty string) if it does occur. Copy C1 to all cells C1:C12555 (see tip at end).As a second step we can sort column C to put the found values at the top of the list. We'll do this with a formula in each cell of C1:C12555 (one cell for each value in col. We need to setup column C to show the values in B which are not in A. Your column B data is in cells B1:B12555.Your column A data is in cells A1:A11592.You have to click the top of the column in order to select the entire column should you want to select only specific cells, simply drag the mouse cursor to create a selection. Open the Excel spreadsheet and highlight the row or column whose values you want to check. You may need to adjust the cells accordingly: Remove Duplicate Data In an Excel Row Or Column. Wondering what other ways conditional formatting can help you find data entry mistakes? Check out how to highlight blank cells or errors with it in Excel.I'm making a couple of assumptions here to allow me to answer the question. And once you do, the formatting will disappear as long as it’s not duplicated elsewhere in your cell selection. Once you have your duplicate data highlighted using conditional formatting, you can make the corrections or adjustments you need to. You’ll see the Custom Format applied to the cell immediately. If you prefer a format that isn’t listed, click “Custom Format” in the drop-down box to pick a font, border, or fill style in the subsequent pop-up window. In the example shown, the formula used to highlight duplicate values is: AND(COUNTIF( range1, B5 ),COUNTIF( range2, B5 )) Both ranges were selected at the same when the rule was created. Highlight duplicate numbers with different colors in two columns Hi, maybe that is incredibly easy, but I really dont know how to do it: I have two columns (D and F) with a quite big amount of numbers (almost 5000) and I would like each different duplicate to be highlighted in a different color, its not important what kind. Click “OK” to apply the format to your duplicate data. To highlight duplicate values in two or more columns, you can use conditional formatting with on a formula based on the COUNTIF and AND functions. Then, click the second drop-down box to select a different format. However, you can change this if you wish.Ĭonfirm that “Duplicate” displays in the first drop-down box. When the Duplicate Values window displays, you should immediately see your duplicates highlighted with the default formatting applied.