Multiplication is one of the most common jobs in a spreadsheet: price times quantity, hours times rate, amount times tax. Excel handles it with the asterisk operator, the PRODUCT function and the SUMPRODUCT function, and with Paste Special for changing a whole column at once. This guide shows each method with worked examples you can check by hand, explains how to multiply a column by a fixed number using an absolute reference, how to multiply by a percentage, how to multiply and add in one step, and how to fix the usual problems such as numbers stored as text, wrong results and the #VALUE! error. It is written for anyone who builds invoices, price lists or reports.
Updated November 2026
In this guide
- Multiply two cells with the asterisk
- The sample data
- Multiply a column and fill down
- The PRODUCT function
- Multiply a column by a fixed number
- Multiply by a percentage
- Multiply and add in one step with SUMPRODUCT
- Multiply a whole column with Paste Special
- Multiply across sheets and with other functions
- Why multiplication gives a wrong result or an error
- Worked example: a simple invoice
- Multiplication table and array multiplication
- Quick recap
- Practice problems
- Common mistakes
Multiply two cells with the asterisk
Every formula in Excel starts with an equals sign. To multiply the numbers in A2 and B2, type =A2*B2 and press Enter. If A2 holds 12 and B2 holds 5, the result is 60. You can also multiply numbers directly, such as =12*5, but cell references are better, because the result updates when the numbers change. You can multiply more than two cells in the same formula, such as =A2*B2*C2. Excel follows the usual order of operations, so multiplication and division happen before addition and subtraction, unless you use brackets.
The sample data
The examples use a small order list in A1:C5.
| Item | Quantity | Unit price |
|---|---|---|
| Notebook | 12 | 5 |
| Pen | 50 | 2 |
| Folder | 8 | 7.5 |
| Marker | 20 | 3 |
Multiply a column and fill down
To calculate the line total for each item, add a heading Total in D1. In D2 type =B2*C2. Excel returns 60 for the notebooks. Then double-click the small square at the bottom right of the cell, called the fill handle, and Excel copies the formula down, adjusting the row numbers. The totals are 60, 100, 60 and 60. To get the order total, use =SUM(D2:D5), which returns 280. You can also do both steps at once with SUMPRODUCT, shown below.
The PRODUCT function
The PRODUCT function multiplies all the numbers you give it. =PRODUCT(A2:C2) multiplies every number in the range. It is shorter than the asterisk when you have many cells, such as =PRODUCT(B2:B20). You can mix ranges and numbers, such as =PRODUCT(B2,C2,1.05). PRODUCT ignores text and empty cells in a range, whereas the asterisk returns an error if a cell has text. Be careful: a blank cell inside a PRODUCT range is ignored, but a zero makes the whole result zero. For two cells, the asterisk is usually clearer, and for a long list, PRODUCT is easier to read.
Multiply a column by a fixed number
To increase all prices by 10 percent, put the multiplier 1.1 in a cell such as F1. In D2 use =C2*$F$1 and fill down. The dollar signs make the reference absolute, so it does not move when you copy the formula. For the notebook, 5 times 1.1 is 5.5, and for the pen, 2 times 1.1 is 2.2. If you forget the dollar signs, the formula in D3 would point to F2, which is empty, and return 0. Press F4 after clicking the reference to add the dollar signs quickly. Keeping the multiplier in a cell lets you change it later without editing every formula.
Multiply by a percentage
To calculate tax of 15 percent on a price, use =C2*15%, or put 15% in a cell and use a reference. For the notebook price of 5, the tax is 0.75. To add the tax, use =C2*(1+15%), which returns 5.75. To subtract a 20 percent discount, use =C2*(1-20%), which returns 4 for the notebook. The brackets matter, because =C2*1+15% would add 0.15 to the price instead. If a percentage cell shows 15 and not 15%, check whether the cell is formatted as a percentage. For more on percentage formulas, see our guide to percentage increase in Excel.
Multiply and add in one step with SUMPRODUCT
SUMPRODUCT multiplies matching cells in two or more ranges and adds the results. =SUMPRODUCT(B2:B5,C2:C5) multiplies each quantity by its price and sums everything: 12 times 5 is 60, 50 times 2 is 100, 8 times 7.5 is 60 and 20 times 3 is 60, giving 280. You do not need a total column. SUMPRODUCT can also apply conditions, such as =SUMPRODUCT((A2:A5="Pen")*B2:B5*C2:C5), which returns 100, the total for pens only. This is useful for quick totals when you want to avoid helper columns. The ranges must be the same size, otherwise you get a #VALUE! error.
Multiply a whole column with Paste Special
If you want to change the numbers themselves instead of adding a formula, use Paste Special. Type the multiplier, such as 1.1, in an empty cell and copy it. Select the cells you want to change, right-click, choose Paste Special, then Multiply, and click OK. Every selected number is multiplied by 1.1 and replaced by the result. Delete the helper cell afterward. This method replaces the original values, so work on a copy of the sheet. It is helpful for converting units, such as multiplying a column of kilograms by 2.20462 to get pounds, as in our kilos to pounds converter.
Multiply across sheets and with other functions
You can multiply a value on another sheet, such as =B2*Rates!B1, where the sheet is named Rates. Combine multiplication with other functions, such as =ROUND(B2*C2,2) to round the result, as explained in our guide to rounding numbers in Excel, or =IF(B2>10,B2*C2*0.9,B2*C2) to give a 10 percent discount for more than 10 units, as shown in our IF statements guide. For a lookup price, use XLOOKUP or VLOOKUP to find the price and multiply it by the quantity in the same formula.
Why multiplication gives a wrong result or an error
If the result is 0 when you expect a number, one of the cells may be empty or may hold text that looks like a number. Numbers stored as text are left-aligned, and Excel may show a green triangle. Convert them as explained in our guide to converting text to numbers in Excel. The #VALUE! error appears when a cell contains words, or a hidden space. The #NAME? error means that you typed a function name wrongly. If the formula appears as text instead of a result, the cell is formatted as Text, so change it to General and re-enter the formula. If the result is a date, change the number format to General or Number.
Worked example: a simple invoice
Build an invoice with the quantities in column B, the unit prices in column C, and the line totals in column D with =B2*C2. Below the lines, add the subtotal with =SUM(D2:D5), which is 280 for the sample order. Put the tax rate of 8 percent in a labeled cell, say G1, and calculate the tax as =ROUND(D6*$G$1,2), which is 22.4. The total is the subtotal plus the tax, which is 302.4. Format the money cells with two decimals and use a bold style for the total. If the client changes the tax rate, only one cell needs editing. Add a drop-down for the item names, as in our drop-down list guide, and look up the unit price automatically with XLOOKUP, so the invoice cannot contain a wrong price. Test it with quantities of zero and with a blank row to make sure the totals still work.
Multiplication table and array multiplication
You can build a multiplication table with one formula using mixed references. Put the numbers 1 to 10 across row 1 from B1 to K1 and down column A from A2 to A11. In B2 type =$A2*B$1 and fill right and down. The dollar sign before the column letter locks column A, and the dollar sign before the row number locks row 1. The cell for 7 and 8 shows 56. In Microsoft 365, you can also multiply two arrays directly, such as =B2:B5*C2:C5, which returns the four line totals in one formula that spills into the cells below.
Quick recap
Use the asterisk for two cells, PRODUCT for long ranges, SUMPRODUCT to multiply and add in one step, absolute references for a fixed multiplier, and Paste Special, Multiply to change a column in place. Use brackets in percentage formulas, convert text numbers first, and check each result against a quick estimate.
Practice problems
Try these on a new sheet. Problem 1: multiply 24 by 7. The answer is 168. Problem 2: a worker is paid 18 per hour and works 37.5 hours. The pay is 18 times 37.5, which is 675. Problem 3: a product costs 80 and tax is 7.5 percent. The total is 80 times 1.075, which is 86. Problem 4: use PRODUCT on 2, 3 and 4. The answer is 24. Problem 5: a list of quantities 3, 4 and 5 has prices 10, 20 and 30. SUMPRODUCT returns 30 plus 80 plus 150, which is 260. Check each with Excel and by hand.
Common mistakes
The most common mistake is to forget the equals sign, which leaves the text in the cell. Another is to use the letter x instead of the asterisk. A third is to forget the dollar signs on a fixed multiplier, which breaks the formula when you copy it down. A fourth is to forget the brackets in a percentage formula. A fifth is to multiply numbers stored as text. Finally, check the result with an estimate: if you multiply 12 by 5 and see 600, something is wrong with the decimals or the references.
Frequently asked questions
How do I multiply in Excel?
Type an equals sign, then the cells separated by an asterisk, such as =A2*B2.
How do I multiply a whole column by a number?
Use a formula with an absolute reference, or copy the number and use Paste Special, Multiply.
What is the PRODUCT function?
It multiplies all the numbers in the arguments or ranges you give it.
How do I multiply by a percentage?
Use the asterisk with a percentage, such as =A2*15%, or =A2*(1+15%) to add it.
Why does my multiplication return zero?
A cell may be empty, or the numbers may be stored as text.


