Tab Merge 101: How to Merge Tabs in a Excel and other Spreadsheets

Looking to merge tabs inside your spreadsheet?

Last week, a content marketing manager at a SaaS company I work with posted in Slack that she had the lead data for every month in 2022, in 12 separate tabs in a spreadsheet file, and she was looking for a way to merge all of them.

Sure, she could have easily gone one tab at a time, copying data from each individual tab and pasting all of it into one.

Seems super-easy, right?

But while doing so, what if she had made an error? She could have easily missed a row or two.

Or she could also have accidentally overwritten a few rows.

There’s always a possibility of human error when manually merging excel tabs. And when you’re playing around with large datasets, the probability of human error is even higher.

And what if she was asked to copy data from hundreds or thousands of tabs into one? Honestly, that’s not humanly possible.

Last but not least, what if the data inside the tabs was too large for Excel to handle?

This made me curious – does Microsoft Excel or Google Sheets have a functionality in place to perform quick and easy tab merge?

In this blog, I will walk you through tab merge in spreadsheets, looking at how it can be done in both applications, as well as introduce you to Gigasheet, our big data cloud spreadsheet that makes tab merge feel like a walk in the park.

How to Merge Tabs in Excel!

Microsoft Excel is one of the best spreadsheet platforms. There’s no doubt about that.

But does it allow users to combine data from multiple tabs into one?

Well, the answer is yes! But the steps are extremely complicated, especially if it’s your first time doing it.

For demonstration, I created a dummy dataset – just like the one content marketing manager in the above example was looking to merge.

As you can see, there are twelve tabs, each representing a month of the year (2022). Each of these tabs comprises information on leads that companies generated in that particular month. We have with us:

1. First Name
2. Last Name
3. Gender
4. Company
5. Job Title
6. Email Address
7. Country

Now, how do I combine all of this data? Well, I asked ChatGPT, but unfortunately, it gave me the wrong answer.

So, I read dozens of articles and watched several tutorials on YouTube. But I didn’t get a concrete answer.

Most of them were about – how to combine multiple spreadsheets. But there wasn’t much about how to combine multiple tabs in a spreadsheet.

Finally, I found a solution to my problem after interviewing a spreadsheet expert with 12 years of experience. He asked me to perform the following steps:

Merging Excel Tabs - Step by Step

● Step I – Click on “Data.”

● Step II – Select “Get Data (Power Query)”

● Step III – Click on “Excel Workbook.”

● Step IV – Select your Excel workbook from your computer by clicking on “Browse” and locating your file. After you do, click on “Next.”

● Step V – In the left-hand panel, select all your tabs.

● Step VI – Click on “Transform Data.”

● Step VII – Select “Source” in the right panel.

● Step VIII – Here, we’ll select “Name” and “Data.” Once you do, right-click or tap and click on “Remove other columns.”

● Step IX – Click on “the upper-left and upper-right arrow” next to “Data” and select all the columns. Tick-mark the “Use original column name as prefix.”

And there you go – Excel will have merged all your data from different tabs into one!

While this is an effective tactic, it’s complicated. And there are a few other problems with using Excel.

Merging Excel Tabs - Challenges

First, Microsoft Excel doesn’t support large CSV files. If your CSV file is too large in size, then either Microsoft Excel will crash, or your computer will hang. Microsoft Excel can seriously eat up your computer memory.

Another problem is – Microsoft Excel has its limitations. We mentioned, in one of our articles, that Microsoft Excel’s max row limit is 1,048,576. Anything more than this, Microsoft Excel will not support your spreadsheet file.

Next, we tried the same on Google Sheets – where it is simpler to merge your tabs.

Google Sheets Merge Tabs Requires an Extension

There’s no default option to merge tabs in Google Sheets, beyond manually copying and pasting data, which is prone to errors!

To merge our tabs, we had to install the “Combine Sheets” extension. While it’s a good extension, it offers a 30-days free trial – after which you’ll have to switch to either the annual or lifetime subscription.

This means – you have to pay money just so you can merge your data. For me, it was really disappointing.

Google Sheets Merge Tabs - Challenges

Also, Google Sheets has its own limitations. And it’s way more disappointing than Excel. Google Sheets has a limitation of 10 million cells. On top of this, if your spreadsheet is too large, then the browser you’re running Google Sheets in will likely hang.

So, if you play around with big data, Google Sheets is a big NO-NO!

Gigasheet solves these limitations.

Gigasheet is the Best Solution for Tab Merge in a Spreadsheet!

I don’t want to seem biased – but Gigasheet is the best affordable and easy-to-use solution I found that can easily help you merge tabs in a spreadsheet file.

If you’re not aware of what our platform is all about, Gigasheet is a free big data cloud spreadsheet platform that supports spreadsheet files with billions of rows. At the same time, you can play around with a large chunk of data using powerful functionalities like filters, grouping, pivot mode, Cross File VLOOKUP, and many more.

Gigasheet Tab Merge - Step by Step

• Step I – Upload your spreadsheet file to Gigasheet.

• Step II - If your spreadsheet file has multiple tabs, Gigasheet will create a folder – in which each of these tabs will be added as separate spreadsheet files. After Gigasheet processes your spreadsheet file, head over to the folder.

• Step III – Now, we’ll combine all these spreadsheet files. If you want to only combine lead data from Q1 and Q2, you can select spreadsheet files comprising data from January to June. Here, we’ll select all the files and click “Combine.”

• Step IV – The combined data will be available in a new spreadsheet file. Let’s name this new file “Combined Lead Data 2022.” After naming it, click on “Combine.”

Gigasheet will combine your file in mere seconds.

Even if you want to combine multiple spreadsheet files, you can use Gigasheet. But before you go ahead and do that, it’s important to ensure that all your column headers are the same. Otherwise, you will get an error.

Also, did we tell you that you can use Gigasheet to quickly analyze your data?

What if you want to nurture your leads from Australia? Or what if you want your data smartly arranged by country or maybe company or job title?

Let’s Use Gigasheet to Filter Your Tab Merge Data

Let’s say you want to nurture your leads based in Australia via an email marketing campaign. You don’t need to go through this massive amount of data or use the search functionality. You can have all your leads from Australia in one place by applying this filter:

Results –

Now, you can export these leads in the form of a CSV file using the “Export” option.

Let’s Try Grouping Tab Merge Data!

Let’s say you want to arrange your data by country for better analysis. To group my data, I clicked on “Group” and grouped my data by “Country.”

Results –

From here, if you want to see the names or email addresses of leads based in China, you can just click on “China” and all the leads from China.

You can add another layer of grouping for diving deep into your data. For instance, let’s add another layer of grouping as “Gender.”

Results –

If you want to run an email marketing campaign to nurture men in China, you can just easily do it from here.

The possibilities of playing around with your data are endless! You can also use several other powerful features like Data Visualization, Cleanup, Enrichment, Cross File VLOOKUP, and more.

We’d love to have you try out our platform for free. Sign up today!

‍