Oct 18, 2023

Combining Multiple Excel Worksheets

By Jamie Cloak, Marketing Director at Omega High Impact Print Solutions

Do you ever find yourself staring at multiple Excel worksheets, scratching your head, wondering how to combine all sheets into one dataset? Have no fear – we've broken down the process step-by-step to ensure you can combine all your data seamlessly.

ENTER: the Excel Power Query Tool! Ahhh, we are so excited about this awesome (and somewhat hidden) feature. In our humble opinion, it’s no coincidence that “Power” is in the name of such a POWER(ful) tool! The Power Query allows you to clean large sets of data, combine multiple sheets into one, and reduce the file size limiting its load time.

Now, if you’re working with 1-2 worksheets, copying and pasting them together may suffice, but the Power Query Tool offers a dynamic feature that allows you to constantly refresh your combined worksheet, meaning if you adjust or add content on one of the individual sheets, you can simply refresh the combined version and the updates will push out universally. Pretty cool, huh? However, we'll get to that a bit later....

Before we get started, one thing to note is that this process will work best if your files are laid-out in a very similar manner. It's recommended to ensure all headers are the same and the data is similar from column to column. In this exercise, you will notice that we have First Name, Last Name and Email Address as our headers on all three of the tabs we are looking to combine.

Set your headers consistently throughout all worksheets — Power Query Tool view 1 - Headers

Now for the fun part!

Step 1

Create and name each table. This will allow for an easy reference, which you will see in a few steps. To do this on a Mac, click and highlight the columns you want to apply the table to.

Create a table and titles each worksheet — Table & Title

Once highlighted, type CMD +T, then click Ok and name the table in the upper left-hand corner.

Follow these steps for all tabs in your master file. Please note that the first window to pop-up will ask if your table has headers. In our case, we selected this as we have matching headers on all files.

Step 2

Click the Data tab. From here, navigate to the left-hand side and select the arrow to the right of the Get Data (Power Query) icon. Then select Launch Power Query Editor.

Click on Data to launch Power Query Tool — Launch Power Query Tool

Now we want to import data from multiple queries so click Get data. From here, you want to select the appropriate file type to gather. For this exercise, we are going to select Excel workbook as our master file is already in an Excel workbook format. From here, click Browse and select the file you are looking to import data from. Once found on your local computer, select it and click Get Data and Next. This will load your relevant tables. Please note, this step is the reason why we named our tables. Now everything is clean and easy to identify. From here, choose the relevant tables that you want to include by check-marking all three and click Create.

Select the appropriate files to combine — Choose Data

Step 3

On this view, you will see that all three tables have been combined into the Power Query, but you’re not done! The last step is to combine them into one master data set. In the queries view, ensure you’re on the Home tab and navigate to the far right where you will find an option titled Combine – select this. Click the drop-down arrow next to Append Queries (option2) and select Append Queries as new. The reason we select Append Queries as new is that we want to create a separate table to keep this combined data as clean as possible.

Step 4

In the append pop-up, you will have the option to select Two tables or Three or more tables. In this exercise, we will select the radio button next to Three or more tables. Next, it will ask you which available tables you want to append. Simply click on each of the tables on the left display window and then click add. It will move the files (one-by-one) into the “Tables to append” window on the right. All we are doing here is specify which tables we want to combine. You also have the ability to organize the tables to display the data in any order that you want. Simply utilize the arrows on the right side of the pop-up window to move the tables into your desired order. Once you’re done, click Ok.

Select the files you want to combine — Append Files

Step 5

Now all three tables are combined in a vertical run – YAY! Great job, you’re crushing this Power Query Tool!!! Once you’re done, click Close and Load! Wahlah – there it is. A beautifully combined table that can easily be updated! Our favorite part of this process is if additional data is added to any of the individual tables, all you need to do is simply hit refresh on the Power Query Tool. To do this, select Data > click the down arrow next to Get Data (Power Query) > Launch Editor and click Refresh in the tool bar.

To see the combined file, select Close & Load — Close and Load

For all intents and purposes, we are officially done. However, sometimes you may have duplicate data from worksheet-to-worksheet, so we would like review how to remove duplications and the blank cells that are inevitable left behind.

Good news is this is a super simple process.

Step 1:

Highlight the cells with the data you want to adjust. Click Data and then Remove Duplicates. Be sure to check off if your worksheet has headers and select the columns you want to remove duplicates from. For this exercise, we selected them all and both the First Name, Last Name and Email Address columns had duplicates. Once you’re done, click OK.

Remove duplications from dataset — Remove duplications

If your sheet has multiple rows, you will likely be left with some blank cells. To remove the blank cells ensure you are on the Home tab in your toolbar. Select Find & Select, Go to Special and click the radio button for Blanks. This will highlight all of the blank cells in your worksheet. To remove them, (ensure you’re still on the Home tab), click the arrow next to Delete and select Delete Sheet Rows.

With this knowledge, you should be able to slice and dice multiple worksheets into one very organized place to ensure your data is ready to go for any request. Merge additional data, create Pivot Tables, drop them into PowerPoint Presentations (both internally and client-facing), and so on. The sky's the limit...you've got this!