How do i split an excel spreadsheet into multiple worksheets without vba?

Sometimes you need to separate data into different worksheets from the table based on some condition over data in the columns.

For e.g.

1. You need to print the sales report monthly, quarterly and get it signed.
2. You need separate sales report of all branches to analyse the same or to send to the branches.
3. You may need to bifurcate the cost of production of different units based on unit column to analyse efficiently

In all the above cases, when your database is too large, it would be cumbersome to manually bifurcate the table data into different sheets. Probably it would consume lots of your valuable time and efforts.

What if you can separate the table data into multiple sheets by just one click of Pivot table? Amazing right?? Let me show you step by step with images.

I would recommended to read little about Pivot tables to newbie. If you know the basics about pivot table, you may skip the next para.

What is Pivot tables?

Pivot table is a powerful excel tool to analyses, summarize and present large data in a way which creates ease in analyzing and drawing conclusion. Pivot table allows you to summarize data into excel, based on selected rows and columns. How to create a pivot table?

Recommended Blogs:

Accelerate Your Analysis with Pivot Table
How to convert dates into months/ quarters/ years in pivot table
How to Change Pivot table Report Filter?
What is Pivot table slicer and how to use it effectively?

Generating Individual Worksheets based on Columns

So Let’s start generating individual sheets.

For better understanding of the same, download the sales register file and follow gradually to get generate sheets.

Generate Individual sheets practice register

Here is the Sales Register containing columns of date, branch, customer Name, Product sold, quantity, rate and sales amount.

1. Insert a Pivot table.
2. Take the column into Filter Field of pivot table based on which you want to separate worksheet. In our example, let’s take Branch as our filter field.
   
3. Now take all data into rows or values field based on your requirement. Here you can even select limited data or full data. (In our example, we are taking all the columns into Rows field to have the same table)
4. Go to Pivot table tools >> Analyse >> Options >> Show Report Filter Pages
   
5. Select the column for which you want to generate individual sheets. Here, we will get only one columns as we have put only one column in Report filter.
   
   
   (Note : Don’t forget to take backup of the file at this step as you may need to change the columns and rows later on)
   
6. Press Ok and done!!
   
   
   It’s done…You will get sales made by all the branches in different worksheets.

Here is the final file, in case you have some confusion. Generate indivual Sheet based on coulums file

Hope so, you loved the post. Stay Connected to Learn more about innovative ways to present or analyse data into excel with cool tricks.

Don’t forget to share and subscribe our blog via email, if you loved post.

Have any suggestion/ recommendation? Leave a comment.

How do you split data into multiple worksheets based on column in Excel without VBA?

How do you split data into multiple worksheets by rows count in Excel?

How do I split an Excel spreadsheet into two workbooks based on columns?

How do you sort data into sub sheets?