4 ways to multiply in Microsoft Excel

Close up Indian woman planning budget, using calculator and laptop


There’s more than one method to multiply in Microsoft Excel, and one of them doesn’t require an expression.

Close up Indian woman planning budget, using calculator and laptop
Image: fizkes/Adobe Stock

Microsoft Excel manipulates data — that’s its purpose in a nutshell. Consequently, there are numerous ways to perform math operations, including multiplication. You can multiply literal values, you can reference the values by cells 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 for multiplying without a formula at all.

I’m using 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.

SEE: Windows, Linux, and Mac commands everyone needs to know (free PDF) (TechRepublic)

How to complete basic multiplication in Excel

When using 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 the multiplication operator. In a simple expression such as

=2*3

or

=A1*B2

Excel multiplies one operand by the other to return the product. The only thing that differs between your paper and Excel is the symbol.

How to multiply using PRODUCT() in Excel

Excel’s PRODUCT() function multiplies all referenced values and returns the product. There’s little difference between PRODUCT() and a simple expression other than you can reference ranges. This function uses the following syntax

PRODUCT(number1, [number2]…)

where Excel requires only one operand. For example, PRODUCT(5) returns 5 instead of an error. This can be a difficult issue to troubleshoot because the function’s purpose implies more than one operand.

PRODUCT() is superior to a simple expression when you have many cells to evaluate. For instance,

PRODUCT(A1:A5, B1:B5)

is the same thing 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’s doubtful that either of the previous sections are brand new information, but multiplying efficiently in Excel is a learned skill. For instance, let’s suppose you track t-shirt sales by client, size and quantity. You can include the price for each individual sale in the expression, but consider making it a constant that changes only when the price goes up or down. In a situation like this, removing the price from the expression and referencing it is much more efficient, because you change only the price value instead of updating all the expressions that evaluate the price value.

Figure A shows one operand as a constant, or input value as I like to refer to them, in action. This configuration allows you to quickly change the total for all sales by changing one single value — the price input value in H2.

Figure A

Multiply an entire column by a single value, known as a constant, or input value.

The expression

=[@Quantity]*$H$1

multiplies every value in the Quantity column by the value in H2. Notice that the H2 reference is absolute because it’s a constant — every expression refers to that single value. To change the total sale price for each record, change the price value in H2. You can do this with all kinds of calculations, not just multiplying.

Figure B shows the introduction of another constant, or input value—a discount percentage that the expression in columns F and G reference:

=IF([@Quantity]>39,[@Total]*$J$4,0)

=IF([@Quantity]>39,PRODUCT([@Total],$J$4),0)

Both expressions evaluate the same; the only difference is one uses a simple expression for multiplying 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 discount of 5%, the value in J4. If the quantity is lower than 39, enter 0.

To change the discount for all the records, update the discount percentage in J4. Take note that the reference to the discount is also absolute, as noted by the $ characters.

Figure B

The discount percentage provides a second constant in the same expression.

How to use multiply without a formula in Excel

One of Excel’s hidden treasures is the ability to multiply a range of operands by a single operand without a formula. You’ll want to use this feature when a formula isn’t necessary.

To multiply the quantity values by the price value without a formula, copy the Quantity values to a new column first. Using the original values will corrupt the other expressions and formulas we’ve already used — this will make more sense when we’re done. After copying the Quantity values, do the following:

  1. Select the cell with 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.
  2. Select the range of values you want to multiply. In our case, that’s H3:H13 — the copied Quantity values.
  3. In the Clipboard group (on the Home tab), click Paste and choose Paste Special from the dropdown.
  4. In the resulting dialog, click the Multiply option (Figure C) and click OK.

Figure C

There’s no formula, only values.

Figure D

The Paste Special feature requires no formula to multiply values.

As you can see in Figure D, there’s 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’ll probably use them all at one time or another.



Source link

more recommended stories