Friday, July 11, 2008

Nifty Excel Trick #1: Format Percentages Correctly

I work a lot with Excel and occasionally have to search the web to remember how to do a seemingly simple task. A common problem is when Excel thinks a number you entered as a percent should be multiplied by 100 rather than by .01.

For example, you have several numbers like so:

You want them to be formatted like percents rather than numbers so you hit the thinking that will solve your problem but Excel goofs it up and makes it look like this:

WTF! That's not what you wanted. Why did it do that! If you've got a ton of numbers you aren't excited about having to manually go in and delete all those zeros but wait there is an easier way.
You could insert a column next to the column you want formatted and add a formula (e.g. =A1*.01) like so:

Now it will have it in the right format and you can now hit the % button to make it work:

Copy the formula down by dragging that little black square in your highlighted cell or double click it and "boom goes the dynamite!":

No comments: