Excel Multiplication Formula: All Methods and Examples

The Excel multiplication formula sits at the heart of nearly every spreadsheet on earth. From a quick tax calculation to a complex financial model, the ability to multiply two or more numbers in a cell is the foundation that everything else builds on. Excel offers half a dozen different ways to multiply, each with its own advantages: the asterisk operator for simple cell-by-cell math, the PRODUCT function for ranges, SUMPRODUCT for weighted sums, Paste Special for in-place value updates and array formulas for industrial-strength calculations.

The simplest version is also the most common. Type =A1*B1 in any cell and Excel multiplies the value of cell A1 by the value of cell B1. Drag the fill handle down a column and Excel rewrites the formula for each row, multiplying A2 by B2, A3 by B3 and so on. Most spreadsheets you will encounter use this exact pattern, sometimes wrapped in conditional logic but built on the same =asterisk operator.

Beyond the asterisk, Excel ships with the PRODUCT function for multiplying a range of cells without typing each one. =PRODUCT(A1:A10) multiplies all ten values together, which is the multiplicative equivalent of SUM. SUMPRODUCT goes one step further, multiplying paired values from two ranges and summing the results — the formula that powers weighted averages, dot products, and any kind of unit-times-price calculation.

This guide covers every way Excel multiplies. We start with the basics, walk through the major functions with worked examples, cover the common errors (#VALUE, text-as-number, and the dreaded circular reference), and finish with advanced patterns: multiplying with absolute references, multiplying entire columns, multiplying with conditions, and using Paste Special to multiply existing values in place. By the end you will know which method to reach for in any situation and why.

The asterisk operator is the bedrock of Excel multiplication. Every formula starts with an equals sign — that is how Excel knows you are typing math instead of text — and the asterisk acts as the multiplication symbol. =5*3 returns 15. =A1*B1 returns the product of the values in those two cells. =A1*B1*C1 multiplies three values together. The order does not matter; A1*B1 and B1*A1 are identical because multiplication is commutative.

Excel respects standard order of operations. =2+3*4 returns 14, not 20, because Excel evaluates the multiplication before the addition. To override the default order, wrap the addition in parentheses: =(2+3)*4 returns 20. This matters more often than people expect, especially in financial models where you might multiply a sum of values by a single rate. Always parenthesize aggressively when there is any chance of ambiguity.

Mixing constants and cell references in a single formula is fine. =A1*0.075 multiplies the value of A1 by 7.5%. =A1*1.05 increases A1 by 5%. =A1*-1 flips the sign. Excel treats everything left and right of the asterisk as a number to multiply, whether that number is hard-coded, pulled from a cell, or returned by another formula. The only restriction is that both sides must evaluate to numbers — text values produce a #VALUE! error.

Filling a formula down or across is the move that turns a single multiplication into a column of products. Type =A1*B1 in cell C1, then double-click the small green square at the bottom-right of the cell (the fill handle) to extend the formula to the bottom of the adjacent data column. Excel automatically increments the row references, so C2 becomes =A2*B2, C3 becomes =A3*B3, and so on. The keyboard equivalent is to select C1:C100 and press Ctrl+D.

The PRODUCT function exists for the same reason SUM does — typing =PRODUCT(A1:A10) is much easier than =A1*A2*A3*A4*A5*A6*A7*A8*A9*A10. The function accepts up to 255 individual arguments or ranges, and it multiplies them all together to produce a single result. Empty cells, text cells and logical TRUE/FALSE values are ignored, so a column with the occasional gap still produces a clean product without errors.

One subtle behavior: PRODUCT treats text values that look like numbers as numbers, but text that does not look like a number is silently skipped rather than causing an error. =PRODUCT(2, "3", 4) returns 24. =PRODUCT(2, "three", 4) returns 8 (the text is ignored). This is the opposite of how the asterisk operator handles text, which throws #VALUE! immediately. Both behaviors are by design and both are sometimes useful.

Mixing references and constants in PRODUCT is fine. =PRODUCT(A1:A10, 0.95) multiplies all ten values together and then by 0.95, which could represent a discount rate, a tax exclusion, a confidence factor — whatever the model needs. Using PRODUCT this way is shorter than wrapping the entire range in a multi-line asterisk chain and easier for a reviewer to read.

SUMPRODUCT is the function that pays the rent for accountants. Given two ranges of equal size, =SUMPRODUCT(A1:A10, B1:B10) multiplies the first cell of range A by the first cell of range B, the second by the second, and so on, then adds all the products. This is exactly what you want when column A is units sold and column B is unit price — the result is total revenue. SUMPRODUCT collapses what would otherwise need a helper column into a single elegant formula.

Absolute references with the dollar sign are the difference between a multiplication formula that breaks when you copy it and one that works. =A1*B1 has both references relative — copy it down a column and Excel changes both row numbers in lockstep. =A1*$B$1 keeps B1 fixed; copying down produces =A2*$B$1, =A3*$B$1, =A4*$B$1 and so on. Use absolute references for any value that should stay constant across the copy.

Mixed references are even more powerful. =$A1*B$1 locks the column of A and the row of B. Copying that formula across a grid creates a complete multiplication table, with row labels in column A and column labels in row 1. This pattern is how multiplication tables, sensitivity tables, what-if matrices and most two-dimensional models get built. The F4 key cycles through reference types as you type — press it once for full absolute, twice for row-only, three times for column-only.

For multiplying entire columns at once, modern Excel offers two solutions: array formulas and dynamic arrays. The legacy approach was to enter a multiplication formula with Ctrl+Shift+Enter, which created a so-called "array formula" that returned multiple results in adjacent cells. The modern approach in Excel 365 uses dynamic arrays — type =A1:A10*B1:B10 in a single cell, press Enter, and Excel "spills" the results into the cells below automatically.

Spilled results are a feature, not a bug. The original cell contains the formula and the cells below show the spilled values; clicking any spilled cell shows the formula in dimmer text indicating that it is part of the spill range. Edits to the formula update all spilled values at once, and inserting a row in the source data automatically extends the spill. The new dynamic-array model is the default in Excel 365 and Excel 2021; older versions still need Ctrl+Shift+Enter.

The text-as-number problem deserves a closer look because it bites everyone eventually. When you import data from a CSV or paste it from a webpage, numbers can arrive as text. They look the same on screen — the cell shows 123 — but Excel internally treats them as a string. The asterisk operator returns #VALUE! when one operand is text. The fix is to convert the text to a number, either by retyping the cells, using VALUE(), or running Data > Text to Columns and finishing the wizard immediately to force a re-parse.

A faster trick is to copy any blank cell, select the text-numbers, and use Paste Special > Add. Excel adds zero to each cell, which forces Excel to evaluate the cell as a number. Now the multiplication formula works. The same trick with Paste Special > Multiply (using a 1) achieves the same conversion. These are real spreadsheet hygiene tools that experienced users reach for several times a week.

Paste Special > Multiply has a use of its own beyond fixing data types. Suppose your column of prices needs to be increased by 5% in place — no helper column, no new formula. Type 1.05 into any blank cell. Copy it. Select the price column. Right-click and choose Paste Special, then Multiply, then OK. Every selected cell is now multiplied by 1.05 in place. Delete the original 1.05 cell when done. The column shows the increased values directly.

Paste Special > Multiply is also how you flip the sign of a column quickly. Type -1 into a cell, copy, select the target range, Paste Special > Multiply. Every value flips sign. The same trick with 1000 converts thousands to ones; with 0.001 converts ones to thousands. It is a deceptively powerful one-step transformation that does not require writing or storing a formula.

For very large datasets, the choice of multiplication method affects performance. Asterisk and PRODUCT are equally fast for thousands of rows. SUMPRODUCT is fast for tens of thousands but slows down for ranges in the hundreds of thousands because it processes the entire range as an array. The dynamic-array equivalent =A:A*B:B used over whole columns is even slower than SUMPRODUCT because it works across more than a million rows. Limit the range to the actual data and these performance issues vanish.

Power Query is the right tool for very large data multiplication. Instead of writing a formula on the worksheet, load the data into Power Query, add a custom column with the multiplication, and load the result back. Power Query processes data row-by-row in memory and writes the final values to the sheet, bypassing the recalculation engine that slows large worksheets. For datasets above 100,000 rows this can be ten to fifty times faster than worksheet formulas.

For multiplying with conditions, SUMPRODUCT shines. =SUMPRODUCT((A1:A100="East")*B1:B100*C1:C100) returns the sum of products only for rows where column A equals "East". The Boolean comparison converts to 1 or 0, which when multiplied by the product of B and C zeros out the unwanted rows. This pattern predates SUMIFS by many Excel versions but still appears in legacy models because it is more flexible.

Modern Excel users would write the same calculation as =SUMPRODUCT(--(A1:A100="East"), B1:B100, C1:C100) to keep the function arguments explicit. The double-negative coerces the Booleans to 1/0 and arguably reads more clearly. Both formulas return the same result. Choose the form that matches the surrounding code in the workbook so reviewers can scan rapidly.

Multiplying with named ranges instead of cell references is a habit worth building for any model that will be maintained over time. Define a name like "TaxRate" pointing at cell D1. Now write =A1*TaxRate instead of =A1*$D$1. Anyone reading the formula understands what the second value represents without having to navigate to D1 to see what it contains. Names live under Formulas > Name Manager and can be defined for cells, ranges or formulas.

Excel tables (Insert > Table or Ctrl+T) take this further with structured references. In a table called "Sales," you can write =Sales[Units]*Sales[Price] in a calculated column, and Excel applies the formula to every row of the table. New rows pick up the formula automatically. Structured references are self-documenting and survive insertion or deletion of columns better than absolute cell references.

For models that span multiple sheets, multiplication formulas reference cells with the sheet name prefix. =Inputs!A1*Rates!B1 multiplies a value from the Inputs sheet by a rate from the Rates sheet. If the sheet name contains a space or special character, Excel wraps the name in single quotes: ='Q1 Inputs'!A1*Rates!B1. The 3D reference =SUM(Sheet1:Sheet5!A1) works for SUM but not for arbitrary multiplication; use a sequence of explicit sheet references instead.

For very precise calculations, be aware of Excel's floating-point arithmetic. The result of =0.1*3 is technically 0.30000000000000004 in IEEE 754 double precision, although Excel rounds the displayed value to 0.3. For most business purposes this is invisible, but financial calculations involving long chains of multiplications can accumulate small errors. Wrap critical calculations in ROUND() at the precision the business actually cares about — typically two decimal places for currency.

Examples from the wild: a small-business invoice template usually contains =B5*C5 in column D for line totals, then =SUM(D5:D20) at the bottom for the subtotal, =D21*0.075 for the tax, and =D21+D22 for the grand total. That pattern repeats across millions of templates, all built on the asterisk operator with one fixed-rate multiplication for tax. Add a column for tax-inclusive line totals and you might use =B5*C5*1.075 inline, although a separate tax row is generally clearer for the reader.

Project finance models use SUMPRODUCT extensively. The annual cash flow of a project might be =SUMPRODUCT(VolumeRow, PriceRow) - SUMPRODUCT(VolumeRow, CostRow), giving a single-cell answer for revenue minus cost across all months. Discounting follows with =NPV(rate, cashflows), but the period-by-period multiplications happen in SUMPRODUCT before they reach NPV. Recognize the pattern in any production-company model and the formulas read themselves.

Retail and inventory systems use the same multiplication building blocks. A reorder-point model multiplies daily demand by lead-time days and adds safety stock; an inventory valuation multiplies on-hand units by unit cost. Sales-tax calculations multiply line totals by jurisdiction-specific rates pulled from a lookup table. Excel's flexibility lets a single workbook handle these radically different domains using exactly the same operator and three or four core functions, which is why the multiplication formula remains the most-typed formula on earth.