How to consolidate data from multiple sheets in Google Sheets

By Jake Marsh · Updated

To consolidate data from multiple sheets in Google Sheets, open Extensions, Tabmeld, Combine sheets, add the tabs or files, tick Group and total rows with the same key (consolidate), choose the columns to group by (such as Region and Product), pick how to total each other column, and click Create tab. You get one row per group with its totals, and the preview shows the total going in and the total coming out.

Combine or consolidate

Combining stacks every row from every source into one long table. Consolidating goes one step further: rows with the same key become one row, and their numbers are added up or otherwise summarized. Three regional sales tabs with 400 rows each might combine into 1,200 rows and consolidate into 36: one per region and product.

Steps

  1. Open Combine sheets and add the sources: tabs in this file, Add all tabs in this file, or tabs from other spreadsheets through Pick a file from Drive....

  2. Line up columns By header name, so "Units" on one tab and "units" on another are the same column.

  3. Tick Group and total rows with the same key (consolidate).

  4. Tick the group-by columns, such as Region and Product.

  5. Pick a total for each other column:

    ChooseFor
    SumUnits, revenue, hours
    Count values, Count uniqueHow many orders, how many customers
    AveragePrice, score
    Smallest, LargestFirst and last dates, lowest and highest price
    First value, Last valueA name or status that's the same for the group
    Join unique valuesA list of the reps or stores in each group
  6. Tick Add a "Rows" column to see how many rows went into each group.

  7. Click Preview. Check the totals in and out, and the list of any value that wasn't a number.

  8. Click Create tab.

Totals you can trust

A value like "12 units" or "n/a" in a number column can't be added. SUM in Google Sheets skips text without a warning, so the total comes out short. Tabmeld lists every value it couldn't add in the preview, next to the totals going in and coming out, so you can fix the source before you build a report on it.

Keep it current

The consolidated tab remembers its sources and settings. Click Refresh in the sidebar or Refresh combined tabs in the menu to rebuild it. If you picked All tabs with a name filter, new tabs that match are included.

What it costs

Tabmeld is $4.99 a month or $39 a year per Google account. Free trial: 14 days or 20 runs, whichever comes first. No card needed. Every plan and the install link are on the Tabmeld page.

Tabmeld: Combine, Merge & Compare Sheets

Combine, consolidate, compare and merge Google Sheets tabs and files in one add-on $4.99 a month or $39 a year per Google account.


Questions

Is this the same as a pivot table?

A pivot table summarizes one range. Consolidating first brings many tabs and files together, then summarizes them into a plain table you can sort, edit or build a pivot table on.

Can I group by more than one column?

Yes. Tick every column that makes up the key, such as Region, Product and Month.

What if the tabs have columns in different orders?

With By header name, order doesn't matter. Columns are matched by their headers, ignoring case.

Does it change my source tabs?

No. The sources are only read. The result goes into a new tab Tabmeld manages.


More Tabmeld guides


More Google guides