Accountants and others often want to show their data in, for example, thousands or millions, even though the data they are working with could be in native format. Keeping it simple, this might mean

  • displayed value £250 but to be shown as £250,000
  • displayed value £640,000 but to be shown as £640,000,000

We can easily achieve our objective by multiplying each value in turn this way: =£250*1000 … £250,000

However, lets assume there are 4,000 cells to be transformed or even 40,000 cells to be transformed: no one has the time to carry out these calculations by hand.

Paste Special Multiply

Excel comes to your help here with the Paste Special Multiply function and I have prepared a video for you to show you how to do that:

Paste Special Multiply in action: 

There is no Excel file for this: as you can see, you can create your own data in seconds, as I did here.

Paste Special Divide

As a matter of interest, I didn’t do this but you might have noticed that there is also a Paste Special Divide function too: that means, you can change, eg, £250,000 into £250 by using that function …

Finally, I hope it is obvious that you don’t have to multiply or divide things only by 1,000: it can be any valid number or fraction or decimal.


Duncan Williamson

%d bloggers like this: