find duplicate cells in rows across multiple column

I have a lot data with duplicate values in a row, I want to check the duplicates and highlight them with different colors. I can do that easily for one row. choose conditional formatting ->Highlight cell rules -> duplicate values ...
But then i have apply this formatting across multiple columns, i have to do it one by one. Any shortcuts or formula ?

First, remove the conditional formatting that you have placed in rows 2 and 3. Then Select all the cells that you want this to apply to (i.e. A2:M32 in your sample file), so that you only need to do it once.

Click on Conditional Formatting | New Rule | Use a formula... , then put this formula in the dialogue box which pops up:

=COUNTIF($A2:$M2,A2)>1

Click on the Format button | Fill tab and choose your background colour. You might also want to click on the Font tab and the Color drop-down to select the foreground colour. Then click OK twice to exit the dialogue box, and then your duplicates should be highlighted.

Note that you might need to use semicolons ( ; ) instead of commas ( , ) in the formula, depending on your regional settings.

Hope this helps.

Pete

Great, However for excel for Mac, it was Highlight cell rules, more rules.. use a formula.. If i were to do the same for column then ? I tried =COUNTIF($A2:$A32,A2)>1 , It still sorts out duplicates in a row. just wondering how would you do it for column ?

For columns, you would need to change over the $ signs, i.e.:

=COUNTIF(A$2:A$32,A2)>1

which is applied to the same range ($A$2:$M$32) with A2 as the active cell, as Excel automatically adjusts the cell references relative to that cell when you exit the CF dialogue box.

Hope this helps, and thanks for the rep.

Pete