Whether you are a student, accountant, or business analyst, learning how to multiply cells in Excel is an invaluable skill. Luckily, Excel provides a few different simple and quick ways to multiply cells and ranges.
In this step-by-step guide, you will learn 5 easy methods to multiply cells in Excel:
- Using the multiplication symbol
- Using the PRODUCT function
- Multiplying by a constant number
- Using array formulas
- Shortcuts for quick calculations
Multiplying Numbers in Excel with the Multiplication Symbol
The most straightforward way to multiply two or more numbers or cell values in Excel is by using the multiplication symbol (*).
To multiply numbers:
- Type the equal sign (=) in a cell
- Enter the numbers you want to multiply separated by an asterisk (*)
- Press Enter to calculate the formula
For example, to multiply 3 by 4, you would enter =3*4 in a cell.
To multiply cell values:
- Type = in a cell
- Click or enter a reference to the first cell
- Type *
- Click or enter a reference to the second cell
- Hit Enter
So to multiply cell A2 by B2, you would enter =A2*B2. You can include multiple cell references separated by * to multiply more cells like this:
This basic approach works well for simple multiplications. For multiplying larger ranges, the PRODUCT function is better suited.
Using the PRODUCT Function to Multiply Ranges
The PRODUCT function in Excel allows you to multiply values from multiple cells or ranges in one formula quickly.
The PRODUCT function syntax is:
=PRODUCT(number1, number2, …)
Where number1, number2, etc. can be cell references or ranges to multiply.
For example:
=PRODUCT(A2,B2,C2) multiplies the values in cells A2, B2, and C2
=PRODUCT(A2:A10) multiplies all cells between A2 and A10
You can also include hardcoded numbers along with cell references:
=PRODUCT(A2:A5, 10) multiplies A2:A5 by 10
Here is a sample image depicting how you should use the formula:
The PRODUCT function is indispensable when you need to multiply values across entire rows, columns, or tables in one go.
Multiplying Columns or Rows by a Constant Number
Sometimes you may need to multiply a column, row, or table by a fixed number. For example, calculating discounts, taxes, percentages, etc. on a range of prices.
Here’s how to do this in Excel:
- Enter the constant number to multiply by in a cell (e.g. 15% tax in cell B1)
- In the first cell where you want output, type the formula: =[Cell to multiply]*$[Constant cell]
For example: =A2*$B$1
- The $ signs make the constant cell reference absolute.
- Now hit the enter button to reveal the result
- Copy the formula down column to multiply the entire range
- The reference to the range (A2) changes while the constant cell (B1) remains fixed. This quickly multiplies the entire column by the constant.
Multiplying in Array Formulas
Array formulas in Excel allow you to perform multiple calculations across ranges in one cell.
To multiply two columns and get the sum of products, use this array formula:
{=SUM(Column1 * Column2)}
- Remember to press Ctrl + Shift + Enter to complete it correctly.
- You can also find the average, min, max, etc. of the multiplied range this way.
For example:
- {=AVERAGE(Column1 * Column2)} gives the average product
- {=MIN(Column1 * Column2)} finds the smallest product
- {=MAX(Column1 * Column2)} gets the largest product
here is a sample of what your results might look like:
Array formulas are powerful but require some effort. For quick calculations, try the shortcuts next.
Quick Shortcuts to Multiply Cells in Excel
Manually building formulas can be slow, especially if you’re not adept with Excel.
Luckily, there are some handy shortcuts and Excel add-ins that can help:
- Paste Special Multiply – Multiplies a range by copying it and pasting it with Paste Special > Multiply.
- Ultimate Suite Calculation Tool – Multiplies cells, columns, or rows in one click with predefined multiplication options. No formulas are needed.
These tools speed up multiplying cells significantly compared to manual formulas. The results are static values instead of formulas.
Conclusion
Whether you need to find a quick product of a few cells or multiply entire columns, Excel provides easy ways to do it fast. The next time you want to multiply cells in Excel, use the multiplication operator, PRODUCT function, constant multiplication, array formulas, or shortcuts as required.
With these simple yet powerful multiplication options, you can breeze through financial models, data analysis, reports, and more. So learn Excel multiplication well and boost your spreadsheeting productivity!
