How to Multiply Cells in Excel: Asterisk, PRODUCT and Paste Special

You have a price column and a quantity column, and you need a third column that shows the line total per row. You have a list of one hundred numbers, and you want a single cell that returns their product. You have a column of dollar prices, and you need to convert every value to euros by multiplying the whole range by 0.92 — without writing a single formula. Three different multiplication tasks, three different tools, all already built into Excel.

The short version. For two cells, type =A1*B1 in a third cell and press Enter. The asterisk is Excel's multiply operator. For a range of cells, use the PRODUCT function: =PRODUCT(A1:A10) returns A1 times A2 times A3 all the way through A10. For multiplying every value in a range by the same number — and writing the answer back in place — copy the multiplier, select the target range, then Paste Special > Multiply. No formula left behind, just the new values.

Most people only ever learn the first one. The asterisk works fine for line-by-line math, gets you 70% of the way there, and is the answer to almost every "how do I multiply two cells in Excel" question. But the moment your list is more than five rows long, or you want to multiply ten different ranges of unequal length, or you want to scale a column without leaving a formula trail behind, the asterisk runs out of road. That is where PRODUCT, SUMPRODUCT, and Paste Special take over.

This guide walks through all three approaches end to end. We will cover the asterisk and how to AutoFill it down a column, the PRODUCT function for whole ranges, SUMPRODUCT for the row-by-row pattern (price times quantity, summed), Paste Special > Multiply for in-place scaling, and the small handful of errors that show up — #VALUE! when a cell holds text, sign flips with negative numbers, and the percentage gotcha that catches people every single time.

Start with the simplest case. Two cells, one product. Suppose A1 holds 12 and B1 holds 5. Click a third cell — say C1. Type an equals sign, then click A1, then type an asterisk, then click B1. Your formula bar should read =A1*B1. Press Enter. C1 now shows 60. That is the whole pattern. The equals sign tells Excel you are writing a formula instead of plain text. The asterisk is the multiplication operator. The two cell references are the values being multiplied.

You can type the references by hand instead of clicking — =A1*B1 works exactly the same — but clicking is usually faster and you cannot fat-finger a cell reference that way. You can also mix references and hard-coded numbers freely. =A1*5 multiplies whatever is in A1 by five. =12*5 just returns 60 without using any cell references at all, which is fine for one-off math but defeats the point of putting numbers in a spreadsheet.

So how do you multiply two cells in Excel when you actually want a column of line totals, not just one answer? You write the formula once for the first row, then drag it down. Click C1 (the cell with the formula). Hover over the small green square at the bottom right corner — that is the fill handle.

The mouse cursor turns into a thin black cross. Click and drag down to C10. Excel copies the formula into every cell, adjusting the row references automatically — C2 becomes =A2*B2, C3 becomes =A3*B3, all the way down. Ten rows, ten line totals, one drag. That is the entire pattern for how to multiply cells in Excel across a list.

If you have a long list and dragging gets tedious, there is a faster shortcut: double-click the fill handle instead of dragging. Excel auto-fills the formula down as far as the adjacent column has data. Five thousand rows of A and B values? Click C1, double-click the fill handle, done. The formula fills all the way to row 5000 in a single click. This is the move that turns line-by-line multiplication from "slow" into "instant."

One thing worth knowing about cell references. The way Excel adjusts =A1*B1 into =A2*B2 when you fill down is called relative referencing. The references move with the formula.

If you ever want a reference that does not move when you copy a formula — for example, multiplying every row by a single tax rate stored in cell F1 — you anchor the reference with dollar signs: =A1*$F$1. The $F$1 stays the same as you fill down, while A1 still becomes A2, A3, A4. This is called an absolute reference, and we will use it again under Paste Special below.

Now suppose the list is longer and you want a single number — not a column of line totals, just one cell that shows the product of every value in a range. The asterisk approach falls apart fast. You could write =A1*A2*A3*A4*A5*A6*A7*A8*A9*A10, and it would work, but past five cells it is unreadable and a single typo silently breaks it. The fix is the PRODUCT function. PRODUCT takes a range and multiplies everything in it. =PRODUCT(A1:A10) returns A1 times A2 times A3 times A4, all the way through A10, with one tidy formula.

The PRODUCT function accepts up to 255 arguments. Most of the time you pass it one or two ranges, but it is happy to take a mix — =PRODUCT(A1:A5, C1:C5, 2) multiplies everything in A1:A5, everything in C1:C5, and the number 2, all together.

Empty cells in the range are ignored (treated as nothing, not as zero — important difference). Text values are ignored too, which is convenient when your range mixes labels and numbers. Logical values TRUE and FALSE are treated as 1 and 0 respectively, which is rarely what you want, so keep ranges clean if you mix data types.

So when do you reach for PRODUCT instead of the asterisk? Whenever you want the cumulative product of more than three or four cells, or whenever the cells you are multiplying live in a contiguous range. =PRODUCT(B2:B20) is dramatically clearer than nineteen asterisks. PRODUCT also handles edge cases gracefully — add a row to the middle of B2:B20 and the formula still works, because the range B2:B20 quietly grows. The asterisk chain would need to be edited by hand.

How do I multiply cells in Excel using PRODUCT when the cells are not next to each other? Pass multiple arguments separated by commas. =PRODUCT(A1, A5, A10) multiplies just those three specific cells. =PRODUCT(A1:A5, C1:C5) multiplies every value in both ranges together — ten values, one product. Comma-separated arguments inside PRODUCT are how you assemble non-contiguous selections into a single calculation.

SUMPRODUCT is the function people overlook for years, then suddenly use everywhere once they learn it. The setup: you have a column of unit prices in B2:B20 and a column of quantities in C2:C20. You want the grand total — every price times its quantity, all summed up — in a single cell, without building a helper column of line totals first. The formula is =SUMPRODUCT(B2:B20, C2:C20). Excel multiplies B2 by C2, B3 by C3, B4 by C4, all the way down, then adds the nineteen line totals into one number. One formula, one cell, no extra columns.

This is the elegant pattern for invoice totals, weighted averages, and anything where the structure is "multiply this by that, row by row, then sum." Compared with building a helper column of =B2*C2 values and then SUMming the helper, SUMPRODUCT is cleaner, takes up zero columns, and updates automatically when you add a row at the bottom of the data (provided your ranges are big enough to include the new row, or you convert the range to a table).

One catch: SUMPRODUCT requires the ranges to be the same shape — same number of rows, same number of columns. If B2:B20 has 19 cells and C2:C21 has 20, you get a #VALUE! error. Excel will not silently guess what to do with the mismatched cell. Always check that both ranges end on the same row before pressing Enter.

SUMPRODUCT can also take more than two ranges. =SUMPRODUCT(A1:A10, B1:B10, C1:C10) multiplies all three ranges row by row, then sums. Rarely needed in everyday work, but occasionally exactly the right answer — for example, if you have prices, quantities, and a discount factor all stored as columns, SUMPRODUCT calculates the discounted total in one shot.

Now the Paste Special trick. Suppose you have a column of dollar prices in B2:B100 and you need to convert every one to euros by multiplying by 0.92. You do not want a second column of formulas — you want the existing column to just hold the new euro values directly. This is what Paste Special > Multiply does. It is one of Excel's quietest power features, and once you see it the first time you will use it forever.

The steps. First, type your multiplier (0.92 in our example) into any empty cell — say D1. Click D1 and press Ctrl+C to copy it. Now select the range you want to scale: B2:B100. Right-click anywhere in the selection and pick Paste Special from the menu (or press Ctrl+Alt+V). The Paste Special dialog opens. In the Operation section, click the Multiply radio button. Click OK. Every value in B2:B100 is replaced with itself times 0.92. No formula bar, no helper column — the cells now hold the new euro values directly.

This works for any constant — divide by 100, multiply by 1.05 for a 5% increase, multiply by -1 to flip signs across a column. The pattern is always: copy the multiplier, select the target range, Paste Special > Multiply, OK. Total time: about four seconds once you have done it twice.

A few small details that come up in practice. The cell you copy must hold a number, not a formula whose result is a number (well, a formula works too, but the underlying cell must evaluate to a number, not text). The target range can contain other numbers, including ones from existing formulas — Paste Special > Multiply replaces those formulas with the computed-times-multiplier value. If you want to preserve the formulas, do the multiplication in a separate column with a real formula instead.

What about errors? Two common ones with Paste Special. First, if the target range contains text, Excel quietly skips those cells — no error, but the text stays untouched, which is usually exactly what you want. Second, if you forget to copy the multiplier first, Paste Special > Multiply uses whatever happens to be on the clipboard, which on rare occasions is a previously copied cell from elsewhere. Always copy the multiplier fresh just before opening the dialog. Three seconds of habit saves a lot of confused troubleshooting.

A few more patterns that come up when you actually use multiplication day to day in Excel. The percentage gotcha is the most common one. You want to add 8% sales tax to a column of prices. You type =A1*8% — Excel converts 8% to the decimal 0.08 internally, so the result is A1 times 0.08, which is the tax amount, not the total. To get the total with tax included, the formula is =A1*1.08 or equivalently =A1*(1+8%). The naming convention catches people the first time and never again, but it catches everyone once.

Negative numbers behave exactly as you would expect. =A1*-1 flips the sign of A1. =(-5)*(-3) returns positive 15. =PRODUCT(-2, -3, -4) returns -24, because the product of three negatives is negative. There is no special handling — Excel just follows ordinary sign rules. The only quirk: if you display a negative result with custom formatting that uses red parentheses, the cell shows (24) instead of -24, which can momentarily confuse a reader. The underlying value is still a normal negative number, just formatted differently.

Rounding sometimes surprises people. =A1*B1 where A1 is 0.1 and B1 is 3 returns 0.30000000000000004 in some calculations, because of how floating-point math works under the hood. Excel normally rounds away the trailing digits when it displays the result, but you can see them by widening the column or by checking the cell with =A1*B1=0.3, which returns FALSE in pathological cases.

The fix is to wrap the formula in ROUND: =ROUND(A1*B1, 2) forces the result to two decimal places, which is usually what you want for currency anyway. Worth knowing if you ever see a financial total that is off by one penny — it is not a bug, it is binary representation.

How do I multiply two cells in Excel and apply formatting to the result? Easy. After the formula returns the number, click the formula cell, then on the Home tab click the number-format dropdown and pick Currency, Percentage, Date, or Number with the decimal precision you want. Or press Ctrl+1 to open the full Format Cells dialog. Formatting is independent of the formula itself — you can multiply two raw numbers and display the result as $1,234.56, or as 1.23K, or as 123,456%, depending on what the report needs.

And how do you multiply two cells in Excel when one of them lives on a different sheet? Cross-sheet references use the syntax SheetName!CellRef. So a formula in Summary!C1 that multiplies Sales!A1 by Inventory!B1 is =Sales!A1*Inventory!B1. Sheet names with spaces need single quotes: ='Q1 Sales'!A1*'Q1 Inventory'!B1. Multi-sheet workbooks rely on this pattern constantly, and it is exactly as straightforward as it looks.

One pattern worth a careful look is multiplying an entire column by a constant stored in another cell — say a tax rate or an exchange rate. You have prices in B2:B100 and the exchange rate in cell E1. You want C2:C100 to hold the converted values. Type =B2*$E$1 in C2, then double-click the fill handle.

The dollar signs anchor E1 so it does not drift to E2, E3, E4 as the formula fills down — every row pulls from the same constant. This is the everyday use of absolute references, and it is the pattern that replaces dozens of repeated values with a single source of truth.

Change E1 from 0.92 to 0.95 and every cell in C2:C100 recalculates instantly. That is the part Paste Special > Multiply does not give you — Paste Special bakes the new values into the cells permanently. The formula approach with $E$1 stays live, so you can adjust the multiplier later. Pick the right tool: Paste Special when you want a one-time conversion with no formula footprint; a formula with $E$1 when you want the relationship to stay editable.

How do you multiply cells in Excel across rows instead of down columns? Same idea, flipped. If your data is in row 1 and row 2 — say A1:E1 holds prices and A2:E2 holds quantities — the formula =A1*A2 in A3 multiplies the first column. Drag the fill handle to the right instead of down, and B3 becomes =B1*B2, C3 becomes =C1*C2, and so on. Excel does not care whether you are working in columns or rows; the relative-reference rules apply equally in both directions.

One pattern that is often overlooked: multiplying every cell in a 2D range by a single number with a single formula. Suppose you have a 5×5 grid in A1:E5 that you want scaled by 1.1 into A7:E11. You can use a dynamic array formula in modern Excel (365 or 2021+): in A7 type =A1:E5*1.1 and press Enter.

The whole 5×5 result spills automatically into A7:E11. No drag, no fill handle, one formula covers all 25 cells. This is the cleanest scaling approach if your Excel version supports dynamic arrays — and if not, the absolute-reference fill-down approach above gets you the same result with two extra keystrokes.

One last family of patterns — multiplication combined with other functions to build slightly fancier calculations. The pattern shows up constantly in real-world workbooks, so it is worth a quick tour even if you do not need it today.

Multiplying with a conditional. Suppose you want to multiply A1 by B1 only when B1 is greater than zero, and return zero otherwise. The formula is =IF(B1>0, A1*B1, 0). IF checks the condition first, then chooses between the multiplication and zero. This is the building block for conditional totals — only multiply if a flag is set, only multiply if the quantity is positive, only multiply if the row is approved.

Multiplying with SUMIF. The classic pattern is multiplying a range by a constant only for rows that match a category. For example, total sales for product "Widget" with quantities in B and unit price 5: =SUMIF(A:A,"Widget",B:B)*5. SUMIF returns the sum of quantities matching the category, then the asterisk multiplies that sum by the unit price. For variable per-product prices you would use SUMPRODUCT with a conditional array — but SUMIF times a constant is the simpler everyday pattern.

Multiplying inside an array formula. Modern Excel (365/2021) handles arrays gracefully. =SUM(A1:A10*B1:B10) typed and pressed Enter (no Ctrl+Shift+Enter needed in modern versions) multiplies the two ranges element-wise and sums the result — exactly equivalent to SUMPRODUCT. In older versions of Excel you would press Ctrl+Shift+Enter to commit the formula as an array. Both produce the same answer; the modern syntax is cleaner.

Multiplying for unit conversion. A column of values in centimeters, scaled to inches: =A1/2.54 (division is multiplication by 1/2.54). A column in pounds, converted to kilograms: =A1*0.4536. A column of UK prices in pence, converted to pounds: =A1/100. Unit conversion is just multiplication or division by a constant, and the absolute-reference pattern (=A1*$E$1 with the constant in E1) keeps everything tidy.

To recap. Four ways to multiply in Excel, and you reach for a different one depending on the shape of the problem. For two cells, =A1*B1 with the asterisk operator — fastest for line-by-line totals, fills down a column with one drag of the handle. For a whole range, =PRODUCT(A1:A10) — cleaner than a long asterisk chain, ignores text values, accepts up to 255 arguments.

For pairwise multiplication followed by a total, =SUMPRODUCT(B2:B20, C2:C20) — the right answer to every "price times quantity, all summed" question. For scaling a column in place by a constant, copy the multiplier, select the range, Paste Special > Multiply — no formula left behind, just the new values.

The two patterns that catch people. First, percentages: =A1*8% returns the tax amount (8% of A1), not the total. For the total use =A1*1.08. Second, #VALUE! errors when a range contains text — the asterisk operator and SUMPRODUCT throw the error, while PRODUCT silently skips text cells. If you get unexpected results, check whether your range has a stray label or a leading space hiding in one of the cells.

The whole feature is small once you have used it a dozen times. The asterisk handles 70% of cases, PRODUCT and SUMPRODUCT handle most of the rest, and Paste Special > Multiply is the niche tool that suddenly becomes essential the day you need to convert a column of values in place without leaving a formula trail. Between all four methods, every "how do I multiply cells in Excel" question that ever shows up at work has a one-line answer.