Merge data from two Google spreadsheets

The Merge Sheets add-on brings the latest data from an additional sheet into your main table. Find and pull matching values, or add non-matching rows, if needed, in five simple steps.
Merge two tables in Google Sheets.

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.

How to use Merge Sheets

Start the add-on

To open the tool, go to Add-ons > Merge Sheets > Start:
Run the add-on from the Google Sheets menu.

Tip. You can also find Merge Sheets in the Merge & Combine group in the Power Tools add-on:

  1. Access Merge & Combine from the corresponding Google Sheets menu:
    Merge & Combine in Power Tools.
  2. Click the Merge sheets icon on the Power Tools sidebar:
    The Merge Sheets icon in Power Tools.

Step 1: Select your main table

Your main table is the one that will be updated as the result of the data processing:
Select the sheet where you want to update the records.
You can choose the sheet with your main table in the Select your main sheet drop-down list.

The add-on highlights the entire table in the sheet of interest by default. You can edit the range either by manually typing the address in the Select the range with your table 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 in this pop-up window.

Tip. Click on the Auto select button to highlight the entire data range in your sheet back.

Don't forget the Create a backup copy of the sheet checkbox to keep the original table.

Click Next to go to the second step.

Step 2: Choose the lookup sheet

The lookup sheet is the table that contains the information you want to copy to your main sheet.

Tip. This table will not be modified in any way.

Select the sheet with the values to pull.
Choose the table using the Select the lookup sheet list with all available sheets.

Select the range with your table field will display the address of cells with your data. You can edit it manually, change it by highlighting a different range in the lookup table, or use the Select range icon and alter the range in the corresponding pop-up window:
Adjust the range of the lookup table.

Tip. The Auto select button will help you automatically define the table range in the sheet.

Click Next to proceed to the third step, or edit the main table by going Back.

Step 3: Identify matching columns

Here you are to pick those columns that appear in your both tables (key columns). The add-on will compare these columns to identify the same (matching) records.

Tip. The columns you select on this step will not be modified.
Note. If you tick off more than one column, a record will be considered the same if the values in all these columns match.

Identify key columns and their equivalents in the lookup table.

  1. To see column titles, check the Main table has headers and Lookup table has headers boxes respectively. If there are no headers in your tables, uncheck these boxes to display column names instead.
  2. The 1st row content fields will help you see the top values in the main table columns.
  3. The add-on can find complete row matches if you use the top checkbox to select all columns.
  4. You can use the Auto detect button to select all columns with the same headers in your tables.
    Tip. All such columns are marked in bold in the Main table columns list.
  5. If you don't want to match empty cells, check the option to Ignore blanks.
  6. Make sure you choose the lookup table columns to compare to the main table columns.

Click Next to go on to step 4.

Step 4: Pick columns to add or update in the main sheet

On this step, you are to decide what to do with other columns of the lookup table:
Choose the columns to add or update in the main sheet.

  1. Tick off the column of interest, click on the Action field and choose what to do with the column:
    • All columns selected as matching on step 3 are displayed with the light gray mark Match with and are grouped at the top of the list by default.
    • The Update values in option will look up the same contents in matching columns and replace corresponding records in the columns you pick on this step:
      Update values in the main table.
    • Choose to Add a missing column to the end, and its values will be inserted for all matching records in a new column to the right of the original table:
      Add values to the end of the main table.
  2. Check this box to select all columns at once and add their values to the end of the original table. Uncheck back to unselect all columns if you don't want to add or update any.
  3. The Auto detect button will automatically pick the Update values in action for all remaining columns with the same headers.
  4. If there are lots of columns, you can filter the list using the respective option in the Show columns drop-down list:
    Filter the list of columns you want to see on this step.

Click Next to follow to the last step.

Step 5: Tweak additional options

This step offers a set of additional options to match, update, or add the values:
Choose the way to merge the data.

Add rows and columns

  • Add non-matching rows to the end of the main table. The add-on will insert the rows with key records that are present only in your lookup table. Thus, if you have any new records, you'll transfer them to the appropriate columns in the main table even if their order is different:
    Add non-matching rows to the end of the main table.
  • Add a status column. This one will show the changes made to the rows and mark them as Matching, Matching and updated, Non-matching, or New row.

Update cells

  • Update only empty and new cells in the main table. If you have blank cells in the column you are updating in your main table, keep all cells that already have records untouched. The existing values in the main table will not be overwritten:
    Update only empty and new cells in the main table.
  • To avoid copying empty cells from the lookup table, tick off the option Update only if cells from the lookup table contain data:
    Update only if cells from the lookup table contain data.

Highlight cells

  • If you already have some rows highlighted, you can remove their current filling using the Clear background color in the columns you are updating option. Any existing background color in the column you selected to update will be reset to the default one.
  • You can see all cells that were changed in the main table by selecting the option Set background color of updated cells.
Tip. Click on the arrow next to this option to pick a hue from the list.

Get the results

Click Finish to merge the tables and you'll see the number of inserted columns, added and updated rows:
Get the result with the number of matching rows found and updated.


I'm trying to record a macro using Merge Sheets, but when I run the macro I get an error message "ReferenceError: "startMergeSheets" is not defined" ... any ideas?


Hi Jared,
Thank you for using our product and for your question. Unfortunately, there is still no way to include an add-on into the macro.


One of my columns has a number like 80. When I merge, the column turns into a date. What am I doing wrong?


Hello, Michelle,
Thank you for reporting this issue to us.

For us to be able to assist you better, please email us to with a more detailed description of your task. Please include the sample tables both main and lookup, so we could see the format of the data in both tables.

We'll look into the problem and do our best to help.
Thank you for your time!


Hi--The system is telling me that my activation code (just sent to me a couple of minutes ago) has expired. Please advise. Thank you, Leigh Moore


Hi, Leigh,

We've got your email and will reply to you there. Please look for a message from Ablebits support team in your Inbox.

Thank you.


I trialed the Merge function and it worked without fail. I purchased the product and now a simple merge of 40 rows of data repeatedly fails. The 2 columns in the main table and lookup tables contain First Name & Surname. I have checked that names in the main table exactly match the names in the lookup table. I have deleted and re-typed the names in new columns. I have removed and re-installed and re-activated the Powertool. After this data in one of the missing 4 rows was inserted. Is there some known issue?
Thank you.


Thank you for reporting this problem to us, Barb. So sorry you're having difficulties with our tool.

If it's not a big trouble for you, I kindly ask you to share your spreadsheet with us ( with your source data and the result you're getting/expect to get. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved. Or you can replace any important information with some irrelevant data, just keep the format.

The screenshots of the options you select on each step of the wizard would also help.

We'll investigate the problem.
Thanks in advance for your responsiveness.

Stefan Boeters says:
February 8, 2019 at 10:00 pm

I would appreciate the option "not case sensitive" in the matching columns. (As you may guess, I have a main sheet which is all lower case and a lookup sheet that uses upper case letters.)


Hi Stefan,
thank you for using our product and for the feedback!
Yes, you are absolutely right, this option is really needed, and we are working on adding it to one of the next upgrades of the add-on!
Thank you.


I would like the option to save a Merge, and re-run the same settings. This way I could keep updating the spreadsheet as new information comes in.


Thank you for contacting us, Jennifer.
At the moment this feature is available for testing in our Remove duplicate rows only. We do consider implementing the same to Merge Sheets in one of the next versions, but I can't give you any timing. If you'd like, we can let you know when it's out for use.
Thank you for understanding.


My main sheet/data set I'm always adding rows to, it would be nice to be able to update the columns I'm pulling data from my database sheet without having to run the process again. otherwise, works as intended.. helpful.


Thank you for your feedback, Ryan.

Though it's technically impossible to run the add-on automatically or keep it running on the background, we plan to add scenarios to Merge Sheets as we did with Remove duplicate rows. If you'd like, we can email you when it's available.


Ask a question (posted publicly)

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
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.