Multiplication in Excel
Four jobs get called multiplication and the asterisk covers only one: a pair of cells, a whole range, row by row then totalled, or the values in place. PRODUCT is the range form, and it differs from the operator on imperfect data — it ignores text and empty cells inside a range where =A2*B2 returns #VALUE! as soon as one cell holds text. SUMPRODUCT multiplies two columns row by row and adds the results in one cell, and Home > Paste > Paste Special > Multiply rewrites the stored numbers with no formula left behind. The failure that fills a column with zeros is none of these: it is multiplying by one fixed cell without locking it, so =A2*D1 filled downward becomes =A3*D2. Write =A2*$D$1.
The formula
=A2*B2 two cells
=A2*$D$1 a column times ONE fixed cell — the dollar signs are what survive a fill-down
=PRODUCT(A2:A10) a whole range, ignoring any text or empty cells inside it
=PRODUCT(A2:A10, C2) ranges and single cells mixed in one call
=SUMPRODUCT(B2:B20, C2:C20) multiply row by row, then add the results: quantity x price in a single cell
=$A2*B$1 one formula for an entire times table, filled right and down
=ROUND(A2*B2, 2) money: multiply first and round second, or the pennies drift across a totalA worked example
B2:B21 holds twenty quantities and C1 holds a single unit price of 4.75. Column D should carry the line total for each row.
=B2*$C$1 in D2, filled down to D21
Each row shows its own quantity multiplied by 4.75. Written as =B2*C1 the reference walks down with the fill — D3 becomes =B3*C2, D4 becomes =B4*C3 — and because C2 and C3 are empty, every row below the first reads 0. The first cell is correct, which is precisely why the mistake survives a glance down the column. Pressing F4 (Command+T on a Mac) with the cursor on C1 while writing the formula inserts both dollar signs.
Which one do I need?
Frequently asked questions
Is there a multiplication function in Excel?
PRODUCT, although it is not usually the right answer. =PRODUCT(A2:A10) multiplies a whole range in one argument, while =A2*B2 is shorter and clearer for two or three cells. The real difference shows up on imperfect data: PRODUCT ignores text and blanks it finds inside a range, whereas the asterisk returns #VALUE! as soon as one referenced cell holds text.
Why does my multiplication column return zeros after the first row?
The fixed cell was not locked, so it moved as the formula was filled. =A2*D1 becomes =A3*D2 on the next row and D2 is empty, which Excel reads as zero. Lock it as $D$1 — put the cursor on the reference and press F4, or Command+T on a Mac — and every row multiplies by the same cell. A column of zeros under one correct value is almost always this.
How do I multiply a column by one number without adding a formula?
Type the multiplier into a spare cell and copy it, select the cells to change, then Home > Paste > Paste Special > Multiply > OK. Excel rewrites the values in place, so nothing is left pointing at the multiplier and you can delete it afterwards. The trade-off is that the change is an edit like any other: once the file is saved and closed there is no undoing it and no record of what the numbers used to be.
How do I multiply and add in one step?
=SUMPRODUCT(B2:B20, C2:C20) multiplies each row's quantity by its price and adds the twenty results, which is a helper column and a SUM in one cell. The ranges must be the same shape or it returns #VALUE!. Conditions fit inside it too: =SUMPRODUCT((A2:A20="West")*B2:B20*C2:C20) totals only the rows whose region matches.
New guides and tools, once a month
DE + EN · double opt-in · no spam