Cellwise Try it free

Excel guide · 3 min read

How to multiply in Excel

Quick answerType = and join the cells with an asterisk, for example =B2*C2, then press Enter. To multiply a whole column by one fixed cell, lock that cell with dollar signs, =C2*$G$1, and fill down. To multiply two columns and add up the results in one step, use =SUMPRODUCT(B2:B6,C2:C6).

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCDEFG
1ProductQtyUnit priceSales tax0.0825
2Desk lamp1234.5Markup1.3
3Monitor arm589
4USB-C hub2027.99
5Keyboard864
6Webcam71.25

Method 1: the * operator

Excel has no MULTIPLY function. You use * between values, cells or both: =B2*C2, =B2*3, =B2*C2*G2. The x key does not work: =B2xC2 gives a #NAME? error.

Here each row is an order line. Put =B2*C2 in D2 and double-click the fill handle to copy it down.

=B2*C2

Result: 414

414: 12 desk lamps at 34.50 each.

Multiply a column by one fixed cell ($)

Prices are marked up by the factor in G2 (1.3, a 30% markup). If you write =C2*G2 and fill down, the next row becomes =C3*G3, which is empty, so every result after the first is 0. That is the most common multiplication mistake in Excel.

Lock the fixed cell with $: =C2*$G$2. The C2 part still moves down the rows, but $G$2 always points at the markup. Select G2 in the formula bar and press F4 to add the dollar signs.

=C2*$G$2

Result: 44.85

44.85, the marked-up desk lamp price. Filled down, every row uses the same 1.3 in G2.

Multiply by a percentage

Percentages are stored as decimals: 8.25% is 0.0825. To get the tax amount, multiply by the rate: =C2*$G$1. To get the price including tax, multiply by 1 plus the rate. For a discount, use 1 minus the rate.

Money results often have long decimals. Wrap the formula in ROUND so later totals add up to the cent, rather than only formatting the cell to 2 decimals.

=ROUND(C4*(1+$G$1),2)

Result: 30.3 shows as $30.30 with Currency format

30.3, the USB-C hub price with 8.25% tax. Without ROUND it is 30.299175.

Multiply and total in one step: SUMPRODUCT

To get the order total without a line-total column, SUMPRODUCT multiplies the two ranges row by row and adds the results. The ranges must be the same size. It works in every Excel version.

In Excel 365 you can also write =B2:B6*C2:C6 in one cell and the line totals spill down by themselves.

=SUMPRODUCT(B2:B6,C2:C6)

Result: 1930.8

1,930.80 for the whole order. The webcam adds nothing because its quantity is blank.

The PRODUCT function

=PRODUCT(B2,C2,$G$2) multiplies all its arguments, which is tidy when you multiply many cells or a whole range, such as growth factors in =PRODUCT(B2:B13). Unlike *, PRODUCT ignores empty cells and text inside a range instead of treating them as zero.

=PRODUCT(B2,C2,$G$2)

Result: 538.2

538.2: 12 lamps times 34.50 times the 1.3 markup.

Multiply cells in place with Paste Special

To change the numbers themselves, for example to raise every price by 5% without a helper column, use Paste Special. The result is plain values, not formulas, so keep a copy if you may need the old prices.

  1. Type 1.05 in an empty cell and copy it (Ctrl+C).
  2. Select the prices C2:C6.
  3. Right-click > Paste Special, choose Multiply under Operation, and click OK.
  4. Delete the 1.05.

Common mistakes

Results turn to 0 after filling down

The fixed cell moved with the formula. Use $G$2 instead of G2.

A blank cell gives 0

The webcam has no quantity, so =B6*C6 is 0. Show a blank until data arrives: =IF(B6="","",B6*C6).

Multiplying by 8.25 instead of 8.25%

If the rate cell shows 8.25 without a % sign, the formula multiplies by 8.25. Type 8.25% or 0.0825 in the cell.

#VALUE! from a number stored as text with a symbol

Text such as "34.50 USD" cannot be multiplied. Clean the cell so it holds only the number.

FAQ

What is the formula for multiplication in Excel?

Use the asterisk: =A1*B1. There is no MULTIPLY function; PRODUCT exists for multiplying many values at once.

How do I multiply a whole column by a number in Excel?

Put the number in one cell, then write =A2*$E$1 next to the first value and fill down. To change the values in place, copy the number and use Paste Special > Multiply.

How do I multiply two columns and get a total?

Use =SUMPRODUCT(B2:B6,C2:C6). It multiplies each pair of cells and adds the results in one formula.

How do I add a percentage to a number?

Multiply by 1 plus the percentage: =C2*(1+8%) or =C2*(1+$G$1) with the rate in G1.

Functions used

Related guides

Updated