Basic mathematical functions are a fundamental part of Excel. They’re key to data analysis, but dividing isn’t as simple as some of the other functions. You have to know how to work around the lack of a function, so here are some tips on how to divide in Excel.
We’ll walk through a few of the basic methods as well as what to avoid so that you don’t get a dreaded #DIV/0! error.
The Easiest Way to Divide in Excel
Learning to divide numbers in Excel is as simple as memorizing the following formula:
- Type the “equals” sign (=)
- Type your first reference cell (A1)
- Type a forward slash (/)
- Type the second reference cell (B1)
- Hit “Enter”
How to divide in Excel
Using Basic Formulas
The very easiest way to divide in Excel is by using a formula within a single cell. This is just like entering a formula on a calculator, and in our example, we have =40/5. Once we hit Enter, you’ll see the answer displayed as 8.
Odds are, you won’t be using Excel to divide within a single square that often, so let’s talk about dividing cells.
Because there is no division function, you’ll have to get used to writing out the formula. If you want to divide one cell by another, you’ll get a formula that looks like this: =A1/B1. Press Enter to get your result.
Excel displays a #DIV/0! error when you try to divide by zero or you divide by an empty cell. The easiest way to remedy the situation is to double-check the cells that you use before you press Enter.
Using QUOTIENT and MOD functions
While there are not standard division functions, there are still two functions that you should know to find integers and remainders from division.
Use the QUOTIENT function to return the integer result of your division. This will discard the remainder in case you are looking for data that requires whole numbers.
To use the QUOTIENT function, start with the =QUOTIENT( section of the formula. Then you can fill in the two cells that you need. In our example, we have =QUOTIENT(A1, B1).
Whereas the QUOTIENT function returns the integer result, the MOD function returns the remainder.
To use the MOD function, enter =MOD( in the formula bar. Fill in the cells we used above, A1 and B1. Then hit enter. Between the two functions, our result is that the integer is 8 and the remainder is 3.
If you needed to find the number of tables you need for an event, the QUOTIENT and MOD functions would work well to show you both full and partial tables.
Now you know how to divide in Excel, but you might want to learn more to truly master your spreadsheets.
Related: How to split cells in Excel
How to Divide Multiple Numbers in Excel
It’s just as easy to divide multiple numbers in Excel as it is to use a simple division formula.
Instead of dividing two cells like this: =A2/A3
Just add another cell to the end of the formula: =A1/A2/A3.
This is helpful for segmenting data, such as when you want to take a figure measured in years and divide it out into months and days.
The first figure, A1 could represent a number measured across an entire year, A2 could represent the number of months in year, giving you a monthly average.
Then, “A3” could represent the average number of days in a month: 30. This would give you an average daily figure.
If you want very simple statistics for your business, this is an easy way to get them.
Dividing By Cells vs Integers
There are two ways you can create simple division formulas to divide numbers in excel: dividing cells by cells or dividing cells by integers.
- Dividing Cells: Type the equal sign, then a cell name, then forward slash, then another cell name. For example, “=A2/B2”
- Dividing Cells by Integers: Type the equal sign, then a cell name, then an integer. For example, “=A2/6”.
Dividing by cells makes the most sense, most of the time. But, if you want to display data separated by months, days, fiscal quarters, etc, using an integer to divide is cleaner and more sensible than typing “12” into a random cell and using it to divide.
Note: be careful when dividing by integers (and by cells, for that matter) that you know whether it is positive or negative to avoid any accidentally funky calculations.
How to Quickly Copy Division Formulas in Excel
Do you have two columns that need to be divided in the same way (for example, if each row in Column C and Column D need to be divided by using the same formula)?
Excel allows users to easily copy formulas (of any kind, not just division formulas) with just one double-click:
All you need to do is double-click the little green box in the right corner of a cell. The formula used above that cell will be copied over and automatically used in the one below it, and on down the column until there are no more cells left to copy.
Excel is essentially using that box to make an educated guess on what you need to do in that cell. Did you just divide A2 and B2, entered in cell C2, and now you’re in C3? Excel will (usually correctly) guess that you want to do the same thing in that cell.
So, when you’ve already typed a division formula in Excel and want to use it in the next cell in that column, there’s no need to re-type it. Just double-click that little green box and get on with your day.
How to Divide by a Constant in Excel
Let’s say you want to divide an entire column or even a group of columns by a single, constant number. The easiest way to do that is by entering the constant number in a cell, then using that cell to divide your entire column.
Then, double-click the green box in the cell holding your constant to copy it down the column if needed.
For example, if you want to divide an entire column by 2, enter “2” into a cell. Then, divide your column using that cell, and the “little green box” to make copying the formulas less tedious.
What Else Can I Learn?
We have in-depth guides on addition and subtraction in Excel, but you might be ready for bigger and better functions. A little instruction is all you need to work with PivotTables, Data Visualization, and more. If you’re keen for more knowledge, you might be interested in a new deal we’re highlighting from Tech Deals right now.
It’s called the Complete Excel Pro Tips Certification Bundle and it includes six hands-on modules to sharpen your skills. You can work through the skills mentioned above or even take more time practicing with formulas. You’re in the driver’s seat, so focus on what interests you.
FAQ: Dividing in Excel
Incorrectly using a division formula in excel will give you a #DIV/0! error, which means that the application cannot complete the calculation.
This error occurs when the cell references in your division formula are empty, leading the formula to attempt to divide by zero, which we all know is impossible.
Cell references are the “names” of a cell. When you type a cell reference into an excel formula, you are telling Excel which cells to use
Excel does not have a “DIVIDE:” function like Google Sheets and other spreadsheet applications do. Instead of typing “DIVIDE:” and then a “A2:B2”-style formula, you need to type “=” instead.
You have likely forgotten to use the equal sign (“=”) before entering your division formula. When you forget to use it, and the numbers entered appear to be a calendar date, say “12/5”, Excel will assuming you’re assigning a calendar date to that column. Don’t forget to use the equal sign!