![]() Notice that I also included dollar signs before the letters in the range that we are referring to, these are needed to make sure the formula works correctly.įollow the same steps from the previous example: We need to make a slight change to the range the formatting is being applied to, and a slight change to the "Custom formula is" formula, so that we are referring to two columns instead of one, like this For example let's say you have two columns of names, and you want to highlight any and all duplicate names that appear in those two columns.ĭoing this is very simple. Now let's go over an example where there are multiple columns of data, and duplicates that appear across multiple columns. Highlight duplicates found within multiple columns Note that the conditional formatting rule above (in the main conditional formatting menu where all of the rules are displayed), the custom formula is shown on top, and the range that the formatting is applied to, is shown on bottom. In this example I have chosen an Orange fill color (background color) for the conditional formatting rule, and as you can see in the image below, after following the steps above, the duplicate names in column A are now highlighted in orange. This formula tells Google Sheets to format the cells in column A that appear / are counted more than once. Then type the formula =COUNTIF(A:A,A1)>1 and press "Enter" on the keyboard. Then click the "Format cells if" dropdown, and choose "Custom formula is" Notice that in the "Apply to range" field, Google Sheets has filled in the cell range for us (A1:A1000) because we selected this range before opening the menu, but this range can also be typed manually if you want. On the top toolbar, click "Format", and then click "Conditional formatting"… and then click "Add another rule". In this example the names are listed in column A, and so we will select column A before opening the Conditional formatting menu, or alternatively you can simply open the menu and then type the range A1:A1000 in the “Apply to range” field Or you can simply open the "Conditional formatting” menu and type the range that you want to apply the formatting to, under the ”Apply to range” field. The first is by selecting the range that you want to format before opening the conditional formatting menu, and Google Sheets will automatically fill in the cell range… Note that you can specify the range that you want to apply the conditional formatting to in two different ways. In this example, we are going to highlight duplicate names within a single column, again by using conditional formatting. ![]() “Custom formula is” allows us to use a formula to specify which cells to format based on a specified criteria, such as telling Google Sheets to format the values that are counted more than once (i.e. The most common way to highlight duplicates in Google Sheets, is by using the “Custom formula is” option in the conditional formatting rules. You can also learn how to remove duplicates with a formula in this lesson.Ĭlick here to get your Google Sheets cheat sheet Method 1: Highlight duplicates with conditional formatting (custom formula is) Now the duplicates on your spreadsheet (Column A in this example) will be highlighted!Ĭheck out this lesson if you want to learn how to remove duplicates in Google Sheets instead of highlighting them.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |