Multiply a Whole Range by One Value with Paste Special

You need to raise every price in a column by 5%, convert a range from thousands to units, or apply an exchange rate. The obvious route is a helper column of formulas, then copy, paste-as-values, delete the helper. Paste Special does the same job in one step, directly in the original cells.

The steps

1. Type the multiplier into any empty cell — 1.05 for a 5% increase, 1000 for thousands to units, 0.92 for a rate.
2. Copy that cell (Ctrl+C).
3. Select the range you want to change.
4. Open Paste Special with Ctrl+Alt+V.
5. Under Operation choose Multiply, then click OK.
6. Delete the helper cell holding the multiplier.

Every selected value is now multiplied in place. The same Operation box also offers Add, Subtract and Divide, which handle “add 10 to every value” or “convert from cents” just as quickly.

Three things worth knowing

• It rewrites the cells. Constants become new constants; there is no formula to audit afterwards and no undo once the file is saved and closed. Work on a copy if the numbers matter.
• Cells containing formulas are modified, not replaced. A cell holding =B2*C2 becomes =(B2*C2)*1.05. That is usually what you want, but check a few cells before trusting a large range.
• Text and empty cells are skipped, so a range that mixes labels and numbers survives intact.

Flipping every sign to negative

The most-searched version of this trick. Accounting exports often arrive with credits as positive numbers when your model needs them negative.

Put -1 in an empty cell, copy it, select the range and Paste Special → Multiply. Every value flips sign in place, and running it a second time flips them back — handy when you are not sure which convention the export used.

The rest of the Paste Special dialog

The Operation box is the half people miss, but the Paste column is worth knowing too.

• Values (Ctrl+Alt+V, then V) — strips formulas and keeps results. The most-used option in the dialog.
• Formats — copies appearance only, a Format Painter that works across workbooks and non-adjacent ranges.
• Column widths — the fix for a pasted table that lands with the wrong layout.
• Transpose — turns a row into a column. Combine it with Values to transpose without dragging formula references along.
• Skip blanks — merges one range into another without the empty cells of the source wiping out existing data underneath.
• Copying a filtered list? Paste Special cannot help there; select visible cells only before you copy, or the hidden rows come along.

When a formula is the better answer

Paste Special is a one-off edit. If the multiplier changes — a rate that is revised each quarter, a percentage under discussion — burning it into the values means redoing the whole operation, and you have no record of what was applied.

In that case keep the rate in its own labelled cell and reference it with an absolute reference:

=B2*$F$1

Change F1 and the whole column follows. Use Paste Special for cleaning up imported data once; use a formula for anything that will be revisited. And when you need the multiplication only to reach a total, SUMPRODUCT does it without touching the data at all.

If nothing happens

Three causes. The Operation options are greyed out because you copied a range rather than a single cell — Multiply needs one source value. The marching-ants border round the copied cell has disappeared, meaning the clipboard was cleared; copy again. Or the target values are text rather than numbers, in which case they are skipped silently. Left-aligned numbers are the tell.

Paste Special is one of the named objectives in Microsoft’s MOS Excel Associate exam. Our MO-210 exam guide lists the full set.