Exceljet

Quick, clean, and to the point

How to do in-place changes with paste special

In this video we'll look at a way to make basic in-place adjustments to numeric values. This is a handy way to quickly change things like dates, prices, and times without using formulas.

Let's take a look.

Here we have some examples of information we want to change. In the first example, we want to increase prices by 10%. Obviously, we could just add a new formula that multiplies the existing price times 1.1 in another column. Then use Paste Special to paste over the original prices.

But how can we do this without all that busy work?

One way is to use Paste Special. Notice that if I add a number to a cell, and copy that number, I can bring up the Paste Special dialog which provides a set of math operations.

So, to increase prices by 10% in place, I can add 1.1 to any cell in the worksheet, copy it to the clipboard, and use Paste Special with the Multiply option. To be sure that the copied value doesn't mess up your formatting when you paste, make sure that you also use the Values option. Now, with no formulas at all, we have prices that are 10% higher.

You can use the same approach with dates. For example, suppose we want to adjust these dates forward by one week? Well, remember that dates are just serial numbers. So, one week equals seven days. To make the adjustment, I can enter 7 in a cell, and then copy. Then I follow the same process as before, but this time using the Add option.

This works for times as well. Suppose that we want to make a time-zone adjustment of + seven hours to these times. One hour equals 1/24. So, to get the equivalent of seven hours, I can just multiply that by 7. Now I have the decimal equivalent of seven hours in a cell, and I can use Paste Special to add that value to the times.

Keep this approach in mind whenever you need to make simple adjustments to numeric values, and you don't want to add extra formulas to a worksheet.

Course 

Related shortcuts

Author 
Dave Bruns

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.