In Microsoft Excel, performing multiplication is a foundational skill that supports everything from simple budgeting to complex financial modeling. Unlike the addition function which has a dedicated "AutoSum" button, multiplication is primarily handled through a specific operator—the asterisk (*)—and specialized functions like PRODUCT and SUMPRODUCT.

To multiply in Excel, the most common formula is =A1*B1. This multiplies the value in cell A1 by the value in cell B1. If you need to multiply a large range of cells, the function =PRODUCT(A1:A10) is more efficient.

The Basic Multiplication Operator: Using the Asterisk

The asterisk (*) is the standard mathematical operator for multiplication in Excel. It is used in the same way you would write a math problem, but it must always begin with an equals sign (=) to tell Excel that you are performing a calculation.

Multiplying Constant Numbers

For a quick calculation where the numbers aren't stored in cells, you can type them directly into the formula bar.

  • Formula: =10*5
  • Result: 50

This is useful for one-off calculations, but it is not considered best practice for spreadsheets because it makes the data "hidden" inside the formula.

Multiplying Cell References

The true power of Excel lies in cell references. When you multiply cells rather than numbers, the result updates automatically if the source data changes.

  1. Enter 10 in cell A1.
  2. Enter 5 in cell B1.
  3. In cell C1, type =A1*B1.
  4. Press Enter.

If you change the 10 in A1 to 20, cell C1 will instantly update to 100. In my experience building dynamic financial reports, using cell references is the only way to ensure your data remains accurate as your inputs evolve.

Multiplying More Than Two Cells

You can chain multiple cells together using the asterisk operator. For example, to calculate the volume of a box where length, width, and height are in cells A2, B2, and C2:

  • Formula: =A2*B2*C2

While this works perfectly for a few cells, it becomes cumbersome once you are dealing with more than five or six variables. In those cases, functions are a cleaner alternative.

When to Use the PRODUCT Function

The PRODUCT function is an alternative to the asterisk operator. While =A1*B1 is faster for two cells, PRODUCT is far superior when dealing with multiple ranges or mixed data types.

Syntax of the PRODUCT Function

=PRODUCT(number1, [number2], ...)

The "number" arguments can be specific numbers, cell references, or entire ranges.

Key Advantages of PRODUCT Over the Asterisk

In our testing with large-scale data entry sheets, the PRODUCT function revealed a significant advantage: it ignores text and empty cells.

If you use =A1*A2*A3 and cell A2 contains a text string like "N/A" or a space, Excel will return a #VALUE! error. However, if you use =PRODUCT(A1:A3), the function will simply ignore the text in A2 and multiply A1 by A3. This makes your spreadsheet much more resilient to data entry errors.

Multiplying Multiple Ranges

You can use PRODUCT to multiply non-contiguous ranges of data simultaneously.

  • Example: =PRODUCT(A2:A10, C2:C10, 5) This formula multiplies all numbers in the range A2 through A10, then multiplies that result by every number in C2 through C10, and finally multiplies everything by 5.

How to Multiply an Entire Column by a Single Number

A frequent task in business is applying a single multiplier—such as a tax rate, a discount, or a currency conversion factor—to a long list of prices. To do this efficiently, you must understand Absolute Cell References.

The Problem with Relative References

If you have prices in column A (A2:A100) and a tax rate in cell B1, and you try to use =A2*B1 and drag it down, the formula will fail. In the next cell, Excel will try to multiply =A3*B2, then =A4*B3. Since B2 and B3 are likely empty or contain text, the calculation breaks.

The Solution: Using the Dollar Sign ($)

To "lock" the multiplier cell, you use the dollar sign to create an absolute reference.

  1. Assume your tax rate is in cell D1.
  2. Your first price is in A2.
  3. In cell B2, type: =A2*$D$1.
  4. Press Enter.
  5. Hover over the bottom-right corner of cell B2 until a small cross (the Fill Handle) appears. Double-click it or drag it down to the bottom of your list.

By using $D$1, you are telling Excel: "As I move this formula down, change the row for column A, but never change the reference to D1."

Advanced Calculations with SUMPRODUCT

One of the most powerful tools in an analyst's arsenal is the SUMPRODUCT function. It is designed to multiply corresponding components in given arrays and return the sum of those products.

Scenario: Calculating Total Sales

Imagine a spreadsheet with:

  • Column B: Quantity Sold
  • Column C: Unit Price

Without SUMPRODUCT, you would need a helper column (Column D) to calculate =B2*C2, =B3*C3, etc., and then use a SUM formula at the bottom to add them all up.

With SUMPRODUCT, you can do it in one single cell:

  • Formula: =SUMPRODUCT(B2:B50, C2:C50)

Why SUMPRODUCT is Better

  1. Efficiency: It eliminates the need for "helper columns," keeping your spreadsheet cleaner.
  2. Accuracy: It reduces the risk of someone accidentally deleting a single row's multiplication formula in a long list.
  3. Versatility: It can handle conditional multiplication (e.g., only multiply and sum if the item belongs to a specific category), though this requires more advanced syntax.

Multiplying by Percentages in Excel

Percentages are essentially decimals (e.g., 20% is 0.20). Excel handles this naturally, but there are two ways to write the formula.

Method 1: Using the Percentage Symbol

If you want to calculate a 15% tip on a $100 bill:

  • Formula: =100*15% Excel automatically treats "15%" as 0.15.

Method 2: Multiplying by the Decimal

  • Formula: =100*0.15 This is mathematically identical but sometimes preferred in data-heavy sheets where the "15" might be a raw number in a cell that needs to be divided by 100 first.

Calculating an Increased Total (Markup)

To find the total cost including a 20% markup, use the following logic:

  • Formula: =A1*(1+20%) This adds the 20% directly to the original value in one step.

Matrix Multiplication Using MMULT

For engineering, scientific, or high-level financial analysis, you may need to multiply two matrices. This is not the same as multiplying individual cells; it follows the rules of linear algebra.

How to use MMULT

  1. Select a range of cells that will hold the resulting matrix. (The size must be correct based on the input matrices).
  2. Type the formula: =MMULT(Array1, Array2).
  3. If you are using an older version of Excel (pre-Microsoft 365), you must press Ctrl + Shift + Enter. In modern Excel, it will automatically "spill" into the adjacent cells.

Matrix multiplication is a niche requirement, but knowing it exists within Excel's standard functions can save hours of manual calculation in specialized fields.

The Secret "Paste Special" Multiplication Method

Sometimes you don't want a formula at all. You just want to change the raw numbers in your cells permanently. For example, you have 1,000 prices and you want to increase them all by 10% immediately without adding new columns.

  1. Type your multiplier (e.g., 1.1 for a 10% increase) in an empty cell.
  2. Copy that cell (Ctrl + C).
  3. Select the range of prices you want to change.
  4. Right-click and select Paste Special....
  5. In the dialog box, under the "Operation" section, select Multiply.
  6. Click OK.

Your original prices are now replaced with the new, higher values. This is a destructive action (you lose the original numbers), so I always recommend creating a backup or testing on a small sample first.

Troubleshooting Common Excel Multiplication Errors

Even for pros, Excel can sometimes return errors. Here is how to fix the most common ones associated with multiplication.

The #VALUE! Error

This is the most common error. It occurs when Excel tries to multiply a number by something it doesn't recognize as a number.

  • Cause: A cell in your formula contains a letter, a hidden space, or a special character.
  • Fix: Use the ISNUMBER function to check the offending cells. Alternatively, use the PRODUCT function instead of the asterisk, as PRODUCT ignores text.

The #REF! Error

  • Cause: You deleted a cell, row, or column that was part of your multiplication formula.
  • Fix: You will need to rewrite the formula or use Ctrl + Z to undo the deletion immediately.

Why is my result Zero (0)?

  • Cause: You might be multiplying by an empty cell. In Excel's multiplication logic, an empty cell (referenced by an asterisk) is treated as zero.
  • Fix: Ensure your data range is fully populated. If you expect some cells to be empty and don't want them to turn the whole result into zero, use the PRODUCT function.

Best Practices for Spreadsheet Integrity

To ensure your multiplication formulas remain robust and easy to audit, follow these guidelines:

  1. Label Your Constants: If you use a fixed multiplier (like a tax rate), put it in a clearly labeled cell (e.g., "Tax Rate") and use a Named Range or an absolute reference.
  2. Avoid Hard-coding: Never type =A1*0.08 if the 0.08 can change. Put 0.08 in a cell so you can update it in one place without editing every formula.
  3. Use Excel Tables: When you use the Ctrl + T shortcut to create a Table, multiplication formulas in columns will automatically expand as you add new rows, which prevents manual errors.
  4. Audit with "Trace Precedents": If a result looks wrong, go to the Formulas tab and click Trace Precedents. Excel will draw arrows showing exactly which cells are being multiplied.

Conclusion

Mastering the Excel multiply formula is about choosing the right tool for the specific task. For simple, one-off calculations between two cells, the asterisk (*) is fast and intuitive. However, as your data grows in complexity, transitioning to the PRODUCT function offers better error handling, while SUMPRODUCT provides a powerful way to aggregate data without cluttered helper columns. By combining these techniques with absolute references and proper data auditing, you can build spreadsheets that are both efficient and accurate.

FAQ

What is the shortcut for multiplication in Excel?

There is no "ribbon shortcut" like AutoSum, but the keyboard symbol is the asterisk (*), usually found at Shift + 8 or on the numeric keypad.

Can I multiply a cell by a percentage?

Yes, you can multiply a cell directly by a percentage by typing =A1*10%.

How do I multiply two columns in Excel?

Enter the formula for the first row (e.g., =A2*B2) and then drag the Fill Handle down to the rest of the column.

Why does Excel multiply by zero when a cell is empty?

When using the asterisk operator, Excel treats an empty cell as a numeric 0. To ignore empty cells instead of treating them as zero, use the PRODUCT function.

How do I multiply multiple cells and then sum them?

Use the SUMPRODUCT function. For example: =SUMPRODUCT(A2:A10, B2:B10) will multiply each pair and then provide the total sum.