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 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 multiples the existing price times 1.1 in another column. Then convert the new prices to values and 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 Paste Special which provides a set of math operations.

So, to increase prices by 10% in place, I can add 1.1 to 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 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. Suppose we want to adjust these dates forward by 1 week? Well, remember that dates are just serial numbers. So, 1 week equals 7 days.

I can just enter 7, copy. Then follow the same process as before, but this time using the Add option

This works for times as well.

Suppose we want to make a time-zone adjustment of + 7 hours to these times.

1 hour equals 1/24. So, To get the equivalent of 7 hours, I can just multiply this by 7. Now we have the decimal equivalent of 7 hours 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 numeric values and don't want add extra formulas to a worksheet.

Course 
Core Formula

Related shortcuts

Author 
Dave Bruns