Do you have trouble looping through rows in Excel in Microsoft Power Automate for Desktop? Fret not! This is a common struggle, and we’ll help you learn how to do it.
With Microsoft Excel being so pervasive in our work and/or school lives, automating repetitive tasks in Excel is one of the most useful ways for everyday users to make their lives easier using Microsoft Power Automate for Desktop. But looking through the myriad Microsoft Power Automate for Desktop actions, it may not be obvious how to make them repeat (loop) for each row in Excel.
Today, we will be walking you through how to loop through each row in Excel, using two different methods.
We will be using the following Excel file to demonstrate both methods. There are 10 rows of data in this Excel file, and both solutions will teach you how to loop through each row and retrieve the individual values.
Method 1: Looping through Excel data, retrieving the value of a single cell each time
Firstly, assuming the Excel file is already open, in your flow designer, add the “Attach to running Excel” action to your flow and select your desired Excel file.
However, if the Excel file is not open, use the “Launch Excel” action instead.
Next we will need to use the Loop action. A loop is a concept in Microsoft Power Automate for Desktop that allows you to repeat one or more actions multiple times. Through the loop action, you are able to specify the number of times you want an action to be repeated. In this case, as there are 10 rows of data, we would need to repeat the loop 10 times. In the Loop dialogue box, set the “Start from” parameter as 2. The 2 corresponds to the second row in the Excel file, which contains the first row of data.
Set the “End to” parameter as 11 – which corresponds to the last row in the Excel file which contains data. The loop index will increment by a value of 1 as we want to loop through each row.
Next, as we want the flow to read from the Excel file, we will select the “Read from Excel worksheet” action. In this case we want choose the option under “Retrieve” to retrieve the value of a single cell.
To obtain the data from the first row and first column, set the Start column as “1” or “A” which would correspond to the column “First Name” in the Excel file. As for “Start row”, it is vital to note that the start row is not “2” but is %LoopIndex%. This is important because as we go through each iteration of the loop, the row changes from 2 to 11.
The variable produced should be changed to %FirstName% in order to make it more intuitive.
Repeat this for all other columns as needed.
Method 2: Looping through Excel data using a datatable (retrieve values from a range of cells)
For this method, we will be using a concept called datatable. A datatable is a variable that stores data in the tabular form – similar to how you would store data using an Excel file.
Step 1: Read from Excel file and store the values as a datatable
Firstly, in the “Read from Excel worksheet” action, set the Retrieve parameter as “Values from a range of cells”. Next, set the Start column, Start row, End column, End row, according to your Excel file. In this case it is “A”, “1”, “G”, “11”, respectively.
Next, under “Advanced” turn on “First line of range contains column names”.
After running this flow and clicking on “ExcelData” in the flow variables pane, you can see that the flow has extracted all the values from the Excel file and stored them as the variable “ExcelData” which is a datatable.
Important Note: Datatables use a zero-based index. This means that the first row and column in the datatable have an index of 0 and not 1. So remember to use a zero-based index while retrieving data.
Step 2: Retrieve the values you need using for each loop
We will be using a for each loop, which is particularly useful while iterating over a datatable or list. In the for each loop, we need to specifiy the data table we wish to iterate over – in this case, it is “ExcelData”. The each of the loop, it will store the data under the variable called “CurrentItem”. The variable “CurrentItem” is a datarow containing all the values in that row.
Work with Excel data in Microsoft Power Automate for Desktop now
So we’ve shown you how to loop through each row in Excel in Microsoft Power Automate for Desktop. Knowing how to do this step is crucial in making full use of the Excel actions in Microsoft Power Automate for Desktop. Now that you know how to do it, it’s time for you to try it out!