- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

How to split a master file into many excel workbook based on a column
Hi all,
I have a masterfile in excel. I want to split this file into many excel workbooks. As a sample, below is the table. I want to split this table into excel workbooks based on Column (Subject), so in this condition, I should get two workbooks as there are two different subjects in column (Subject).
Name | Subjects | Marks |
A | Maths | 12 |
A | Science | 23 |
B | Science | 21 |
B | Maths | 25 |
Thanks!
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Power Query is not the right tool for this as it doesn't produce different Excel files. You will have to do quite a manual work and save all files one by one after applying different filters in PQ.
Right tool for this is Excel itself if you want automated version.
I have written a VBA macro in which you need to select the column and it will generate those many files corresponding to distinct entries. Hence, if you run macro after selecting Subjects column or just selecting any cell in Subjects column, you will get two files.
Article 48 – Split A Workbook into Multiple Workbooks on the basis of a Column
If you are doing some transformations in PQ, do it and save the output in an Excel. Then run the above macro.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Power Query is not the right tool for this as it doesn't produce different Excel files. You will have to do quite a manual work and save all files one by one after applying different filters in PQ.
Right tool for this is Excel itself if you want automated version.
I have written a VBA macro in which you need to select the column and it will generate those many files corresponding to distinct entries. Hence, if you run macro after selecting Subjects column or just selecting any cell in Subjects column, you will get two files.
Article 48 – Split A Workbook into Multiple Workbooks on the basis of a Column
If you are doing some transformations in PQ, do it and save the output in an Excel. Then run the above macro.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hello! Does this code work the same on PC and MAC (I have a MAC)?
Also, can you explain a little bit more how to setup and run the Macro, the shortcut keys don't work on MAC. Is the same if I go to Tools>Macros>Record New Macros>(paste code)?
Thank you!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi!
I would be interested in the VBA Code, but the link provided doesn't work. It gets me to a general page where it presents different courses.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

The link is still not working. Can you provide a new one?
thanks

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
05-09-2024 09:04 AM | |||
05-07-2024 10:08 PM | |||
01-19-2024 01:01 PM | |||
05-26-2024 07:18 PM | |||
06-09-2024 11:42 AM |
User | Count |
---|---|
32 | |
19 | |
14 | |
11 | |
10 |