Excel’s multiply function is one of the most frequently used arithmetic tools in spreadsheets. It helps users quickly find totals, calculate costs, analyze performance metrics, and more. Whether you’re handling personal finance, sales data, or academic projects, multiplication in Excel is essential for accurate results.
How to Multiply in Excel
Excel doesn’t have a specific “MULTIPLY” function, but you can use the asterisk symbol * or the PRODUCT() function to get results.
Basic Multiplication Formula
Formula: =number1 * number2
Example: =5*6 will return 30.
Using the PRODUCT Function
Formula: =PRODUCT(number1, [number2], ...)
This is more efficient when multiplying multiple cells or ranges.
Real-Life Use Case
Brenda, a stay-at-home mom, tracks her children’s school expenses using Excel. She uses multiplication to calculate the total price of stationery items like pens, notebooks, and folders.
| Item | Quantity | Price | Total (Qty x Price) |
|---|---|---|---|
| Notebook | 3 | $4 | =3*4 = $12 |
| Pen | 5 | $1.5 | =5*1.5 = $7.5 |
| Folder | 2 | $3.5 | =2*3.5 = $7 |
Multiply Cells in Excel
Formula: =E6*E7
Answer: 704
Multiply Columns in Excel
Formula: =E6*F6 or =PRODUCT(E6, F6)
Answer: 320
Multiply Multiple Columns
Formula: =E6*F6*G6 or =PRODUCT(E6, F6, G6)
Answer: 640
Common Mistakes to Avoid
- Using text values instead of numbers in formulas
- Forgetting to add equal sign
=at the beginning of formulas - Accidentally using
xinstead of* - Incorrect cell referencing when dragging formulas
Benefits of Using PRODUCT()
- Handles a range of numbers easily
- Fewer chances of formula error
- Clean syntax for multiplying many values
- Supports dynamic data ranges
Formulas Comparison
| Method | Formula | Use Case |
|---|---|---|
| Multiply Symbol | =A2*B2 | Quick two-cell multiplication |
| PRODUCT Function | =PRODUCT(A2:A4) | Multiplying a range of values |
Frequently Asked Questions
1. Can I multiply two ranges in Excel?
No, Excel does not support multiplying two ranges directly using the asterisk. You can multiply cell by cell or use array formulas.
2. How do I multiply and sum in Excel?
Use the SUMPRODUCT() function. Example: =SUMPRODUCT(A2:A4, B2:B4)
3. What if one of my cells is blank?
Blank cells are treated as 0 in multiplication. Result will be 0 unless handled via IF() or ISBLANK() conditions.
Conclusion
Multiplication in Excel is quick, flexible, and easy once you understand both the * operator and the PRODUCT() function. From budgeting to business analysis, Excel’s multiplication features help streamline calculations and reduce manual work. Make sure to double-check your formulas, use absolute cell references where necessary, and explore combinations like SUMPRODUCT() for advanced tasks.
Ready to try more? Explore how division formulas work in Excel next and master every arithmetic operation with ease!