How to Count Rows & Columns in SpreadSheet by Power Automate Desktop?

Recently, I got a requirement to count the rows and columns of an Excel sheet by using Power Automate Desktop. In this tutorial, I will show you how you can count the total number of rows and columns present in an Excel worksheet using Power Automate Desktop. I will also show you how to get the Excel item count based on a Condition using PAD.

Count Rows & Columns in Excel using Power Automate Desktop

Here, I will explain how to retrieve the total number of rows and columns in an Excel sheet using Power Automate Desktop.

Below is an Excel spreadsheet containing ‘Project Details‘ with the following data.

Within this Excel worksheet, I wanted to get the count of the total number of rows and columns present.

Follow the steps below to achieve this:

1. Launch your Power Automate Desktop app. Then, create a new flow and provide a name to it. Then, it will open the workspace window, where you add actions.
2. Next, search for ‘Launch Excel‘ in the Actions pane and drag and drop it into the workspace. Set the parameters. Then, click on the Save button.
   • Launch Excel: Choose and open the following document option.
   • Document path: Provide the document path, or select the document from the ‘Select file’ icon.

This will produce the ‘ExcelInstance‘ variable.

3. Then, add a ‘Read from Excel worksheet‘ action that reads the values of all cells in the worksheet. It will give the ‘ExcelData‘ variable. Save the action.
   • Retrieve: Choose the ‘All available values from worksheet‘ option.

Read Send Outlook Email with Attachments using Power Automate Desktop

Get Total Number of Excel Rows Count

4. Then, to display the count of Excel rows, take the ‘Display message‘ action to the workspace. Set the parameters below. Click on the Save button.
   • Message box title: Enter a name for the message box.
   • Message to display: Click on {x} -> Expand ExcelData variable -> Choose.RowCount.

Excel Row Count: %ExcelData.RowsCount%

Check out Find and Replace Text in Power Automate Desktop

Get Total Number of Excel Columns Count

I will show you how to retrieve the column count from an Excel spreadsheet using Power Automate Desktop.

5. In the same way, add another ‘Display message’ action to show the Excel column count value. Set the parameters.
   • Message box title: Provide a title for the message box.
   • Message to display: Click on {x} -> Expand ExcelData variable -> Choose Columns.Count.

Then, Save the action to the workspace.

Excel Column Count: %ExcelData.Columns.Count%

Note:

You can also get the Excel column count by providing the variables with column properties : %ExcelData.ColumnHeadersRow.ColumnsCount%.

5. At last, add a ‘Close Excel‘ action that will close the Excel document opened on the desktop.

Now, the flow is ready. Save the flow and run it. After the flow execution, you can see the outputs mentioned below.

Output: Row Count

Output: Column Count

This way, you can count Excel rows and columns using Power Automate Desktop.

Check out Extract Data From a Web Page to Excel Using Power Automate Desktop

Filter & Count Excel Rows Based on Condition in Power Automate Desktop

In this section, let’s see how to count the Excel rows based on a specific condition using Power Automate Desktop.

Scenario:

Here, I will use the same Excel worksheet that consists of ‘Project Details‘. Refer to the image above for your reference.

Now, I wanted to count the number of Excel rows where the ‘Status‘ column has the value ‘Completed‘. Let’s start with the steps below.

1. On the Power Automate desktop workspace, add the ‘Launch Excel‘ action by taking the document path.
2. In the next step, drag and drop the ‘Read from Excel worksheet‘ action. Set the parameters as shown in the above example. Save the action.
   • First line of range contains column names: Toggle on this button, so that Power Automate recognizes that the first line contains column names.

3. Then, add the ‘Set variable‘ action to assign the count to a variable. I have renamed the variable name to ‘CompletedCount‘. Click on the Save button.
   • Value: Provide the value to ‘0‘.

4. After that, drag and drop a “For each” loop into the workspace that iterates over the list of items in the Excel worksheet. Save the action.
   • Value to iterate: Click on {x} -> Select ‘ExcelData‘ variable.
   • Store into: I have renamed the variable to ‘CurrentRow’.

5. Next, add an ‘If‘ action inside the loop that filters the Excel rows whose status is ‘Completed‘. Set the parameters.
   • First operand: Select the CurrentRow variable. Then, within brackets, enter the column name.
   • Operator: Select the operator based on your requirement.
   • Second operand: Enter the value as ‘Completed‘.

6. Inside the if condition, take the ‘Increase variable‘ action that will increase the completed count variable count each time the condition is met.
   • Variable name: Select the ‘CompletedCount‘ variable.
   • Increase by: Enter the value by 1.

7. Outside the For each loop, drag and drop ‘Set variable‘ to show the count of Excel rows with ‘Status’ column value ‘Completed’. Click on the Save button.
   • Variable: Provide a name for the variable.
   • Value: Choose the ‘CompletedCount’ variable.

8. At last, add ‘Close EXZ. Save and execute the flow.

Result:

Open and check the [CompletedRowCount] variable to see the Excel row count.

This way, you can count the Excel rows and columns by using Power Automate Desktop.

I hope you can now understand how to count rows and columns in Excel using Power Automate Desktop. If you have any queries related to this, do mention them in the comments section below.

Learn More about Power Automate Desktop:

• Insert Row into Data Table in Power Automate Desktop
• Update Data Table Items Power Automate Desktop
• Export and Import Power Automate Desktop Flows
• Find and Replace Text in Power Automate Desktop

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.