How to multiply in Excel is one of the most used arithmetic tools in spreadsheets. When you understand the Excel PRODUCT function, an array formula in Excel, or just a simple multiplication operator, you save time, reduce mistakes, and make your sheets much more reliable It helps whether you track personal expenses, sales data, or just mess around with school work. The habit that unlocks everything is simple: begin with an equal sign, point to the cells that matter, and let the sheet carry the math with zero drama. If this becomes muscle memory, multiplying values for budgets, sales lines, or academic tables becomes automatic and error resistant.
What it means to multiply in Excel
A lot of people ask “what’s the formula for multiplying in Excel?” — the truth is, there is no built-in MULTIPLY function, but learning how to multiply in Excel usually starts with either the * operator or the PRODUCT() function.
- Use the asterisk * operator
- Use the PRODUCT() function which can handle many numbers or ranges at once
Knowing how to multiply in Excel formula this way means you can scale from small to large datasets without rewriting everything.
Basic examples of how to multiply in Excel with the product function
If you are learning how to multiply in Excel, the most basic way is with a formula like =5*6. This is the default Excel formula multiply method. If you want to use the Excel PRODUCT function, you can also write =PRODUCT(5,6):
=5*6
That gives 30. Simple. If you want to multiply cells, say A2 and B2, you do:
=A2*B2
This is the simplest way to understand how to multiply in Excel with formulas using cell references.
If you have many cells, many numbers, maybe ranges, the PRODUCT() function is very helpful:
=PRODUCT(A2:A10, B2, 5)
This multiplies everything in A2 through A10, with B2, and with the fixed number 5.
Working with large data: Ranges with 10 K-100 K rows/values
When you are working with huge datasets (10,000–100,000 rows), knowing how to multiply in Excel at scale becomes critical. Instead of repeating *, you can use the Excel PRODUCT function or an array formula in Excel. This is where SUMPRODUCT also helps with large-scale Excel multiplying tasks.
For example you might do:
=PRODUCT(A1:A50000)
Or, to multiply two big columns row-by-row:
=SUMPRODUCT(A1:A100000, B1:B100000)
That way each pair gets multiplied, and then summed, which is much faster than writing out A1B1 + A2B2 + … manually.
How to multiply in Excel with percentages
Sometimes you want to multiply by a percent. Suppose you want 40 % of something:
=20*40%
This shows another angle of how to multiply in Excel, especially when working with percentages in reports or budgets.
That gives 8. Or if A3 contains 20 and B3 contains 40%, then =A3*B3. Or you can convert percent to decimal (=20*0.4) if you prefer.
Array formulas & using PRODUCT with arrays
When you want to apply operations over ranges, maybe multiply many things together or do matrix-like multiplication, array formulas (or functions like MMULT()) are useful. Excel requires you sometimes to press Ctrl+Shift+Enter (depending on version) so Excel treats formula as array.
Also PRODUCT() accepts ranges, so it handles arrays implicitly if your range is array-like. If there are blank cells, in many cases PRODUCT will treat blanks as zeros? Actually blank cells often are treated like zero in direct multiplication with *, but sometimes ranges inside PRODUCT might ignore blanks or errors depending on context (check your version).
Fill handle in Excel: multiplying quickly across rows/columns
Here is a trick: suppose you want to multiply a constant by each cell in a large column. You write one formula, like =A2*$B$2. The $ signs make the reference to B2 absolute, so when you drag using the fill handle down, it always multiplies by B2. Then you drag down (fill handle) to apply to all cells.
If you have multiple columns, you can drag across rows similarly. The fill handle makes life much easier when dealing with large ranges (10 K-100 K etc) or many columns.
Common mistakes, things to watch out for
Here are some minor but painful pitfalls (I learned these the hard way):
- Forgetting the = sign at the start of an Excel formula multiply.
- Using x instead of * when trying to show how to multiply in Excel.
- Confusing relative vs absolute references when doing Excel multiplying with fill handle
Why prefer PRODUCT and SUMPRODUCT etc
Because when your dataset is large (10 K-100 K rows, many columns), using PRODUCT or SUMPRODUCT is cleaner, fewer chances of error, easier to adjust ranges, formulas less verbose. Also Microsoft’s support article mentions you can combine single cells, numbers, and ranges inside PRODUCT.
Frequently asked question
what is the formula in Excel to multiply?
When learning how to multiply in Excel, a very common question is “what is the formula in Excel to multiply two ranges?” Normally you cannot do =A2:A10*B2:B10 without an array formula in Excel. Instead, use SUMPRODUCT, which is the most reliable Excel formula multiply option for ranges.
What if one of the cells is blank?
Blank is often treated like zero in multiplication with *, so result becomes zero. You can check using IF or ISBLANK to avoid that. PRODUCT might behave differently depending on version/range.
How do I do multiplication in Excel for large data?
If you are asking “how do I do multiplication in Excel when I have thousands of rows?”, the best option is to use the Excel PRODUCT function or SUMPRODUCT. Both make Excel multiplying efficient, especially when handling 10k–100k rows.
Example: real-life scenario
Imagine you are tracking sales in a big store. Across 50,000 rows, column A lists quantity, column B lists price, and column C shows the tax percentage. To compute total revenue including tax, you might do:
=SUMPRODUCT(A2:A50001, B2:B50001, 1 + C2:C50001)
That multiplies each quantity by price, then by (1 + tax percent), then sums across all rows. That uses arithmetic for large ranges, uses percentages. Very efficient.
Conclusion
What this really means is: mastering how to multiply in Excel gives you leverage. PRODUCT, SUMPRODUCT, array formulas, fill handle – these are not fancy extras, they are essential when you work with medium or large data (10 K-100 K or more). Always check your formulas, use absolute references as needed, treat blank or text values carefully, and practice with real spreadsheets. Once you are comfortable, you can move to more operations (division, addition, combinations etc) with confidence.