How to Apply Excel Formulas with Power Automate Desktop

Introduction:

Power Automate Desktop is an incredibly powerful tool from Microsoft that enables you to automate various tasks on your Windows computer. Among its many use cases, one of the most popular ones is processing data from Excel files using formulas. In this step-by-step tutorial, you will learn how to utilize Power Automate Desktop to apply Excel formulas, saving you hours of manual work.

Step 1: Make an Excel Spreadsheet with Transaction Data

Start by creating an Excel spreadsheet with the transaction data you want to process. Here's an example of what it could look like:

Save the spreadsheet and remember the filename and folder path where it is stored. For this tutorial, we'll use the path:

"C:\TEST\TEST DATA.xlsx"

If you prefer not to create the Excel file manually, you can download it from my Github repository

 

Step 2: Get the Values from the Excel Worksheet

Now, let's build the Power Automate Desktop flow to process the data. Open Power Automate Desktop and create a new flow.

Step 3: Add the 1st Flow Action: Get Current Date and Time

To begin, we need to get the current date and time as we'll use this later to append it to the output Excel file. Add the "Get current date and time" action.

Step 4: Add the 2nd Flow Action: Convert Datetime to Text

Next, we need to convert the obtained datetime into a text format to make it easily readable. Use the "Convert datetime to text" action, setting the format to Custom (yyyyMMddhhmmss).

Step 5: Add the 3rd Flow Action: Set Variable

We will now set a variable to store the folder path and filename of the Excel file. Enter the path "C:\TEST\TEST DATA.xlsx".

Step 6: Add the 4th Flow Action: Launch Excel

To work with the Excel file, we need to launch Excel. Use the "Launch Excel" action, and in the document path, reference the variable created in Step 5.

Step 7: Add the 5th Flow Action: Get First Free Column/Row from Excel Sheet

In this step, we'll determine the last column and row that have no data. This will help us know where to insert the new data. Save the first free column and row values to the variables "FirstFreeColumn" and "FirstFreeRow."

Step 8: Add the 6th Flow Action: Write to Excel Worksheet (Column Header)

Before applying the formula, let's write a column header for the data we'll edit. Write the value "TRANSID" to the first cell in the new column (e.g., Column C).

Step 9: Add the 7th Flow Action: Write Formula to Excel Worksheet

We'll now write the formula we want to use for data extraction. In this example, we'll use "=LEFT(B2,11)" to extract the first 11 characters from Column B into Column C.

Step 10: Add the 8th Flow Action: Copy Cells from Excel Worksheet

In this step, we'll copy the formula we applied in the previous step.

Step 11: Add the 9th Flow Action: Select Cells in Excel Worksheet

Now, we need to select the cells where we want to paste the formula. In this case, we'll select all cells from row 2 to the last row with data(%FirstFreeRow - 1%).

Step 12: Add the 10th Flow Action: Send Keys (Paste Formula)

To paste the formula in the selected cells, use the "Send Keys" action and send the keys "{Control}({V})."

Step 13: Add the 11th Flow Action: Select Cells in Excel Worksheet (Data with Formula)

Now, let's select the cells that contain the formula and data we want to extract (e.g., Column C).

Step 14: Add the 12th Flow Action: Copy Cells from Excel Worksheet (Data with Formula)

We'll copy the data with the formulas, so we can use it for extraction.

Step 15: Add the 13th Flow Action: Send Keys (Remove Formula)

To extract only the data without the formulas, we'll use the "Send Keys" action and send the keys "{{Alt}({E}{S}{V}){Return})."

Step 16: Add the 14th Flow Action: Close Excel

In this final step, we save the processed data and close Excel. We use the "Save Document As" option to append the date and time to the file name, creating a new file each time data is processed.

How the Processed excel will Look Like;

Conclusion:

In this blog post, you learned how to leverage Power Automate Desktop to process data in an Excel file using Excel formulas. This automation can significantly reduce manual effort and improve efficiency in various business and personal tasks. Now you can automate and apply Excel formulas with ease, unlocking new possibilities for your productivity.

How to Apply Excel Formulas with Power Automate Desktop

“Why fit in when you were born to stand out?” Dr. Seuss

July 05, 2023

0

4