How to Multiply Two Columns in Excel Row by Row

Manually calculating row-by-row product totals in large Excel spreadsheets often leads to tedious repetition and costly formula errors. When managing complex budgets across standard funding sources-such as municipal allocations or institutional endowments-precision is non-negotiable. Implementing the robust multiplication formula, =A2*B2, grants financial managers immediate operational clarity and automated accuracy.

Key Stipulation: To prevent calculation errors, both targeted columns must contain strictly numeric formatting without empty text strings. For example, multiplying "Quantity" by "Unit Price" ensures flawless line-item auditing.

Below, we will outline the step-by-step process to apply this formula down entire columns and leverage advanced array alternatives for larger datasets.

Whether you are managing a retail inventory, calculating payroll, processing invoices, or analyzing financial statements, multiplying two columns row by row is one of the most fundamental tasks you will perform in Microsoft Excel. For example, if you have a "Quantity" column and a "Unit Price" column, you will need to multiply these two values in each row to determine the "Total Cost" for every individual item.

Excel offers several ways to achieve this, ranging from the classic basic multiplication operator to advanced dynamic arrays and specialized formulas like SUMPRODUCT. In this comprehensive tutorial, we will explore every method in detail, helping you choose the best approach for your specific Excel version and workflow.

Method 1: The Basic Multiplication Formula (Asterisk Operator)

The simplest and most common way to multiply two columns row by row is by using the basic mathematical multiplication operator: the asterisk (*). This method works in all versions of Excel, from legacy desktop applications to Excel Online.

Step-by-Step Instructions:

1. Click on the first cell where you want the result to appear (for example, cell C2).
2. Type the equal sign (=) to start the formula.
3. Click on the first cell in the first column (e.g., A2), or type its coordinates.
4. Type the asterisk (*).
5. Click on the corresponding cell in the second column (e.g., B2), or type its coordinates. Your complete formula should look like this:
   =A2*B2
6. Press Enter to get the result for the first row.

Applying the Formula to the Entire Column (AutoFill):

You do not need to type this formula manually for every row. Excel makes it easy to copy the formula down the entire column using the AutoFill handle:

• Select the cell containing the formula you just created (C2).
• Hover your mouse cursor over the bottom-right corner of the cell until the cursor changes into a small black plus sign (+). This is the Fill Handle.
• Double-click the fill handle to automatically copy the formula down to the last row of your data set. Alternatively, click and drag the fill handle down to the desired row.

Method 2: Using the PRODUCT Function

Excel features a built-in mathematical function called PRODUCT. While typically used to multiply large ranges of numbers, it can also be used to multiply cell by cell.

The syntax for this function is:

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

To multiply row by row, you would enter the following in cell C2:

=PRODUCT(A2, B2)

Just like the asterisk method, you can drag the fill handle down to apply this formula to subsequent rows.

Why Use PRODUCT Instead of the Asterisk?

The main advantage of the PRODUCT function is how it handles non-numeric data. If one of your target cells contains text (such as "N/A" or "Pending"), using the basic * operator will result in a #VALUE! error. However, the PRODUCT function ignores text values and only multiplies the numbers present in the specified cells, preventing your spreadsheet from displaying unsightly errors.

Method 3: Dynamic Array Formulas (The Modern Way)

If you are using Microsoft 365 or Excel 2021 and newer, you can take advantage of Excel's powerful dynamic arrays. This method allows you to multiply entire columns row by row using a single formula written in a single cell.

Instead of referencing individual cells and dragging them down, you reference the entire ranges of both columns. For example:

=A2:A10 * B2:B10

How It Works:

1. Select the top cell of your output column (e.g., C2).
2. Type the formula referencing the entire active range of both columns: =A2:A10 * B2:B10
3. Press Enter.

Excel will automatically "spill" the results down into cells C2 through C10. You will notice a thin blue border around the output range, indicating that it is a dynamic array. If you add or change data within the referenced range, the spilled values update instantaneously without requiring you to manually re-drag any formulas.

Note: If you get a #SPILL! error, it means there is already text or data in the cells below your formula. Clear those cells to allow the formula room to spill down.

Method 4: Multiplying and Summing in One Step (SUMPRODUCT)

Often, multiplying columns row by row is just an intermediary step to finding the grand total of all those multiplications combined. If you do not actually need to see the individual row-by-row results and only want the final sum, Excel has a dedicated function for this called SUMPRODUCT.

The SUMPRODUCT function multiplies corresponding components in the given arrays and returns the sum of those products. This eliminates the need for an extra "helper" column entirely.

The syntax is:

=SUMPRODUCT(array1, [array2], ...)

Example:

If you want to multiply Column A (A2 to A10) by Column B (B2 to B10) and sum the total results directly into a single summary cell, use the following formula:

=SUMPRODUCT(A2:A10, B2:B10)

Internally, Excel calculates: (A2*B2) + (A3*B3) + (A4*B4) ... + (A10*B10) and outputs a single, clean total. This keeps your sheets streamlined and reduces clutter.

Advanced Scenario: Multiplying a Column by a Fixed Constant

Sometimes you need to multiply an entire column, row by row, by a single fixed value located in another cell-such as a sales tax rate, a commission percentage, or a currency conversion rate.

To prevent Excel's relative cell referencing from shifting your constant cell down as you copy your formula, you must use Absolute Cell References (using the dollar sign $).

Example:

Assume your row values are in Column A (starting at A2), and your fixed tax rate (e.g., 8%) is located in cell E2. Your formula in cell B2 should look like this:

=A2 * $E$2

The dollar signs lock the reference to cell E2. When you drag the formula down to row 3, the formula changes to =A3 * $E$2. The row reference for Column A updates relatively, while the tax rate remains locked on E2.

Troubleshooting & Handling Edge Cases

1. Handling Empty Cells or Zeroes

If one of the cells in your columns is blank, Excel treats it as a zero (0), resulting in a calculated product of zero. If you want to keep the output cell blank instead of displaying 0, you can wrap your multiplication in an IF statement:

=IF(OR(A2="", B2=""), "", A2*B2)

This formula checks if either cell A2 or B2 is empty. If either is blank, it returns an empty string (""); otherwise, it executes the multiplication.

2. Dealing with Text Formatting Issues

If you encounter a #VALUE! error, it usually means Excel is trying to multiply a number by text. This often occurs when numbers are accidentally formatted as text or include non-numeric characters (like "10 kg" instead of "10"). Ensure your input columns contain clean, raw numbers and use Excel's built-in Number Formatting options to add currency symbols or units of measurement.

Conclusion

Multiplying two columns row by row is a versatile and essential operation in Excel. For simple, backwards-compatible worksheets, the standard =A2*B2 approach remains the gold standard. For users on modern versions of Office, dynamic array formulas like =A2:A10*B2:B10 offer an elegant, set-and-forget solution. Finally, when your ultimate goal is simply finding a grand total, the SUMPRODUCT function bypasses the need for manual row calculations entirely. By mastering these formulas, you can significantly speed up your data analysis and build cleaner, more robust spreadsheets.