How to Multiply by in Excel: A Practical Guide
Learn how to multiply values in Excel using the * operator, the PRODUCT function, and cell references. This practical guide covers relative vs absolute references, real-world examples, and common mistakes to avoid.
In this guide you’ll learn how to multiply by in excel using the asterisk operator (*) and related methods. You’ll see simple formulas, how to multiply a cell by a constant, how to combine factors, and how to manage references when you copy formulas. By the end, you’ll multiply data quickly and reliably with clear examples and best practices.
Why mastering multiplication in Excel matters
How you multiply numbers in Excel is foundational to data analysis and modeling. Whether you’re calculating totals, scaling factors, or combining datasets, the ability to multiply accurately saves time and reduces errors. According to XLS Library, mastering multiplication workflows unlocks faster experimentation with hypotheses and scenario testing. If you’re new to Excel, or if you’ve previously relied on manual calculations, learning how to multiply by in excel will empower your spreadsheets and improve your confidence with data. This section introduces the core idea and sets the stage for practical, repeatable techniques you can apply immediately.
Understanding the multiplication operator
The simplest way to multiply in Excel is with the asterisk operator, represented by the symbol . A formula like =A2B2 returns the product of the values in cells A2 and B2. The operator has the same precedence as other arithmetic operators, so you can combine it with addition or subtraction in longer formulas (e.g., =A2*B2 + C2). When you press Enter, Excel computes the result and displays it in the cell. This section explains the operator’s role, behavior with zero and negative numbers, and how Excel interprets numeric text.
Multiplying a single cell by a constant
One common task is to scale an amount by a fixed factor. For example, to increase a price in A2 by 20%, you would use =A21.20. If you’re applying a constant multiplier across a column, you can write the formula once (e.g., =A2B$1) and then copy it down. The dollar sign in B$1 fixes the row reference, so when you fill the formula, Excel consistently uses the multiplier from B1 for all rows. This approach keeps your workbook tidy and consistent.
Multiplying two cells and the PRODUCT function
Multiplying two cells is straightforward with the * operator (e.g., =A2*B2). For more than two factors, or when you want to multiply a range of cells, the PRODUCT function is helpful: =PRODUCT(A2,B2,C2) or =PRODUCT(A2:B2). PRODUCT returns the product of all supplied factors, and it can be used across multiple columns or rows in a single formula. This makes it easier to manage complex calculations without nesting many operators.
Relative vs absolute references
When copying formulas, relative references adjust automatically (A2 becomes A3, etc.). Absolute references stay fixed (using $ signs, like $A$2 or B$1). This distinction is crucial when multiplying across a table, as you often need one factor to stay constant while the other changes. Use mixed references (e.g., A2*$B$1) when appropriate to control how the formula shifts as you drag the fill handle.
Copying, filling, and validating results
After creating your multiplication formula, use the fill handle to copy it down or across your sheet. Always validate a sample of results by performing a quick check, such as verifying that =A2*B2 equals the product of A2 and B2 as expected. If values come from text or appear as 0, confirm that cells are numeric and not stored as text. Consistent validation helps catch data quality issues early.
Multiplying across ranges with SUMPRODUCT
When you need to multiply corresponding elements across two ranges and then sum the results, use SUMPRODUCT. For example, =SUMPRODUCT(A2:A10, B2:B10) multiplies pairs and sums the products. This avoids intermediate helper columns and is efficient for larger datasets. As you work with bigger worksheets, SUMPRODUCT can simplify your formulas and improve readability.
Practical examples you can reuse today
Example 1: Calculating a total after applying a discount. If prices are in A2:A10 and discounts in B2:B10, use =A2:A10*(1-B2:B10) as a concept and then summarize with SUMPRODUCT as needed. Example 2: Commission calculations can be modeled as =Price*Rate, with Rate stored in a separate reference cell and absolute references used to keep the rate fixed as you copy formulas. These patterns map directly to everyday tasks and illustrate how flexible multiplication can be in Excel.
Common mistakes and how to fix them
Common errors include mixing text with numbers (which returns #VALUE!), forgetting to fix references with dollar signs, and accidentally overwriting formulas with static values. To fix, ensure cells contain numeric data, convert text to numbers if needed, and review the range references in your formula. A quick audit of your sheet can prevent cascading mistakes across many rows.
Tools & Materials
- Excel software (Microsoft 365 or Excel 2021+)(Ensure calculation mode is automatic for immediate results)
- Sample dataset with numeric values(Include a mix of integers and decimals to test multiplication)
- Digital notebook or comments pane(Use for jotting formulas and notes during practice)
- Keyboard and mouse for quick editing(Helpful for dragging the fill handle and editing long formulas)
Steps
Estimated time: 15-25 minutes
- 1
Open workbook and locate cells
Open your workbook and identify the two numeric cells you want to multiply. Confirm both are numeric (not text) to ensure Excel computes a valid product.
Tip: If a cell looks empty, check for spaces or non-printable characters that may cause text values. - 2
Enter the multiplication formula
In the target cell, type =A2*B2 to multiply the values in A2 and B2. Press Enter to see the result.
Tip: Use cell references first; you can replace with constants later if needed. - 3
Copy the formula down or across
Select the formula cell and drag the fill handle to fill adjacent cells. Excel will adjust relative references automatically.
Tip: Double-click the fill handle for auto-fill down a contiguous data region. - 4
Fix a factor with absolute references
If you need to multiply by a fixed factor in, say, B1, use =A2*$B$1 and copy down. The $ keeps B1 constant while A2 changes.
Tip: Mix absolute and relative references to control which parts move when copied. - 5
Multiply multiple factors with PRODUCT
For three factors, use =A2*B2*C2 or =PRODUCT(A2,B2,C2). This keeps the formula readable and scalable.
Tip: PRODUCT is cleaner for many factors and scales better for large datasets. - 6
Sum products across ranges
To multiply pairs across two ranges and sum, use =SUMPRODUCT(A2:A10, B2:B10).
Tip: SUMPRODUCT avoids intermediate helper columns and is efficient for large ranges. - 7
Validate results and troubleshoot
Check a sample of results manually or with a quick check formula. Investigate #VALUE!, #DIV/0!, or unexpected zeros by verifying data types.
Tip: If numbers are stored as text, use VALUE() or Text to Columns to convert them.
People Also Ask
What is the simplest way to multiply two cells in Excel?
Type =A2*B2 in a cell and press Enter. This returns the product of the two numbers in A2 and B2.
Type equals A2 times B2, then press Enter.
When should I use the PRODUCT function?
Use PRODUCT when you multiply more than two numbers or ranges. It keeps formulas readable and scalable as factors increase.
Use PRODUCT when you have many factors.
How do absolute references help in multiplication?
Absolute references (with $) keep a factor fixed when you copy formulas across rows or columns. This is essential for fixed rates or constants.
Absolute references lock a factor while you copy formulas.
What errors should I watch for when multiplying in Excel?
Watch for #VALUE! when data types are mixed. Check for numbers stored as text and ensure proper use of operators and functions.
Look for #VALUE! errors and fix numeric data types.
Can I multiply ranges without helper columns?
Yes, SUMPRODUCT multiplies corresponding elements in two ranges and sums the results, avoiding extra columns.
SUMPRODUCT multiplies ranges and sums results.
Is there a limit to how many factors I can multiply in a single formula?
Excel supports multiple factors, but for readability and maintenance, prefer PRODUCT or structured ranges for many factors.
You can multiply many factors, but keep formulas readable.
Watch Video
The Essentials
- Multiply with * or PRODUCT for clarity and scalability
- Use absolute references to lock fixed factors
- SUMPRODUCT is ideal for range-based products and sums
- Validate results to catch data-type issues
