Split Excel Worksheet

How to split Excel worksheets to separate workbooks or tabs? Our Excel data processing series continues by introducing a new Excel tool. It can be helpful to speed up your data cleansing project before creating your first complex dashboard.

We strongly believe that today’s presentation will bring data processing professionals again one step closer to success.

The cleansing, merging, and grouping of data frequently causes difficult moments, but there is always a practical solution!

We don’t want to cut up in clichés, but time is scary, and the workload is large! So, keeping this in mind, let’s start today’s lesson, and by the end of the day, it will be child’s play for you to handle a large amount of data.

In the next paragraph, we introduce our table containing the base data.

Split Excel Worksheet – Data Table

split-excel-worksheet-featured-01-table

We will present the problem by an often occurring structure and the elegant solution to that problem.

Since last week, we combined the text manipulation tool and the Excel worksheet splitter into one add-in.

You can see a picture of this here. At this stage of development, you can already use two advanced Excel data processing modules using only one program.

We can see from experience that a sales report will contain these same data in 99% of the cases.

How to split the selected data range?

split-excel-worksheet-featured-02-select-data-range

Let’s see how we might partition this Excel table based on an arbitrarily chosen column.

In the first step, we highlight the range we would like to partition.

The importance is that you do not highlight the whole Excel worksheet because we only need those cells that contain real data (values).

Define the split key column

split-excel-worksheet-featured-03-add-a-split-column

You have to pay the most attention to the second step! When we define the split key column, we have to enter that column which will be the base for classifying the data in the Excel worksheet.

If we mark column “B,” then the sales from the same region will be put into a separate table.

Following the same logic, if we mark the Item column, we will divide the sales data based on the sold products.

As a manager, we want to see the sales performance of the representatives, and we’d like to examine every representative’s performance separately.

Split Data into Tabs

We can choose from two different possibilities. We’ll store the desired data in the worksheet we are currently working with with the first one.

The source data will remain, and the table will be supplemented with the grouped data.

In this picture, we have circled the checkbox in question. You can choose this one when you would like to work with worksheets inside one workbook.

split-excel-worksheet-featured-04-split-tab

You can see the result of the data processing. Because we’ve done the grouping based on column C, the name of every single sales representative got on a new tab.

Look at the name of the worksheet tab, which is “Smith”.

In the data table created just now, we can only and exclusively see the sales data.

Ergo we have reached our goal.

In the following, let’s examine that case when we do not want to store the data in the source workbook.

split-excel-worksheet-featured-05-split-tab2

Separate Data into Multiple Worksheets

split-excel-worksheet-featured-06-split-worksheets

Let’s return a little bit to the previous step!

The business needs now will be the following: based on the Excel sales database, choose the sales representatives and save the report displaying the performance of every single representative into an individual workbook.

So at first, this doesn’t sound very simple.

In this article, we wouldn’t like to waste your time with the secrets of VBA programming. There is an appropriate forum for this.

It is enough to know that there is a solution available with only a few clicks. Now, mark the “create new files…” menu in the checkbox.

split-excel-worksheet-featured-07-splitted-file-list

The structure seen here can be done in seconds by clicking on the Split button located at the bottom of the userform.

Looks good.

Split Excel Worksheet – Convert Options

We haven’t talked about one thing yet! This is a trifle but can improve the final result considerably.

Before you click the Split button, you can make some adjustments.

As a supplement, you can name each table, so next to the name of the sales representatives, we can automatically subjoin the comment SalesReport.

The previous picture can be seen as the file name extended by the names of the sales representatives.

split-excel-worksheet-featured-08-output-formatting

There is a possibility to keep the source format and the formulas by marking the appropriate checkboxes.

Final Thoughts

How to split an Excel worksheet? Maybe some people would say that there is nothing extra in the introduced Excel tips, and the work is done in a few seconds.

All right! You can see why Excel productivity tools are so popular. Try to think out of the box!

Try this free Excel spreadsheet VBA tool, and don’t forget that you have to install the add-in. It cannot be started by direct running. Download the free add-in!