Compare data from two columns or sheets

The Compare Columns or Sheets tool from the Remove Duplicates add-on for Google Sheets scans two spreadsheets or columns of your choice for duplicated or unique data. The results can be colored, deleted, copied, or moved to another location.

Video: How to compare Google Sheets for duplicates

Before you start

We care about your data and suggest you always create backup copies of your spreadsheets. A special option of the add-on will do that for you if you select it.

Please bear in mind that Google Sheets limits the total number of possible cells in your file up to 2 million cells.

Tip. This tool will help you compare two sheets or tables for duplicates or uniques.

If your task is to find identical or unique rows in a table, take advantage of the Find duplicate or unique rows add-on instead.

To look for dupes or uniques in all single cells all over an entire spreadsheet, Find duplicate or unique cells is what you should use.

How to use Compare Columns or Sheets

Start the tool

Find Remove Duplicates in the Add-ons menu and click on the Compare columns or sheets tool to open it:
Run Compare sheets tool from the Google Sheets menu.

Tip. You can also find the utility in the Dedupe & Compare group in the Power Tools add-on:

  1. Access Dedupe & Compare from the corresponding Google Sheets menu:
  2. Find the utility in the Data group of Power Tools.

  3. And click the Compare two sheets icon on the Power Tools sidebar:
    Compare two sheets in Power Tools.

Step 1: Select your main table

Choose the sheet and the range with your main table:
Select the sheet with your main table.
You can choose any of the open sheets in the Select the main sheet drop-down list.

Note. The action you choose for the results will be applied to this sheet.

The add-on highlights the entire used range in the sheet of your choice automatically and shows it in the Select your column or table field. You can alter it by changing the selection manually, entering the range address in this field, or by clicking the Select range icon. You will then see a corresponding dialog box that will let you pick the necessary cells:
Adjust the range.

Note. If you want to delete duplicates, not just color them, it's best to select the whole table so that the add-on removes entire rows avoiding data corruption.
Tip. Use the Auto select button to automatically pick the range with all the data you have.

You can choose to back up the original table by ticking off the Create a backup copy of the sheet checkbox.

Click Next to proceed to the second step.

Step 2: Choose the table for comparison

Choose the sheet with the table you want to compare to your main one:
Select the second table for comparison.
You can choose any of the open sheets in the Select the second sheet drop-down list.

The Select your second column or table field shows the currently highlighted range. Define another one in this field by entering it on your keyboard, picking it directly in the sheet, or by using the Select range icon. The latter will open a dialog box so you could pick the necessary cells:
Adjust the range for the lookup table.

Tip. Use the Auto select button to indentify the range with all your data automatically.

In case you want to change the main table/column, click the Back button.

Otherwise, choose Next to proceed to the next step.

Step 3: Decide what to find

The add-on allows you to find singular or repeating values in the tables:
Choose Duplicate values to find all the repetitions.

  • Pick Duplicate values to look for the records you have in both tables/columns.
    Note. The action you select for the found duplicates will be applied to the main table.
  • Choose Unique values to find those entries that are in your main table only.

Click Next to go on to the next step.

Step 4: Pick the columns to compare

On this step, you will see a list of columns in your main table. Select the checkboxes next to the key columns you want to compare in your sheets and pick the corresponding column in your second table.

Tip. If you select more than one column, a row will be considered duplicate if the values in all these columns are the same.

Choose columns you want to compare.

  1. If your tables have header rows, make sure to tick off the checkboxes Table 1 has headers and Table 2 has headers respectively to see the column titles. If you don't have headers, unselect these boxes to display column names.
  2. You can use the 1st row content fields to see what kind of data you have in your main table columns.
  3. If you want to look for complete row duplicates, select all columns by using this checkbox in the header row.
  4. Automatically select all columns that have the same headers in both tables by using the Auto detect button.
  5. Check the Skip empty cells box to ignore duplicated blanks in the selected columns. Otherwise, blank matches will be considered duplicates.
  6. Use the Case sensitive option to consider those records that differ only in case as uniques.
    Note. Leading and trailing spaces in cell values are ignored by default.
  7. Make sure to indicate the corresponding Table 2 columns by clicking the down arrow for each of them and picking the necessary column from the drop-down list.

Once everything is set, press Next to proceed to the last step.

Step 5: What to do with the results

There are six ways to deal with the found values:
Fill found dupes with color.

Note. The action you select on this step will be applied to your main table.
  • Color the rows with the found values by picking the Fill with color option. Click on the down arrow next to this option to choose a hue you'd like to use.
  • Add a status column to your main table.
    Tip. You'll be able to sort out duplicates easily after that.
  • Decide to Copy to another location and have the results in a new sheet, new spreadsheet, or another existing sheet in the current file.
    Tip. The Existing sheet option lets you pick the necessary sheet from the list and specify a destination cell or a range:
    Choose one of the existing sheets and its cell or range.
    If you click the Select range icon, you'll be able to pick the top-left cell for the result directly from the sheet:
    Decide where to see the result.
  • The same goes for the Move to another location option. The values will be cut and pasted to a place of your choice.
  • Pick Clear values to remove the found records in the selected columns and leave all other data intact.
  • You can also remove all rows with the found dupes using the Delete rows within selection option.

Сlick Finish to find duplicate or unique values and mark them on the main sheet.

See the result

Once the add-on completes the search, you will see the summary of the results with the number of found values and the action that was applied to your main table:
See the number of found dupes in the result message.

Tip. If you'd like to run the add-on again, click on the Start over button.

Post a comment

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.
Privacy policy Terms of use Contact us

Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.