This article was co-authored by How.com.vn Staff. Our trained team of editors and researchers validate articles for accuracy and comprehensiveness. How.com.vn's Content Management Team carefully monitors the work from our editorial staff to ensure that each article is backed by trusted research and meets our high quality standards.
The How.com.vn Tech Team also followed the article's instructions and verified that they work.
This article has been viewed 68,408 times.
This How.com.vn teaches you how to use a custom formula in Google Sheets conditional formatting tool in order to highlight the cells with duplicate values, using a computer.
Steps
Open Google Sheets in your internet browser. Type sheets.google.com into the address bar, and hit ↵ Enter or ⏎ Return on your keyboard.
Click the spreadsheet you want to edit. Find the spreadsheet you want to filter on your saved sheets list, and open it.Advertisement
Select the cells you want to filter. Click a cell, and drag your mouse to select the adjacent cells.
Click the Format tab. This button is on a tabs bar at the top of your spreadsheet. It will open a drop-down menu.
Select Conditional formatting on the menu. This will open the formatting sidebar on the right-hand side of your screen.
Click the drop-down menu below "Format cells if" on the sidebar. This will open a list of filters you can apply to your spreadsheet.
Select Custom formula is on the drop-down menu. This option will allow you to manually enter a filter formula.
Type =COUNTIF(A:A,A1)>1 into the "Value or formula" box. This formula will let you highlight all the duplicate cells in the selected range.- If the range of cells you're editing is in a different column than column A, change A:A and A1 in the formula to your selected column.
- For example, if you're editing cells on column D, your formula should be =COUNTIF(D:D,D1)>1.
Change A1 in the formula to the beginning cell of your selected range. This part of the custom formula indicates the first cell in your selected data range.- For example, if the first cell of your selected data range is D5, your formula should be =COUNTIF(D:D,D5)>1.
Click the blue Done button. This will apply your custom formula, and highlight every duplicate cell in the selected range.Advertisement
Expert Q&A
About this article
1. Open Google Sheets.
2. Click a spreadsheet.
3. Select a cell range.
4. Click the Format tab.
5. Select Conditional formatting.
6. Select Custom formula is on the "Format cells if" menu.
7. Type "=COUNTIF(A:A,A2)>1" into the formula box.
8. Click Done.
Is this article up to date?
⚠️ Disclaimer:
Content from Wiki How English language website. Text is available under the Creative Commons Attribution-Share Alike License; additional terms may apply.
Wiki How does not encourage the violation of any laws, and cannot be responsible for any violations of such laws, should you link to this domain, or use, reproduce, or republish the information contained herein.
- - A few of these subjects are frequently censored by educational, governmental, corporate, parental and other filtering schemes.
- - Some articles may contain names, images, artworks or descriptions of events that some cultures restrict access to
- - Please note: Wiki How does not give you opinion about the law, or advice about medical. If you need specific advice (for example, medical, legal, financial or risk management), please seek a professional who is licensed or knowledgeable in that area.
- - Readers should not judge the importance of topics based on their coverage on Wiki How, nor think a topic is important just because it is the subject of a Wiki article.
