cancel
Showing results for
Did you mean:
Regular Visitor

## Excel: How to convert range of numbers to percentages of rounded to 1 decimal (0.0%)?

Hello community,

How do I convert a range of numbers to percentages rounded to 1 decimal (0.0%) in Power Automate Desktop?

Thank you

Rick

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

I have written a vbscript which can be utilised as a customised function and can work for ANY excel file path, ANY sheet, From ANY cell, To ANY cell, ANY number of decimal places (one or two as you needed these two options).

This is how the overall Flow looks like.

Before running the Flow

After running the Flow for 2 decimal places

Simply change your inputs as per your needs in the Set variable statements.

Below is the script for the vbscript action.

``````Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open("%ExcelFilePath%")

objWorkbook.Sheets("%SheetName%").Activate

If "%SingleDecimal%" = True then
objExcel.Range("%FromRange%" & ":" & "%ToRange%").NumberFormat = "0.0%%"
Else
objExcel.Range("%FromRange%" & ":" & "%ToRange%").NumberFormat = "0.00%%"
End if

objWorkbook.save
objWorkbook.close
objExcel.Quit
set objWorkbook=nothing
set objExcel=nothing``````

Alternatively:

Paste the below code in a blank flow editor, change the Excel path, set the values of the desired variables and run the flow

``````SET ExcelFilePath TO \$'''C:\\test\\filename.xlsx'''
SET SheetName TO \$'''Sheet1'''
SET FromRange TO \$'''A1'''
SET ToRange TO \$'''A3'''
SET SingleDecimal TO \$'''False'''
Scripting.RunVBScript VBScriptCode: \$'''Set objExcel = CreateObject(\"Excel.Application\")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open(\"%ExcelFilePath%\")

objWorkbook.Sheets(\"%SheetName%\").Activate

If \"%SingleDecimal%\" = True then
objExcel.Range(\"%FromRange%\" & \":\" & \"%ToRange%\").NumberFormat = \"0.0%%\"
Else
objExcel.Range(\"%FromRange%\" & \":\" & \"%ToRange%\").NumberFormat = \"0.00%%\"
End if

objWorkbook.save
objWorkbook.close
objExcel.Quit
set objWorkbook=nothing
set objExcel=nothing''' ScriptOutput=> VBScriptOutput ScriptError=> ScriptError``````

7 REPLIES 7
Super User

Could you please be more specific.  Where does this range of numbers exist?  If it helps to explain, share a screenshot.

Super User

I would attempt the following assuming you have already launched excel and the current excel instance is in focus:

Go to the Home tab on the ribbon (the top menu items) -> under number formatting you should see an icon for %. This defaults to 0 decimal places. You should see another icon for Left by 1 decimal place.

set up your flow like this:

Select cells

click UI element in window (the % sign)

click UI element in window (the increase decimal place)

should be done.

Note:  if you want a whole column, the best way would be to click UI element in window (column c) instead of select cells.

good luck!

Super User

@rickvvaldez , yes please. could you share some more details where is the data coming from and what type of variable is it currently in.

Also please confirm if the below is what you are looking for?

Example 1:

Input: 10.23

Output: 10.2%

Example 2:

Input: 20

Output: 20.0%

Please share all possible scenarios you have for the input data.

Regular Visitor

Below is the before and after that I am attempting. The numbers a of type General to be converted to Decimal.

The goal is to convert a range of cells into percentages with one ( and two ) decimal places.

Super User

The solution I gave earlier should work, here is some more added to it:

After launch Excel

Get First Free Column/Row to %FirstFreeColumn% and %FirstFreeRow%

Select cells from A2 to (whatever the last column is, if it moves, try %FirstFreeColumn - 1%) %FirstFreeRow%

click UI element in window (the % sign)

click UI element in window (the increase decimal place)

Super User

I have written a vbscript which can be utilised as a customised function and can work for ANY excel file path, ANY sheet, From ANY cell, To ANY cell, ANY number of decimal places (one or two as you needed these two options).

This is how the overall Flow looks like.

Before running the Flow

After running the Flow for 2 decimal places

Simply change your inputs as per your needs in the Set variable statements.

Below is the script for the vbscript action.

``````Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open("%ExcelFilePath%")

objWorkbook.Sheets("%SheetName%").Activate

If "%SingleDecimal%" = True then
objExcel.Range("%FromRange%" & ":" & "%ToRange%").NumberFormat = "0.0%%"
Else
objExcel.Range("%FromRange%" & ":" & "%ToRange%").NumberFormat = "0.00%%"
End if

objWorkbook.save
objWorkbook.close
objExcel.Quit
set objWorkbook=nothing
set objExcel=nothing``````

Alternatively:

Paste the below code in a blank flow editor, change the Excel path, set the values of the desired variables and run the flow

``````SET ExcelFilePath TO \$'''C:\\test\\filename.xlsx'''
SET SheetName TO \$'''Sheet1'''
SET FromRange TO \$'''A1'''
SET ToRange TO \$'''A3'''
SET SingleDecimal TO \$'''False'''
Scripting.RunVBScript VBScriptCode: \$'''Set objExcel = CreateObject(\"Excel.Application\")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open(\"%ExcelFilePath%\")

objWorkbook.Sheets(\"%SheetName%\").Activate

If \"%SingleDecimal%\" = True then
objExcel.Range(\"%FromRange%\" & \":\" & \"%ToRange%\").NumberFormat = \"0.0%%\"
Else
objExcel.Range(\"%FromRange%\" & \":\" & \"%ToRange%\").NumberFormat = \"0.00%%\"
End if

objWorkbook.save
objWorkbook.close
objExcel.Quit
set objWorkbook=nothing
set objExcel=nothing''' ScriptOutput=> VBScriptOutput ScriptError=> ScriptError``````

Regular Visitor

Thank you MichaelAnnis and VJR for all your help.

The VB Script will be a great contribution to the community.

Announcements

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.