How to split Excel worksheet to separate workbooks or tabs? Our Excel data processing series continues with the introduction of a new Excel tool. It can be useful if you want to speed up your data cleansing project before create 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 effective solution!
We don’t want to get cut up in clichés but time is scares and the workload is large! 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 large amount of data.
In the next paragraph we shortly introduce our table containing the base data.
Table of Contents
- 1 Split Excel Worksheet – Data Table
- 2 Select Data Range to split Worksheet
Split Excel Worksheet – Data Table
We will present the problem by an often occurring structure and after this the elegant solution of that problem.
Since last week we have combined the text manipulation tool and the just seen 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 module with a use of only one program.
We can see from experience that a sales report will contain these same exact data in 99% of the cases.
Select Data Range to split Worksheet
Let’s see how we might partition this Excel table based on an arbitrarily chosen column.
In the first step we highlight the range that 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).
Split Excel Worksheet – Add a split key 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 the classification of the data of 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 then we will divide the sales data based on the sold products.
Let’s say that as a company manager we want to see the sales performance of the representatives and we’d like to examine every single representative’s performance separately.
Split Excel Worksheet – Split Data into Tabs
We can chose from two different possibilities. With the first one we’ll store the chosen data in the worksheet that we are currently working with.
The source data will remain and the table will be supplemented with the grouped data.
On this picture we have circled the checkbox in question. You can choose this one when you would like to work with worksheets that are inside one workbook.
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.
The pertinence of the calculation can be easily checked.
Look at the name of the worksheet tab which in this case is “Smith”.
In the data table created just now we can only and exclusively see the sales data belonging to him.
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 – Split Data to separated workbooks
Let’s return a little bit to the previous step!
The business need now will be the following: based on the Excel sales database chose 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 and it’s available with only a few clicks. In the checkbox now mark the “create new files…” menu.
By clicking on the Split button located at the bottom of the userform the structure seen here can be done in seconds.
Looks good, doesn’t it?
Split Excel Worksheet – Convert Options
We haven’t talked about one thing yet! This is trifle but can improve the final result considerably.
Before you click the Split button you have the opportunity to 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.
On the previous picture can be seen the file name extended by the names of the sales representatives.
There is a possibility to keep the format of the source and the formulas by marking the appropriate checkboxes.
Split Excel Worksheet – Summary
How to split an Excel worksheet? Maybe there are some people who would say to this that there is nothing extra in the just introduced Excel tips and the work is done in a few seconds.
All right! And what if the sales database contains the data of about 10 years and the participating number of representatives over 500? It is not surprising that the Excel productivity tools are so popular!
In the case of a business analyst or a CEO time can save life with the think out of the box point of view!
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!