To multiply in Excel, use the asterisk (*) as the multiplication operator in a formula, such as =A1*B1, or use the PRODUCT function for multiple cells or ranges, like =PRODUCT(A1:A5). This is the direct answer, and the rest of this article explains the different ways to multiply, how to choose between them, and common pitfalls to avoid.
Why the Asterisk (*) Is the Multiplication Symbol
In Excel, the asterisk (*) is the multiplication operator, just as the plus (+) and minus (-) are for addition and subtraction. You cannot use the × symbol or an x; Excel will treat those as text or invalid characters. For example, to multiply the value in cell A1 by the value in B1, you would click in cell C1 and type =A1*B1 and press Enter. The result appears in C1.
You can also multiply numbers directly in a formula, such as =5*4, which returns 20. This is useful for quick calculations, but referencing cells is better because it makes your spreadsheet dynamic—if you change a value in A1 or B1, the result updates automatically.
Using the PRODUCT Function for Multiple Values
When you need to multiply more than two numbers or an entire range, the PRODUCT function is more efficient. Its syntax is =PRODUCT(number1, [number2], ...). For example, with values in cells A1, B1, and C1, you could write =PRODUCT(A1:C1) to multiply all three. The function accepts individual cells, ranges, or a mix: =PRODUCT(A1, B2, C3:D4).
PRODUCT is especially useful when you have a long list of factors, such as a column of prices in A2:A10. Instead of typing =A2*A3*A4..., you simply write =PRODUCT(A2:A10). This reduces errors and is easier to read.
Multiplying a Column by a Constant (Absolute References)
A common task is multiplying an entire column by a fixed number, like applying a tax rate or a conversion factor. Suppose you have prices in column A (A2:A10) and a tax rate in cell D1 (e.g., 0.08 for 8%). In cell B2, you would enter =A2*$D$1 and then copy the formula down to B10. The dollar signs ($) make the reference to D1 absolute, so it doesn’t change as you copy. Without them, copying down would change D1 to D2, D3, etc., giving incorrect results.
If you prefer, you can also use a named range for the constant. For example, select cell D1, type “TaxRate” in the Name Box (left of the formula bar), and then use =A2*TaxRate. This makes the formula self-documenting.
Multiplying Two Columns Row by Row
To multiply corresponding cells in two columns (e.g., quantity in column A and unit price in column B), you write a formula in the first row and copy it down. In cell C2, enter =A2*B2. Then double-click the fill handle (the small square at the bottom-right of the cell) to copy the formula down the entire column. Excel adjusts the row numbers automatically, so C3 becomes =A3*B3, and so on.
If you need to multiply entire ranges and get an array of results (e.g., A1:A3 * B1:B3), you can use the dynamic array feature in Excel 365. Select a range of cells, type =A1:A3*B1:B3, and press Ctrl+Shift+Enter. In Excel 365, it will spill the results automatically. In older versions, you must press Ctrl+Shift+Enter to create an array formula.
Common Errors and How to Fix Them
- #VALUE! – This error appears when a cell contains text or a non-numeric value. Check that all referenced cells contain numbers.
- #NAME? – Usually means you misspelled a function name or used a cell reference incorrectly. For example, typing
=PRODCT(A1:A3)gives this error. - #DIV/0! – Not directly related to multiplication, but if you divide by zero in a combined formula, you’ll see it. Ensure divisors are not zero.
- Forgetting the equals sign – If you type
A1*B1without the =, Excel treats it as text. Always start with =. - Using × or x – These are not recognized. Use the asterisk key on your keyboard.
Multiplying with Paste Special (No Formulas)
If you want to multiply a range of numbers by a constant without creating formulas, use Paste Special. For example, to multiply all values in A1:A10 by 2, first type 2 in an empty cell (say D1), copy that cell (Ctrl+C), select A1:A10, right-click and choose Paste Special > Multiply. This replaces the original values with the multiplied results. This method is handy when you want to permanently change data without leaving formulas. Note that this overwrites your original data, so consider saving a backup first.
Multiplying in Excel on Mac and Web
The multiplication operator and PRODUCT function work identically in Excel for Mac and Excel for the web. The main difference is keyboard shortcuts: on Mac, copy is Command+C and paste is Command+V. The Paste Special menu is accessed via Edit > Paste Special > Multiply, or by right-clicking and choosing Paste Special. In Excel for the web, Paste Special is available under the Home tab > Paste > Paste Special, but the Multiply option is only available in the desktop versions. For the web, you can use a formula like =A1*B1 as usual.
Frequently Asked Questions
How do I multiply a cell by a percentage? Simply multiply by the decimal form. For example, to increase a price in A1 by 15%, use =A1*1.15. To get just the 15% amount, use =A1*0.15.
Can I multiply two different ranges and sum the results? Yes, use the SUMPRODUCT function. For example, with quantities in A2:A5 and prices in B2:B5, =SUMPRODUCT(A2:A5, B2:B5) multiplies each pair and sums them, giving a total cost.
Why does my multiplication formula show 0? This usually happens when one of the referenced cells is empty or contains text. Excel treats empty cells as 0 in multiplication. Check that all cells have numeric values.
Is there a way to multiply without using a formula? Yes, use Paste Special as described above, or use the PRODUCT function in a cell. If you just need a one-off calculation, you can type =5*4 in any cell and press Enter.
How do I multiply a whole column by a number and put the result in another column? Enter a formula like =A2*$B$1 in the first cell of the target column, where B1 holds the constant, and then copy the formula down.
Related
- Useful MacOS Shortcuts: 40+ You’ll Actually Use
- What Is enum in Java? Definition & Usage
- How to Update a Dictionary in Python
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel XLOOKUP with CONCATENATE: How to JoinYes, you can use XLOOKUP with concatenated values by building the lookup value with the & operator or…
- COUNTIF Date Range in Excel: Count Dates Between Two DaysTo count dates between two limits in Excel, use COUNTIFS with a start-date condition and an end-date condition.…
- Excel Table of Contents: Create a Clickable Sheet IndexExcel does not have a dedicated command that automatically creates a table of contents for every worksheet. The…
- How to Multiply Hours in Excel: Formulas and FormattingTo multiply a duration by a factor in Excel, enter the duration in A2 and the factor in…
- Days Left in Excel: Calculate Workdays and Calendar DaysTo calculate days left in Excel, subtract today’s date from the deadline: if the deadline is in A2,…
- Excel Filter Duplicates in a Column: A Practical GuideYes. In Microsoft Excel for Microsoft 365, add a helper column with =COUNTIF($A$2:$A$100,A2)>1, fill it down, then turn…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.