I have received a few questions recently relating to working with data spread across multiple sheets. In general, if the data is in the same layout, keep it in one table.

Two common reasons to split up data are.

1. Time-based – eg keep transactions for a month or a year in separate sheets
2. Category-based eg keep transactions for a state or a department in a separate sheet

The problem arises when you want to accumulate all the months, years, states or departments.

Excel doesn’t work well with summarising multiple sheets. It can be done, but it is clunky and not scalable.

It is much easier to put the data into a single table and then use filtering to handle the segmentation. Then Pivot Tables can be used for reports and dashboards.

If there is access/security concerns, then you can use Power Query to automate extracting states or departments ready for distribution.

Power Query can also offer a solution to existing split data. Power Query offers an automated way to consolidate separate tables into a single table.

You can download the before and the after files at the buttons at the bottom of the post.

Consolidating

Let’s look at how to combine existing split data. The image below has three separate sheets, with three separate state tables.

The data is structured the same in each sheet. I have used a formatted table for each table.

The tables are named tblNSW, tblVIC and tblWA. I prefix my table names with tbl so I know they are a formatted tables.

I use range names and I find it handy to know the difference between range names and table names. Also, when I type tbl when creating a formula, all the tables are listed together.

We will first create three separate Power Queries, one for each table.

Click inside the table and click the Data ribbon tab and click the From Table/Range button (left-hand side of ribbon).

This opens the Power Query window – note this is a separate window to Excel and you can’t access Excel while this window is open.

On the right of screen change the name by a adding q to the start of the name and remove the Changed Type step by clicking the cross on the left of the Change Type Step – see images below.

Click the Close & Load drop down (far left of the Home ribbon) and choose Close & Load To.  Choose Only Create Connection and click OK.

On the right of screen a Query task pane will display with the name of the query.

Follow those same steps for the other two tables. It takes less then 30 seconds per table.

Now that we have three queries, we can Append them to create a complete table.

Appending assumes the columns are the same between the tables.

In the Data ribbon click the Get Data drop down and choose Combine Queries and Append

Click the Three or more tables option and select each query and click the Add button then click OK.

The Power Query window opens again. In the Date column click the little icon on the right of the header and select Date.

That’s it!

Click the Close and Load button to create a combined table.

The final table is shown below.

This solution handles additional data added to the bottom of the each table.

I will look at automating splitting a table in a future post.

Download the files with the buttons below.

The Before File

 

The After File