How to Use Office Scripts to Split One Excel Table into Two Tables
Question details
The user needs to create an Office Script that reads an existing Excel table, divides its rows based on specific criteria (like department or category), and outputs the filtered data into two separate tables.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Managing large expense-claims tables by automating the process of categorizing and separating data into distinct, targeted tables for reporting.
- Observed behavior
- A single comprehensive data table needs to be programmatically split and reformatted into two separate, dynamically sized tables on different worksheet ranges.
Ensure that your workbook is saved to OneDrive or SharePoint, as Office Scripts require cloud storage to run, and verify the exact name of the source table you intend to split.
Write an Office Script to Filter and Split the Data
Use the Code Editor to extract the data, filter it by your target criteria, and generate two independent tables on separate worksheets.
Office Scripts use TypeScript to interact with Excel on the web. The logic involves reading the values of the source table, separating the header row, grouping the remaining rows based on your specified category, and outputting them into new destinations.
Navigate to the 'Automate' tab on the Excel ribbon and click 'New Script' to open the Code Editor panel.
Use workbook.getTable('YourTableName').getRange().getValues() to retrieve all rows from the source table. Extract the first row to save your headers.
Create two empty arrays. Loop through the data rows and use an 'if' statement to push rows into the respective arrays based on the specific department or category criteria.
Select the destination worksheets. Use the clear() method on the target ranges to delete any existing data, preventing old data from overlapping with the new output.
Write the saved headers and the filtered arrays into the new worksheet ranges. Finally, use workbook.addTable() to convert these dynamically populated ranges into formatted Excel tables.
Use WPS JS Macros to Automate Table Data Splitting
WPS Office features a powerful, locally executed JavaScript Macro (JSA) engine that allows you to easily read, filter, and split table data without requiring a cloud connection or specialized Microsoft 365 licenses.
- 1. Enable Developer Tools: Open your workbook in WPS Spreadsheet, go to the 'Tools' tab, and click on 'Developer' to access the macro environment.
- 2. Open the WPS Macro Editor: Click on 'WPS Macro' to launch the integrated JavaScript code editor.
- 3. Write the Split Logic: Write a JavaScript function to retrieve the used range, iterate through the rows, filter them by your criteria, and write them to target worksheets.
- 4. Run the Macro: Save your script and click 'Run'. WPS Spreadsheet will instantly process the logic and split the source table.
Frequently Asked Questions
Can I use VBA instead of Office Scripts to split a table?
Yes. VBA can easily achieve this using the AutoFilter method and Range.Copy to transfer filtered data to new worksheets. VBA is ideal for desktop versions of Excel and WPS Office.
Why do I need to clear the destination range before writing the new arrays?
If your new filtered dataset has fewer rows than the data previously stored in that destination, the leftover old data at the bottom won't be overwritten. Clearing the range first prevents data mixing.
Can Office Scripts run automatically when data changes?
Office Scripts cannot automatically trigger based on cell changes from within Excel itself. To run them automatically, you need to use Power Automate to schedule the script or trigger it based on specific workflow events.
