Dynamically Create Excel Table with Power Automate

In this tutorial-style video, Jonathon Silva of Pragmatic Works demonstrates how to use Power Automate to dynamically convert a worksheet range into a formatted Excel table stored in SharePoint or OneDrive. Jonathon walks viewers step-by-step through a real-world scenario: a worksheet arrives as a plain range and must automatically become a proper Excel table whenever it is added or modified.

Why automate table creation?

Jonathon explains the business value: tables in Excel unlock filtering, structured references, and downstream automation capabilities. Automating table creation removes manual effort and ensures consistency when files are uploaded or changed by different users or integration processes.

Key prerequisites and setup

• Have the Excel file stored in SharePoint or OneDrive so Power Automate can access it.
• Know the SharePoint site address, document library, and folder where files appear.
• Ensure the worksheet name is identifiable — if it contains spaces, it will need single quotes in formulas.
• Be aware of the number of columns in the target range, because the offset formula requires that value.

Step-by-step flow Jonathon demonstrates

1. Trigger: Jonathon starts with a SharePoint trigger — “when an item or file is modified” — but notes this could equally be a “when a file is created” trigger depending on the scenario.
2. Select the file dynamically: He emphasizes using the trigger’s dynamic content for File Name with Extension so the flow points to any file that meets the trigger rather than a single hard-coded file.
3. Add the Excel action: Choose Excel Online (Business) and the Create table action, then map the location, document library, and file using the dynamic file name output.
4. Define the table range using OFFSET: The core technique is to use Excel’s OFFSET formula to point to the dynamic range. Jonathon pastes an OFFSET expression that references the worksheet and calculates the range from the header through the last row of data.
5. Adjust column count: The final, crucial number in the OFFSET formula is the number of columns. Jonathon demonstrates changing that value to match the actual worksheet columns (for example, from a default 10 to the actual 3 columns in his sample).
6. Optional table name and column headers: You may supply a table name or leave it blank. If column headers do not already exist in the worksheet, Power Automate can add them — in Jonathon’s case, the sheet already contains headers so he leaves the column names field blank.
7. Test the flow: He shows a manual test: renaming the file to simulate modification, then running the flow and confirming it completes successfully and creates a proper Excel table with filtering enabled.

Tips and troubleshooting

• Always verify the SharePoint path and file mapping — an incorrect path will cause the action to fail.
• Double-check the worksheet name in the OFFSET expression; include single quotes around names with spaces.
• Confirm the number of columns in your range. A wrong column count will mis-shape the table range.
• If the worksheet structure changes frequently, consider pre-processing steps to detect the current column count dynamically.

Outcome and benefits

After running the flow, Jonathon shows the newly created table in Excel with filters and a proper table range. This enables users to immediately work with structured data and lets later automations rely on consistent table objects rather than unstructured ranges. The method works whether files are stored in SharePoint or OneDrive, so long as Power Automate can access the location.

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Power Automate and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.