Power Automate: How to Create a Table in Excel and HTML [Step-by-Step]

Power Automate gives you two powerful ways to create tables — a structured Excel table inside a workbook stored in SharePoint or OneDrive, and an HTML table formatted for email delivery. These are entirely different actions that serve different purposes, and knowing when to use each is key to automating data-driven workflows effectively.

In this tutorial, you will learn exactly how to use both approaches with real, step-by-step examples — from creating a simple Excel table with defined columns to dynamically building an HTML table from a SharePoint list and styling it with CSS for professional email reports.

What You’ll Learn:

• How to create a named Excel table in a workbook using Power Automate
• How to create an HTML table from an array, a filter array, and a SharePoint list
• How to format an HTML table with CSS for email
• How to create a table in a specific worksheet with custom column names
• How to build a table dynamically from a JSON array
• How to create a table with a dynamic range when columns are unknown at design time

Quick Reference: Which Action Should You Use?

Before You Start — Prerequisites

Before building any of these flows, make sure the following are in place:

• Excel file location: Power Automate’s Excel Online (Business) connector requires the file to be stored in SharePoint or OneDrive for Business. Files on your local machine or personal OneDrive will not work.
• Named Excel Tables: The “Add a row into a table” action only works with formal named Excel Tables — not plain cell ranges. Always create the table structure first (either manually or via the “Create table” action in Power Automate).
• License: A Power Automate license is required. This is included in most Microsoft 365 business plans.
• Permissions: You need read/write access to the SharePoint site or OneDrive folder where the Excel file is stored.

How to Create a Table in Excel Using Power Automate

This is one of the most common automation scenarios — you have an Excel file with raw data in a cell range, and you want to convert it into a formal, structured Excel Table so flows can work with it reliably.

Scenario

You have a product list saved in an Excel file in SharePoint. The data is laid out in rows and columns (A1:E6), but is not yet a named Excel Table. You want Power Automate to create a formal table from this range.

Steps

Step 1: Open Power Automate and create a new flow. Select Manually trigger a flow as the trigger. Click New step.

Step 2: Search for and select the Create table action from the Excel Online (Business) connector.

Step 3: Configure the action with these settings:

• Location: Choose SharePoint or OneDrive for Business
• Document Library: Select the library where the file is saved
• File: Browse to and select your Excel file
• Table Range: Enter the range in this format: SheetName!A1:E6 — for example, Product!A1:E6
• Table Name: Enter a descriptive name, such as ProductTable
• Columns: Leave blank if your first row already contains headers. Power Automate will use row 1 as column headers automatically.

⚠️ Important:

If you enter custom column names in the Columns field, they will overwrite your existing header row. Only fill in the Columns field if you’re creating a table in an empty range and want to define the headers.

Step 4: Click Save, then run the flow manually. Open the Excel file in SharePoint, and you will see the range is now a formal, named Excel Table with filter dropdowns on each column header.

How to Create an HTML Table from an Array in Power Automate

HTML tables are ideal when you want to display structured data inside an email body. This example starts with a JSON array stored in a variable and converts it into a formatted HTML table.

Scenario

You have employee data stored as a JSON array, and you want to display it in a weekly report email with columns for Name, Age, and Department.

Steps

Step 1: Create a new flow with a Manually trigger a flow trigger.

Step 2: Add an Initialize variable action:

• Name: varArray
• Type: Array
• Value: Paste the following array:

[
  { "Name": "Alex", "Age": 28, "Department": "Finance" },
  { "Name": "Michel", "Age": 38, "Department": "Development" },
  { "Name": "Celia", "Age": 30, "Department": "Testing" },
  { "Name": "Ruby", "Age": 32, "Department": "Finance" }
]

Step 3: Add a Parse JSON action:

• Content: Select varArray from dynamic content
• Schema: Click Generate from sample, paste the array above, and click Done. This generates the schema automatically.

Step 4: Add a Select action. This step lets you control exactly which columns appear in the HTML table and rename them:

• From: Select the Body output of Parse JSON
• Map: Switch to text mode and enter each key-value pair:
  • Key: Employee Name → Value: Name (from dynamic content)
  • Key: Age → Value: Age
  • Key: Department → Value: Department

💡 Pro Tip: Use the Select action to rename columns (e.g., “Name” → “Employee Name”) or reorder them before passing data to Create HTML table. This gives you full control over what appears in the final table without modifying the original data.

Step 5: Add a Create HTML table action:

• From: Select the Output of the Select action

Step 6: Save and run the flow. In the run history, expand the Create HTML table step. You will see the generated HTML output, which you can copy into an email Send action.

How to Create an HTML Table from a Filter Array in Power Automate

This example is useful when you need to filter data from one source based on values in another before displaying it in an HTML table — a common pattern in reporting flows.

Scenario

You have two Excel sheets: a Products sheet with product name, category, unit price, and stock, and a Category sheet with a list of approved categories. You want to show only products whose category matches one of the approved categories.

Steps

Step 1: Create a flow with a Manually trigger a flow trigger.

Step 2: Add a List rows present in a table action to retrieve data from the Products sheet. Provide the Location, Document Library, File, and Table name.

Step 3: Add a second List rows present in a table action to retrieve data from the Category sheet. This gives you the approved category list.

Step 4: Add a Filter array action:

• From: Select the value output from the Products table action
• Left side: Select Category from dynamic content (this will auto-wrap in an Apply to each loop)
• Operator: is equal to
• Right side: Select Category from the Category table output

Step 5: Inside the Apply to each loop, add a Select action:

• From: Select the Body of the Filter array action
• Map: Switch to text mode and enter:

{
  "Product Name": "@item()?['Product name']",
  "Category": "@item()?['Category']",
  "Unit Price": "@item()?['Unit Price']"
}

Step 6: Add a Create HTML table action with the Output of Select as the source.

Step 7: Save and run the flow. The result is one HTML table per approved category, showing only products in that category.

How to Create a Table in a Specific Worksheet Using Power Automate

Sometimes you need to programmatically create a new worksheet and immediately build a table inside it — useful for generating monthly report tabs in a shared Excel workbook.

Scenario

You have a shared Excel workbook in SharePoint. Each month, a new worksheet should be created for a region, and a structured table should be created immediately within it.

Steps

Step 1: Create a flow with a Manually trigger a flow trigger.

Step 2: Add a Create Worksheet action from Excel Online (Business):

• Location: SharePoint
• Document Library: Your library
• File: Your Excel workbook
• Name: Enter the worksheet name, e.g., Customer

Step 3: Add a Create table action:

• Location, Document Library, File: Same as above
• Table Range: Customer!A1:C1 — this creates a table with 3 columns in the new worksheet
• Table Name: CustomerDataQ1
• Columns: First Name;Last Name;Email — column names separated by semicolons

💡 Pro Tip: The worksheet name in the Table Range must exactly match the Name you provided in the Create Worksheet step. A mismatch will cause the flow to fail with a “worksheet not found” error.

Step 4: Save and run the flow. Open the Excel workbook in SharePoint, and you’ll see a new worksheet tab with a formatted table ready for data entry.

How to Create a Table with Custom Column Names in Power Automate

When creating a table in an empty range, you must define the column headers explicitly. This is commonly used when setting up a tracking spreadsheet from scratch as part of a larger automation.

Scenario

You need to create a regional sales tracking table in an existing worksheet called Region, with columns for Address, Region, City, Country, and Pincode.

Steps

Step 1: Create a flow with a Manually trigger a flow trigger.

Step 2: Add a Create table action from Excel Online (Business):

• Location: SharePoint
• Document Library: Your library
• File: Your Excel file (e.g., SalesTracker.xlsx)
• Table Range: Region!A1:E1 — this defines 5 columns (A through E) in the Region worksheet
• Table Name: Region
• Columns: Address;Region;City;Country;Pincode — enter these semicolon-separated

Step 3: Save and run the flow. The result is a properly structured Excel table with five labeled columns, ready for “Add a row into a table” actions in subsequent steps.

⚠️ Important: The number of columns you define in the Columns field must match the number of columns in your Table Range. If your range is A1:E1 (5 columns), you must provide exactly 5 column names.

How to Create a Table with Dynamic Range in Power Automate

This is the most advanced Excel table scenario and one that AI-generated content rarely covers accurately. Use this pattern when you don’t know the number of columns at design time — for example, when processing arrays with variable structures.

Scenario

You receive employee data as a JSON array, but the number of fields (columns) may change over time. You want Power Automate to automatically calculate the correct table range and column names, no matter how many fields the array contains.

Steps

Step 1: Create a flow with a Manually trigger a flow trigger.

Step 2: Add an Initialize variable action:

• Name: varData
• Type: Array
• Value: Your JSON array (e.g., the employee array used in the HTML Table example)

Step 3: Add a Parse JSON action with the array variable as Content. Generate the schema from the sample.

Step 4: Add a Select action — map the properties to custom column headers as needed.

Step 5: Add a Create CSV table action:

• From: Use this expression in the From field: first(body('Select'))
• This produces a single-row CSV that contains only your column headers.

Step 6: Add a Compose action to extract column names as an array. In the Input field, enter this expression:

split(first(split(body('Create_CSV_table'),decodeUriComponent('%0D%0A'))),',')

This first splits the CSV header row on line breaks, then on commas, yielding an array of column names.

Step 7: Add another Compose action to create an alphabet mapping for column letters. In Input, enter:

split('A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z',',')

Name this action A1_Notation_Mapping.

Step 8: Add a Create table action:

• Location, Document Library, File: Your Excel file in SharePoint
• Table Range: Use this expression to build the range dynamically: Start: Customer!A1: End column (dynamic):textoutputs('A1_Notation_Mapping')[sub(length(outputs('Array_of_col')),1)]Append 1 at the end, so the full range expression resolves to something like Customer!A1:C1 for 3 columns.
• Columns (dynamic): Use this expression to pass the column names: first(split(body(‘Create_CSV_table’),decodeUriComponent(‘%0D%0A’)))

Step 9: Save and run. The flow calculates the correct column range and names automatically, regardless of how many fields are in the array.

💡 When to Use This Pattern: Use dynamic range only when your data structure is truly variable. For fixed structures, hardcode the range (e.g., Sheet1!A1:D1) — it’s simpler and easier to maintain.

How to Format an HTML Table with CSS in Power Automate

The default HTML table output from the Power Automate Create HTML table action is functional but plain. You can apply CSS styling to add colors, borders, and fonts — making it look like a professional report when sent as an email.

Scenario

You want to pull open tasks from a SharePoint list called Project Management and email them as a formatted, color-branded HTML table every Monday morning.

Steps

Step 1: Create a flow with a Recurrence trigger set to run weekly on Mondays at 8:00 AM.

Step 2: Add a Get items action from SharePoint:

• Site Address: Your SharePoint site URL
• List Name: Project Management

Step 3: Add a Create HTML table action:

• From: Select the value output from Get items
• Click Show advanced options, change Columns from Automatic to Custom
• Map only the columns you want to display: Task Name, Assigned To, Due Date, Status

Step 4: Add a Compose action. This is where you wrap the table with a <style> block. In the Input field, combine the CSS and table output like this:

<style>
  table {
    font-family: Arial, Helvetica, sans-serif;
    border-collapse: collapse;
    width: 100%;
  }
  td, th {
    border: 1px solid #dddddd;
    padding: 8px 12px;
    text-align: left;
  }
  th {
    background-color: #0078D4;
    color: white;
    font-size: 14px;
  }
  tr:nth-child(even) {
    background-color: #f2f2f2;
  }
</style>

Then, append the dynamic content output of the Create HTML table action directly after the closing </style> tag.

⚠️ Outlook CSS Warning: Many email clients, including desktop Outlook, strip <style> tags from the <head> of an HTML email. To ensure consistent rendering, embed your CSS as inline styles directly on each element: <table style="font-family:Arial; border-collapse:collapse; width:100%;">. The Compose-based approach above works well with Outlook 365 web client, but if you’re targeting desktop Outlook clients, use inline styles in the HTML table header fields instead.

Step 5: Add a Send an email (V2) action from Outlook:

• To: Your manager or distribution list
• Subject: Weekly Project Status Report – @{formatDateTime(utcNow(),'dddd, MMMM d')}
• Body: Select the Output of the Compose action. Switch the body to HTML mode using the </> toggle.
• Enable Is HTML: Yes

Step 6: Save and run the flow. You’ll receive a formatted, color-coded table in your inbox.

How to Create a Table from a JSON Array in Power Automate

This pattern combines table creation and data population in a single flow — you define the structure, create the table, and immediately populate it with rows from a JSON array.

Scenario

Your HR system exports employee records as a JSON payload. You want to log these records into a new Excel table in SharePoint every time the flow runs.

Steps

Step 1: Create a flow with a Manually trigger a flow trigger.

Step 2: Add an Initialize variable action with an array of employee records:

[
  { "Name": "Alex", "Age": 28, "Department": "Finance" },
  { "Name": "Michel", "Age": 38, "Department": "Development" },
  { "Name": "Celia", "Age": 30, "Department": "Testing" },
  { "Name": "Ruby", "Age": 32, "Department": "Finance" }
]

Step 3: Add a Parse JSON action. Content = the variable. Generate the schema from the sample array.

Step 4: Add a Create table action from Excel Online (Business):

• Table Range: EmployeeData!A1:C1 — this defines 3 columns (Name, Age, Department) in a worksheet called EmployeeData
• Table Name: HRRecordsTable
• Columns: Name;Age;Department

⚠️ Important: The range $A1:$C1 tells Power Automate this table has exactly 3 columns. If your JSON has 4 fields, update the range to $A1:$D1 and add the 4th column name.

Step 5: Add an Apply to each action. The input is the Body output of Parse JSON.

Step 6: Inside Apply to each, add a Compose action to build each row. Map it:

{
  "Name": "@items('Apply_to_each')?['Name']",
  "Age": "@items('Apply_to_each')?['Age']",
  "Department": "@items('Apply_to_each')?['Department']"
}

Step 7: Add an Add a row into a table action inside Apply to each:

• Location, Document Library, File: Your Excel file
• Table: Select HRRecordsTable from dynamic content
• Row: Select the Output of the Compose action

Step 8: Save and run the flow. The Excel table is created and populated with all employee records in a single run.

Common Errors and How to Fix Them

This section addresses the real-world issues you’ll encounter — content that no AI overview can replicate from your first-hand experience.

Decision Guide: Excel Table or HTML Table?

Use this to quickly decide which approach fits your use case:

Do you need to SAVE the data permanently?
  ├── YES → Use Create table (Excel Online) + Add a row into a table
  └── NO → Do you need to DISPLAY the data in an email?
              ├── YES → Use Create HTML table (Data Operations)
              └── YES, with formatting → Use Create HTML table + Compose (CSS)

Summary of Expressions Used in This Tutorial

Also, you may like some more Power Automate tutorials:

• Power Automate Create an Array from JSON Objects
• Parse JSON Object to Array in Power Automate
• Convert String to Date in Power Automate
• Fix “The specified object was not found in the store” in Power Automate

Conclusion

In this Power Automate tutorial, you learned how to create tables in two completely different ways — structured Excel tables stored in SharePoint or OneDrive, and HTML tables formatted for email delivery.

The key takeaway is to always match your approach to your goal — use Excel tables when you need to store and reference data in future flows, and use HTML tables when you need to display data in a formatted email.

If you have any questions about any of the examples above, feel free to drop them in the comments section below — I’m happy to help.

Bijay Kumar

After working for more than 18 years in Microsoft technologies like SharePoint, Microsoft 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Our audiences are from the United States, Canada, the United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a Microsoft SharePoint MVP (12 times). I have also worked in companies like HP, TCS, KPIT, etc.