Countif(G2:G8,G2) 1, if there is more than one instance of a data combination, that indicates a duplicate row, and the cells in columns A:F will be coloured.
Because we selected the range A2:A20 color efex pro 3.0 serial code before we clicked on Data Validation, Excel automatically copies the formula to the other cells.
Add a new heading in cell G1 AllData and in cell G2, enter this formula, to combine the data from all the cells in that row.
Note: to enter an input message and error alert message, go to the Input Message and Error Alert tab.Watch the Video, to see the steps for setting up the conditional formatting, please watch this video tutorial.Next, copy the formula down to the last row of data.Countif(A2:A20,A2) counts the number of values in the range A2:A20 that are equal to the value in cell.There is a built-in rule for highlighting duplicate values in a single column, but nothing that will check an entire row.This value may only occur once (1) since we don't want duplicate entries.Excel shows an error alert.
Select the range A2:A20.The first step is to use the concatenate function to combine all the data into one cell in each row.So, well create our own rule, and it will require a new column on the worksheet, before we add the conditional formatting.To check this, select cell A3 and click Data Validation.The two rows with duplicate records are highlighted, so our conditional formatting formula worked!Well use the countif function to check for duplicates in the AllData column.Highlight Duplicates, in this example, we want highlight the duplicate rows in a table.