How to Multiply in Excel: 5 Methods (Formulas & Tips)

How to Multiply in Excel: An Overview

Multiplying in Excel is something you'll do constantly — calculating totals, applying tax rates, scaling values, combining unit prices with quantities, or applying percentage adjustments across a column of figures. Excel offers several different ways to multiply, each suited to different situations. The simplest is the asterisk operator (*) in a formula; the most powerful is SUMPRODUCT for multiplying and summing arrays in a single step. Understanding when to use each method makes your spreadsheets faster to build and easier to maintain.

The core principle is the same regardless of which multiplication method you choose: Excel multiplies the values you specify and returns the result in the cell containing the formula. You can multiply individual cells (=A1*B1), a cell by a fixed number (=A1*10), a cell by a percentage (=A1*1.2), or entire arrays of cells at once. The method you choose determines how flexible the formula is, how easily it copies to other cells, and whether the result updates automatically when the source data changes.

This guide covers five distinct multiplication methods in Excel with step-by-step instructions and practical examples: the asterisk operator for simple multiplication, the PRODUCT function for multiplying multiple values, Paste Special for applying a fixed multiplier to existing data without formulas, SUMPRODUCT for weighted calculations and array multiplication, and array formulas for multiplying ranges. Each method has clear use cases, and knowing which to reach for in which situation will make your work in Excel substantially faster. For a broader foundation in Excel formulas, the formulas guide covers the full range of mathematical, lookup, and logical functions.

One thing that trips up many users is how Excel handles cell formatting in multiplication. If a cell is formatted as Text rather than Number, Excel may treat the value as a string and return a #VALUE! error when you try to multiply it. Always verify that cells containing values you intend to multiply are formatted as Number, Currency, or General — never as Text. You can check and fix cell format quickly by selecting the cells, right-clicking, and choosing Format Cells. Converting text-formatted numbers to proper real numbers is covered in the troubleshooting checklist below.

Multiplying Cells, Columns, and Ranges in Excel

The most practical multiplication task in Excel is multiplying two columns — unit price by quantity, hours by rate, or score by weight. The standard approach is to write a formula in the first result row and copy it down the column. In a table with unit prices in column B (rows 2-100) and quantities in column C, type =B2*C2 in D2, press Enter, then select D2 again and double-click the fill handle. Excel copies the formula to D3 through D100, adjusting the row references automatically so each row multiplies its own price and quantity.

Multiplying a column by a fixed value — applying a tax rate, a discount, or a markup — requires an absolute reference to prevent the fixed value's cell from shifting as you copy the formula. If your tax rate is in cell F1, write =B2*$F$1 in C2. The dollar signs lock F1 in place.

When you copy the formula down, the B2 reference changes to B3, B4, and so on, but $F$1 always points to the tax rate cell. Without the dollar signs, copying the formula would change F1 to F2, F3, and so on — producing wrong results or errors.

You can also multiply by a hardcoded number directly in the formula without using a separate cell. =B2*1.2 multiplies the value in B2 by 1.2 (a 20% increase). =B2*0.85 applies a 15% discount. This approach is simpler but has a maintenance downside: if the rate changes, you have to edit every formula in the column rather than changing a single rate cell. For rates that might change (tax rates, commission percentages, exchange rates), use a rate cell with an absolute reference.

When working with multiplication across multiple columns or large ranges, the Excel guide for beginners covers how Excel tables can make formula management easier — Excel tables automatically extend formulas to new rows and use structured references that are easier to read and maintain than standard cell references. For repetitive tasks like copying formulas across large ranges, the Excel shortcuts guide covers keyboard shortcuts for filling formulas down and across columns quickly.

Using the PRODUCT Function in Excel

PRODUCT is Excel's dedicated multiplication function. While the asterisk operator (*) is more common for simple two-cell multiplication, PRODUCT shines when you need to multiply many values from a range. =PRODUCT(A1:A10) multiplies all ten values together — far cleaner than =A1*A2*A3*A4*A5*A6*A7*A8*A9*A10. PRODUCT also handles blank cells better than chained asterisks: a blank cell in an asterisk formula returns zero because Excel treats blank as zero in arithmetic; PRODUCT ignores blank cells entirely, treating them as if they weren't in the range.

PRODUCT accepts up to 255 arguments, which can be individual values, cell references, ranges, or combinations. =PRODUCT(A1:A5,B1:B5,2) multiplies all values in both ranges together and then multiplies the result by 2. This flexibility makes PRODUCT useful for compound calculations where you need to multiply values from multiple sources in a single formula. You can also mix ranges and constants: =PRODUCT(A1:A10,0.9) multiplies all ten values together and then applies a 10% reduction to the compound result — a fast way to apply a bulk discount to a compound calculation without adding an extra step.

Text values and TRUE/FALSE logical values in a range are handled differently by PRODUCT versus the asterisk operator. PRODUCT ignores text values in a range, treating them as if they don't exist, whereas =A1*B1 where A1 contains text returns a #VALUE! error. This makes PRODUCT more forgiving in datasets where some cells may contain labels or annotations alongside numeric data.

PRODUCT converts TRUE to 1 and FALSE to 0, which makes it useful in some conditional calculations but can produce unexpected results if your data contains logical values inadvertently — always audit your source ranges if PRODUCT returns a surprising result.

One particularly practical use of PRODUCT is calculating compound growth rates over multiple periods. If you have annual growth rates in cells A1:A5 (expressed as multipliers — 1.05 for 5% growth, 0.95 for 5% decline), =PRODUCT(A1:A5) gives the compound factor across all five periods. Multiply a starting value by this compound factor to get the end value: =B1*PRODUCT(A1:A5). This is far more readable and maintainable than chaining five separate multiplications. For advanced formula techniques including array formulas and SUMPRODUCT, the comprehensive Excel formulas guide covers the full function library with examples.

SUMPRODUCT: Multiply and Sum in One Formula

SUMPRODUCT is one of Excel's most useful functions for working with quantities and prices, grades and weights, or any scenario where you need to multiply corresponding values in two arrays and then add up the results. The syntax is =SUMPRODUCT(array1, array2) where array1 and array2 are ranges of equal length. Excel multiplies the first value in array1 by the first value in array2, the second by the second, and so on, then sums all the products.

The classic use case is calculating total revenue from a table with unit prices in one column and quantities in another. Instead of adding a revenue column (=B2*C2 copied down) and then summing it, =SUMPRODUCT(B2:B100,C2:C100) returns the total in a single cell. This is cleaner, less prone to error from accidentally omitting rows from the SUM, and doesn't require a helper column. The formula works across the entire range regardless of how many rows you have.

SUMPRODUCT also handles conditional multiplication using logical arrays. =SUMPRODUCT((A2:A100="North")*(B2:B100)*(C2:C100)) multiplies each row's quantity and price only when the region in column A is 'North' — the (A2:A100="North") part returns TRUE/FALSE for each row, which multiplies to 1 or 0, effectively including or excluding each row's contribution. This is the SUMPRODUCT equivalent of a conditional sum, and it's often more flexible than SUMIF for complex conditions.

A weighted average using SUMPRODUCT follows the pattern =SUMPRODUCT(values,weights)/SUM(weights). If you have student scores in B2:B10 and each assignment's maximum points in C2:C10, =SUMPRODUCT(B2:B10,C2:C10)/SUM(C2:C10) gives the weighted percentage score. This is how SUMPRODUCT replaces what would otherwise require a helper column and multiple formulas. For a broader look at what Excel can do with data analysis tools, conditional formatting lets you visually highlight cells based on their values — useful when reviewing multiplied results against thresholds. The COUNTIF function works alongside SUMPRODUCT for counting rows that meet specific criteria before multiplying.

Practical Examples: Multiplying in Excel Worksheets

Let's walk through three real-world scenarios to see how different multiplication methods apply. These examples cover the situations most Excel users encounter regularly: applying a price markup, calculating invoice totals, and computing a weighted score.

Scenario 1 — Price list markup: You have 200 product prices in column B (B2:B201) and need to create a column showing each price with a 12% markup. In C2, type =B2*1.12. Select C2, double-click the fill handle. All 200 rows populate instantly. If the markup rate might change, put 1.12 in a separate cell (say E1) and use =B2*$E$1 instead — changing E1 updates all 200 formulas at once. The alphabetizing guide covers how to sort this price list by name or value once your markup column is complete.

Scenario 2 — Invoice total: You have an invoice with item descriptions in column A, unit prices in column B, and quantities in column C. In D2, type =B2*C2 and copy down for the line totals. In D12, type =SUM(D2:D11) for the subtotal. Alternatively, skip the helper column entirely: in a single summary cell, use =SUMPRODUCT(B2:B11,C2:C11) to get the invoice total directly. Add tax with =SUMPRODUCT(B2:B11,C2:C11)*1.2 for a 20% VAT total.

Scenario 3 — Weighted grade calculation: A student has five assignment scores in B2:B6 and each assignment's maximum points in C2:C6. The percentage grade is =SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6)*100. This multiplies each score by its maximum, sums the results, divides by total possible points, and converts to a percentage. Change any score or maximum and the weighted grade recalculates instantly. For managing large grade books or datasets in Excel, the guide to moving columns shows how to rearrange data efficiently without breaking formula references.

These three scenarios demonstrate a pattern: choose the asterisk operator when multiplying two specific values row by row, PRODUCT when multiplying a long series of values in a single range, and SUMPRODUCT when you need both multiplication and aggregation in one step. Most Excel multiplication tasks you encounter fall cleanly into one of these three categories. Building the habit of identifying which category applies before starting to type a formula is one of the most useful Excel skills to develop — it prevents overcomplicating simple tasks and ensures you use the most appropriate tool for each calculation.

Tips for Multiplying in Large Datasets

When your multiplication formulas span thousands of rows, a few techniques keep your workbook fast and your formulas manageable. First, consider Excel Tables (Insert → Table). When you type a formula in one column of an Excel Table, it automatically fills down to all rows — you don't need to manually copy the formula or worry about new rows missing the formula. Excel Tables also use structured references like [@UnitPrice]*[@Quantity] instead of B2*C2, which are easier to read and don't break when you insert or delete rows.

For very large datasets where calculation speed matters, Paste Special Multiply is an option for one-time bulk adjustments. Rather than adding a formula column that recalculates constantly, type the multiplier in an empty cell, copy it, select all the values, use Paste Special → Multiply, and the values are permanently updated. The workbook then doesn't carry the recalculation overhead of thousands of live formulas. This only makes sense for data that won't change — don't use it when the source values are updated regularly.

SUMPRODUCT is often faster than helper-column approaches for aggregate calculations on large ranges. Rather than a column of =B2*C2 formulas summed at the bottom, a single =SUMPRODUCT(B2:B10000,C2:C10000) cell calculates in one step. For complex multi-condition totals on large datasets, SUMPRODUCT with logical conditions is often clearer and faster than equivalent SUMIFS formulas.

When you need to multiply by values that change based on some condition — different tax rates by region, different markup by product category — nested IF within a multiplication formula handles simple cases: =B2*IF(A2="US",1.1,1.2). For more complex rate tables, VLOOKUP or XLOOKUP retrieves the appropriate multiplier from a rate table. This keeps your rates in a visible, editable table rather than buried in formulas. For building more advanced Excel solutions using lookups and data analysis, exploring Excel's formula library opens up powerful combinations of multiplication with conditional and lookup functions.

Freeze header rows and lock formula rows when sharing multiplication-heavy spreadsheets with others. A colleague who accidentally overwrites a formula cell can corrupt months of calculations. Protecting the worksheet (Review → Protect Sheet) and locking formula cells while leaving input cells editable prevents accidental formula deletion. Use Excel's built-in cell styles or conditional formatting to visually distinguish input cells (where users enter data) from formula cells (where calculations happen) — this makes shared workbooks far less prone to unintended edits. The Excel guide covers worksheet protection and table setup in more detail.