Reporting & Analysis
20.11.2020
TLDR
Consolidating data from multiple spreadsheets into one can be done in two steps. First, import data from different sheets into separate tabs on a master spreadsheet using the IMPORTRANGE function. Then, consolidate the data from these tabs into one master tab using array formulas and a QUERY function to remove empty cells. This process ensures all your data is combined efficiently. For detailed guidance, a video tutorial is available from Learn Google Spreadsheets.
The hard way and the easy way
This is a scenario that most of us have faced at some point. Our data is spread across multiple spreadsheets and we need to consolidate them into one sheet. Sometimes, we also want to "lookup" and add new columns from other sheets to not just combine, but also map, the data. I will handle the data mapping in another blog post—for now, let's just combine data coming from multiple sources.
To start off, let's assume that you have the same number of columns to import and they contain the same data. E.g., sales by reps from different locations. You're combining the same data (sales in $$'s, sales rep's name, etc.) across these sheets.
The hard way: a two-step process.
Step 1: Importing data from multiple sheets into different tabs on a master spreadsheet
First, we are going to bring the data in from multiple different sheets into different tables in the same spreadsheet. To do this, first create a new sheet. Then add a new tab in that sheet to pull data in from your first source: let's call this tab 'Partial Data 1'.
We will use the IMPORTRANGE function to fill this sheet with data. This is how the function works:
Go to cell A1 of your 'Partial Data 1' sheet and add the formula above. Replace the stock hyperlink with a link to the GSheet that you wish to import from. Then replace the column indicator to highlight the columns you wish to import.
Hit enter after you type in the formula above. You should now see a #REF error. Hover over the cell to see this option asking you to provide access.
Click on the Allow access button and sit back as GSheets updates your new sheet with the data! Simply repeat this process as many times as you need with new tabs on your MASTER file.
Step 2: Consolidating the data from multiple tabs into one master tab
Great, now that you have all the data into your master spreadsheet, you still need to combine them into one Master tab. For this, we will use an array formulas combined with a query formula. Not ideal, I know.
To start off, let's copy and paste all the headers into a Master tab. We won't be pulling the headers from the Partial Data 1, 2, 3... tabs you've created on Step 1. On the new Master tab, type this in:
This will pull in all the data in the columns you've highlighted from all your partial data sets. However—here's the kicker—it will also download all the empty cells. ?
This means you will see your data from Partial Data 1, then a lot of empty cells (usually 1000 rows), then your data from Partial Data 2 and so on.
To remove these blanks, we will use the Query function. Let's start with wrapping a query function around the previous formula.
Improve your DTC game. Sign up for weekly tips.
Next, we will write a short query to only include rows which aren't blank. Like this formula below.
Great, this should combine all the data you need into your Master tab.
The good folks at Learn Google Spreadsheets have created a video to walk through the process, in case it's easier to follow-on.
