If you need to modify multiple numbers at once, you can use the Paste Special feature of Excel. Although pasting only values or formatting are more common use cases of the Paste Special feature, it has more tricks in its pocket. In this guide, we’re going to show how to use Paste Special to multiply numbers in Excel.
Paste Special Basics
Paste Special dialog can be accessed from the right-click context menu, or by pressing the Ctrl + Alt + V key combination instead of just Ctrl + V when pasting data.
The dialog contains all available paste methods. The options we are going to need for the purposes of article are under the Operation section.
Paste Special supports 4 main operations:
You can select only one operation at a time when pasting. Excel adds the copied cell value or formula to the target cells with the selected operation. Let’s see this functionality with some examples.
How to use Paste Special to multiply numbers in Excel
Let’s say you have a set of cells that you want to multiply by 1.1. For this, do the following:
- Type 1.1 to a cell
- Copy the 1.1 cell
- Select the range of numbers
- Press Ctrl + Alt + V
- In the Paste Special dialog, select Multiply
- Click OK
All values in the range will be updated with their multiplied results. For example, 85.5 instead of 78.
How to use Paste Special to modify the nth number
What if you want to change the every nth number of a list? All you need to do is to create a range which replicates the single period of the cycle. For example, if you want to modify every 4th number, place 1 into the first 3 cells and actual modifier into the 4th.
- Copy the range
- Select the numbers you want to modify
- Paste special with multiply modifier
The copied range will be multiplied repeatedly. Because 1 is a multiplicative identity, the first 3 numbers of every 4-number remain the same.