Excel Everest

It’s a Bird! It’s a Plane! It’s Paste Special!

Functions,Tips and tricks Sean Duffy

Imagine, if you will, a scenario that goes something like this. 

You have a Microsoft Excel file that contains (apart from lots of other things) a column with percentage data. Isn’t that much difficult to imagine, is it?

There – you don’t even have to stretch your imagination that little bit – that’s what the data looks like. Now, the more observant among you will have promptly noticed that the data is actually in integer notation, not percentage form.

What’s worse is, if you try to convert it into percentages by clicking on that percentage button on the home tab…

… the column turns into this, well, monstrosity.

Familiar scenario? Well, this Excel tutorial explains how to gaily skip over this rather petty (but time-consuming hurdle) and move on to matters more deserving of your attention.

The conventional way would be to either manually write over the original data (overwrite 43 as .43, 23 as .23 and so on) and then use that percentage sign, or to insert a second column which divides the first by 100. Either ways, it is time consuming, and involves much gnashing of the teeth.

Well, un-gnash those teeth, ladies and gentlemen, because we are going to invoke a rather nifty weapon from the versatile arsenal of Paste Special. Here’s what you have to do:

simply enter 100 in any cell of your choice, and copy that cell.

Select the range on which the operation needs to be performed, and choose paste special (the keyboard shortcut for which happens to be ALT+E+S)

Under operation, you’ll notice there are two buttons over on the right: Multiply, and Divide. Choose Divide, hit OK, and this is what you get:

Voila, problem solved!