Google Sheets Conditional Formatting

How to color duplicate items in Google Sheets?

To apply conditional formatting to color cells that contain repeated items in a Google Sheets, you can use the COUNTIF function. Here's an example of how you can use it:

• Select the range of cells that you want to format.
• Click on the "Format" menu and select "Conditional formatting".
• In the "Conditional format rules" pane, select "Format cells if..." from the dropdown menu.
• Select "Custom formula is" from the list of options.
• Type the following formula in the input box:

=COUNTIF(A1:A10, A1)>1

Replace A1:A10 with the range of cells that you want to format, and A1 with the first cell in the range.

Click on the "Formatting style" button to select the formatting style that you want to apply to the cells that contain repeated items. Click on the "Done" button to apply the conditional formatting. This formula uses the COUNTIF function to count the number of times that each value in the range appears in the range. If the count is greater than 1, it means that the value is repeated, and the cell is formatted according to the selected formatting style.

Note that this formula will apply the conditional formatting to the entire range that you selected in step 1. If you want to apply the formatting to a different range, you can change the range in the formula.

Also, keep in mind that the conditional formatting will be applied only to the cells that contain repeated items, and not to the entire range. If you want to highlight the entire range that contains repeated items, you can use the FILTER function to filter out the unique values and apply the conditional formatting to the filtered range. Here's an example:

=FILTER(A1:A10, COUNTIF(A1:A10, A1:A10)>1)

This formula uses the FILTER function to filter out the cells that contain repeated items, and applies the conditional formatting to the filtered range.