Paste Special Multiplier

Cast your mind... It’s the last Friday of your fiscal quarter and you have generated the Sales forecast budget for the president of the company. Just when you’re feeling good and relaxed (5 minutes before the numbers are displayed on a projector for all to see) your arch office enemy smugly tells you that the numbers you were given were the old ones. The new ones are 5% higher!

He proceeds to start measuring your desk as he is sure you will be fired and he will take over your job (and prestigious salary).

No need to panic, you just need to break out the mad crazy Excel skills that everyone in your office have grown to depend upon.

Here is some sample data that we can use for our example. It has a category and total sales. For our example we will need to change all these values by increasing them by 5%.

Equipment	Sales Total
Baseball	603.00
Basketball	485.00
Fishing	638.00
Golf	348.00
Hockey	338.00
Tennis	526.00
Grand Total	2,938.00

Start by placing 1.05 into any blank cell.

Equipment	Sales Total
Baseball	603.00	1.05
Basketball	485.00
Fishing	638.00
Golf	348.00
Hockey	338.00
Tennis	526.00
Grand Total	2,938.00

Now copy the value in that cell by Edit, Copy or press Ctrl and C. 

Next Select all the values that you need to change (our Sales Total data). The data will highlight grey.

Equipment	Sales Total
Baseball	603.00	1.05
Basketball	485.00
Fishing	638.00
Golf	348.00
Hockey	338.00
Tennis	526.00
Grand Total	2,938.00

Now select Edit, Paste Special to bring up the Past Special dialog box. Select Multiply and click the OK button.

Instantly the numbers increase by the 5%.

Equipment	Sales Total
Baseball	633.15
Basketball	509.25
Fishing	669.90
Golf	365.40
Hockey	354.90
Tennis	552.30
Grand Total	3,084.90

The day saved, you once again are the Excel hero.

You are not limited by multiplication. Notice that in the Paste Special dialog box, you have the options to add, subtract, multiply and divide.  

Break out this useful skill in crunch time and WOW your clients.

Copying Data Validations to Different Areas of Your Worksheet

Data validation allows you to control what information is allowed to be in a cell. This is a handy feature to help ensure data accuracy. Quite often you may need to repeat the data validation in different areas of your worksheet. Well rather than recreating the data validation for each area, here’s a useful way to copy an existing cells validation to additional cells or ranges of cells.


In the below example I have setup the following validation for cells (A2..A7) where only whole numbers between 1 and 5 will be allowed.

It’s easy to copy this validation to different areas of my worksheet by first copying one of the cells between A2 and A7 using CTRL+C.

Next go to your target cell or range of cells and then press ALT+ESN. This copies only the data validation rule to your target cells or range of cells. You could also select Paste Special and then select Validation from the Paste options.

Click the OK button to complete the copy of your data validation.