Microsoft Power Automate | Add data to Excel, get data from Excel, Conditions and Send Email | Guide

Introduction

This tutorial guides you through using Microsoft Power Automate to manage data in Excel. You will learn how to add data from user input to an Excel table, retrieve data from Excel, apply conditions on that data, and send automated emails based on the results. This process enhances efficiency by automating repetitive tasks and making data handling seamless.

Step 1: Update Excel Tables with Power Automate

To begin working with Excel in Power Automate, you need to create a flow that allows users to input data manually.

1. Create a new flow:

   • Open Microsoft Power Automate.
   • Select "Create" from the menu.

2. Choose a manual trigger:

   • Select "Instant cloud flow".
   • Name your flow and choose the "Manually trigger a flow" option.

3. Add user input fields:

   • Click on "Add an input" to define what data users can provide.
   • Choose input types such as "Text" for names or "Number" for quantities.

Step 2: Add Data to an Excel Table

Once you have set up the user input, you will add the data to an Excel table.

1. Select Excel Online (Business):

   • Click on "New Step".
   • Search for "Excel Online (Business)" and select it.

2. Add a Row into a Table:

   • Choose the "Add a row into a table" action.
   • Specify the location of your Excel file (OneDrive or SharePoint).
   • Select the specific Excel file and the table where you want to add the data.

3. Map the user inputs to Excel columns:

   • Use dynamic content to map the inputs from the previous step to the corresponding columns in your Excel table.

Step 3: Import Data from Excel

Now that we can add data, let’s retrieve existing data from an Excel table for further processing.

1. List Rows Present In Table:
   • Click on "New Step" and select "Excel Online (Business)" again.
   • Choose "List rows present in a table".
   • Specify the location and table to get all rows.

Step 4: Iterate Through Each Row

You will now set up a way to process each row retrieved from the Excel table.

1. Apply to Each:
   • Add an "Apply to each" action.
   • Set the output to the value from the "List rows present in a table" step.
   • This allows you to work with each row individually.

Step 5: Apply Conditions on Data

To perform actions based on certain criteria, you will set up conditions.

1. Add a Condition:
   • Inside the "Apply to each", click on "Add an action" and select "Condition".
   • Define your condition based on the data (e.g., check if a value is greater than a certain number).

Step 6: Send Email Notifications

Finally, set up an action to send emails based on the conditions you've applied.

1. Send Email:
   • In the "If yes" or "If no" branches of your condition, add the "Send an email" action.
   • Fill in the recipient, subject, and body of the email. You can use dynamic values from the Excel rows to personalize the message.

Conclusion

You've now created a Power Automate flow that allows you to add data to Excel, retrieve and process that data, apply conditions, and send automated email notifications. This process can save time and streamline your data management tasks. For further learning, explore more complex flows or integrate additional applications with Power Automate to enhance your automation skills.