In many organisations, finance teams prepare a consolidated Excel workbook containing multiple worksheets, generally one for each department. While this centralised approach is efficient for data entry and analysis, it creates challenges when distributing data to different departments.
For example, least-privilege access may be required so each department only sees its own figures. Manually splitting workbooks into multiple files is time-consuming, error-prone, and unsustainable at scale.
In this tutorial, we’ll demonstrate how to use the Excel – Extract Worksheets Flowr Action in Power Automate to automatically split a workbook into individual departmental files. Our working example uses a file named Budget.xlsx containing worksheets for Sales, Marketing, IT, Legal, and HR.
By the end, you’ll know how to:
Splitting Excel worksheets into separate files helps organisations:
Consider this scenario: the Finance department uploads Budget.xlsx into the Finance Files folder in SharePoint. A Power Automate workflow triggers, loops through each department, extracts the relevant worksheet, and creates a new Excel file for each department.
The Budget.xlsx file contains the following sheets:
We will build a Power Automate workflow using Encodian Flowr’s Excel – Extract Worksheets action. The flow will automatically split worksheets into individual Excel files when a new workbook is uploaded.
1.a. Flow Name: Provide a name for your flow.
1.b. Trigger: Select the ‘When a file is created (properties only)’ SharePoint trigger.
1.c. Click ‘Create’.
2.a. Click the ‘Site Address’ and select the SharePoint Site where the Budget.xlsx will be uploaded.
2.b. Click the ‘Library Name’ to select the Document Library of the folder you want to drop the Excel file to.
2.c. Click on the ‘Show All’ button then click the folder icon to select which folder the workflow will monitor.
3.a. Configure the ‘Site Address’ as the same Site Address as the trigger.
3.b. Next is to configure the ‘File Identifier’ property. Type ‘/’ or click the lightning icon to insert a ‘dynamic content’.
3.c. In the ‘dynamic content’ field, type ‘Identifier’ and select the ‘Identifier’ dynamic value.
4.a. Add a name for the variable, for example ‘Departments’, then select the type ‘Array’.
4.b. In the ‘Value’ property, add an array based on the names of your ‘Sheets’ in your Budget.xlsx file which is formatted like this: [ “Sales”, “Marketing”, “IT”, “Legal”, “HR” ]
6.a. Configure the ‘File Content’ property by inserting a dynamic value and selecting ‘File Content’ under the ‘Get file content’ action.
6.b. Insert a dynamic value in the ‘Worksheet Names’ property by selecting ‘Current item’ under the ‘Apply to each’ loop.
7.a. Configure the ‘Site Address’ and the ‘Folder path’ where you want to save the resulting Excel files.
7.b. Under the File Name property, set up a dynamic file name. In this example, “Budget_(Department).xlsx” where ‘Department’ is the dynamic value from the array variable.
7.c. To achieve this, type “Budget_” in the File Name property then add a dynamic value ‘Current item’.
7.d. After this, add the file extension .xlsx at the end of the file name. It should look like this:
7.e. Lastly, configure the ‘File Content’ property by selecting the dynamic value ‘File Content’ under the ‘Excel – Extract Worksheets’ Encodian action.
When you upload Budget.xlsx, the workflow automatically generates new Excel files, one for each worksheet:
Check out our companion video on YouTube.
Q: How do I split Excel worksheets into separate files in Power Automate?
A: Use the Encodian Flowr Excel – Extract Worksheets action to automatically create a new file for each worksheet.
Q: Can I restrict access to only certain departments?
A: Yes. Each generated file can be saved into department-specific folders with permissions applied.
Q: Does this support dynamic worksheet names?
A: Yes. By extracting worksheet names programmatically, you can handle variable inputs.
Q: What file types are supported?
A: Excel files (.xlsx). Other Flowr actions support PDF, Word, PowerPoint, and more.
With the Encodian Flowr’s Excel – Extract Worksheets action, splitting Excel workbooks into departmental files becomes fully automated. This approach saves time, reduces errors, and ensures secure distribution of sensitive data.
Search 200+ Actions to see how Flowr can save you time
Sign up for your free 30-day trial; no cards, catches, or contracts.
CEO
