Multiply Two Columns in Microsoft Excel and Show Results in a Third
Question details
Multiply corresponding values in two Excel columns and display each row's result in a third column.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Values are stored in columns A and B, and the user wants their row-by-row products in column C.
- Observed behavior
- Each cell in the result column should show the product of the two input cells in the same row.
Identify the first data row and confirm that both input columns contain numbers rather than numbers stored as text. If row 1 contains headers, start the formula in row 2 instead.
Multiply corresponding cells and fill the formula down
Use a simple multiplication formula to produce a separate result for every row in the two input columns.
Select cell C1, type =A1*B1, and press Enter. If your columns have headers in row 1, select C2 and enter =A2*B2 instead.
Select the cell containing the formula and drag the fill handle at its lower-right corner down to the last data row. Excel adjusts the relative references automatically, so the next row uses =A2*B2 when you start in C1.
Click a filled cell in column C and check the formula bar to confirm that it references columns A and B in the same row. For example, if A1 contains 4 and B1 contains 5, C1 should display 20.
Calculate Row-by-Row Products in WPS Spreadsheets
WPS Spreadsheets can perform the same calculation using =A1*B1. Open your workbook, enter the formula in the result column, and fill it down to multiply each pair of values.
- 1. Open your workbook: Launch WPS Spreadsheets and use Menu > Open to select your Excel workbook, or create a blank spreadsheet and enter your values in columns A and B.
- 2. Enter the result formula: Click C1, type =A1*B1, and press Enter. If row 1 contains headers, enter =A2*B2 in C2.
- 3. Copy the formula down: Select the formula cell and drag its lower-right fill handle down through the rows that contain input values.
- 4. Save the calculated workbook: Choose Menu > Save As, select the Excel .xlsx format, and save the workbook with the result column included.
Frequently Asked Questions
What formula multiplies two columns without adding the results together?
Enter =A1*B1 in C1 and fill it down. Each cell in column C will display a separate row's product rather than a single combined total.
How do I multiply columns when the first row contains headers?
Start in the first data row. For headers in row 1, click C2, enter =A2*B2, press Enter, and drag the fill handle down the result column.
Why does my multiplication formula show #VALUE!?
One of the referenced cells may contain text that Excel cannot interpret as a number. Click the input cells in columns A and B and check their contents in the formula bar. Remove nonnumeric characters or use Convert to Number if Excel displays a warning for a number stored as text.
Should I use SUMPRODUCT to multiply two columns?
Use SUMPRODUCT when you want one total of all row-by-row products, such as =SUMPRODUCT(A1:A10,B1:B10). To show a separate result for each row in a third column, use =A1*B1 and fill down instead.
Can I use the same formula in WPS Spreadsheets?
Yes. Open the workbook in WPS Spreadsheets, enter =A1*B1 in C1, and drag the fill handle down. The cell references adjust for each row, just as they do in Excel.
