How to add a percentage sign without multiplying by 100 in Excel

I become frustrated with software that tries to do everything for me. Automatic install wizards dumping icons all over my desktop without asking or my word processor auto correcting things it thinks are wrong. Luckily, while we all universally hate Microsoft (yet use it every day) there are options to customize and do it your way. Maybe that is why I don't hate them entirely. But, hey, today's post is about a trick in Microsoft Excel, not in hating software giants!   



There are lots of websites that have thousands of Microsoft Office tips and tricks. But today I wanted to feature a less well known trick that is harder to find online. 


How to add a percentage sign without multiplying by 100 in Excel?


For example, when I do my work I do my own calculations. After calculating a row, or column of numbers I wanted to format them so that they displayed with the percentage symbol. If you have ever used Excel for your budgeting you know that a simple Right Click and Format Cells will allow you to choose Currency and voila! All your cells are instantly formatted to two decimal places with the $ sign. 


If you choose the percentage format, there is this caveat. 

"Percentage formats multiply the cell value by 100 and displays the result with a symbol."

If your number is '4' it will be represented as '400%'. Argh! I don't want Excel to do anything to my numbers. What can I do?


Option 1) Create a new column or modify the equation preceding your results to divide them all by 100.

But what if they are already divided by 100? Or you have your own reasons…


Option 2) Right Click, Format Cells, Custom. Manually type in "0.0\%" without the quotes.

If you want more decimal places to show, just add in zeroes. "0.000\%"


For example, if your cell contents are '4.35' then with the above custom format it would appear as '4.350%'.

This solved a big headache for me. I found it answered by J. E. McGimpsey on a Mac forum for MS Office users! Hence, my jab at the controversy for fun at the beginning of this post.

Hope it helps!