How to quickly remove duplicates in Google Sheets
Google Sheets is a powerhouse spreadsheet tool. Whether auto-suggesting formulas or fixing them for you, Sheets has all the tools you need to manage all but the most challenging spreadsheet tasks. And it’s easy to use, especially if you do most of your work in the cloud and are thinking about ditching your old laptop for one of thebest Chromebooks you may buy.
Sheets organizes and analyzes data to reduce the errors that come with handling large amounts of information. Large data sets are prone to duplicate data. Finding and correcting duplicate rows and cells is a painstaking task. Here’s an easy way to clean up your spreadsheet and verify you have only the data you need.
How to remove duplicate entries from Sheets
There are two ways to remove duplicates from a Sheets data set. You can use the UNIQUE function if you’re comfortable working with functions. The other way is to click your way to the Data menu, which is as powerful as it is user-friendly. Both methods have strengths and weaknesses and affect your data differently.
Using UNIQUE to remove duplicate data
The biggest difference between using UNIQUE to remove redundant data and using the menu function is UNIQUE doesn’t change your data set. Instead, it produces a new range of cells with the unique data.
You can also use UNIQUE to modify and output more than one column of data at a time.
When using UNIQUE like this, it looks to see if the data in the rows are identical. In this instance, if the names are different, but the Twitter handles are the same, UNIQUE counts those rows as unique and they aren’t omitted.
Where UNIQUE is superior to the Remove Duplicates function in the Data menu is its ability to work with data separated by column, not just row. The pop-up help window that appears when typing in a function shows that there’s more to the UNIQUE function than a range of data. The full specification for UNIQUE looks like this:
“UNIQUE(range, [by_column], [exactly_once])”
The two bits enclosed by brackets are optional, so Sheets doesn’t care if you include them. You can take advantage of these extra options by typing eithertrueorfalseafter the range (if you omit them, Sheets defaults their value tofalse).
Here’s how to remove duplicates with data arranged horizontally.
The last optional modifier outputs only entries that appear exactly one time. The formula"=UNIQUE(A2:C21, FALSE, TRUE)“outputs a new table in which none of the names that appeared more than once would survive the cut.
One last benefit to using the UNIQUE function is that it can be combined with other functions to get you exactly the data you want, precisely how you want it organized.
Using Remove Duplicates to clear redundant data
While UNIQUE outputs new data based on a given set of cells, Remove Duplicates changes the original set of data. Here’s how you use it.
To understand what sets Remove Duplicates apart from UNIQUE, you must go back to the checkboxes that ask you which columns to analyze. Those checkboxes tell Sheets which information to check for redundancy. Here’s an example to make it clearer.
Remove Duplicates only modifies data that’s been highlighted. If you only highlight the row of last names, then none of the rows in adjacent columns are modified.
The option to analyze columns individually or as a whole gives Remove Duplicates a finer degree of control than UNIQUE. On the other hand, Remove Duplicates can only analyze data row by row, whereas UNIQUE can analyze data column by column. Given their differences in how they modify data and the difference in how they analyze them, two different tools can help you reach the same goal.
How to highlight duplicates in Google Sheets
Deleting duplicate data might not always be the best thing to do. If changing data is beyond your pay grade, the best course of action might be to highlight the duplicate data instead and let someone else figure out what to do with it. If this sounds like you, it’s time to unlock the power of conditional formatting.
Here’s the result:
Start fresh with a clean worksheet
When you want to learn more about data manipulation in Google Sheets, we have guides that show youhow to get rid of data,how to preserve it, andhow to put it in some sort of order. Then, take a look at some of thebest add-ons for Google Sheets, so even if you’re not a spreadsheet wizard, it’s possible to at least pretend to be.
The note-taking app I should have used all along
Broader branding hints at wider paid-tier ambitions
Keep privacy a priority with the best VPNs
PlayStation Plus subscribers of all tiers are getting access to three excellent titles, including Psychonauts 2, in September
This article is sponsored by Total Wireless.
I found the only AI photo app worth keeping on my phone
