Are you trying to create a new Excel table during your flow?

Tables are the default when reading and writing data to an Excel file with Power Automate. Usually, the table will already exist in your workbook but it might be the case you need to create a table during the flow.

In this article, you’ll learn how to create new worksheets and tables within your files.

Create a New Worksheet with the Create Worksheet Action

Before creating your new table, it’s a good idea to create a new worksheet specifically for the table.

This way you won’t need to worry about the flow failing due to an incorrect sheet name reference or placing the table in a location that already contains data.

Here’s how you can create a new sheet with Power Automate.

1. Add the Create worksheet action from the Excel connector to your flow.
2. Select the SharePoint site that contains your Excel file from the Location dropdown.
3. Select the Document Library that contains your Excel file.
4. Select the File to which you want to add your worksheet with the file picker menu.
5. Add the Name of the new sheet to add. This can be a value or you can use dynamic content.

💡 Tip: You can leave the Name field empty and this will use the default names of Sheet1, Sheet2, Sheet3, etc. This can be useful to avoid conflicts with existing sheet names.

This action will add a new blank worksheet to your workbook. You can then reference this new sheet later in your flow with dynamic content.

Create a Table with Power Automate

Now that you have a new sheet in your workbook, you won’t have to worry about where you place the table.

Here’s how to create the table within the worksheet.

1. Add the Create table action to your flow.
2. Select the same Location for your file as the previous Create worksheet action.
3. Select the same Document Library for your file as the previous section.
4. Select the same File as the previous section.
5. Specify the Table range for where you would like to place the new table. This should be in the format 'Sheet Name'!A1 with the sheet name in single quotation marks, followed by an exclamation mark and the row letter and column number. You only need to reference the top left cell of the table location.

For example, if you want to reference the name from the Create workbook action, you can use '@{outputs('Create_worksheet')?['body/name']}'!B2 for the Table range reference.

6. Give the table a Table name. This will be what you can use later in the flow to reference the table if you want to add data to your new table. If you leave this field blank, it will create tables with the default table names Table1, Table2, Table3, etc. This can be useful to avoid conflict with existing table names in the file.
7. Add the comma or semicolon separated list of column headings to the Columns names input to create the new table. For example, Name;Age;Email will create a table with 3 columns named Name, Age, and Email. If you leave this field empty, it will create a table with one column named Column1.

Now you should have a new empty table in your new worksheet.

Once the table is created, you can insert, update, or modify the data within it using various Power Automate actions such as Add a row into a table or Update a row actions depending on your required outcome.

Conclusions

This article showed you how to create an Excel table in a worksheet with Power Automate.

You can use the Create Worksheet action and the Create Table action to create your new table without worrying about errors caused by referencing sheets that don’t exist or cells that already contain data.

Have you used Power Automate to create Excel tables on the fly? What were you trying to do with them? Let me know in the comments!