The Excel workbook is included with our video training.

Abstract 

One of the cool things you can do with Paste Special is make "in place" changes to numbers. This is a handy way to quickly change things like dates, prices, and times without using formulas.

Transcript 

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.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.