How to Multiply Columns in Excel: 4 Methods

Picture a spreadsheet with quantities in column A and unit prices in column B. Filling column C with the totals looks trivial. If you’re searching for how to multiply columns in Excel, this is usually the job you’re trying to finish. So you type a formula in the first cell, drag it down, and half the numbers come out wrong. Row two looks fine. Row three is nonsense, and by row twenty the whole column has quietly fallen apart.

Most people blame themselves here. The formula was right, after all. What actually broke is something Excel does on its own when you copy a formula, and almost nobody explains it before you hit the wall.

That one behavior – absolute references versus relative references – is why a working multiplication formula stops working partway down a column. This guide walks through the four real ways to multiply columns and flags exactly where each one falls apart, so you can catch the failure before it spreads.

Where the usual advice quietly fails

Search this topic and you’ll get a tidy list. Type =A2*B2, drag the corner, done. Use PRODUCT. Try Paste Special. All correct, all incomplete.

The gap sits in what happens when you copy a formula downward. Excel treats cell references as relative by default, meaning it shifts them to match each new row. That’s usually what you want when you’re multiplying two columns side by side, because row 3 should use row 3’s values, not row 2’s.

But the moment one side of your multiplication is a single cell – a tax rate, a conversion factor, a fixed price – that automatic shifting turns into a bug. Your formula in row 2 points at the rate cell. Copied to row 3, it points one cell lower, which is probably empty. Excel multiplies by nothing and hands back a zero or an error.

Guides list the methods and never mention this. So the reader does everything “right,” watches the column collapse, and has no idea which of the four steps caused it. The fix is one character. You just have to know where it goes.

How to multiply columns in Excel: the four methods that actually work

Let’s break down each approach, starting with the one you’ll reach for most.

Method 1: The asterisk formula. Excel has no dedicated multiply button, so multiplication runs on the asterisk. To multiply two columns Excel-style, click the first empty cell in your result column and type:

=A2*B2

Press Enter. That cell now holds the product of A2 and B2. It’s the core of nearly every method that follows, and Microsoft’s own support pages treat it as the default building block for multiplying a column of numbers.

Method 2: Fill down. One formula isn’t a column. To multiply an entire column Excel needs that formula repeated on every row, and dragging by hand gets old fast.

Select the cell with your formula. See the small square at the bottom-right corner? That’s the fill handle. Double-click it and Excel copies the formula down as far as your adjacent data runs. Drag it manually if you want more control. Either way, Excel adjusts the references per row, so =A2*B2 becomes =A3*B3, then =A4*B4, and on down. As a tutorial from DataCamp on multiplying cells and columns points out, this is the fastest path for two matched columns.

=PRODUCT(A2,B2)

Or across a range:

=PRODUCT(A2:D2)

PRODUCT multiplies everything you feed it. Handy when a row has four or five factors and you’d rather not chain asterisks. It also ignores empty cells and text instead of throwing an error, which the plain asterisk won’t always do. Fill it down the same way as any other formula.

Method 4: Paste Special multiply. The first three write results into a new column. Paste Special is different – it changes your existing numbers in place, no third column needed.

Say column A holds prices and you want every one bumped by a fixed factor. Type that factor into an empty cell, copy it, select your price column, then open Paste Special (Ctrl+Alt+V), choose Multiply under Operations, and click OK. Excel multiplies each selected cell by the copied value and overwrites it. How-To Geek’s walkthrough on multiplying columns in Excel covers this one well.

Here’s the catch, though: Paste Special is destructive. There’s no formula left behind, so if the factor changes later you’re redoing the whole thing. Use it for one-off adjustments, not living spreadsheets.

The dollar sign problem, explained properly

This is the part the other guides skip, and it’s the whole reason your column broke.

You want to multiply a column by a fixed number, say a tax rate sitting in cell E1. You type =A2*E1, get the right answer, and fill down. Row 2 is correct. Everything below it is wrong.

Watch what Excel does. In row 3 your formula silently becomes =A3*E2. The A3 part is fine – that’s the relative shift working as intended. But E1 also shifted, to E2, which is empty. Row 4 uses E3. Every row is now multiplying by a blank cell.

The reference to your rate cell needs to stay put no matter where the formula gets copied. That’s the dollar sign’s job. Locking it looks like this:

=A2*$E$1

The $ before the column letter freezes the column. The $ before the row number freezes the row. With $E$1, Excel keeps pointing at E1 on every single row, while A2 still slides to A3, A4, and down. One part moving, one part locked – that’s exactly what a fixed-multiplier column needs.

One shortcut worth memorizing: click the cell reference inside the formula bar and press F4. Excel cycles through the lock options – E1, $E$1, E$1, $E1 – so you never have to type the dollar signs by hand.

Which method for which job

Two matched columns, quantity times price? Asterisk formula, fill down. Multiplying by a single fixed cell? Same approach, but lock that cell with $ first. Several factors per row means PRODUCT. And a one-off price bump you’ll never revisit is a Paste Special job.

Troubleshooting the common breaks

A few failures show up again and again.

#VALUE! errors almost always mean one of your cells contains text, or a number stored as text with a stray space or currency symbol. Clean the source cell, or switch to PRODUCT, which skips non-numeric entries instead of choking on them.

Zeros where you expected numbers point straight back to the dollar-sign issue. Click into a broken row’s formula and check whether your fixed reference has drifted. If $E$1 turned into E2 somewhere, you filled down without locking it.

Results that don’t update usually mean calculation got flipped to manual. Head to Formulas, then Calculation Options, and set it back to Automatic. Rare, but it happens after opening files from other people.

A column of formulas showing as text means the cell format is set to Text, so Excel reads =A2*B2 as a literal string. Change the format to General or Number, then re-enter the formula.

GeeksforGeeks has a solid rundown of these edge cases in their piece on multiplying numbers, cells, and columns if you want more examples.

FAQ

Why does my formula work in the first row but not below it?
You’re multiplying by a fixed cell that isn’t locked. Excel shifts the reference down as you fill, so it stops pointing at the right cell after row one. Add dollar signs, $E$1, and refill.

What’s the difference between =A2*B2 and =PRODUCT(A2,B2)?
Nothing, for two cells – they return the same result. PRODUCT earns its keep when you have several factors or a range, and it politely ignores blank and text cells instead of erroring.

How do I multiply an entire column without dragging?
Enter your formula in the first cell, then double-click the fill handle at the cell’s bottom-right corner. Excel copies it down to match your data’s length automatically.

Can I multiply two columns without creating a third column?
Yes, with Paste Special multiply, but it overwrites your original numbers and leaves no formula behind. Fine for a one-time change, risky for anything you’ll edit later.

What does F4 do in a formula?
It toggles the reference between relative and absolute. Click a cell reference in the formula bar, press F4, and Excel cycles through E1, $E$1, E$1, and $E1, saving you from typing dollar signs by hand.

The short version

Four methods, one recurring trap. The asterisk formula and fill-down handle most column multiplication, PRODUCT tidies up multi-factor rows, and Paste Special adjusts values in place. That’s how to multiply columns in Excel without letting the formula break halfway down. Whenever one side of the equation is a single fixed cell, lock it with $ before you copy the formula down. That one character is the entire difference between a column that works and one that breaks at row two.

Getting Excel to behave in the first place starts with a legitimate, activated copy of Office – no nag bars, no feature lockouts.