Don’t forget to share this post

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.
Power Automate Excel Examples and Use Cases Quick Access
Power Automate & Excel: Examples and Use Cases - Quick Access
  1. 3. You should be able to see a similar view as shown below.
Power Automate Excel Examples and Use Cases Power Automate
Power Automate & Excel: Examples and Use Cases - Power Automate

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 Excel Power Query for Beginners Promotion
How To Use Excel Power Query for Beginners?

With Power Query, you can import data from different sources and prepare the data for analysis. Here's how to use Power Query in Excel.

READ MORE

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.
Power Automate Excel Examples and Use Cases Instant Cloud flow
Power Automate & Excel: Examples and Use Cases - Instant Cloud flow
  1. 2. Select “Manually trigger a flow” from the trigger options.
Power Automate Excel Examples and Use Cases Manually trigger a flow
Power Automate & Excel: Examples and Use Cases - Manually trigger a flow
  1. 3. Click on “Next step” to set up the first trigger.
Power Automate Excel Examples and Use Cases Next step
Power Automate & Excel: Examples and Use Cases - Next step
  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.
Power Automate Excel Examples and Use Cases Excel Online
Power Automate & Excel: Examples and Use Cases - Excel Online
  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”.
Power Automate Excel Examples and Use Cases Get worksheets
Power Automate & Excel: Examples and Use Cases - 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.”.
Power Automate Excel Examples and Use Cases Share Point site
Power Automate & Excel: Examples and Use Cases - SharePoint site
  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.
Power Automate Excel Examples and Use Cases Select File
Power Automate & Excel: Examples and Use Cases - Select File
  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.
Power Automate Excel Examples and Use Cases Test
Power Automate & Excel: Examples and Use Cases - Test
  1. 9. You can click on “Review connections and actions” to make changes or “Run flow” to start testing.
Power Automate Excel Examples and Use Cases Run Flow
Power Automate & Excel: Examples and Use Cases - Run Flow
  1. 10. If your set-up was successful, then you should see green ticks and the success message, “Your flow ran successfully”.
Power Automate Excel Examples and Use Cases Successful flow
Power Automate & Excel: Examples and Use Cases - Successful flow

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 Macros In Excel To Automate Tasks?

Macros are a powerful tool that can be used to automate tedious tasks in Excel. Here's how to use Macros in Excel.

READ MORE
How To Use Macros In Excel To Automate Tasks

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.
Power Automate Excel Examples and Use Cases Templates
Power Automate & Excel: Examples and Use Cases - Templates
  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.
Power Automate Excel Examples and Use Cases Continue
Power Automate & Excel: Examples and Use Cases - Continue
  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.
Power Automate Excel Examples and Use Cases For selected row
Power Automate & Excel: Examples and Use Cases - For selected row
  1. 4. Then, fill in the “Create item” step to locate the SharePoint list to which your data will be exported.
Power Automate Excel Examples and Use Cases Create item
Power Automate & Excel: Examples and Use Cases - Create item

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.

How to automate Excel tasks with Layer?

Layer is a collaboration platform for spreadsheets that works on top of your existing Excel files and Google Sheets. Share parts of your spreadsheet, collect and consolidate data, and review changes to make collaboration seamless and more efficient while keeping full control over your data. Using Layer, you can:

  • Manage Access: Give spreadsheet access on a tab or cell level to relevant stakeholders.
  • Collaborate: Automate tasks, set deadlines, and communicate on top of your files.
  • Review & Track: Consolidate input, track changes, and restore previous versions.

Sign up to get started with Layer right now or schedule an onboarding call so we can help you set up and share your spreadsheet flows.

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.

Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published May 27 2022, Updated Jun 13 2022