For example, there are data such as the image below.
How is the fastest way to subtract numbers in column A with a number in cell B1?
Subtract Multiple Cells Using Formula
You need an absolute cell reference for subtracting numbers with a number. Make the cell reference of the deduction number absolute, to prevent the cell address changing when the formula is copied.
Here are the steps to make the formula:
- Place the cursor in cell C1
- Type the equal sign “=”
- Point the cursor to cell A1
- Type the minus sign “-“
- Point the cursor to cell B1, make it an absolute reference by pressing the F4 key once
- Press the Enter key
For other numbers, make a copy in cell B1 (CTRL+C), then paste (CTRL+V) in range C2:C10. The result is like the animation below.
All numbers in column C are numbers in column A subtracted by a number in cell B1.
Please read the article below for a more detailed explanation of absolute reference
Subtract Multiple Cells Using No Formula
The solution to subtract several numbers with a number without using a formula is to use special paste.
Here are the steps:
- Place the cursor in the subtraction number position (cell B1)
- Do a copy (CTRL+C)
- Make a range A1:A10, containing the numbers that will be subtracted
- Do a paste special (CTRL+ALT+V)
- Select “Subtract” in the Operation section
- Click the OK button
The result is as shown below. All numbers in column A automatically change.
Which One is the Best Solution?
Which is the best solution? It depends on your needs. If you want to keep the original number, using a formula solution is better for you. Otherwise, the paste special solution is better for you.
