How To Highlight Duplicates in Google Sheets
Microsoft Excel has occupied the high ground of the spreadsheet wars for many years. Business and home users alike relied on Excel to organize and analyze their data. Whether for check lists, budgets and financial records, graphs, or any other type of dataset, Excel makes it easy to organize your information. There were alternatives at lower prices, and even in some cases for free—but if you wanted the most powerful spreadsheet program imaginable, you’d want to use Microsoft Excel.
In 2006, however, another alternative began to take form. That’s the year that Google began to work on their own web-based office suite for text, spreadsheets, and presentations. The development process continued for years, until, finally, Google Sheets was able to match some of the power of Excel without costing its users hundreds of dollars to purchase – in fact, it’s completely free. While it isn’t a perfect product, anyone looking for a solid, consumer-level spreadsheet tool really doesn’t have to look farther than Google Sheets. Sheets cannot do everything that Excel can do, but odds are you don’t actually need it to do everything Excel can do. If Sheets does everything you need it to do, then you don’t need to buy Excel.
If you’re constantly using Sheets to add and calculate data, you’ve probably run into a problem where you accidentally add duplicate entries to your spreadsheet. After all, the more data you add, the more likely you are to accidentally insert duplicated data into a spreadsheet, which can throw off the dataset you’ve been working so hard to put together. That’s bad enough, but the worst part comes later on when you’re trying to look over your work. Since spreadsheets can be heavy documents, it becomes difficult to spot and remove duplicates as time goes by, leading to calculation errors in your work without an obvious source for where the problem is coming from.
Fortunately, we’ve found a pretty solid way to highlight duplicates inside of Google Sheets. It’s important that our method not automatically remove duplicate information, as not all duplicates are errors. But if it’s just highlighted, then you can determine which ones don’t need to be there yourself, without needing to comb through the entire spreadsheet.
Data points can be identical, so removing duplicates automatically can cause problems with your information. Instead, it’s a good idea to highlight your duplicate information to manually identify whether or not a duplicate was purposeful. Manually checking your information, though time consuming, is the only way to guarantee your data is properly inputted into the program. Thankfully, by using some of the tools inside of Google Sheets, we can speed up the process of checking for data anomalies and duplicates to make sure our spreadsheets are on point. Let’s take a look at how it’s done.
Highlight duplicates in Google Sheets
Since we want Google Sheets to automatically highlight our information for us, we’ll be using a formula to tell Sheets to bring forward and highlight specific, unique information. There are actually two ways to force Sheets to highlight copied information: the first highlights all duplicated information for manual confirmation, while the second will copy unique cells to a selected column, allowing you to check for differences and delete where necessary.
Highlight duplicates using color
As far as being able to identify errors in your spreadsheets, using color highlights to spotlight any information that has been inputted incorrectly is the most obvious, most visible way to go. By highlighting information, it’s easy to identify the mistakes very quickly, since you can simply run down the list of content that you need to identify. We’ll be using a red highlight in this step to identify our duplicate content, since red catches the eye (especially compared to the usual white and grey color scheme of Sheets) and is the universal color for error messages.
Start by opening up the Sheets file you want to check. You’ll want to make sure your information is well organized by both column and row, in order to easily check the content in your document; no need to make your own work more difficult. Now, highlight the column you want to sort at the top of the document and select Format in the top menu of your document. Select “Conditional Formatting” from the list of options in this menu, and an alternate menu will appear on the right side of the display. In this menu, select the range by using the corresponding letter and number combinations (for example, A1 to A76).
Once you’ve selected your content, change “Format cells if” to “Custom formula is” in the dropdown menu and type or paste the following without quotes: “=countif(A:A,A1)>1” into the box beneath your menu. This will complete the formula for your content. Once this is set, change the formatting style to highlight your content with a red cell background, and click the done icon on your menu. Your spreadsheet will now highlight your duplicate cells in red, and you can scan the selection for any duplicates. Make sure that any existing duplicates are correct, then delete the ones that aren’t. Close the formatting menu and you can restore the normal color to your cells.
Copy only unique cells
Alternatively, if you’d rather automatically sort your raw data, copying only unique cells instead of your duplicate cells can be really useful for fast sorting and filtering. If you’re sure that your information is correct and you’d rather just outright remove the duplicate information you don’t need, you should try this method instead.
As with the last step, start by opening the document you wish to sort inside of Google Sheets. Highlight the column you wish to edit. Once you’ve highlighted the cell, click on an empty cell in the top of an empty column in order to make sure that your information is moved to the side of the chart. This column will be used to display the results of your sort and formula below. Now, at the top of the document in the formula input box, type or paste the following without quotes: “=UNIQUE()”. This formula will tell Sheets to copy and display only unique cell inputs, and to ignore any information that copies or duplicates other cells. Within the parenthesis of the formula above, make sure to type the cell coordinates using the standard spreadsheet method (for example, typing (A1:A75) will scan all the information from column A row 1, to column A row 75). Once you’ve entered the new information, hit enter to move your new data to the column you designated earlier. Once this is complete, you can either check manually or import your data into your working spreadsheet.
Using an Add-On
We should note that there are a number of plugins available for use with Google Sheets online through the Chrome Web Store, including a tool for removing duplicate data entries automatically from the document. “Remove Duplicates” is an aptly-named tool offered by developer ablebits that allows you to quickly find duplicates throughout an entire sheet of information or by searching up to two columns at once. You can move, delete, and highlight results, just as you can with the formula tasks above, although this process is far more automated than we’ve seen previously, so keep that in mind if you want more control over the process. The tool includes two wizard setups that allow you to both find and delete duplicates or unique qualities from your document, making it easy to track the information as you go.
Overall, using a wizard tool to find your information might be worth it in the long-run for users who are constantly looking for duplicates in their spreadsheets and would understandably rather spend their time doing something else, but users who only need to check once or twice every few months may be better off just using the formula tools above to manually identify their information. That said, Remove Duplicates has a solid rating on the Chrome Web Store, with strong reviews and an active development team that responds to critiques and complaints. Overall, it’s a great choice if you’re looking to find a more streamlined way to sort your content and find duplicates within a spreadsheet.
Spreadsheets are often quite a bit more complicated than similar documents made in Google Docs or in Microsoft Word. Because they’re built to deal with organized data, it’s important to make sure that your content is accurate at all times. Keeping a duplicate cell in your files can really cause some serious problems with your data if you aren’t careful, especially when trying to sort financial information into a useful spreadsheet. Compounding matters, trying to find the erroneous cell on your own can be a hassle that’s nearly impossible if the spreadsheet is large. Luckily, identifying, removing, and deleting identical cells of data is surprisingly easy in Google Sheets, something that’s a positive if you’re constantly dealing with spreadsheets in your day-to-day workflow. And if you’re looking for something that makes it a little easier to sort your content, you can always use an add-on like Remove Duplicates to make sure your information is well-sorted and well organized. In 2019, there’s no reason to keep a messy, error-filled spreadsheet, so make sure that your data is correct and your information is verified by using the methods outlined above.