Multiply a column by a number in Excel

When working with price lists, statistics, or other numerical data, you often need a simple operation — multiplying an entire column or range by a specific number. This page explains the easiest ways to do it.

Multiply a column by a number using standard Excel tools

There are two main ways to multiply a column or row by a number in Excel without add-ins: using formulas or using the Paste Special feature.

Using formulas

You can use the PRODUCT function, but it’s usually easier to use the asterisk (*) multiplication operator. PRODUCT multiplies all numbers in a range, and the result can change if you insert a column or row inside the range. With the asterisk, cells are multiplied individually, so inserting new columns or rows won’t affect the result.

To multiply an entire column by a number, you can copy the formula down the entire adjacent column.

Using Paste Special

This is a quick built-in option. First, copy the cell containing the number you want to multiply by. Then select your target column or range, right-click, and choose Paste Special → Multiply. Here’s an example:

Multiply a column in place with !SEMTools

To avoid creating extra columns and formulas, the !SEMTools add-in includes several procedures for multiplying a range in place, quickly and easily.

Multiply a column by a single number (constant)

The simplest and most common option. You can either select a cell containing the number or just type the number directly.

Multiply a column by a random number within a range

Useful when you need to randomize a dataset. This is handy for generating test data.

Multiply a column by a percentage

Multiplying by a percentage works the same as multiplying by a regular number. For example, multiplying by 120% to include tax is the same as multiplying by 1.2.

Why multiplying a column goes wrong: common mistakes

• The multiplier reference isn’t fixed. If the number sits in B1, the formula =A2*B1 turns into =A3*B2 when you fill it down. Fix the reference with dollar signs, =A2*$B$1, or press F4 right after selecting the cell.
• The formulas stay in place. If you delete the source column, every result turns into #REF!. Paste the results as values first, then delete the source.
• Percentages mixed up. To add 20%, multiply by 1.2 (or 120%). Multiplying by 20% leaves only a fifth of the original value.

In-place procedures avoid the first two problems by design: there are no formulas and no helper column to clean up.