Excel: Subtract the Same Value From Multiple Cells

How-to basics · Updated November 19, 2026

To subtract the same number from multiple Excel cells at once, copy that number, select the cells, then choose Home > Paste > Paste Special > Subtract. If you want to keep the original values and show the results elsewhere, use a formula such as =B2-$E$1 and fill it down.

Choose between changing cells and using formulas

There are two useful approaches, and the right one depends on what you need afterward. Paste Special changes the selected cells in place, which is convenient for a one-time adjustment. A formula calculates new results in other cells, leaving the source values available for checking, later edits, or recalculation.

  • Use Paste Special when the selected numeric values should be permanently reduced by a fixed amount and you do not need to preserve their original contents in those cells.
  • Use formulas when you need an auditable calculation, expect the amount to change, or want to retain the original data.

For example, if a list of prices needs a one-time $5 reduction, Paste Special is fast. If the reduction might later change to $7, a formula linked to a cell containing the reduction is easier to maintain. Before changing a large or important range, save a copy of the workbook or try the method on a small sample.

Subtract the amount in place with Paste Special

In Excel for Microsoft 365 on Windows, Paste Special can apply an arithmetic operation to the cells you select. The copied number acts as the amount to subtract; it is not pasted over the selected cells as an ordinary paste would be.

  1. Enter the amount to subtract in an unused cell. For example, type 5 in H1.
  2. Select H1 and copy it with Ctrl+C.
  3. Select the numeric cells to change, such as B2:B20. Avoid selecting a heading or other unrelated cells.
  4. Choose Home > Paste > Paste Special.
  5. In the Paste Special dialog, choose Subtract under Operation, then select OK.
  6. Check several results, then press Esc to clear the copy outline.

If B2 contains 28 and H1 contains 5, B2 becomes 23. The operation updates the chosen cells rather than creating a separate result column. On Windows, Ctrl+Alt+V opens the Paste Special dialog after you copy the amount. On Mac, use Home > Paste > Paste Special; shortcut assignments can vary with the keyboard and Excel setup, so the ribbon path is a dependable alternative.

Enter a positive amount for an ordinary reduction. Subtracting a negative number increases a value: subtracting -5 is mathematically the same as adding 5. If the selected range contains formulas, test on a copy first; an in-place operation is not the best way to preserve and document formula logic.

Use a formula to preserve the original values

A formula is usually the clearer choice when the original figures should remain untouched. Put the fixed adjustment in one cell, then refer to that cell with absolute references so it stays fixed as you copy the formula.

With the original numbers in B2:B6 and the amount 5 in E1, enter this in C2: =B2-$E$1. With B2 equal to 28 and E1 equal to 5, the result in C2 is 23. The dollar signs lock the reference to E1 when the formula is copied; B2 remains a relative reference and changes to B3, B4, and so on.

To apply the calculation to the remaining rows, select C2 and drag the fill handle, the small square at the lower-right corner of the selection, down to C6. You can also copy C2, select C3:C6, and paste. The resulting values update if you change E1, while the original values in column B remain available for comparison.

If the adjustment is a permanent constant and does not need its own cell, a formula can subtract it directly. With the original value in B2, enter =B2-5 in C2; if B2 is 28, C2 returns 23. Using a separate input cell is easier to audit and update, especially in a workbook that other people will use.

Apply one subtraction to separated cells

Sometimes the target cells are not adjacent—for example, values in B2, B5, and B9, with labels or notes between them. You can select multiple ranges by holding Ctrl while selecting additional cells or ranges on Windows, then use Paste Special as described above. On Mac, hold Command while adding separate selections. Check the highlighted selection carefully before applying the operation, because every selected numeric cell may be changed.

For separated cells where you want to preserve the source values, formulas are often safer. Put each result in a corresponding output cell and refer to the fixed adjustment cell, such as E1. Copy a formula to each needed row, or enter an individual formula for each target. This makes it easier to see which records were included and reduces the risk of changing an unintended cell in a scattered selection.

If the target cells are in different worksheets, consider whether the workbooks need a consistent, reviewable method. A formula on each sheet can point to a clearly labeled adjustment cell. For an occasional correction, use Paste Special only after checking the selected cells and confirming that the change should be in place.

What happens to blanks, formulas, and other data

Paste Special is best suited to a range of ordinary numeric values. Mixed ranges require care: a selection can contain blanks, text labels, formulas, or errors as well as numbers. Do not assume that applying an operation to a mixed selection will have the outcome you intend. Test a representative copy of the range, and use a formula column if you need a transparent way to inspect the results before replacing anything.

Text that looks like a number may be stored as text, especially when imported from another system. Such entries may not behave like numeric values in an arithmetic operation. If a formula returns a value error, check whether the source cells contain text, stray spaces, or an existing error. Converting imported data to real numbers may be necessary before doing the subtraction.

Dates are stored as serial numbers in Excel, so subtracting a number from a date can move it backward by that many days. For example, subtracting 7 from a valid date calculates a date seven days earlier, provided the result cell is formatted as a date. Percentages are also numeric values: subtracting 5 from a cell containing 20% gives a negative result because 5 is treated as 500%, not as five percentage points. To reduce 20% by five percentage points, the amount must be represented as 5%.

Check results and avoid common mistakes

  • Wrong range selected: Check the selection border and the name box before running Paste Special. A single extra row can alter a total or note unintentionally.
  • Wrong operation chosen: Confirm that the Operation setting says Subtract, not Add, Multiply, or Divide.
  • Amount copied incorrectly: Verify the source cell contains the intended number. Copying a cell with a formula or unexpected value can produce a different result than expected.
  • Unexpected negative results: Confirm the adjustment is not larger than the original values and that you did not enter a negative adjustment by mistake.
  • Formula reference moves: Use an absolute reference such as $E$1 for the shared amount. Without the dollar signs, the reference may shift as you fill the formula down.

For a quick validation, calculate one result by hand and compare it with the worksheet. If B2 is 28 and the reduction is 5, the result must be 23. For formulas, click a few output cells and inspect the formula bar to make sure each one refers to the intended source row and the same adjustment cell.

Undo, keep a backup, and choose a repeatable workflow

Immediately after an accidental Paste Special operation, Ctrl+Z on Windows or Command+Z on Mac will normally undo the change. Undo history can be lost after other actions or after closing the workbook, so do not treat it as a substitute for a backup. If the workbook is important, save a copy before a broad in-place edit. If you are uncertain, calculate results in a new column, review them, and only then decide whether to replace the original values.

For recurring work, keep the adjustment in a labeled cell and use formulas. That makes the amount visible, lets you change it once, and provides a record of how the result was calculated. For a one-off cleanup of confirmed numeric inputs, Paste Special is quicker. Both approaches are ordinary spreadsheet calculations; neither changes the underlying meaning of a value unless you choose the correct range and unit.

Frequently Asked Questions

Can I subtract the same number from an entire Excel column?
Yes. Enter and copy the adjustment amount, select the intended numeric cells in the column, then choose Home > Paste > Paste Special > Subtract. If the column has a heading or mixed content, select only the data rows rather than the whole column.

How do I subtract a number without changing the original cells?
Put the amount in a cell and use a formula in a separate column. For example, with the original value in B2 and the adjustment in E1, enter =B2-$E$1 in C2 and fill down. The source value remains unchanged.

Can I subtract different values from different cells?
Yes, but that is a different task from subtracting one shared amount. Put each adjustment beside its matching original value and use a row-by-row formula, such as subtracting the adjustment in C2 from the original in B2. Check the row alignment before filling the formula down.

Why did subtracting 5 from a percentage give an unexpected answer?
Excel treats 5 as the number five, equivalent to 500%, while 5% is stored as 0.05. If you mean five percentage points, enter the adjustment as 5% rather than 5. If you mean a relative 5% reduction, that requires a different calculation.

Can I subtract days from dates in Excel?
Yes. Excel dates are numeric serial values, so subtracting 7 from a valid date moves it seven days earlier. Format the result cell as a date if it displays as a number.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.