Multiplication Formula in Excel — Complete Guide (2026)

Multiplication is one of the most common operations you'll perform in Excel—whether you're calculating revenue (price times quantity), applying a tax rate to a list of totals, or scaling a dataset by a fixed factor. The good news: Excel gives you several ways to do it, and they're all straightforward once you know which tool fits your situation.

The workhorse is the asterisk operator *. Type =A1*B1 in a cell and Excel multiplies whatever's in A1 by whatever's in B1. Simple. You can chain as many factors as you like—=A1*B1*C1 is perfectly valid. When you need to multiply a cell by a hard-coded number rather than another cell, just substitute it directly: =A1*1.08 applies an 8% uplift in one stroke. That's the excel times formula in its simplest form, and it works in every version of Excel going back decades.

For longer ranges, the PRODUCT function is your friend. Instead of writing =A1*A2*A3*A4*A5, you write =PRODUCT(A1:A5). Same result, far less typing—and the formula doesn't break when you insert rows in the middle of the range. PRODUCT also ignores empty cells and text, which can save you from mysterious #VALUE! errors when your data isn't perfectly clean.

Then there's SUMPRODUCT—a function that multiplies pairs of values across two ranges and sums the results. It's the go-to for weighted averages, invoice totals, and any scenario where you multiply then add. =SUMPRODUCT(A1:A5, B1:B5) does in one formula what would otherwise take a helper column plus a SUM. Financial analysts use it constantly; once you learn it, you'll find yourself reaching for it in situations you'd never expect.

There's also a lesser-known trick: Paste Special Multiply. It lets you multiply an entire range in-place—no formula required. You copy a constant, select your target range, open Paste Special, and choose Multiply. Every value in the range gets multiplied immediately. It's ideal for one-off adjustments when you don't want a formula cluttering the sheet, or when you're cleaning data and need to normalize units quickly. For instance, if all your values are in cents and you want dollars, multiply by 0.01 using Paste Special—done in five seconds with no formula trace left behind.

If you use Excel 365, dynamic arrays add another dimension. A formula like =A1:A5 * B1:B5 spills five products into five consecutive cells automatically. You don't need to select an output range; Excel figures out the size. This is the array formula for multiplying ranges, modernized—no more Ctrl+Shift+Enter gymnastics required.

Understanding how to do multiplication in Excel also means knowing when each method breaks down. The asterisk fails silently if a cell contains text—you get #VALUE! with no further explanation. PRODUCT handles that more gracefully. SUMPRODUCT can mask errors too if you wrap ranges in IFERROR. Paste Special is permanent—there's no undo after you close the dialog in some older Excel versions, so work on a copy of your data. Knowing these edge cases is the difference between a formula that works in testing and one that holds up in production.

Whether you're new to excel or brushing up on your formula skills, this guide walks through every multiplication technique with real examples you can adapt immediately. By the end, you'll know exactly which method to reach for—whether you're multiplying two cells, an entire column by a constant, or building a SUMPRODUCT-powered dashboard.

Basic Multiplication Formulas

The multiplication formula in Excel starts with an equals sign and uses the asterisk (*) as the multiplication operator. To multiply two cells, the formula is simply =A1*B1. Click a blank cell, type that formula, and press Enter—Excel displays the product of whatever values sit in A1 and B1. Change either input cell and the result updates instantly.

Multiplying a cell by a hard-coded number is just as easy. If you want to add 10% VAT to a price in A2, write =A2*1.1. Need to double every value in column A? Use =A1*2 and fill down. Need to calculate 65% of a number? Try =A1*0.65. The syntax never changes—asterisk between two values, one or both of which can be cell references. This is what most people mean when they talk about the excel multiply formula.

You can chain the operator for more complex calculations too. =A1*B1*C1 multiplies three cells. =A1*B1*1.08 multiplies two cells and applies an 8% uplift in one formula. There's no limit to how many times you can chain *, though long chains get hard to read—that's when PRODUCT starts making more sense.

Multiplying an Entire Column by a Constant

This is where absolute references really pay off. Say column A holds 100 product prices and cell D1 holds your tax multiplier (1.08 for 8% tax). You want column B to show the tax-inclusive price for each row. Here's the approach:

1. Click B2 and enter =A2*$D$1
2. Press Enter, then select B2 again
3. Double-click the fill handle (the small square at the bottom-right of the cell), or drag it down to B101

The $ signs lock D1 so it doesn't shift when you fill down—without them, the formula in B3 would reference D2, B4 would reference D3, and your results would be garbage. With absolute references, every row multiplies its own price against the same constant in D1. That's how you excel multiply column by number across hundreds of rows without retyping anything.

You can lock just the row ($D1), just the column (D$1), or both ($D$1). For this use case, lock both—you want the reference to stay on exactly one cell no matter where the formula moves. If you're filling across columns instead of down rows, you might only need to lock the row.

You can also apply two constants at once. =A2*$D$1*$E$1 multiplies by a tax rate in D1 and a currency conversion rate in E1 simultaneously—Excel evaluates left to right, so the result is A2 times D1 times E1. Keep your multipliers labeled in a dedicated parameter section of the sheet so anyone reviewing the workbook can see exactly what those constants represent.

Once you've mastered this, you'll want to explore the excel multiplication formula page for additional techniques specific to different Excel versions. And when your formula is ready to scale across dozens of rows, the guide on how to copy a formula in Excel walks through every copy method—drag fill handle, double-click, Ctrl+D, and named range tricks.

A few things to keep in mind as you build your multiply formulas. First, if a cell contains text instead of a number, Excel returns #VALUE!—check that every input cell actually holds a numeric value. Use =ISNUMBER(A1) to test cells when in doubt. Second, multiplying by zero gives zero—obvious, but worth double-checking when a row unexpectedly shows 0. Third, very large products can exceed Excel's number precision limit—rare in most business contexts, but worth knowing if you work with scientific data or very large financial models.

Common Errors in Multiplication Formulas

The two most frequent errors are #VALUE! and #REF!. You get #VALUE! when one of the cells in your formula contains text, a date formatted oddly, or a formula that itself returned an error. The fix: check your inputs with ISNUMBER, clean the data, or wrap the formula in IFERROR. The #REF! error appears when the cell a formula was pointing to no longer exists—usually because a row or column was deleted. Rebuild the reference or use named ranges to avoid it. Neither error is fatal; both are easy to diagnose once you know what to look for.

SUMPRODUCT: Multiply Then Sum in One Formula

SUMPRODUCT is one of Excel's most versatile functions—and once you see what it does, you'll wonder how you managed without it. The formula =SUMPRODUCT(A1:A5, B1:B5) multiplies A1×B1, A2×B2, A3×B3, A4×B4, and A5×B5, then adds all five results together. That's a whole helper column and a SUM function compressed into a single cell. No intermediate results cluttering your sheet, no extra column to maintain.

The classic use case is an invoice total. Put quantities in column A and unit prices in column B. One SUMPRODUCT formula at the bottom gives you the grand total: =SUMPRODUCT(A2:A100, B2:B100). It's also perfect for weighted averages: put scores in column C and weights in column D, then write =SUMPRODUCT(C2:C10, D2:D10) / SUM(D2:D10). That calculates your weighted average without any helper column—clean and transparent in one formula.

SUMPRODUCT also handles conditional multiplication—essentially acting as SUMIFS with even more flexibility. You can filter rows by a condition before multiplying. For example, to total revenue only for a specific region, you'd write something like =SUMPRODUCT((C2:C10="East")*(A2:A10)*(B2:B10)). The condition array returns 1s and 0s; rows where C doesn't match get multiplied by 0, effectively removing them from the total. This approach handles multiple conditions easily—just add more condition arrays multiplied together.

For those using Excel 365, dynamic array multiplication is another option worth knowing. The formula =A1:A5 * B1:B5 entered in a single cell spills five results automatically. It doesn't sum them—you'd wrap it in SUM for that—but it's useful when you need the individual products visible side by side. This spill behavior means you never need to select the output range in advance. Excel figures out the size based on the input ranges and adjusts automatically if the data grows. It's the array formula for multiplying ranges, reborn as a first-class feature rather than a legacy Ctrl+Shift+Enter workaround.

If you work with conditional counts alongside your multiplication, the countifs excel function pairs naturally with SUMPRODUCT-based approaches—both use array logic under the hood. You might count how many qualifying rows exist with COUNTIFS, then calculate their weighted value with SUMPRODUCT, giving you a complete picture of your dataset in just two formulas.

One practical tip: SUMPRODUCT treats errors differently from SUM. If one cell in your range contains an error, SUMPRODUCT returns an error for the entire result—no partial totals. Wrap the problematic range in IFERROR to handle this cleanly: =SUMPRODUCT(IFERROR(A1:A5,0), B1:B5). This substitutes zero for any errors in column A before multiplying, so your total reflects as much data as is available rather than failing entirely. That kind of defensive formula writing pays dividends when data comes from external sources you don't fully control.

SUMPRODUCT's flexibility makes it a staple for Excel users who deal with data analysis, financial modeling, or any reporting that involves conditional aggregation. Once you understand the pattern—multiply arrays together, sum the result—you'll start seeing opportunities to use it across your work.

Paste Special Multiply: In-Place Multiplication Without Formulas

Paste Special Multiply is Excel's hidden gem for one-off data adjustments. Instead of adding a helper column with a formula, you multiply a range's values in-place—the source data changes permanently, with no formula trace left behind. That makes it ideal for currency conversions, price adjustments, or unit conversions where you want clean, formula-free numbers in your cells without the overhead of maintaining formulas.

Here's the full workflow. First, type your multiplier in any blank cell—say 1.07 for a 7% price increase, or 0.01 to convert cents to dollars. Copy that cell with Ctrl+C. Then select the range you want to update—it can be a single column, multiple columns, or even a non-contiguous selection using Ctrl+Click.

Open Paste Special with Ctrl+Alt+V on Windows (or right-click and choose Paste Special from the menu). In the dialog, look for the Operation section near the bottom and click the Multiply radio button. Hit OK. Every value in your selection is instantly replaced by its product with your constant. Delete your temporary cell when done and the sheet looks exactly as before—just with updated values.

There are two key limitations to be aware of. First, Paste Special Multiply doesn't do what you'd expect on cells containing formulas—it modifies the formula itself rather than the computed result. If cell A2 contains =C2*D2 and you Paste Special Multiply by 2, you end up with =C2*D2*2—which may or may not be what you wanted. Stick to raw-value cells. Second, this operation is difficult to undo once you close the workbook in some Excel versions, so always work on a copy of your data or at minimum make sure you haven't saved yet before undoing.

Good scenarios for Paste Special Multiply: updating a price list by a percentage, converting imported data from one unit system to another, or applying a one-time currency conversion rate across a column of values. It's much faster than adding a formula column, copying values, and pasting-as-values to replace the originals.

Multiplying Across Sheets

When your data spans multiple sheets, Excel's cross-sheet reference syntax lets you multiply values from different tabs without copying anything. The format is =Sheet1!A1 * Sheet2!A1—sheet name, exclamation mark, cell address. If the sheet name contains spaces, wrap it in single quotes: ='Sales 2026'!A1 * 'Costs 2026'!A1.

This pattern is common in financial models where each month or product line lives on its own sheet. A summary sheet can multiply revenue rates from one tab by volume data from another—keeping source data organized without duplicating it. You can combine cross-sheet references with PRODUCT and SUMPRODUCT too: =SUMPRODUCT(Sheet1!A1:A5, Sheet2!A1:A5) works exactly as you'd expect, multiplying matched pairs across the two sheets and summing the results.

One thing to watch: if you rename or delete a source sheet, formulas that reference it break with a #REF! error. Use consistent, descriptive sheet names and avoid renaming sheets that formulas depend on. Named ranges can add another layer of resilience—a named range like SalesData still works after a sheet rename, because Excel tracks the range by name rather than by sheet-and-cell address.

For related operations, the excel sum formula guide covers adding values with the same cross-sheet syntax—useful when you're building summary dashboards that pull from multiple data tabs. SUM and multiplication work the same way with cross-sheet references, so the patterns transfer directly.

Building on these fundamentals—knowing when to reach for asterisk vs PRODUCT vs SUMPRODUCT vs Paste Special—puts you well ahead in Excel proficiency. Once your formulas are solid, think about presentation too. You might explore how to change column width in excel to make large numbers display without truncation, or use how to indent in excel to visually separate categories and sub-totals in your multiplication tables. A polished, well-formatted spreadsheet with accurate formulas is a genuinely useful business tool.

And if you need to navigate your sheet quickly—deleting scratch rows used during testing or cleaning up temporary constant cells—knowing the keyboard shortcut to delete row in excel saves time when you're iterating on a model. These small efficiency gains add up across a spreadsheet session.