How to Divide in Excel: Formulas, Columns and #DIV/0!

To divide in Excel, you write a formula with the forward slash: =B2/C2 divides the value in B2 by the value in C2. There is no DIVIDE function, so the slash is all you need. This guide is for beginners and job seekers, and it covers dividing cells, dividing a whole column by one number, rounding the answer and stopping the #DIV/0! error.

How to divide two numbers or cells#

Type an equals sign, the number you want to divide, a forward slash and the number you divide by. Then press Enter.

The number before the slash is the dividend (the amount being split). The number after it is the divisor (what you split by). Order matters: =12/4 is 3, but =4/12 is about 0.33.

Using cell references is better than typing numbers. When the data changes, the result updates by itself. If formulas are new to you, start with how to write your first Excel formula.

How to divide a column of numbers#

Write the formula once in the first row, then fill it down so every row divides its own cells. Here is a small purchase list:

1. Click D2 and type =B2/C2.
2. Press Enter. The result is 4.
3. Click D2 again and fill down to D5. Drag the small square at the bottom-right corner of the cell, or select D2:D5 and press Ctrl+D (Mac: ⌘D).

Excel changes the references as it fills: D3 becomes =B3/C3, D4 becomes =B4/C4, and so on. These are called relative references.

How to divide by a fixed number in another cell#

To divide every row by the same cell, lock that cell with dollar signs so it doesn't move when you fill down. Say the costs are shared between team members, and F1 holds the team size, 4:

=B2/$F$1

In row 2 this returns 12 (48 / 4). Fill it down and row 3 returns 63.75 (255 / 4). Without the dollar signs, the formula in row 3 would point at F2, which is empty, and you'd get #DIV/0!.

To add the dollar signs quickly, click on F1 in the formula bar and press F4 (Mac: ⌘T). The absolute references lesson lets you practise this until it's automatic.

Divide a range in place with Paste Special#

If you want to change the numbers themselves, not add a new column, use Paste Special. For example, to show amounts in thousands:

1. Type 1000 in an empty cell and copy it (Ctrl+C; Mac: ⌘C).
2. Select the numbers you want to change.
3. Open Paste Special (Ctrl+Alt+V; Mac: ⌘+Control+V).
4. Choose Divide and click OK.

This overwrites the original values, so keep a copy if you might need them.

Why brackets matter when you divide#

Excel follows the normal order of operations: it divides and multiplies before it adds and subtracts. So these two formulas give different answers:

If you want to divide a total, put the total in brackets first. This is a common mistake when people average two numbers by hand. Division works with the other operators in the same way, so see also how to multiply in Excel and how to subtract in Excel.

How to round the result of a division#

Use the ROUND function when you need a result with a fixed number of decimals. Pens cost 25 for 3, which is 8.333333…:

=ROUND(B5/C5,2)

This returns 8.33. ROUND changes the actual value. Formatting the cell to show 2 decimals only changes what you see, and Excel still uses 8.333333 in later sums. Use ROUNDUP or ROUNDDOWN if you must always round one way, such as when you need whole boxes.

When you divide one value by a total to get a share, format the result as a percentage. For example, =45/60 returns 0.75, which shows as 75%. The guide to percentage formulas in Excel covers shares, percent change and more.

How to stop the #DIV/0! error#

Excel shows #DIV/0! when the divisor is zero or empty. If row 6 had Labels with a cost of 20 and 0 units, =B6/C6 would return #DIV/0!. You have two common fixes.

Check the divisor first with IF:

=IF(C6=0,"",B6/C6)

Or catch any error with the IFERROR function:

=IFERROR(B6/C6,0)

The IF version returns an empty-looking cell only when the divisor is 0 or blank. The IFERROR version returns 0 for any error, including a typo, so use it with care. The IFERROR lesson lets you practise it, and the full guide to fixing the #DIV/0! error explains when to show 0, a blank or a label.

How to get the whole number and the remainder#

Sometimes you need "how many full groups, and how many left over". Excel has two functions for this:

• =QUOTIENT(50,12) returns 4, the whole-number part of 50 / 12.
• =MOD(50,12) returns 2, the remainder.

So 50 items fill 4 boxes of 12, with 2 items left. =INT(50/12) also returns 4 for positive numbers.

Common mistakes when dividing in Excel#

These slips cause most wrong answers:

1. Using a backslash. =B2\C2 is not a formula. Use the forward slash /.
2. Forgetting the equals sign. Without =, Excel stores B2/C2 as text, or turns 1/2 into a date.
3. Numbers stored as text. A value like "48" imported as text can cause #VALUE! or wrong results. Convert it with VALUE.
4. A moving divisor. If every row should divide by F1, write $F$1.
5. Rounding with formatting only. The cell shows 8.33, but the value is still 8.333333.

Practise it#

You learn division fastest by typing it. Practise your first Excel formulas in a live spreadsheet: you write =B2/C2-style formulas and the sheet checks them instantly. Then lock a divisor in the absolute references lesson and handle zeros in the IFERROR lesson.