How to Split Excel Documents into Separate Workbooks in Power Automate
Any given Excel workbook can contain numerous worksheets — but not all of those are necessarily relevant at the same time. Carrying extra worksheets in our workbook increases the size of our Excel file, making it clunkier and more burdensome to share across our network.
Sometimes, it’s best to split our Excel documents into separate, unique workbooks so we can work with each spreadsheet subtopic individually.
Conveniently, we can automate the process of splitting up Excel documents without writing any code. Using the Cloudmersive Document Conversion connector in Power Automate, we can programmatically divide our Excel documents into separate workbooks, creating exactly one new workbook per worksheet from the original document.
We’ll walk through a quick example instant cloud flow to see how this works.
We’ll begin by using a Get file content action to grab a multi-worksheet Excel document from our file system. The file I’m using is a fake employee expense report containing two separate worksheets of expenses.
Next, we’ll add a new action and type “Cloudmersive” into the search bar. This brings up a list of Cloudmersive connectors, and from here, we’ll look for the Document Conversion connector with the green logo.
We’ll click “see more” to view the actions list, and from there, we’ll CTRL+F search for an action called Split a single Excel XLSX into Separate Worksheets.
We’ll notice this (and other) actions are labeled as “premium.” We will need a premium Power Automate license (i.e., not a basic license) to use Cloudmersive connectors, but as long as we have that, we can use them at no additional cost with a free API key. These allow a limit of 800 API calls per month with zero additional commitments.
After we’ve created and authorize our connection, we’ll add our file content and file name to our two Split XLSX request parameters.
If we click “show all” to view the advanced parameters, we’ll find the option to “return the contents of each worksheet directly” by setting the dropdown to “yes.” The distinction here is between returning XLSX file bytes and returning a temporary editing URL (which we can use to perform subsequent document edits using other Cloudmersive Document Conversion connector actions).
We’ll elect to return contents directly.
In the final step of our flow, we’ll add a Create file action to generate new Excel documents with our individual worksheet file bytes.
As shown in my example below, we’ll have the option use the original worksheet names and worksheet numbers (i.e., worksheet indices) to generate appropriate names for each respective workbook we create. Since we elected the option to “return the contents of each worksheet directly”, we’ll be able to create our file with XLSX file bytes using the value WorksheetContents. Power Automate automatically wraps our action in a For each control to cycle through values for each worksheet.
When we save and run our flow, we’ll create one new Excel workbook per worksheet contained in our original document, each named appropriately with dynamic response content.
That’s all there is to it! We’ve instantly generated new Excel documents and dynamically labeled them with information from the original workbook.
