How to Divide in Excel (and Stop the #DIV/0! Error)

Dividing in Excel looks like it should be the easiest job on the list, and mostly it's, right up until Excel turns your sum into a date or fills the column with #DIV/0! errors. Both problems have simple causes, and Microsoft's help pages explain them clearly.

I'll start with the basic formula, then show you how to divide a whole column by one number, how to get a whole-number answer with QUOTIENT, and how to stop those error messages cluttering your sheet.

The division formula in Excel

The forward slash is the division sign. Microsoft points out that there's no DIVIDE function, so the slash is all you need.

1. Click an empty cell.
2. Type =10/5.
3. Press Enter. The cell shows 2.

The equal sign matters more here than anywhere else. Microsoft warns that without it, Excel reads what you type as a date. Type 7/30 on its own and you may see 30-Jul. Type 12/36 and Excel converts it into a date and shows 1-Dec. If a cell ever surprises you with a date, my guide to changing date formats in Excel helps you spot what Excel did.

Dividing one Excel cell by another

Pointing at cells keeps the formula live, so the answer changes when the numbers do.

1. Put the number you're dividing in A2 and the number you're dividing by in A3.
2. Click another cell and type =.
3. Click A2, type /, then click A3.
4. Press Enter.

Microsoft's own example puts 15000 in A2 and 12 in A3, and =A2/A3 returns 1250. That's the sort of sum I'd use to turn a yearly total into a monthly average across 12 months.

Dividing a whole column by one number in Excel

Say column A holds totals and you want each one divided by a single number sitting in C2. If you write =A2/C2 and copy it down, Excel shifts C2 to C3, C4 and beyond. Those cells are empty, so you'll get errors.

The fix is an absolute reference, which keeps C2 locked:

1. In B2, type =A2/ and click C2.
2. With the cursor still beside C2 in the formula, press F4. Excel adds symbols before the C and the 2.
3. Press Enter.
4. Drag the formula down column B, or double-click the small square at the bottom-right of B2.

Every row now divides by C2. Change the number in C2 and the whole column updates.

Getting a whole-number answer with QUOTIENT in Excel

Sometimes you only want the whole part of a division. How many full boxes can you pack from a pile of stock, or how many complete weeks are in a run of days? QUOTIENT throws away the remainder and keeps only the whole number.

Formula	What happens	Result
=5/2	Standard division keeps the decimal	2.5
=QUOTIENT(5,2)	QUOTIENT drops the remainder	2

The syntax is QUOTIENT(numerator, denominator): the number being divided first, then the number to divide by. Microsoft notes that if either part isn't a number, QUOTIENT returns #VALUE!.

QUOTIENT isn't the same as rounding. It always drops the decimal, so anything from 2 up to just under 3 comes back as 2.

Fixing the #DIV/0! error when dividing in Excel

Microsoft says Excel shows #DIV/0! when a number is divided by zero, such as =5/0, or when a formula points at a cell that's 0 or blank. In a half-finished sheet, that's often just a gap waiting for data.

Check the cell you're dividing by

Make sure it isn't zero or empty, or point the formula at a different cell that holds a real value.

Hide the error with IF

When a blank cell is expected, wrap the division in IF. Microsoft's examples:

• =IF(A3,A2/A3,0) shows 0 until A3 has a value.
• =IF(A3,A2/A3,"") leaves the cell looking empty instead.

Hide the error with IFERROR

=IFERROR(A2/A3,0) returns 0 for any error. Microsoft's warning is worth repeating: IFERROR hides every error, not just #DIV/0!, so check your formula works before you add it. Otherwise a real mistake gets covered up.

Division sums that come up in small business sheets

A few divisions I see small business owners and creators use all the time:

• Average per order: total takings divided by the number of orders.
• Monthly view: a yearly total divided by 12.
• Per-item share: a delivery or packaging total divided across the items in a shipment.
• Engagement per post: total interactions divided by the number of posts.

Most of these start with a total, so adding up a column with SUM usually comes first, then the division sits right beside it.

Dividing in Excel for Mac, the web and the mobile app

The slash formula is the same in every version, because it's typed into the cell rather than picked from a menu. Microsoft's QUOTIENT help applies to Excel for Mac as well as Windows. If F4 seems to change your screen brightness instead of locking the cell, your keyboard's function row is set to media keys, so try it together with the Fn key.

Browser and phone versions of Excel handle division identically. Clicking cells to build a formula is fiddly on a small touchscreen, though, so I tend to set the sheet up at a desk and just check numbers on the go.

If Excel division gives a strange result

• A date appears: you left out the equal sign. Delete the cell's contents, set the format to General and type the formula again starting with =.
• Too many or too few decimals: the result is right, but the number format decides what you see. Use the increase or decrease decimal buttons on the Home tab.
• #VALUE! with QUOTIENT: one of the cells holds text instead of a number.
• Errors after copying down: the cell you divide by wasn't locked with F4.

Frequently asked questions

What's the division symbol in Excel?

The forward slash (/). Type =10/5 and Excel shows 2. Microsoft notes there's no DIVIDE function, so the slash is the way to divide.

Why does Excel turn my division into a date?

Microsoft says that without an equal sign, Excel reads entries like 7/30 as dates. Always start the formula with =.

How do I divide a column by the same number in Excel?

Write =A2/C2, press F4 with the cursor beside C2 to lock it, then copy the formula down. Every row divides by the value in C2.

How do I get rid of #DIV/0! in Excel?

Check the cell you're dividing by isn't 0 or blank. To hide the error while you wait for data, use =IF(A3,A2/A3,0) or =IFERROR(A2/A3,0).

How do I divide without a remainder in Excel?

Use QUOTIENT. Microsoft's example shows =QUOTIENT(5,2) returns 2, while =5/2 returns 2.5.