Division in Excel is a fundamental arithmetic operation that allows users to divide numbers and perform calculations with precision. Whether you’re dividing individual cells, ranges of data, or complex formulas, Excel provides versatile tools for executing division operations efficiently. By using the division operator (/) or specific functions like DIVIDE or QUOTIENT, you can divide numbers, percentages, or even text strings to derive meaningful insights and generate accurate results. Division in Excel is essential for various tasks, including financial analysis, budgeting, forecasting, and data manipulation. With Excel’s built-in features and functions, users can perform division operations seamlessly, ensuring accuracy and consistency in their calculations. Understanding and mastering division in Excel is key to unlocking the full potential of the software and enhancing productivity in both personal and professional settings.
This Tutorial Covers:
- Divide the symbol in Excel
- How to divide numbers in Excel
- How to divide cell value in Excel
- Divide function in Excel (QUOTIENT)
- Three factors to be aware of when using the QUOTIENT function
- How to divide columns in Excel
- How to do divide two columns in Excel by copying a formula
- How to use an array formula to divide one column by another
- How to do divide a column in Excel by a number
- Apply a formula to divide a column by numbers
- Utilizing Paste Special, divide a column by the same number
- How to divide by percentage in Excel
- Excel DIV/0 error
- Suppress #DIV/0 error with IFERROR
- Handle Excel DIV/0 error with IF formula
- Important Things To Note
1. Divide symbol in Excel:
Division is one of the fundamental mathematical operations used in Excel. It involves dividing one number by another to get a quotient or a result. In Excel, the common way to perform division is by using the forward slash (/) symbol, which replaces the obelus symbol (÷) used in mathematics.
To use division in Excel, you need to write a formula that contains the dividend and the divisor. The dividend is the number that you want to divide, while the divisor is the number by which you want to divide the dividend. In Excel, the formula for division takes the form of “=a/b” (without the quotes), where ‘a’ represents the dividend and ‘b’ represents the divisor.
2. How to divide numbers in Excel?
In Excel, you must first enter the equals symbol (=) in a cell, then the two values you wish to divide, a forward slash, then the number you wish to divide by. Finally, you must press the Enter key to compute the formula.
Procedure of dividing numbers in excel is shown below:
Step 1: Enter the following formula in a cell to divide 20 by 2:
=20/2
A few more instances of a straightforward division formula in Excel are shown in the screenshot below:
It’s important to keep in mind that Excel (PEMDAS) calculates formulas in the following order: parentheses first, exponentiation (raising to a power), followed by multiplication or division, depending on which comes first, and finally addition or subtraction, depending on which comes first.
3. How to divide cell value in Excel?
Use the divide symbol precisely as it is in the examples above to divide cell values, but substitute cell references for the numbers.
For instance:
- To divide a number in cell A2 by 20, enter =A2/20
- To divide cell A2 by cell B2: =A2/B2
- If you want to divide several cells at once, use cell references followed by the division symbol. For example, use the following formula for dividing the number in A2 by the number in B2, then divide the outcome by the number in C2:
=A2/B2/C2
4. Divide function in Excel (QUOTIENT):
I must state it clearly: There is no division formula in excel. As shown in the examples above, use the division sign whenever you want to divide one integer by another.
However, you may use the QUOTIENT function to only return the integer part of a division and ignore the remainder:
QUOTIENT(numerator, denominator)
Where:
Numerator (required) – the dividend, or the amount that needs to be divided.
Denominator (required) – the divisor, or the quantity to be divided by.
The division sign and a QUOTIENT formula yield the same answer when two numbers divide equally without leaving a remainder. For instance, the two formulas below both produce 40.
=200/5
=QUOTIENT(200, 5)
When there is a remainder after division, the QUOTIENT function only returns the integer portion, while the divide sign returns a decimal number. For instance:
=200/16 returns 12.5
=QUOTIENT(200,16) yields 12
-
Three factors to be aware of when using the QUOTIENT function:
While the Excel QUOTIENT function is a simple tool for performing division operations, there are some important considerations to keep in mind:
- The numerator and denominator arguments used in the QUOTIENT function should be either numbers, references to cells containing numbers, or other functions that return numbers.
- If either the numerator or the denominator argument is non-numeric, the QUOTIENT formula will return an error message “#VALUE!”.
- If the denominator argument is equal to zero, the QUOTIENT formula will return an error message “#DIV/0!” for the divide-by-zero error.
It’s important to ensure that the input arguments used in the QUOTIENT function are valid, and that any errors are handled appropriately. By following these guidelines, you can make sure that your QUOTIENT formulas work correctly and produce accurate results.
5. How to divide columns in Excel?
Performing division operations on columns in Excel is a straightforward process that can be accomplished in two ways. The first method involves copying a regular division formula down the entire column. The second method involves using an array formula, which may seem unnecessary for such a simple task. However, there are specific scenarios where using an array formula is useful, and we will explore those reasons in a moment.
-
How to do divide two columns in Excel by copying a formula?
Simply follow these steps in Excel to divide two columns:
Step 1: Apply the below formula in cell C2
=A2/B2
To copy the formula down the column, insert it in the first cell (let’s say, C2) and double-click the tiny green square in its lower-right corner. Done!
This division formula will change depending on the relative position of the cell where it is copied because we utilize relative cell references (without the $ sign).
Tip: In Excel, you may split two rows in a similar way. For instance, to divide values in row 1 by values in row 2, enter =A1/A2 in cell A3 and replicate the formula to as many further cells on the right as needed.
-
How to use an array formula to divide one column by another?
Insert an array formula in the entire range when you wish to prevent unintentional deletion or modification of a formula in a single cell.
Follow the steps below to accurately input the array formula:
Step 1: Wherever you wish to enter the formula, select the full range.
Step 2: Enter the below formula in the formula bar by typing it there and then pressing Ctrl + Shift + Enter. As soon as you do this, Excel will indicate that the formula is an array formula by enclosing it in “curly braces.”
=A2:A5/B2:B5
The numbers in column A will be divided by the numbers in column B all at once as a result.
Excel will display a warning that a section of an array cannot be altered if someone tries to edit your formula in a single cell.
You must first pick the entire range before making any modifications to the formula, whether it be deletion or modification. Select the entire range, including the new rows, edit the formula bar’s cell references to include the new cells, and then update the formula by pressing Ctrl + Shift + Enter.
6. How to do divide a column in Excel by a number?
You can use a division formula or the Paste Special function to divide a column of numbers by a constant number, depending on whether you want the output to be formulae or data.
-
Apply a formula to divide a column by numbers:
As you are already aware, using the divide symbol in Excel is the quickest way to perform division. Therefore, you enter a typical division formula in the first cell, copy it along the column, and divide each integer in a particular column by the same number. All there is to it is that!
The steps to divide a column by number using formula are described below:
Step 1: Insert the following formula in cell A2 and then copy it down to as many other cells as you wish to divide the values in column A by the number 4.
=A2/4
The use of a relative cell reference (A2) guarantees that the formula is properly modified for each row, as demonstrated in the example above. In other words, the equation in B3 becomes =A3/4, the equation in B4 becomes =A4/4, and so on.
You can enter the divisor in a cell, as B2, and divide by that cell instead of including it in the formula itself. Because this reference needs to remain constant regardless of where the formula is duplicated, it’s crucial to lock the cell reference with the dollar sign (like $B$2) to make it an absolute reference.
The results of the formula =A2/$B$2 are exactly the same as those of =A2/4, as can be seen in the screenshot below.
-
Utilizing Paste Special, divide a column by the same number:
If you wish the results to be values rather than formulas, you can divide as normal and then swap out the formulas with the desired values. Or you can use the Paste Special > Divide option to get the same result more quickly.
How to divide a column by the same number utilizing Paste Special is shown below:
Step 1: Copy the original numbers to the column where you want the results to appear if you don’t want to change them. We copy numbers from column A to column B in this example.
Step 2: As seen in the screenshot below, place the divisor in a cell, let’s say D2.
Step 3: To copy the divisor cell (D5) to the clipboard, select it and press Ctrl + C. Choose the cells you want to divide.
Step 4: The shortcut for Paste Special > Divide is Ctrl + Alt + V, followed by I, and Enter.
As an alternative, you can choose “Paste Special…” from the context menu when you right-click the chosen numbers, choose “Divide” under “Operation”, and then click OK.
In either case, the results of dividing each of the chosen numbers in column A by the number in D2 will be returned as values, not formulas:
7. How to divide by percentage in Excel?
When calculating percentages in Excel, it’s important to remember that percentages represent parts of a whole, and as such, they are calculated by multiplying a number by a percentage, not by dividing it. For example, to find 30% of 100, you would multiply 100 by 30% or 0.3 to get the result of 30.
However, there are certain situations where you may need to divide a number by a percentage. One example is when you need to find a specific number given a percentage of that number. For instance, to find out what number 100 represents as 50% of that number, you can use the equation X = Y/P%. In this case, Y is equal to 100, and P is equal to 50%, so the equation becomes X = 100/50% or X = 100/0.5. Both formulas will give you the same result, which is 200 as shown below screenshot.
8. Excel DIV/0 error:
When dividing a number by zero or an empty cell in Excel, the result is undefined, and as such, the divide by zero error (#DIV/0!) is returned. This error message can be useful in some cases, as it can alert you to possible issues with your data set.
However, there are situations where you may prefer to replace the #DIV/0! error with an empty cell or a custom message. This can be accomplished using either an IF formula or the IFERROR function. These tools allow you to check for errors in your formulas and replace them with a specified value, such as an empty cell or a custom message.
By using these methods to handle divide by zero errors in Excel, you can ensure that your formulas continue to produce accurate results and provide meaningful insights into your data.
-
Suppress #DIV/0 error with IFERROR:
Wrapping your division formula with the IFERROR function in Excel is the simplest approach to solve the #DIV/0! error.
How to suppress #DIV/0 error with IFERROR is shown below:
Step 1: Insert the following formula in cell C2 and then copy it down to as many other cells as you wish to divide the values.
=IFERROR(A2/B2, “”)
The formula verifies the outcome of the division, and if it evaluates to an error, returns an empty string (“”), the result of the division otherwise.
Note: Excel’s IFERROR function disguises not just #DIV/0! errors, but all other error kinds such as #N/A, #NAME?, #REF!, #VALUE!, etc. Use an IF formula, as shown in the next example, to suppress DIV/0 errors only.
-
Handle Excel DIV/0 error with IF formula:
Use an IF function in Excel to conceal just DIV/0 error.
How to handle DIV/0 error with IF formula is shown below:
Step 1: Insert the following formula one of them in cell C2 and then copy it down to as many other cells as you wish to divide the values.
=IF(B2=0,””,A2/B2)
Or
=IF(B2<>0,A2/B2,””)
Cell A2 is divided by cell B2 by the formulas if the divisor is any number other than zero. The formulas return nothing (an empty string) if B2 is blank or equals 0.
You might alternatively choose to show a customized message in place of an empty cell, similar to this:
=IF(B2<>0, A2/B2, “Error in calculation”)
9. Important Things To Note:
When using division in Excel, there are a few possible error messages that you may encounter:
- If the denominator in your formula is zero, you will see the “#DIV/0!” error.
- If the numerator, denominator, or both are non-numeric values (such as text), you will see the “#VALUE!” error.
- If you accidentally use the backslash “” instead of the forward slash “/”, you will see the “#NAME?” error.
- When using the QUOTIENT function, both the numerator and denominator arguments are required.
By understanding these potential error messages, you can write more effective and error-free formulas in Excel.
Application of Division in Excel formula
- Basic Arithmetic: Excel’s division function is essential for performing basic arithmetic operations, allowing users to divide numbers and calculate quotients accurately.
- Financial Analysis: Division is frequently used in financial analysis for calculating ratios, such as profit margins, return on investment (ROI), and debt-to-equity ratios.
- Budgeting: Division in Excel helps with budgeting tasks by dividing total expenses or revenues by the number of periods to determine average costs or income per period.
- Data Manipulation: Division can be used to normalize data by dividing values in one column by values in another, facilitating comparisons and trend analysis.
- Error Checking: Division operations in Excel can help identify errors in formulas or data inconsistencies by flagging divide-by-zero errors or unexpected results.
- Statistical Analysis: Division is employed in statistical analysis for calculating averages, percentages, and other metrics, aiding in data interpretation and decision-making processes.
For ready-to-use Dashboard Templates:
