There is more than one method of multiplication in Microsoft Excel, and one of them does not require an expression.
Microsoft Excel manipulates data – that’s the goal in a nutshell. Consequently, there are numerous ways to perform mathematical operations, including multiplication. You can multiply literal values, refer to the values by cell or by range, and much more. In this tutorial, I’ll show you how to multiply in Excel by starting with the basic operator and ending with a technique of multiplication without formula.
I use Microsoft 365 on a Windows 10 64-bit system, but you can use earlier versions. Microsoft Excel for the web supports the first three methods. You can download the demo .xls and .xlsx files for this Excel tutorial.
TO SEE: Windows, Linux, and Mac Commands Everyone Should Know (Free PDF) (TechRepublic)
How to Complete Basic Multiplication in Excel
When we use pen and paper to write an expression that uses multiplication, many of us still use X as the multiplication operator. Excel uses an asterisk as a multiplication operator. In a simple expression like
=2*3
or
=A1*B2
Excel multiplies one operand by another to return the product. The only thing that differs between your paper and Excel is the symbol.
How to Multiply with PRODUCT() in Excel
Excel’s PRODUCT() function multiplies all referenced values and returns the product. There is little difference between PRODUCT() and a simple expression, except you can refer to ranges. This function uses the following syntax:
PRODUCT(number1, [number2]…)
where Excel only requires one operand. For example, PRODUCT(5) returns 5 instead of an error. This can be a difficult problem to solve because the purpose of the function implies more than one operand.
PRODUCT() is superior to a simple expression when you need to evaluate many cells. For example,
PRODUCT(A1:A5, B1:B5)
is the same as
=A1*A2*A3*A4*A5*B1*B2*B3*B4*B5
The number argument can be a cell or range reference, a single cell reference, or a literal value. In addition, you can mix references.
How to multiply when one operand is a constant in Excel
It is doubtful that any of the preceding sections are brand new information, but efficient multiplication in Excel is a learned skill. Let’s say you track t-shirt sales by customer, size, and quantity. You can include the price for each individual sale in the expression, but consider making it a constant that only changes when the price goes up or down. In such a situation, it is much more efficient to remove and reference the price from the expression because you are only changing the price value rather than updating all the expressions that evaluate the price value.
Image A shows one operand as a constant, or input value as I like to call them, in action. With this configuration, you can quickly change the total for all sales by changing a single value – the price entry value in H2.
Image A
The expression
=[@Quantity]*$H$1
multiplies each value in the Quantity column by the value in H2. Note that the H2 reference is absolute because it is a constant – each expression refers to that single value. To change the total sales price for each record, change the price value to H2. You can do this with all sorts of calculations, not just multiplication.
Figure B shows the introduction of another constant, or input value, a percentage discount referenced by the expression in columns F and G:
=IF([@Quantity]>39,[@Total]*$J$4,0)
=IF([@Quantity]>39,PRODUCT([@Total],$J$4),0)
Both expressions evaluate the same; the only difference is that one uses a simple expression to multiply the constant and the other uses PRODUCT(). The IF() statement checks for a condition – is the quantity greater than 39, and if so, return the 5% discount, the value in J4. If the number is less than 39, enter 0.
To change the discount for all records, update the discount percentage in J4. Note that the reference to the discount is also absolute, as indicated by the $ signs.
Figure B
How to multiply without using formula in Excel?
One of Excel’s hidden treasures is its ability to multiply a series of operands by a single operand with no formula. You want to use this function when a formula is not needed.
To multiply the quantity values by the price value without a formula, first copy the Quantity values to a new column. Using the original values will damage the other expressions and formulas we’ve already used – this makes more sense once we’re done. After copying the Quantity values, do the following:
- Select the cell containing the single value you want to multiply by. In our case, that’s the price value now in K2. Press Ctrl + C to copy that value to the clipboard.
- Select the range of values you want to multiply. In our case it is H3:H13 – the copied quantity values.
- In the Clipboard group (on the Home tab), click Paste and choose Paste Special from the drop-down list.
- In the resulting dialog box, click the Multiply option (Figure C) and click OK.
Figure C
Figure D
As you can see in Figure Dthere is no formula, but the results are the same as the expression used in column E.
Microsoft Excel is flexible when it comes to math equations. In this article, you learned basic ways to multiply in Excel. In your work you will probably use them all at one time or another.
