{"id":50259,"date":"2024-08-06T16:12:46","date_gmt":"2024-08-06T14:12:46","guid":{"rendered":"https:\/\/blog.sheetgo.com\/?p=50259"},"modified":"2026-10-13T16:06:53","modified_gmt":"2026-10-13T14:06:53","slug":"resaltar-duplicados-en-google-sheets","status":"publish","type":"post","link":"https:\/\/www.sheetgo.com\/es\/blog\/google-sheets-formulas\/highlight-duplicates-in-google-sheets\/","title":{"rendered":"C\u00f3mo resaltar duplicados en Google Sheets (Tutorial 2026)"},"content":{"rendered":"\n[et_pb_section fb_built=&#8221;1&#8243; _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221; da_is_popup=&#8221;off&#8221; da_exit_intent=&#8221;off&#8221; da_has_close=&#8221;on&#8221; da_alt_close=&#8221;off&#8221; da_dark_close=&#8221;off&#8221; da_not_modal=&#8221;on&#8221; da_is_singular=&#8221;off&#8221; da_with_loader=&#8221;off&#8221; da_has_shadow=&#8221;on&#8221; da_disable_devices=&#8221;off|off|off&#8221;][et_pb_row _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][et_pb_column type=&#8221;4_4&#8243; _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][et_pb_text _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<p>You are reading this post because you are probably tired of searching for duplicates in your <a href=\"https:\/\/www.sheetgo.com\/for-google-sheets\/\">Google Sheets<\/a>. Finding these duplicates is like searching for a needle in a haystack, especially if there are 100+ rows.<\/p>\n<p>In this article, we will share a quick way to find and highlight duplicates in Google Sheets, and share formulas and pro tips. You&#8217;ll also see how to remove duplicates or list each value once with the UNIQUE function. Keep reading!<\/p>\n<p><b>Key Takeaways:<\/b><\/p>\n<ul>\n<li>To highlight duplicates in Google Sheets, select the range &gt; click Format &gt; Conditional Formating &gt; Format Rules &gt; Custom Formula Is. Enter the formula =COUNTIF(A:A, A1) &gt; 1 and click Done.<\/li>\n<li>=COUNTIF(A:A, A1)&gt;1 is the formula to highlight duplicates in Google Sheets. Replace the letter A with the corresponding column.<\/li>\n<li>=COUNTIFS($B$1:$B,$B1,$C$1:$C,$C1) is the formula for highlighting duplicates in two columns. You can edit it to affect more columns.<\/li>\n<\/ul>[\/et_pb_text][et_pb_text module_id=&#8221;highlight&#8221; _builder_version=&#8221;4.27.2&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<h2><b>How to Highlight Duplicates in Google Sheets<\/b><\/h2>\n<p>To highlight duplicates in Google Sheets, you need to set \u201cConditional Formatting\u201d and use the formula \u201c=COUNTIF(A:A, A1) &gt; 1\u201d.<\/p>\n<p>Imagine you&#8217;re reviewing your inventory to identify the most popular products.<\/p>\n<p>Your inventory list contains multiple entries for the same product, but they might be scattered throughout the sheet. Manually scanning the &#8220;Product Name&#8221; column would be inefficient and prone to errors, especially if your inventory is large.<\/p>\n<p>Let\u2019s see how to highlight duplicates using the formula mentioned above.<\/p>\n<p>1. Select the entire &#8220;Product Name&#8221; column (Column B in our example).<\/p>[\/et_pb_text][et_pb_image src=&#8221;https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/08\/how-to-highlight-duplicates_1.webp&#8221; alt=&#8221;Highlight duplicates in Google Sheets \u2014 Product Name column B selected in an inventory sheet&#8221; title_text=&#8221;Product Name column B selected in an inventory sheet&#8221; _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][\/et_pb_image][et_pb_text _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<p>2. Go to the &#8220;Format&#8221; menu and select &#8220;Conditional formatting.&#8221;<\/p>[\/et_pb_text][et_pb_image src=&#8221;https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/08\/how-to-highlight-duplicates_2.webp&#8221; alt=&#8221;Highlight duplicates in Google Sheets \u2014 Format menu open with Conditional formatting marked&#8221; title_text=&#8221;Format menu open with Conditional formatting marked&#8221; _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][\/et_pb_image][et_pb_text _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<p>A sidebar will appear on the right.<\/p>[\/et_pb_text][et_pb_image src=&#8221;https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/08\/how-to-highlight-duplicates_3.webp&#8221; alt=&#8221;Highlight duplicates in Google Sheets \u2014 Conditional format rules sidebar open for B1:B1000&#8243; title_text=&#8221;Conditional format rules sidebar open for B1:B1000&#8243; _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][\/et_pb_image][et_pb_text _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<p>3. In the sidebar, under &#8220;Format rules,&#8221; choose &#8220;Custom formula is.&#8221;<\/p>[\/et_pb_text][et_pb_image src=&#8221;https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/08\/how-to-highlight-duplicates_4.webp&#8221; alt=&#8221;Highlight duplicates in Google Sheets \u2014 Custom formula is chosen under Format cells if&#8221; title_text=&#8221;Custom formula is chosen under Format cells if&#8221; _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][\/et_pb_image][et_pb_text _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<p>4. Enter the following formula in the text box: \u201c=COUNTIF(B:B, B1) &gt; 1\u201d and click \u201cDone.\u201d To use a different color, change the fill or text color under Formatting style before you click Done.<\/p>[\/et_pb_text][et_pb_image src=&#8221;https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/08\/how-to-highlight-duplicates_5.webp&#8221; alt=&#8221;Highlight duplicates in Google Sheets \u2014 the COUNTIF(B:B, B1) rule marks repeated product names in green&#8221; title_text=&#8221;The COUNTIF(B:B, B1) rule marks repeated product names in green&#8221; _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][\/et_pb_image][et_pb_text _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<p>=COUNTIF(B:B, B1) looks at each cell in column B (Product Name). It then counts the number of times a product name appears in the whole column. &gt; 1 means that if a product name appears more than once, it should be highlighted.<\/p>\n<p>Our inventory example shows that \u201cLaptop Pro\u201d and \u201cWireless Headphones\u201d appear more than once.<\/p>\n<p>If you want to check column A, change the B in the formula to A. The new formula will be =COUNTIF(A:A, A1) &gt; 1, and column C will be =COUNTIF(C:C, C1) &gt; 1. Remember to select the matching range or column: A column for the A formula and C column for the C formula.<\/p>[\/et_pb_text][et_pb_image src=&#8221;https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/08\/how-to-highlight-duplicates_6.webp&#8221; alt=&#8221;Highlight duplicates in Google Sheets \u2014 the COUNTIF(C:C, C1) rule applied to the Supplier column&#8221; title_text=&#8221;The COUNTIF(C:C, C1) rule applied to the Supplier column&#8221; _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][\/et_pb_image][et_pb_text _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<p>If you only want to highlight duplicates that appear more than ten times, change the number 1 to 10 at the end of the formula. The new formula will be: =COUNTIF(B:B, B1) &gt; <b>10<\/b>.<\/p>\n<p>Want to learn more about formulas? Check our list of <a href=\"https:\/\/www.sheetgo.com\/blog\/excel-features\/100-functions-formulas-for-google-sheets-excel-basics\/\">100+ Google Sheets and Excel formulas<\/a>.<\/p>\n<p>The screenshot below shows a new scenario; \u201cLaptop Pro\u201d was highlighted because it appears more than ten times.<\/p>[\/et_pb_text][et_pb_image src=&#8221;https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/08\/how-to-highlight-duplicates_7.webp&#8221; alt=&#8221;Highlight duplicates in Google Sheets \u2014 a greater than 10 COUNTIF rule marks Laptop Pro, found 11 times&#8221; title_text=&#8221;A greater than 10 COUNTIF rule marks Laptop Pro, found 11 times&#8221; _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][\/et_pb_image][et_pb_text _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<p>Check out our complete guide on <a href=\"https:\/\/www.sheetgo.com\/blog\/google-sheets-formulas\/countifs-in-google-sheets\/\">how to use COUNTIF in Google Sheets<\/a>.<\/p>[\/et_pb_text][et_pb_text module_id=&#8221;multiple&#8221; _builder_version=&#8221;4.27.2&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<h2><b>How to Highlight Duplicates in Multiple Columns<\/b><\/h2>\n<p>Assume you want to know if you&#8217;ve bought a specific product from the same supplier more than once. This will help you identify your most frequent partners and negotiate better deals. In this case, you need to count the events where BOTH the &#8220;Product Name&#8221; AND the &#8220;Supplier&#8221; columns match.<\/p>\n<p>So, let\u2019s see how to highlight duplicates in multiple columns using \u201cconditional formatting\u201d and \u201cabsolute values.\u201d<\/p>\n<p>1. Select the entire table or the needed columns. In our case, we will select the &#8220;Product Name&#8221; and &#8220;Supplier&#8221; columns (columns B and C).<\/p>[\/et_pb_text][et_pb_image src=&#8221;https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/08\/how-to-highlight-duplicates_8.webp&#8221; alt=&#8221;Highlight duplicates in multiple columns \u2014 Product Name and Supplier columns B and C selected&#8221; title_text=&#8221;Product Name and Supplier columns B and C selected&#8221; _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][\/et_pb_image][et_pb_text _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<p>2. Go to the &#8220;Format&#8221; menu and select &#8220;Conditional formatting.&#8221;<\/p>[\/et_pb_text][et_pb_image src=&#8221;https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/08\/how-to-highlight-duplicates_9.webp&#8221; alt=&#8221;Highlight duplicates in multiple columns \u2014 Format menu open with Conditional formatting marked&#8221; title_text=&#8221;Format menu open with Conditional formatting marked&#8221; _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][\/et_pb_image][et_pb_text _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<p>3. Under &#8220;Format rules,&#8221; choose &#8220;Custom formula is.&#8221;<\/p>[\/et_pb_text][et_pb_image src=&#8221;https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/08\/how-to-highlight-duplicates_10.webp&#8221; alt=&#8221;Highlight duplicates in multiple columns \u2014 Custom formula is chosen for B1:B1000 and C1:C1000&#8243; title_text=&#8221;Custom formula is chosen for B1:B1000 and C1:C1000&#8243; _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][\/et_pb_image][et_pb_text _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<p>4. Enter the following formula: =COUNTIFS($B$1:$B,$B1,$C$1:$C,$C1) &gt; 1<\/p>[\/et_pb_text][et_pb_image src=&#8221;https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/08\/how-to-highlight-duplicates_11.webp&#8221; alt=&#8221;Highlight duplicates in multiple columns \u2014 COUNTIFS rule marks rows where product and supplier repeat&#8221; title_text=&#8221;COUNTIFS rule marks rows where product and supplier repeat&#8221; _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][\/et_pb_image][et_pb_text _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<p>=COUNTIFS($B$1:$B,$B1,$C$1:$C,$C1) looks at each cell in column B (Product Name) and column C (Supplier). It then counts the number of times columns B and C match. &gt; 1 means B and C match more than once, it should be highlighted. The dollar signs ($) in the formula create absolute references. It ensures the formula works even when copied to other cells.<\/p>\n<p>To highlight a value that appears more than once anywhere in columns B and C, rather than a repeated product and supplier pair, select both columns and use =COUNTIF($B:$C, B1) &gt; 1 instead.<\/p>[\/et_pb_text][et_pb_text module_id=&#8221;remove&#8221; _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<h2><b>How to Remove Duplicates in Google Sheets?<\/b><\/h2>\n<p>Follow the steps below to remove duplicates in Google Sheets:<\/p>\n<p>1. Select the range or entire sheet containing the data.<\/p>[\/et_pb_text][et_pb_image src=&#8221;https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/08\/how-to-highlight-duplicates_12.webp&#8221; alt=&#8221;Remove duplicates in Google Sheets \u2014 Product Name column B selected&#8221; title_text=&#8221;Product Name column B selected&#8221; _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][\/et_pb_image][et_pb_text _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<p>2. Go to Data &gt; Data cleanup &gt; Remove duplicates.<\/p>[\/et_pb_text][et_pb_image src=&#8221;https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/08\/how-to-highlight-duplicates_13.webp&#8221; alt=&#8221;Remove duplicates in Google Sheets \u2014 Data menu with Data cleanup and Remove duplicates marked&#8221; title_text=&#8221;Data menu with Data cleanup and Remove duplicates marked&#8221; _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][\/et_pb_image][et_pb_text _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<p>3. Choose whether your data has headers. In our case, we will check \u201cData has header now\u201d since there is a header called \u201cProduct Name.\u201d<\/p>[\/et_pb_text][et_pb_image src=&#8221;https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/08\/how-to-highlight-duplicates_14.webp&#8221; alt=&#8221;Remove duplicates in Google Sheets \u2014 Data has header row checked in the dialog&#8221; title_text=&#8221;Data has header row checked in the dialog&#8221; _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][\/et_pb_image][et_pb_text _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<p>4. Select the columns you want to check for duplicates.<\/p>[\/et_pb_text][et_pb_image src=&#8221;https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/08\/how-to-highlight-duplicates_15.webp&#8221; alt=&#8221;Remove duplicates in Google Sheets \u2014 Column B &#8211; Product Name checked under Columns to analyze&#8221; title_text=&#8221;Column B &#8211; Product Name checked under Columns to analyze&#8221; _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][\/et_pb_image][et_pb_text _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<p>5. Click &#8220;Remove duplicates.&#8221;<\/p>[\/et_pb_text][et_pb_image src=&#8221;https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/08\/how-to-highlight-duplicates_16.webp&#8221; alt=&#8221;Remove duplicates in Google Sheets \u2014 Remove duplicates button in the dialog&#8221; title_text=&#8221;Remove duplicates button in the dialog&#8221; _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][\/et_pb_image][et_pb_text _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<p>A popup will appear and indicate the number of rows removed.<\/p>[\/et_pb_text][et_pb_image src=&#8221;https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/08\/how-to-highlight-duplicates_17.webp&#8221; alt=&#8221;Remove duplicates in Google Sheets \u2014 4 duplicate rows found and removed, 17 unique rows remain&#8221; title_text=&#8221;4 duplicate rows found and removed, 17 unique rows remain&#8221; _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][\/et_pb_image][et_pb_text _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<p>The downside is this method removes entire rows, not individual cells.<\/p>\n<p>If duplicates keep coming back from repeated imports or several files, see how to <a href=\"https:\/\/www.sheetgo.com\/blog\/how-to-solve-with-sheetgo\/remove-duplicates-google-sheets\/\">remove duplicates across multiple spreadsheets automatically<\/a>.<\/p>[\/et_pb_text][et_pb_text module_id=&#8221;how-to-filter-out-duplicates-with-the-un&#8221; _builder_version=&#8221;4.22.2&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<h2>How to filter out duplicates with the UNIQUE formula<\/h2><p>Remove duplicates deletes rows from your data. To keep the original data and get a separate list without duplicates, use the <a href=\"https:\/\/www.sheetgo.com\/blog\/google-sheets-formulas\/unique-formula-google-sheets\/\">UNIQUE function<\/a> instead. It returns each value once.<\/p><ol><li><strong>1.<\/strong> Open your Google Sheets and type in \u201c=UNIQUE&quot; into an empty cell next to your data. You will see how Google Sheets prompts the full formula.<\/li><\/ol><ol><li><strong>2.<\/strong> You can complete the formula by clicking the letter at the top of the column you want to find duplicates in. Google Sheets will also include the column range for you. Here, I only want to find duplicates in Column A, so my completed formula looks like this: =UNIQUE(A:A).<\/li><\/ol><ol><li><strong>3.<\/strong> Press \u201cEnter\u201d to apply the formula. It will fill your column with only the unique values in your data range, skipping any duplicates.<\/li><\/ol><p>You can also apply this formula to entire duplicate rows. To do that, give it a range that covers every column of your data, for example =UNIQUE(A2:F17). The formula then returns each row once and skips any row that matches an earlier row in every column.<\/p><p>When you check whole rows, the results are as wide as your data, so place the formula where there is room, such as a few rows below your data or on a new sheet. If any cell the results need already holds data, the formula returns a #REF! error.<\/p>[\/et_pb_text][et_pb_text module_id=&#8221;edit&#8221; _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<h2><b>Edit, Add, or Delete a Conditional Formatting Rule<\/b><\/h2>\n<p>Follow the steps below to edit, add, or delete a conditional formatting rule.<\/p>\n<p>1. Select the range with the conditional formatting you want to edit.<\/p>[\/et_pb_text][et_pb_image src=&#8221;https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/08\/how-to-highlight-duplicates_18.webp&#8221; alt=&#8221;Edit a conditional formatting rule \u2014 highlighted Product Name column B selected&#8221; title_text=&#8221;Highlighted Product Name column B selected&#8221; _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][\/et_pb_image][et_pb_text _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<p>2. Go to the &#8220;Format&#8221; menu and select &#8220;Conditional formatting.&#8221;<\/p>[\/et_pb_text][et_pb_image src=&#8221;https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/08\/how-to-highlight-duplicates_19.webp&#8221; alt=&#8221;Edit a conditional formatting rule \u2014 Format menu with Conditional formatting marked&#8221; title_text=&#8221;Format menu with Conditional formatting marked&#8221; _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][\/et_pb_image][et_pb_text _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<p>3. The sidebar will open and show the existing formatting rules for the selected range. Click on the rule you want to edit.<\/p>[\/et_pb_text][et_pb_image src=&#8221;https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/08\/how-to-highlight-duplicates_20.webp&#8221; alt=&#8221;Edit a conditional formatting rule \u2014 the COUNTIF(B:B, B1) rule listed for B1:B1000&#8243; title_text=&#8221;The COUNTIF(B:B, B1) rule listed for B1:B1000&#8243; _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][\/et_pb_image][et_pb_text _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<p>4. Update the format rules, formula, formatting style, or range as needed. Click &#8220;Done&#8221; to save the changes.<\/p>[\/et_pb_text][et_pb_image src=&#8221;https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/08\/how-to-highlight-duplicates_21.webp&#8221; alt=&#8221;Edit a conditional formatting rule \u2014 formatting style changed to a yellow fill&#8221; title_text=&#8221;Formatting style changed to a yellow fill&#8221; _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][\/et_pb_image][et_pb_text _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<p>To add a new conditional formatting rule, follow steps 1-2 and click \u201cAdd another rule.\u201d<\/p>[\/et_pb_text][et_pb_image src=&#8221;https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/08\/how-to-highlight-duplicates_22.webp&#8221; alt=&#8221;Add a conditional formatting rule \u2014 Add another rule in the sidebar&#8221; title_text=&#8221;Add another rule in the sidebar&#8221; _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][\/et_pb_image][et_pb_text _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<p>To delete a conditional formatting rule, follow steps 1-2, hover your mouse over the rule, and click on the trash can icon next to it.<\/p>[\/et_pb_text][et_pb_image src=&#8221;https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/08\/how-to-highlight-duplicates_23.webp&#8221; alt=&#8221;Delete a conditional formatting rule \u2014 trash can icon next to the rule&#8221; title_text=&#8221;Trash can icon next to the rule&#8221; _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;][\/et_pb_image][et_pb_text _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<p><b>Rule Order Pro Tip:<\/b><\/p>\n<p>If you have multiple rules for the same range, their order matters. Rules are evaluated from top to bottom, and the first rule that applies to a cell determines its formatting. You can reorder rules by clicking and dragging them in the sidebar.<\/p>\n<p>If the formatting applied by a higher rule covers the conditions of a lower rule, the lower rule might be overridden or might not appear at all.<\/p>\n<p>For more ways to use these rules, see how to apply <a href=\"https:\/\/www.sheetgo.com\/blog\/google-sheets-features\/conditional-formatting-based-on-another-cell-in-google-sheets\/\">conditional formatting based on another cell<\/a>.<\/p>[\/et_pb_text][et_pb_text _builder_version=&#8221;4.24.1&#8243; _module_preset=&#8221;default&#8221; global_colors_info=&#8221;{}&#8221; theme_builder_area=&#8221;post_content&#8221;]<h2><b>FAQ<\/b><\/h2>\n<p><b>Can I highlight duplicates in Google Sheets?<\/b><\/p>\n<p>Yes, you can easily highlight duplicates in Google Sheets using conditional formatting.<\/p>\n<p><b>What is the formula to highlight duplicates in Google Sheets?<\/b><\/p>\n<p>The formula to highlight duplicates is: =COUNTIF(A:A, A1)&gt;1. Use this formula if the range is A and change A to B if the range is B. You can use the formula in Conditional Formatting.<\/p>[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]\n","protected":false},"excerpt":{"rendered":"<p>You are reading this post because you are probably tired of searching for duplicates in your Google Sheets. Finding these duplicates is like searching for a needle in a haystack, especially if there are 100+ rows. In this article, we will share a quick way to find and highlight duplicates in Google Sheets, and share [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":44470,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[54],"tags":[39,28],"class_list":["post-50259","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-google-sheets-formulas","tag-connections-t","tag-spreadsheets"],"acf":[],"permalink_manager":null,"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts\/50259","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/comments?post=50259"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts\/50259\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/media\/44470"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/media?parent=50259"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/categories?post=50259"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/tags?post=50259"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}