Division Formula in Excel for Multiple Cells: 5 Suitable Methods

Method 1 – Applying Formula to Divide Entire Column Values by Specific Number in Excel

Steps:

  • In the cell C5, insert this formula:
=B5/10

Formula to Divide Entire Column Values by a Specific Number in Excel

  • Drag the Fill Handle icon to paste the used formula respectively to the other cells of the column or use CTRL+C  and CTRL+V  to copy and paste.

Drag fill handle icon

  • On the right side of the image, you will see the formulas. I have used to FORMULATEXT function to get the formulas used in column C.


Method 2 – Dividing Values Using Absolute Cell Reference with Dynamic User Input in a Column

Steps:

  • Insert the value of the Dividend in cell C4.
  • Insert the formula into cell C7 to get the result.
=B7/$C$4

Divide Values Using Absolute Cell Reference with Dynamic User Input in a Column

  • Drag the fill handle icon to get the column filled with a similar formula. In column D, you will see the formula text which is used in column C.

Output of Dynamically divide numbers with User Input

  • By inserting different values in cell B2, we can divide the column with different numbers.

Check out the following GIF image.

Dynamically divide numbers with User Input

 


Method 3 – Applying Division Formula with Excel QUOTIENT Function for Multiple Cells

You can use the QUOTIENT function to get the quotient value in the integer form excluding the remainder. This function returns the integer part of a division without remainder.

To use this function, insert this formula into the cell D5:

=QUOTIENT(B5,C5)

Formula Explanation:

Syntax of QUOTIENT function is: QUOTIENT(numerator, denominator)

  • Numerator = B5: It is the Dividend which is the number to be divided
  • Denominator = C5: It is the Divisor by which the number will be divided.

Using QUOTIENT Function to Divide Multiple Cells in Excel

  • Drag the Fill Handle icon to paste the used formula respectively to the other cells of the column or use Excel keyboard shortcuts CTRL+C  and CTRL+V  to copy and paste.

output of QUOTIENT Function to Divide in Excel

 


Method 4 – Inserting Division Formula in Excel to Divide Multiple Cells by 1000

  • You can directly divide cell B5 by 1000. For this, use this formula in cell C5:
=B5/1000
  • You can insert the divisor 1000 in a cell and then, make a cell reference to divide. In this case, use the formula below when you have inserted the value 1000 in cell G4:
=B5/$G$4
  • You will get the same result. In the first case, you can’t change the dividend value all at once, but you can in the second case.

Division Formula to Divide multiple cells by 1000


Method 5 – Using Excel Formula in Multiple Cells to Add and Divide Simultaneously

If you want to add some numbers and then divide the sum value with another number, this is a little bit tricky. Suppose you want to add two numbers (say they are 50 and 60) and then want to divide the result with another number 11.

Here is the formula: (50 + 60)/11 = 110/11 = 10

How Do You Add then Divide in Excel

If you write the formula in this way: 50 + 60/11, it will result = 50 + 5.45 = 55.45; you will not get the desired result. According to the order of calculations, the division will be performed first (with the left to right associativity) and then the addition.

You have to place the addition within a parenthesis. Parenthesis has a higher order of calculations than division.

Now, let’s see several formulas to add and divide in Excel.

Add and Divide by Typing Numbers Within Cell:

Let’s assume 7 randomly chosen numbers to be added and then divided by a fractional number, e.g. 1.052632. We will perform these two operations simultaneously using the following formula within a cell.

=(2500+2300+1700+2600+3000+3500+2300)/1.052632

Excel Formula to Add and Divide Simultaneously within a cell

Using Cell Reference and Excel Functions to Add and Then Divide:

Typing numbers in a formula is not a good idea. Using a single formula, it will be better to use Excel functions with cell references to add and divide.

Here we will show you 3 exemplary formulas.

Excel Formula to Add and Divide Simultaneously with or without using functions

Formula Using Cell References Only:

=(B5+B6+B7+B8+B9+B10+B11)/D5

Formula Using SUM Function:

=SUM(B5:B11)/D5

Formula Using QUOTIENT Function:

=QUOTIENT(SUM(B5:B11),D5)

Handling #DIV/0! Error in Excel Division Formula

In mathematics, you cannot divide a number by zero (0). It is not allowed.

So, when you divide a number by zero in Excel, it will show an error. It is #DIV/0! Error.

Handling #DIV/0! Error in Excel

We can handle #DIV/0! Error in Excel using two functions:


1. Handling #DIV/0! Error Using IFERROR Function

Use the IFERROR function to handle #DIV/0! Error.

Syntax of IFERROR Function:

IFFERROR(value, value_if_error)

We used the IFFERROR function to handle the #DIV/0! Error.

Generic Formula:

=IFERROR(Division_formula, Value_if_error)

Used Formula in the Following Example:

=IFERROR(B3/C3,"Not allowed")

Handling #DIV/0! Error Using IFERROR Function


2. Handling #DIV/0! Error Using IF function

Use the IF function to handle the #DIV/0! Error.

Syntax of IF Function:

IF(logical_test, [value_if_true], [value_if_false])

Use the IF function to handle #DIV/0! Error.

Generic Formula:

=IF(Set condition as denominator equals to zero, Value_if_true, division_formula)

Used Formula in the Following Example:

=IF(C19=0, "Not allowed",B19/C19)

Handling #DIV/0! Error Using IF function


Download Practice Workbook

You can download the practice workbook from here:


<< Go Back to Divide in Excel | Calculate in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Kawser Ahmed
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo