In this article:

May 8, 2024

Consolidating data in Google Sheets can be accomplished in several ways, depending on the nature of your data and what you mean by "consolidate." Here are a few methods you might find useful:

If you have data spread across multiple Google Sheets files and you want to bring it all into one sheet, you can use the IMPORTRANGE function. This function allows you to import a range of cells from a specified spreadsheet.

**=IMPORTRANGE("spreadsheet_url", "range_string")**

Where:

**spreadsheet_url**is the URL of the spreadsheet from which you want to import data.**range_string**specifies the range of cells to be imported, e.g., "Sheet1!A1:C10".

Example:

You have two Google Sheets. Sheet1 contains sales data for January, and Sheet2 contains sales data for February. You want to import the February data into Sheet1.

In Sheet1, you would use the IMPORTRANGE function like this:

**=IMPORTRANGE("https://docs.google.com/spreadsheets/d/ABC123", "Sheet1!A1:C30")**

- Replace "https://docs.google.com/spreadsheets/d/ABC123" with the URL of Sheet2.
- "Sheet1!A1:C30" specifies that you're importing data from A1 to C30 on Sheet1 of the second spreadsheet.

The QUERY function is useful for aggregating and filtering data within a single Google Sheet or from an IMPORTRANGE. You can use it to consolidate data based on certain conditions.

**=QUERY(range, query_expression, [headers])**

Where:

**range**is the range of cells to perform the query on.**query_expression**is a text string in the Google Visualization API Query Language format.**headers**is an optional argument indicating the number of header rows at the top of the data range.

Suppose you have a Google Sheet that contains sales data for different products over various months:

Let's say you want to filter this data to show only the sales for "Product A" and also to see the total sales for "Product A" in the given months.

You can use the following QUERY function to achieve this:

**=QUERY(A1:C7, "select A, B, sum(C) where B = 'Product A' group by A, B", 1)**

A1:C7 is the range of cells that contain the data you're querying.

"select A, B, sum(C) where B = 'Product A' group by A, B" is the query expression where:

- select A, B, sum(C) tells Sheets to select the month (column A), product (column B), and the sum of sales (column C).
- where B = 'Product A' filters the data to include only rows where the product is "Product A".
- group by A, B groups the results by month and product, which is necessary for aggregation functions like SUM().

1 indicates that there is 1 header row in the data range.

This QUERY will produce a result that looks something like this:

Pivot Tables are powerful tools in Google Sheets for summarizing, analyzing, exploring, and presenting your data. You can use Pivot Tables to consolidate data by summarizing it through various dimensions (e.g., sum, average).

To create a Pivot Table:

Select your data.

Go to **Insert > Pivot table**.

Choose where you want the Pivot Table to be placed.

Configure your Pivot Table by adding rows, columns, values, and filters as needed. Below is a sample table from our given data:

You can also use functions like SUMIF, VLOOKUP, HLOOKUP, and ARRAYFORMULA to consolidate data by criteria or match data across different ranges.

**SUMIF(range, criterion, [sum_range]) **allows you to sum values based on a single criterion.

Suppose you have a list of sales transactions with the following information:

To achieve this, you can use the SUMIF function as follows:

**=SUMIF(A2:A7, "Alice", B2:B7)**

After applying this SUMIF formula, the result will be the total sales made by Alice, which, based on the sample data provided, would be 700 (300 + 150 + 250).

**VLOOKUP(search_key, range, index, [is_sorted])** looks for a value in the first column of a range and returns a value in the same row from a specified column.

Using the same set of data above:

Let's say you want to find the sales amount for Bob's first entry in the list. To find this information, you can use the VLOOKUP function as follows:

**=VLOOKUP("Bob", A2:B7, 2, FALSE)**

**Plan Your Data Structure:** Before consolidating, plan how you want your data to be structured. This can save you time and make the consolidation process smoother.

**Use Named Ranges:** For complex sheets, consider using named ranges to make formulas easier to understand.

**Regularly Update Imports**: If you're using IMPORTRANGE, remember that changes in the source sheet can affect the destination sheet. Keep an eye on your imports to ensure they're up to date.

**Check Access Permissions:** When importing data from different sheets, ensure you have the necessary permissions to access those sheets.

We hope that this article has helped you and given you a better understanding of how to consolidate data in Google Sheets. If you enjoyed this article, you might also like our articles on how to convert CSV to Google Sheets and how to perform a fuzzy match in Google Sheets.

Get Google Sheets productivity and automation tips delivered straight to your inbox

We'll email you 1-3 times a week — and never share your information.

Get your copy of our free Google Sheets automation guide!

- 27 pages of Google Sheets tips and tricks to save time
- Covers pivot tables and other advanced topics
- 100% free

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->