Multiplying cells in Excel is a fundamental operation that powers everything from basic arithmetic to complex financial modeling. Instead of typing static numbers, you link together cell references so that results update instantly when source data changes. This approach saves time, reduces errors, and builds dynamic spreadsheets that behave like calculators.
Basic Multiplication Formula
To multiply two cells, type an equal sign, click the first cell, add an asterisk, then click the second cell, and press Enter. For example, entering =A1*B1 multiplies the values in A1 and B1 and displays the result in the cell where the formula is entered. You can also combine more than two cells with additional asterisks, such as =A1*B1*C1, to calculate the product of three values in a single step.
Using the PRODUCT Function
The PRODUCT function is designed specifically for multiplication and offers cleaner syntax when working with many numbers. You enter =PRODUCT(A1, B1) to multiply two cells or =PRODUCT(A1:C1) to multiply an entire range in one go. This function ignores text and empty cells, which helps prevent errors that might occur if you accidentally include non-numeric data in your references.
Method | Example | Best For
Simple Asterisk | =A1*B1 | Quick multiplication of two or three cells
PRODUCT Function | =PRODUCT(A1, B1, C1) | Multiplying many cells or ranges at once
Multiplying an Entire Column or Row
To multiply every value in a column by a constant, use an absolute reference so the constant does not shift when you copy the formula down. For example, entering =A1*$B$1 and dragging down keeps B1 fixed while A1 changes row by row. This technique is ideal for applying discounts, tax rates, or conversion factors to an entire list of numbers without editing each formula individually.
Array Formulas for Advanced Scenarios
When you need to multiply corresponding items in two ranges and sum the results in a single cell, an array approach can streamline your workflow. In older versions of Excel, you press Ctrl+Shift+Enter to create an array formula, such as {=SUM(A1:A5*B1:B5)}, which multiplies pairs of values and adds them together. Modern Excel versions support the SUMPRODUCT function directly, so typing =SUMPRODUCT(A1:A5, B1:B5) delivers the same result without special key combinations.
Handling errors is an important part of building robust multiplication models. The IFERROR function lets you trap #N/A, #VALUE!, or division by zero issues and return a clean value instead of error messages. For example, wrapping your calculation as =IFERROR(A1*B1, 0) ensures that cells display zero when the multiplication cannot be completed, keeping reports tidy and professional-looking.
Practical Tips for Reliable Results
Format cells as numbers before entering multiplication formulas to avoid unexpected text concatenation. Keep related data together in contiguous ranges, which makes it easier to select entire blocks for functions like PRODUCT. Use named ranges for constants such as tax rates or currency factors, because descriptive names are easier to read and update than cell references scattered across a workbook.
