Excel Formulae, Tips and Tricks - #5 (Paste Special)

The features and possibilities of using Paste Special can be a big time saver - so it's worth taking a look, and getting familiar with what it can achieve.

Applying numerical operations to multiple cells at once

Starting with an example: let's say we want to convert figures shown as thousands (eg. 1,234) to millions (eg. 1,234,000):

1. Type 1000 into a cell, right click on the cell and select 'Copy'
2. Select the numbers to be converted, right click and select 'Paste Special'
3. Select 'Values' from the 'Paste' section (this ensures any formats aren't copied over or removed) and 'Multiply' from the 'Operation' section
4. Hit 'OK', and your numbers have all been multiplied by 1000.

You can use this exact same approach to manipulate a range of numbers either to add, subtract, divide or multiply them all with the copied number.

One I find myself using all the time is multiplying a range of values by -1, quickly converting them all from positive (eg. 1,234) to negative (eg. -1,234). This is great if, for example, you download a list of revenue transactions from the general ledger, but don't want them showing as negative/credit values.

Fixing numbers stored as text

Another very powerful way to use Paste Special is to help convert numbers stored as text, back to numbers. Sometimes when you download a system report, you'll see figures with a small green triangle in the corner of the cell, warning you that the values are stored as text. This prevents you using them in formulas such as SUM, XLOOKUP, etc.

Simply apply the 'Paste Special' process (as above) with a numerical operation which doesn't actually affect the number (for example: multiply by 1; or add 0), and the numbers will convert back to the correct format (you'll see the small green triangle disappear).