Cellwise Try it free

Excel guide · 3 min read

How to divide in Excel

Quick answerType = and put a forward slash between the two values, for example =B2/C2, then press Enter. To divide every row by one fixed cell, lock it with dollar signs, =B2/$G$1. If the divisor can be 0 or blank, use =IF(C2>0,B2/C2,"") so you get an empty cell instead of #DIV/0!.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCDEFG
1RepRevenueDealsPallet boxes250
2Dana8460018Boxes per layer36
3Kofi6120012
4Mei457500
5Raj9730023
6Sofia38400

Method 1: the / operator

Excel has no DIVIDE function. Use / between numbers or cells. The backslash \ and the ÷ sign do not work. Here revenue divided by deals gives the average deal size for each rep.

=B2/C2

Result: 4700

4,700: Dana closed 84,600 over 18 deals.

Why you get #DIV/0! (blanks count as zero)

Dividing by 0 is undefined, so Excel returns #DIV/0!. An empty cell counts as 0 in arithmetic, so Sofia, whose deal count has not been entered, gets the same error as Mei, who really closed 0 deals.

One error cell is annoying, but it also spreads: any SUM or AVERAGE that includes it becomes #DIV/0! too.

=B4/C4

Result: #DIV/0!

#DIV/0! because Mei has 0 deals.

Fix: test the divisor first

Check the divisor before dividing. C6>0 is FALSE for both 0 and blank, so this one test covers both cases. Return "" for an empty-looking cell, or a label such as "No deals" if readers need to know why.

IFERROR(B6/C6,"") is shorter, but it hides every error, including a typo in a cell reference or text in the revenue column. Use the IF test when the only expected problem is a zero.

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

Result: "" (empty text, so the cell looks blank)

An empty cell for Sofia instead of #DIV/0!. Rows with deals still show their average.

Divide by a fixed cell or a total ($)

Share of total is the classic case: each rep divided by the team total. Lock the total with $ so it does not move as you fill down. Then format the result as a percentage (Home > %) instead of multiplying by 100.

=B2/SUM($B$2:$B$6)

Result: 0.25851795264 shows as 25.9% with Percent format

0.2585, Dana brought in about a quarter of team revenue.

Average of a ratio: divide the totals

To get the team average deal size, do not average the per-rep column. Averaging ratios gives every rep equal weight, whether they closed 1 deal or 23. Divide total revenue by total deals instead.

Only include rows where both numbers are known. Sofia is left out here because her deals are missing.

=SUM(B2:B5)/SUM(C2:C5)

Result: 5450

5,450 per deal across Dana, Kofi, Mei and Raj (288,850 over 53 deals).

Whole number and remainder: INT and MOD

How many full layers of 36 boxes fit in a 250-box pallet, and how many are left over? INT(G1/G2) drops the decimals to give the whole number, and =MOD(G1,G2) gives the remainder: 34 boxes. Excel also has QUOTIENT(250,36), which returns the same whole number.

Use ROUND, not INT, when you want the nearest whole number rather than rounding down.

=INT(G1/G2)

Result: 6

6 full layers. =MOD(G1,G2) shows the 34 boxes left over.

Common mistakes

Writing =B2/C2*12 to divide by a year of months

Excel works left to right, so that is (B2/C2)*12. Use brackets: =B2/(C2*12).

The fixed divisor moved when filling down

B2/G1 becomes B3/G2, B4/G3. Lock it as $G$1.

Using IFERROR to hide every problem

IFERROR also hides broken references and text. Test the divisor with IF(C2>0,...) when zero is the only expected case.

Multiplying by 100 for a percentage

=B2/SUM($B$2:$B$6)*100 shows 25.85 and breaks if someone then applies % format (2585%). Leave the decimal and apply Percent format.

FAQ

What is the divide formula in Excel?

Use a forward slash: =A1/B1. There is no DIVIDE function in Excel.

How do I divide a whole column by one number?

Put the number in a cell and use =A2/$E$1, then fill down. To change values in place, copy the number and use Paste Special > Divide.

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

Test the divisor: =IF(B2>0,A2/B2,""). Or wrap the division in IFERROR(A2/B2,"") if you are sure no other errors can occur.

How do I divide and get only the whole number?

Use =INT(A1/B1) or =QUOTIENT(A1,B1). Use =MOD(A1,B1) for the remainder.

Functions used

Related guides

Updated