The Excel multiply symbol is the asterisk (*), used in formulas to perform multiplication between numbers, cell references, and ranges. Understanding how to enter and control this operator helps you build accurate calculations for financial models, inventories, and analytics.
You can combine the multiply symbol with functions, arrays, and structured references to create flexible, readable spreadsheets. This article covers syntax, best practices, common errors, and real-world scenarios where the Excel multiply symbol is essential.
| Operator | Meaning | Example | Returns |
|---|---|---|---|
| * | Multiply | =5*2 | 10 |
| =A1*B1 | Multiply cell values | A1=4, B1=3 | 12 |
| =PRODUCT(A1:A3) | Multiply a range | A1=2, A2=5, A3=4 | 40 |
| =A1*{1,2,3} | Array multiplication | A1=6 | {6,12,18} |
Basic Syntax and Direct Number Multiplication
Entering the Multiply Symbol in a Formula
To multiply directly, type an equal sign followed by the numbers separated by the asterisk, such as =7*6. Press Enter to see the product, in this case 42, displayed in the cell.
Using Parentheses to Control Order
When combining multiplication with other operations, wrap the multiplication part in parentheses. For example, =(3+2)*4 ensures the addition occurs first, yielding 20 rather than 19.
Multiplying Cell References and Dynamic Updates
Referencing Single Cells
Use =A1*B1 to multiply the values in A1 and B1. If either referenced cell changes, the product updates automatically, keeping your model current without manual edits.
Combining with Other Functions
Wrap multiplication inside functions like =SUM(A1:A5)*0.1 to compute a 10 percent total of a range. This pattern is common in financial calculations such as weighted totals and commissions.
Array and Range Multiplication Techniques
Multiplying a Range by a Constant
Select a column, type =A1:A5*2, and enter as an array formula in older Excel versions or simply press Enter in Excel 365 to see each item doubled instantly.
Cross-Multiplying with Array Syntax
Use =SUM(A1:A5*B1:B5) to compute the sum of pairwise products. This avoids helper columns and is useful for dot product calculations and weighted scoring models.
Common Errors and Troubleshooting the Multiply Symbol
Dealing with Text and Unexpected Results
#VALUE! appears if text is included in a multiplication operation. Use =VALUE(A1)*B1 after cleaning data or wrap with IFERROR to manage errors gracefully in production sheets.
Handling Blanks and Zero Values
Empty cells are treated as zero in multiplication, which can lead to unexpected results. Use =A1*B1*IF(OR(ISBLANK(A1),ISBLANK(B1)),"",1) to skip calculations when inputs are missing.
Best Practices and Key Takeaways for the Excel Multiply Symbol
- Use the asterisk (*) as the standard multiply symbol in formulas.
- Prefer cell references over hardcoded numbers to keep models flexible.
- Wrap combinations of addition and multiplication in parentheses to control evaluation order.
- Leverage SUMPRODUCT for conditional multiplication across arrays.
- Handle blanks and text explicitly to avoid #VALUE! errors.
- Use dynamic arrays in Excel 365 to multiply ranges efficiently without helper columns.
FAQ
Reader questions
Why does my multiply formula show #VALUE!? when I include text?
The multiply symbol expects numeric inputs, and text causes a type mismatch. Clean source data with VALUE or use IFERROR to handle invalid entries gracefully.
Can I multiply entire columns without helper columns in newer Excel versions?
Yes, dynamic array formulas such as =A1:A10*B1:B10 spill results directly onto the sheet, making it easy to multiply entire ranges in a single step.
How do I multiply values based on a condition?
Use =SUMPRODUCT(--(A1:A10>5), B1:B10) to multiply only the values in B where the corresponding A values exceed a threshold, combining condition and multiplication in one step.
What is the difference between * and PRODUCT for multiple cells?
The multiply symbol works well for two numbers, but =PRODUCT(A1:A10) is cleaner, ignores text and logical values, and is safer when handling long ranges.