Excel is an extremely useful spreadsheet tool that can be used for many applications. The possibilities for data management are endless, from work-related accounting and expenses to budgeting at home. For example, you may need to calculate your monthly salary and expenses. This can be done quickly and easily with a simple Excel formula.
For this reason, it's worth learning the different methods of dividing using Excel. Not only will being proficient in Excel save you time, but it will also likely come in handy in the workplace.
Using Excel for division may seem daunting at first, particularly when there are multiple functions that you can use to achieve your goal. But thankfully, you don't need to be a data whizz to make use of the software. With a little guidance, you'll be away in no time.
In this article, we'll cover all you need to know about dividing in Excel, from the rules of writing Excel formulas to the different methods of dividing. We'll also run through the order of operations that Excel follows so when it comes to writing formulas, they are accurate. Let's get started!
Yes, you can divide in Excel using different formulas. However, before we jump straight into the different methods of division, we'll cover the basics of division and the rules of the maths protocol called the 'order of operations'. This will give you a solid understanding of how dividing in Excel works.
In mathematics, a division formula is represented by the use of the obelus symbol (÷). A division formula can also be displayed in shorthand as a fraction whereby the top number is the numerator, and the bottom number is the denominator — the numerator is the number that is divided by the denominator.
However, it may surprise you to learn that Excel doesn't have a divide function. This said, to divide in Excel, you can use the forward slash symbol (/) instead of the division symbol (÷).
This means you can divide in Excel by doing the following:
- Select an empty cell and type in the numerator
- Follow the numerator with the / symbol on the keyboard
- Follow it with the denominator
- Press enter to complete the formula
- Excel will then display the result
The formula will then be expressed as =a/b where:
- a is the numerator which is the number being divided
- b is the denominator which is the number you are dividing by
Before you jump straight into dividing in Excel, you'll need to follow a few basic rules for writing Excel formulas:
- All Excel formulas must begin with an equals sign (=) so that it is recognized as a formula.
- When you type a formula into a cell and press enter, the cell will only display the result, not the formula. For this reason, it is beneficial to use cell references instead of simply typing out the formula. This is because if you need to go back to the values later, they will be easier to understand.
- When you use arithmetic operators in your Excel formula, you need to remember and follow the order of operations to ensure you get the right result. We'll explain the order of operation in the next section.
Order of operations
When Excel evaluates a formula, it follows the maths protocol called the 'order of operations' — this is displayed by the acronym PEMDAS. PEMDAS stands for:
- P: Parentheses
- E: Exponents
- M: Multiplication
- D: Division
- A: Addition
- S: Subtraction
So if you were to write a formula with expressions in parentheses, that would be evaluated first, and so on down the order.
It is also worth noting that the order of operations for Excel has been slightly adjusted in alignment with the formula syntax that Excel uses. For this reason, Excel's order of operations is as follows:
| Order | Operation | Symbol | Example |
| 1 | Parentheses | () | =(5-2)*4 = 12 |
| 2 | Reference operators | : , | =SUM(A1:A5) |
| 3 | Negation | - | =-3^2 = 9 |
| 4 | Percent | % | =5%*100 = 5 |
| 5 | Exponentiation | ^ | =5^2*2 = 50 |
| 6 | Multiplication and Division | * / | =7-6/2 = 4 |
| 7 | Addition and Subtraction | + - | =6/2=1 = 4 |
| 8 | Concatenation | & | ="score: "&5+1 = score: 6 |
| 9 | Logical operators/comparisons | >< + >< | =3^2>5+3 = TRUE |
Now you know the rules for writing Excel formulas and how equations are evaluated, you're ready to start using Excel functions to divide!
There are several ways to divide in Excel depending on what you are trying to calculate. In this section, we'll cover the different methods of doing this:
- Divide numbers in a cell
- Divide numbers by using cell references
- Divide multiple cells by the same cell
- Divide columns
- Divide using the QUOTIENT function
Now you know the rules for writing Excel formulas and how equations are evaluated, you're ready to start using Excel functions to divide!
Divide numbers in a cell
If you want to divide numbers directly in a cell, here's how you do it:
- Select the cell in which you want the result to appear.
- Type the equals symbol (=) so Excel recognizes it as an equation.
- Input the numbers you want to divide and use the forward slash (/) as a division symbol. For example, if you want to divide 18 by 6, you'd input the equation (with no spaces) in the cell as the following, =18/6.
- When you are happy with the equation, press enter. Excel will then display the result, which in this instance is 3.
Divide numbers by using cell references
As previously mentioned, using cell references instead of typing directly into cells is beneficial if you want to go back to your formulas later, as they will be easier to understand.
To divide in Excel using cell references, here's what you need to do:
- Input the numbers you want to use as your cell references (these are the numbers you want to divide)
- Select the cell in which you want the result to appear.
- Type the equals symbol (=) so Excel recognizes it as an equation.
- Type in the cell reference you want to divide by. For example, if you want to divide the number in cell A1 by 2, you would type =A1/2. You can also divide one cell by another, so if you want to divide cell A1 by A2, you'd type the equation =A1/A2.
- When you are happy with your equation, press enter so Excel displays the result.
When you type formulas into a cell, you can either type the cell reference itself (e.g., A1) or select each reference cell after you've typed in the equals sign or the forward slash symbol.
Also, it's useful to be aware that when you use cell references, Excel takes any changes you make to them into consideration. So, say you have the number 12 in cell A1 and 2 in cell B1. If you then put the equation =A1/B1 in cell C1 and press enter, it will appear as 6. However, if you then go back and edit the original value in cell A1 and make it 18 instead of 12, Excel will consider this, and the result in cell C1 will be updated — this would show as 9 (18 divided by 2).
Divide columns
There are several ways to divide a column in Excel. We'll run through the three main ways you can do this.
Divide numbers in a column by a constant number (by copying a division formula)
You may want to divide a column of values by the same denominator. For example, if you want to divide a set of values in column A by a set of values in column B, you can do this by copying a division formula.
Here's the quickest and easiest way to do this:
- Select the adjacent cell in column C — the one to the right of the top value in column B.
- Type the following equation: =A1/B1.
- Press enter so the result shows in cell C1.
- Select cell C1 and hover your cursor over the green square in the bottom right-hand corner of the cell. Your cursor will then change to a black cross.
- Click your mouse on the cross and hold. Drag the cursor down and stop on top of the final cell in which you want to copy the formula.
- When you release your mouse, the formula will copy to the highlighted cells and display the results.
This method uses relative cell references, meaning it takes into account the cell positions and changes the formula accordingly. For example, in cell C2 the formula will be altered to display =A2/B2 instead of =A1/B1.
Divide numbers in a column using an absolute reference
You may want to divide a column by a constant value in the spreadsheet. To do this, when typing the formula, you need to make the cell an "absolute reference" by locking the column and row values using a dollar symbol ($).
For example, if you want to divide the cells in column A by the value in cell B1, you'd do the following:
- Select the cell you want the result to appear in.
- Type in the formula for your equation, remembering to make the constant value an absolute reference by locking the reference cell with the dollar symbol ($). So for this equation, the absolute reference is $B$1, which would make the formula =A1/$B$1. This notifies Excel that the value in cell B1 is the absolute reference.
- Press enter so the result shows.
- Select the cell again and hover your cursor over the green square in the bottom right-hand corner of the cell. Your cursor will then change to a black cross.
- Click your mouse on the cross and hold. Drag the cursor down and stop on top of the final cell in which you want to copy the formula.
- When you release your mouse, the formula will copy to the highlighted cells and display the results.
Divide one column by another with an array formula
If you would like to prevent your results from being disrupted by the accidental deletion of formulas in single cells, you can use an array formula for an entire range of values.
For example, if you want to divide the values in cells A1:A25 by the values in cells B1:B25, you'd use the following formula: =A1:A25/B1:B25.
To insert the formula, here's what you need to do:
- Select the range of cells where you want to enter the formula. In this example, this would be C2:C8.
- Type the formula in the bar at the top of the spreadsheet and press the following buttons: Ctrl + Shift + Enter. When you do this, Excel will enclose the formula in curly brackets ({}) to signify it is an array formula.
- The formula will automatically calculate the result in the cells highlighted.
If you use this formula, if you try to edit any of the individual cells used, you'll get a warning that notifies you that a cell in an array cannot be changed. If you do need to delete or edit a cell, you'll need to select the whole range to do this.
Also, if you add new rows to the bottom of the existing ones and you want to extend the formula to cover them, you'll need to do the following:
- Select the range of cells you want to cover, including the new ones.
- Edit the cell references in the bar to cover the new cells.
- Press Ctrl + Shift + Enter to update the Excel formula.
Divide using the QUOTIENT function
When dividing in Excel, you'll often end up with multiple decimal places in your results. If you want your results to show only the integer portion of a division, you can use the QUOTIENT function.
To do this, these are the steps to follow:
- Select the cell you want the integer to appear in
- In the cell, type the following: =QUOTIENT(
- After the parenthesis, type the numerator followed by a comma and the numerator. Close the parenthesis, so your formula looks like the following: =QUOTIENT(5,2).
- When you are happy with your equation, press enter so Excel displays the result.
Using the QUOTIENT function is simple, however, there are some things to be aware of:
- The numerator and denominator should be entered as numbers, cell references for cells that contain numbers, or functions that return numbers.
- If the denominator is zero, the QUOTIENT function will result in the following error message: #DIV/0!
- If either the numerator or denominator are non-numeric values, the QUOTIENT function will result in the following error message: #VALUE!
