Power Automate & Excel: Examples And Use Cases

Formerly known as Microsoft Flow, Power Automate unlocks a range of features and tools that help you automate any business process, from running tasks on a schedule to sending automatic reminders for past deadlines. If you’re subscribed to Microsoft 365, then you already have access to all Power Automate tools.

In this article, you’ll see how Power Automate works with Excel, learn how to create an Excel file, and get data from this file automatically. We’ll also provide an alternative solution to overcome Power Automate limitations when combined with Excel.

Does Power Automate Work With Excel?

Power Automate is a service included in the Microsoft 365 subscription, which helps you automate a great variety of business processes and workflows between all purchased apps, including Excel. Creating your first flow will be easy since you’ll find the most common flows already available in the Power Automate Template Library. However, if you’re using the free Office version, then you’ll find that some features are missing.

Let’s explore how to access Power Automate and how to create an Excel file automatically.

How to use Power Automate with Excel?

To access Power Automate in a few simple steps, this is what you need to do:

1. 1. Log in to your Office 365 account and launch.
2. 2. Type in “Power Automate” in the search bar for quicker access.

1. 3. You should be able to see a similar view as shown below.

Now that you’ve seen how quickly it is to access it, let’s see how you can use it to get data from an Excel file.

How to use Power Automate to get data from Excel?

1. 1. Click on “Create”, on the left-side menu. Then, “Instant cloud flow” under the “Start from blank” menu.

1. 2. Select “Manually trigger a flow” from the trigger options.

1. 3. Click on “Next step” to set up the first trigger.

1. 4. Choose the application you would like to connect to. In this case, you’ll see that “Excel Online” shows up among the first options. Click it to include it.

1. 5. Now, you should see a list of “Actions” that the flow should perform. Type “Get” in the search bar, and it will prompt all actions that entail this action verb. Here, I will select “Get worksheets”.

1. 6. Now, choose the location of your Excel data. Here, I will choose the SharePoint site you created previously, “SharePoint Site Finance Dep.”.

1. 7. Make sure you saved your Excel file in the site’s “SharePoint Document Library”. If not, it won’t appear among the options below. Select “Documents”, then select the file to get data from.

1. 8. Click “Save” to finish creating the flow. You’ll see how Power Automate recommends testing the flow. To test, simply click on the “Test” button to the far right.

1. 9. You can click on “Review connections and actions” to make changes or “Run flow” to start testing.

1. 10. If your set-up was successful, then you should see green ticks and the success message, “Your flow ran successfully”.

Now you know how to create a flow in Power Automate to get data from Excel. However, you can customize your flow to automate more complex and specific tasks. For example, Power Automate allows you to loop through Excel rows. This loop can be set to search for specific data for retrieval or removal.

Let’s now see how you can use Power Automate to export Excel data to a Sharepoint list.

How to use Power Automate to export Excel data to SharePoint?

The great thing about Power Automate is that you can select the range of data you would like to export to SharePoint from Excel. You simply need to make sure that you have stored the Excel data in the cloud to use the Excel Online (Business) connector. Let’s see how to use Power Automate to export Excel data to a SharePoint List using a built-in flow.

1. 1. Click on “Templates” from the left-side menu.

1. 2. Type in “Create an item in SharePoint for a selected row” and click on the template. You might be asked to sign in next to the applications connected in the flow, as shown below with the green circled ticks. Click “Continue” to set up the flow.

1. 3. Fill in the fields to locate the Excel data to export to SharePoint. Then, click on “+ Add an input” according to the data type you want to export.

1. 4. Then, fill in the “Create item” step to locate the SharePoint list to which your data will be exported.

As in the previous case, always make sure to test your flow to check that it runs correctly. Note that what you’ve learned is how to use Power Automate to establish a one-way connection between Excel and SharePoint. If you want to learn how to create a two-way automatic connection between these two applications, you’ll find Import And Export From SharePoint To Excel (& Vice Versa) very useful.

Power Automate and Excel limitations

Although it is currently one of the most powerful tools offered by Microsoft Office to date, there are some limitations that you should consider when setting up a flow between Power Automate and Excel.

• The maximum number of rows that can be retrieved from Excel is 256. This means that your dataset should be limited to this amount. Otherwise, the flow may freeze up or ignore rows that exceed the limit.
• The maximum size of an Excel file is 25 MB.
• Recalculations can cause a timeout. If the dataset contains complex formulae, then the flow will constantly recalculate or reach timeout.
• Column headers that are named with a number may result in errors or timeouts for flows that have “Update a row” or “Delete a row” as actions.

Although you might be able to find workaround solutions for some issues, these won’t apply to the limitations shown above. As an alternative, an all-around automation tool such as Layer can help you.

Conclusion

Power Automate is a versatile and customizable tool. It not only allows you to create workflows between Microsoft applications, but it also integrates with over 450 of the most commonly used applications. Although this article has explained two common automated tasks in Excel, Power Automate can also be used to create Excel files, refresh Excel data connections, and convert CSV files to Excel.

By the end of this article, you should now know how Power Automate works with Excel, how to create flows using Power Automate to get data from Excel, and export Excel data to Sharepoint. Although it is a powerful feature, it has some limitations that can be solved by using an alternative automation tool, such as Layer.