How to Multiply and Divide in Excel

Multiplication and division are two of the very first things I ever did in Excel, long before I knew what a pivot table or a VLOOKUP even was. They’re simple operations, but there are several different ways to do them depending on what you’re working with — a couple of numbers, a whole column, or values scattered across different sheets. In this article, I’ll cover every method I use to multiply and divide in Excel, along with the mistakes that trip up beginners.

The Basics: Excel Operators

Excel uses specific symbols for math operations:

• Multiplication: the asterisk *
• Division: the forward slash /

Every formula in Excel starts with an equals sign =, so multiplying 5 by 3 looks like =5*3, and dividing 10 by 2 looks like =10/2.

Step-by-Step: Multiplying Two Numbers

1. Click on an empty cell.
2. Type = followed by your first number, *, and your second number. For example: =8*4.
3. Press Enter.

The cell will now display 32.

Step-by-Step: Multiplying Cell References

More commonly, I multiply values that already exist in other cells rather than typing raw numbers:

1. Click an empty cell where you want the result.
2. Type =, then click the first cell (say A2), type *, then click the second cell (say B2).
3. Your formula will look like =A2*B2.
4. Press Enter.

Step-by-Step: Dividing Two Numbers or Cells

Division works exactly the same way, just with the / symbol:

• Raw numbers: =20/4 returns 5.
• Cell references: =A2/B2 divides the value in A2 by the value in B2.

Multiplying or Dividing an Entire Column

Let’s say I have quantities in column A and unit prices in column B, and I want the total price in column C for every row.

1. Click cell C2.
2. Type =A2*B2.
3. Press Enter.
4. Hover over the small square at the bottom-right corner of C2 (the fill handle) until the cursor turns into a thin black cross.
5. Double-click it, or click and drag it down through the rest of your rows.

Excel automatically adjusts the row references for each row, so C3 becomes =A3*B3, C4 becomes =A4*B4, and so on.

Multiplying or Dividing by a Fixed Number (Absolute Reference)

Sometimes I need to multiply an entire column by one fixed number — like converting a column of prices in USD to PKR using a single exchange rate cell.

If my exchange rate is in cell E1, and my USD prices are in column A, I don’t want the E1 reference to shift as I copy the formula down. So I use a dollar sign to lock it:

=A2*$E$1

The $ signs make E1 an absolute reference — it stays fixed no matter where I copy the formula, while A2 still adjusts normally to A3, A4, and so on as I drag down.

Multiplying or Dividing Without Typing a Formula (Paste Special)

This is a trick I use often when I need to multiply or divide an entire range by a single number, without writing individual formulas:

1. Type your number (say, 1.1 for a 10% increase) into any empty cell, and copy it (Ctrl+C).
2. Select the range of cells you want to multiply.
3. Right-click and choose Paste Special.
4. Under “Operation,” choose Multiply (or Divide).
5. Click OK.

Every selected cell is instantly multiplied by that number, permanently changing the values — this is different from a formula, since it overwrites the original numbers rather than calculating a new result.

Using the PRODUCT and QUOTIENT Functions

For multiplying several numbers or ranges at once, the PRODUCT function is often cleaner than chaining asterisks:

=PRODUCT(A2:A10)

This multiplies every value in that range together.

For division that returns only the whole number part (ignoring the remainder), I use:

=QUOTIENT(10, 3)

This returns 3, dropping the remainder — useful when I need whole units, like calculating how many full boxes of 12 items I can make from a total quantity.

Handling the #DIV/0! Error

One of the most common errors I run into is dividing by zero or by an empty cell, which produces #DIV/0!. I usually wrap my formula in IFERROR to handle it gracefully:

=IFERROR(A2/B2, 0)

This returns 0 (or any value I choose) instead of an ugly error if B2 is zero or blank.

Practical Example

Suppose I run a small shop and have a spreadsheet with:

• Column A: Product Name
• Column B: Quantity Sold
• Column C: Unit Price
• Column D: Total Revenue (Quantity × Price)
• Column E: Cost per Unit
• Column F: Profit per Unit (Unit Price − Cost per Unit)
• Column G: Profit Margin % (Profit ÷ Unit Price)

My formulas would look like:

• D2: =B2*C2
• F2: =C2-E2
• G2: =IFERROR(F2/C2, 0)

Dragging these down for every row instantly gives me revenue, profit, and margin for the entire product list.

Common Mistakes I See

• Forgetting the equals sign, so Excel treats the formula as plain text instead of calculating it.
• Not locking a fixed reference with $ when multiplying/dividing a whole column by one constant number, causing the reference to shift incorrectly as it’s dragged down.
• Dividing by a blank or zero cell without handling it, resulting in #DIV/0! errors scattered across the sheet.
• Using Paste Special Multiply/Divide accidentally on the wrong range, permanently altering original values without realizing it — I always double-check my selection first since this isn’t a formula I can trace back later.
• Mixing text and numbers, where a cell that looks numeric is actually stored as text, causing multiplication/division formulas to fail or return unexpected results.

Troubleshooting Tips

• If a formula shows as text instead of calculating, check that the cell is formatted as “General” or “Number,” not “Text,” under Home > Number Format.
• If you’re getting #DIV/0!, check whether the divisor cell is genuinely empty or zero, and consider wrapping the formula in IFERROR.
• If dragging a formula down produces wrong results, check whether you needed an absolute reference ($) for a fixed value.
• If PRODUCT or QUOTIENT return errors, confirm the range doesn’t include text or blank cells that shouldn’t be part of the calculation.

Real-World Use Cases

• Retailers calculating total revenue (quantity × price) across an entire product list.
• Students calculating percentages and averages from raw marks.
• Finance teams calculating currency conversions using a fixed exchange rate.
• Manufacturers calculating cost per unit by dividing total production cost by quantity produced.
• Households splitting shared expenses evenly among a fixed number of people.

Best Practices I Follow

• Use cell references instead of typing raw numbers directly into formulas, so your calculations update automatically if the source data changes.
• Lock fixed values with absolute references ($) whenever you’re multiplying or dividing a whole range by one constant.
• Wrap division formulas in IFERROR to avoid ugly #DIV/0! errors appearing across your sheet.
• Use Paste Special only when you intend to permanently overwrite values — for anything you might need to adjust later, a formula is safer and more transparent.
• Double-check that your numeric columns are actually formatted as numbers, not text, especially when importing data from other sources like CSV files.

Multiplying and dividing in Excel is about as basic as it gets, but the small details — absolute references, error handling, and choosing between a formula versus Paste Special — are what separate a spreadsheet that just “works” from one that’s genuinely reliable as your data grows.