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

[rickvvaldez]

Hello community,

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

Your help is very appreciated.

Thank you

Rick

[mdevaney]

@rickvvaldez 

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

[MichaelAnnis]

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!

[VJR]

@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.

[rickvvaldez]

Hello helpful community,

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.

[MichaelAnnis]

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)

[VJR · Accepted solution]

Hi @rickvvaldez 

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

[rickvvaldez]

Thank you MichaelAnnis and VJR for all your help. 

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