CODEWITHSUNDEEP
excelexcel-formulavba
Tommy
Tommy

Reputation: 27

Formatting excel percentage decimals

I have a row of excel percentage decimals that I have imported from CSV.

The row contains data such as "95%" or "99.5%"

After the import, all the "99.5%" changes to "99.50%"

I can right click on the cell and change the cell's decimal place to 1. However there are 20,000 rows I have to go through manually.

If I select the entire row and change to 1 decimal place, then even those with "95%" changes to "95.0%".

How to I prevent excel from messing up my format.

Upvotes: 1

Views: 1615

Answers (2)

T.Hannah
T.Hannah

Reputation: 111

Jeeped's answer works. If for some reason you want to avoid conditional formatting and keep the formatting static, another solution would be to add a temporary column and use the formula Jeeped used:

=rounddown(A2,2)=rounddown(A2,3)

Filter to TRUE and highlight the column - set the formatting at 0 decimal points. Filter to FALSE and highlight the column - set the formatting to 1 decimal point.

My 2 cents - if 3 significant digits are necessary, it's best to keep all the data at 3 sig figs. This usually leads to a lot less confusion than removing extra 0s.

Upvotes: 0

user4039065
user4039065

Reputation:

If it is mission critical that you display 95% instead of 95.0% you could add a Conditional Formatting Rule based on the following formula:

=ROUND(A2, 3)=ROUND(A2, 2)

First set the percentage cell number format for 1 decimal then add a CFR that changes it to zero decimals based on that formula.

enter image description here

Upvotes: 2

Related Questions