If you’ve ever worked with spreadsheets where each team or month has its own tab, you know how messy it can get when you need to combine all that data into one place. Whether it’s monthly sales reports, survey responses, or department logs, manually copying and pasting everything into a master sheet is time-consuming — and prone to errors. Fortunately, Google Sheets gives us powerful tools like QUERY functions and Apps Script to automatically consolidate data from multiple tabs into a single master sheet. In this guide, we’ll show you step-by-step how to do it — with and without code.
Why Consolidate Data from Multiple Tabs?
Consolidating helps you:
- Get a full picture of your data in one place
- Build dynamic dashboards and summaries
- Reduce the risk of data entry errors
- Save hours of manual copy-pasting
Real-Life Scenario: Weekly Team Logs
Imagine you’re managing a project where each team logs their weekly progress in separate tabs: “Team A”, “Team B”, and “Team C”. Each sheet contains the same columns:
| Date | Team | Task | Status |
|---|---|---|---|
| 2024-04-01 | Team A | Design UI | Completed |
| 2024-04-02 | Team A | Client Feedback | Pending |
You want to combine all of these into one “Master” sheet that updates automatically. Let’s explore how.
Method 1: Use QUERY + INDIRECT (Good for Few Known Tabs)
Step-by-Step Instructions
- Create a new sheet named Master.
- List your tab names in a column (e.g., A2:A4 = Team A, Team B, Team C).
- Use this formula in another cell (e.g., B1):
=QUERY({
INDIRECT("'Team A'!A2:D");
INDIRECT("'Team B'!A2:D");
INDIRECT("'Team C'!A2:D")
},
"SELECT Col1, Col2, Col3, Col4 WHERE Col1 IS NOT NULL", 0)
Limitations
- You must manually add each tab to the formula
- Does not work across different files
- Tabs must exist and be named exactly as typed
Method 2: Use Google Apps Script (Best for Dynamic Tabs)
Script to Combine All Tabs
function consolidateTabsToMaster() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const masterSheet = ss.getSheetByName("Master") || ss.insertSheet("Master");
masterSheet.clear();
masterSheet.appendRow(["Date", "Team", "Task", "Status"]);
const allSheets = ss.getSheets().filter(sheet => sheet.getName() !== "Master");
allSheets.forEach(sheet => {
const lastRow = sheet.getLastRow();
if (lastRow > 1) {
const data = sheet.getRange(2, 1, lastRow - 1, 4).getValues();
masterSheet.getRange(masterSheet.getLastRow() + 1, 1, data.length, 4).setValues(data);
}
});
}
How to Use the Script
- Go to Extensions > Apps Script
- Paste the script above and click Save
- Run
consolidateTabsToMaster()using the ▶️ button
Pro Tips
- Add a timestamp to track last update
- Use a button in your sheet to trigger the script
- Exclude archived tabs by adding filters in your script
Sample Data in Sheets
Each sheet (Team A, Team B, etc.) should have a structure like this:
| Date | Team | Task | Status |
|---|---|---|---|
| 2024-04-01 | Team B | Code Backend | In Progress |
Key Benefits
- Eliminates manual consolidation
- Dynamic updates with a single click or auto-trigger
- Ideal for reports, dashboards, and summaries
Quick-Reference Cheat Sheet
| Tool | Best For | Pros | Cons |
|---|---|---|---|
| QUERY + INDIRECT | Few, known tab names | No code needed, easy to use | Manual updates, limited flexibility |
| Apps Script | Many dynamic tabs | Scalable, reusable, automatable | Needs minimal scripting knowledge |
Consolidating data from multiple tabs doesn’t have to be a hassle. Start with a simple QUERY formula if your setup is small. But for larger, dynamic spreadsheets — especially those with changing sheet names — Apps Script is the way to go. With just a few lines of code, your master sheet can stay up-to-date and ready for action!