Retrieve Excel Column Values Into List Using Power Automate Desktop

A few days ago, a client reached out to us for one of his requirements on Power Automate Desktop. The client has stored ‘Project Details’ in an Excel worksheet locally on the desktop.

He asked us to retrieve only the values from a specific column, such as Project Name, within a particular range of rows. For example, retrieve the values of project names from rows 1 to 10.

In this Power Automate desktop tutorial, I will guide you through how to extract specific column values from an Excel table and convert them into a list using Power Automate Desktop.

Extract Excel Table Column Values Into a List In Power Automate Desktop

Let me explain how to extract specific column values from an Excel table and display them as a list using Power Automate desktop flow.

Below, you can see an Excel file that is formatted as a Table, which consists of columns related to ‘Project Details’.

From the above displayed screenshot, you can see there is a ‘Project Name‘ column in the Excel Table. I wanted to retrieve the Project Names up to 10 rows [Sustainable Energy Project] and display the values in a list.

Here are the steps to follow:

1. Launch the Power Automate desktop app and create a new flow in it. Next, add actions to the created flow.
2. Then, expand the Excel section and drag and drop the Launch Excel action. It opens a new instance of Excel on the desktop. Then provide the information below and click on the Save button.
   • Launch Excel: Select ‘and open the following document‘ option
   • Document Path: Provide the file path by clicking on the Select file icon.

3. Under the Actions pane, search for and drag and drop the ‘Select cells in Excel worksheet‘ action to the workspace. This action will select the Project Name values that I want to extract. Configure the parameters below.
   • Excel instance: By default, the Excel instance variable will be selected.
   • Select: Select the Range of cells option.
   • Start column: Provide the Start column as 1.
   • Start row: Provide the start row as 2.
   • End column: Provide the end column as 1.
   • End row: Provide the end row as 10.

Now, the values have been selected. Let’s copy the selected values from the Excel Table.

4. Next, from the Excel Advanced section, drag and drop the ‘Copy cells from worksheet‘ action, then provide the information below.
   • Excel Instance: Select the Excel instance variable
   • Copy mode: Select the ‘Values from Selection‘ option

5. To get the copied text from the clipboard, expand the clipboard section, then drag and drop the ‘Get Clipboard text‘ action to the workspace.
   • This will produce the ClipboardText variable.

6. Next, expand the Text section and then drag and drop the ‘Split text‘ action to the workspace. It will split the text and convert it into a list. Configure the parameters and click on Save.
   • Text to split: Here, select the ‘ClipboardText’ variable.
   • Delimiter type: Select the type as ‘Standard’
   • Standard delimiter: Select the Standard delimiter as New line.
   • Times: Select the Times as 1

Here, I have renamed the variables produced to ‘ProjectNamesList‘.

7. To close the Excel instance on the desktop, drag and drop the ‘Close Excel‘ action.
   • Excel instance: By default, it will take the ExcelInstance variable.
   • Before closing Excel: Choose the ‘Do not save document‘ option.

8. To display the output, I took a ‘Set variable‘ action and added it to the workspace. Set the variable name and value as below.
   • Value: Click on {x} -> Select the ‘ProjectNamesList’ variable.

Now run the flow by clicking on the run button.

9. Once the flow execution is completed, on the right side, you can see the generated flow variable list. Open ‘ProjectNames‘ flow variable. Refer to the image, which shows the list of project names.

Retrieve Last ‘N’ Values from a Specific Excel Column Into a List Using PAD

This example will show you how to get the last ‘N’ values from a specific Excel column and then convert them into a list using Power Automate Desktop.

I will take the same Excel that was used in the previous example, ‘Project Details’. Have a look at the image below:

In the screenshot above, I wanted to retrieve the last ‘5‘ values from the Project Name column and convert the values into a list. So, I need to retrieve the Project names from [Remote Work Enablement – Sustainable Energy Project].

To achieve this, look at the steps below:

1. First, add a Launch Excel action that will open the existing file on the desktop.
2. Next to that, to retrieve the first free column or row of an active worksheet, I will add the ‘Get first free column/row from Excel worksheet‘ action. This will be useful to extract the End columns of an Excel sheet dynamically.
   • It produces FirstFreeColumn and FirstFreeRow variables.

3. Next, drag and drop the “Read from Excel worksheet” action to read values from Excel. Enter the details below:
   • Excel instance: By default, it will take the Excel variable.
   • Retrieve: I have selected the “Values from a range of cells” option.
   • Start column: Enter the value as 1.
   • Start row: Provide value as 1.
   • End column: Select FirstFreeColumn variable -1, because this will give the end column dynamically.
   • End row: Choose FirstFreeRow variable -1. This will get the end row dynamically without hardcoding it.

I have renamed the variable to ‘ColumnData’, where the Excel data will be stored. Click on the Save button.

4. In this step, I will add a Set variable action to the workspace to extract the start index for the last ‘5‘ rows from the Excel table.
   • Variable: Renamed this as ‘StartIndex‘
   • Value: Click on {x}-> Choose %ColumnData.RowsCount% from ColumnData variable. It will give the total row count present in Excel.

%ColumnData.RowsCount - 5 + 2%

Suppose I wanted to retrieve the last five rows from Excel, so I need to subtract the ‘5’ from

Note:

Suppose I wanted to retrieve the last five rows from Excel, so I need to subtract the ‘5’ from the Total rows.

But, Power Automate Desktop uses, ‘0’ based index, so the first row is index as ‘0’. In the Excel, the values are starting from row ‘1’ and first row has column headers. So, basically the row values will start from ‘2’.

For that, we need to add ‘2’ to the expression -> that will give the start index for the last 5 rows.

Example: 10 Excel rows -> Start row Index to retrieve last 5 values from Excel will be ‘6’.

5. To select the values from the Excel sheet, add a ‘Select cells in Excel worksheet‘ action to the workspace. Configure the parameters. Save the action.
   • Select: Select the Range of cells option.
   • Start column: Provide the Start column as 1.
   • Start row: Select the %StartIndex% variable.
   • End column: Provide the end column as 1.
   • End row: Select %FirstFreeRow – 1% variable.

6. Next, drag and drop the ‘Copy cells from worksheet’ action; this will copy the selected range of values.
   • Copy mode: Select the ‘Values from Selection‘ option

7. To get the copied values retrieved to the clipboard, add a ‘Get Clipboard text‘ action to the workspace.
   • This will produce the ClipboardText variable.

8. Then add a ‘Split text’ action to the workspace. It will split values and convert them into a list. Provide the parameters and click on Save.
   • Text to split: Here, select the ‘ClipboardText’ variable.
   • Delimiter type: Select the type as ‘Standard’
   • Standard delimiter: Select the Standard delimiter as New line.
   • Times: Select the Times as 1

Here, I have renamed the variables produced to ‘ProjectNamesList‘.

9. To close the Excel instance open on the desktop, drag and drop the ‘Close Excel‘ action.
   • Before closing Excel: Choose the ‘Do not save document‘ option.

10. Once the flow is executed, open and see the ‘ProjectNamesList’ variable from the split text action. It will display the last ‘5’ values from a specific column in the Excel worksheet. Refer to the image:

This is how you can retrieve column values from an Excel worksheet and convert them to a list using Power Automate Desktop.

Apart from the ‘Split text’ action in Power Automate desktop, which generates the text values into a list of items. There is also a Create new list action that generates the text values in the form of a list. You can use all these actions according to your preference.

I trust this tutorial helped you retrieve the specific data from your Excel table and convert it into a list by using Power Automate desktop flows.

You may also like:

• Power Apps Bulk Approvals Using Power AutomateFind and Replace Text in Power Automate Desktop
• Upload File in SharePoint Power Automate Desktop
• Update Data Table Items Power Automate Desktop
• Get Values from Data Table Power Automate Desktop
• Power Automate Send Reminder Emails

Bijay Kumar

After working for more than 18 years in Microsoft technologies like SharePoint, Microsoft 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Our audiences are from the United States, Canada, the United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a Microsoft SharePoint MVP (12 times). I have also worked in companies like HP, TCS, KPIT, etc.