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
-
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....
-
Line up columns By header name, so "Units" on one tab and "units" on another are the same column.
-
Tick Group and total rows with the same key (consolidate).
-
Tick the group-by columns, such as Region and Product.
-
Pick a total for each other column:
Choose For Sum Units, revenue, hours Count values, Count unique How many orders, how many customers Average Price, score Smallest, Largest First and last dates, lowest and highest price First value, Last value A name or status that's the same for the group Join unique values A list of the reps or stores in each group -
Tick Add a "Rows" column to see how many rows went into each group.
-
Click Preview. Check the totals in and out, and the list of any value that wasn't a number.
-
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
- How to compare two Google Sheets for differences
- How to update one Google Sheet from another by a key column
More Google guides
- How to move GA4 Reports Builder reports to Trafficledger (Trafficledger)
- How to make a copy of a template tab for each name in a list (Tabherd)
- How to email each person only their rows from a Google Sheet (Scheduled Sheet Reports)
- How to show dotted-line reporting on an org chart from Google Sheets (Rowtree)