Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Divide One Column by Another in Excel (7 Quick Ways)

Sometimes, we need to divide excel values from one column by another. It will be more efficient if we can divide the entire column by another. Then, you don’t have to divide individual values one by one. It will save your time and energy. In this article, I will show you the easiest and quickest tricks to divide one column by another in Excel.


Practice Workbook

You can download our practice workbook here.


7 Easy Methods to Divide One Column by Another in Excel

Here, in our dataset, We have taken two columns A & B. We want to divide ‘A’ column data by ‘B’ column data. The ‘Result’ column shows the divided output. And the ‘Formula’ column shows the formula of the result. Here, I will show you 7 easy methods to divide one column by another in Excel.

How to Divide One Column by Another in Excel


1. Divide One Column by Another Using Basic Mathematical Formula

In Excel, you can divide two columns by putting a formula in the formula bar and copying it through the column using the fill handle. To do so, follow the steps outlined below:

Steps:

  • To begin, select the cell where you want your result to appear. Put an equal symbol (=) in the cell now. Because any formula must include this sign. Select the data you wish to divide next. Remember that the forward-slash (/) symbol is the division sign in Excel.

How to Divide One Column by Another in Excel

  • Now press the Enter The result will be displayed in the selected cell. The formula will be seen in the formula bar, which is located at the top of the column headers.

How to Divide One Column by Another in Excel

  • Now, move your pointer to the cell’s bottom right The cursor will change the appearance and turn into a black fill handle. Drag the mouse’s left-click button below and through the cells where you wish the identical formula to appear.

How to Divide One Column by Another in Excel

  • As a result, the formula will be duplicated for every cell. You may also copy the formula to the bottom of the dataset by double-clicking on the fill handle.

Finally, the columns are divided completely. And the end outcome will be as follows.👇

How to Divide One Column by Another in Excel

Read More: How to Divide Without Using a Function in Excel (with Quick Steps)


2. Divide One Column by Another Using Array Formula

The array formula can also be used to divide one column by another. To do so, follow the instructions below:

Steps:

  • To begin, select the cell where you want your result to appear. Put an equal symbol (=) in the cell now. Because any formula must include this sign.
  • To divide the two columns, select the complete range of data individually and use the forward-slash (/) sign between the column ranges.

How to Divide One Column by Another in Excel

  • Then, press Ctrl+Shift+Enter. Thus, you will see the formula is bracketed inside curly braces. This indicates this is an array formula.

How to Divide One Column by Another in Excel

  • Now, move your pointer to the cell’s bottom right The cursor will change the appearance and turn into a black fill handle. Drag the mouse’s left-click button below and through the cells where you wish the identical formula to appear.

  • As a result, the formula will be duplicated for every cell. You may also copy the formula to the bottom of the dataset by double-clicking on the fill handle.

Finally, the columns are divided. And the result will be as follows:

How to Divide One Column by Another in Excel

Read More: How to Divide for Entire Row in Excel (6 Simple Methods)


3. Divide One Column by Another Using Excel QUOTIENT Function

You can also divide a column with the QUOTIENT function. Only the integer component of the division will be returned by this function. The remainder will be trimmed with this function. You can divide using this method by following the steps below:

Steps:

  • Choose the cell where you wish the desired division to appear. Now write QUOTIENT with an equal sign (=). Select the two cells you want to divide next. A comma (,) will be used to separate these cells.

How to Divide One Column by Another in Excel

  • Press the Enter Your desired result will appear in the specified cell. The formula can also be checked in the formula bar.

How to Divide One Column by Another in Excel

  • Now, move your pointer to the cell’s bottom right The cursor will change the appearance and turn into a black fill handle. Drag the mouse’s left-click button below and through the cells where you wish the identical formula to appear.

  • The formula will then be copied to all of the cells. You may also copy the formula to the bottom of the dataset by double-clicking on the fill handle.

As a result, the entire column has been divided. And the result will be as follows:

How to Divide One Column by Another in Excel

Read More: How to Divide with Decimals in Excel (5 Suitable Examples)


4. Divide an Entire Column by a Constant Number Using Formula

You can divide an entire column by a constant number too. Follow the steps below to do so:

Steps:

  • Let’s say we need to divide column ‘A’ by two. Put an equal (=) sign in the result cell now. Then select cell B5 and divide by two. To divide, use the forward-slash (/)

How to Divide One Column by Another in Excel

  • Now press the Enter The desired outcome will appear in the result cell. Additionally, the formula bar located on top of the column heading can be used to check the formula.

  • Now, move your pointer to the cell’s bottom right The cursor will change the appearance and turn into a black fill handle. Drag the mouse’s left-click button below and through the cells where you wish the identical formula to appear.

  • Thus, the formula will be copied to all the cells. You can also double-click on the fill handle to copy the formula to the bottom of the dataset.

Finally, the entire column is divided. And, the result will look like this. 👇


5. Divide by a Constant Number Using Cell Absolute Reference

Using absolute cell reference, you can divide a whole column by a fixed integer. The dollar ($) symbol indicates an absolute cell reference. Absolute cell reference means that no matter how much you drag your formula, the absolute cells will maintain their reference. However, if the cell is not absolute, the cell reference will change as you drag it. To try this method, follow the instructions below:

Steps:

  • Let’s say we need to divide column ‘A’ by two, which is in the C5 cell. Put an equal (=) sign in the result cell now. Select the B5 cell and divide it by C5. After that, hit F4. The C5 cell will become absolute. To divide, use the forward-slash (/)

How to Divide One Column by Another in Excel

  • Now press the Enter The desired outcome will appear in the result cell. And, you can also check the formula from the formula bar situated on top of the column heading.

How to Divide One Column by Another in Excel

  • Place your cursor in the cell’s bottom right corner at this point. The cursor will change its appearance and turn into a black fill handle. Drag the mouse’s left-click button below and through the cells where you wish the identical formula to appear.

  • Finally, the formula will be duplicated for all of the cells. You can also double-click on the fill handle to copy the formula to the bottom of the dataset.

Thus, the entire column is divided. And, the result will look like this. 👇

How to Divide One Column by Another in Excel


6. Divide an Entire Column Using Paste Special Option

Using the Paste Special option in Excel, you can divide an entire column by another. To do so, follow the instructions below:

Steps:

  • Say, we have to divide column ‘A’ by two which is placed inside the C5 cell.

  • Now, Copy the range of column ‘A’.

  • Then Paste it into the ‘Result’ column cells where the divided results should appear.

  • After Pasting, the ‘Result’ column will look like this.

  • Now, Copy the C5 cell again.

  • Then, select the ‘Result’ column range and press the right-click button on the mouse. Now, click on the Paste Special option from the context menu.

How to Divide One Column by Another in Excel

  • The Paste Special window should now appear. Put a radio button next to the Operation group’s Divide Click on the OK button.

How to Divide One Column by Another in Excel

As a result, the ‘Result’ column will now display the divided results. And the end outcome will be as follows. 👇

How to Divide One Column by Another in Excel

Read More: [Fixed] Division Formula Not Working in Excel (6 Possible Solutions)


7. Divide One Column by Another Using Percentage

You can also use percentages to divide an entire column. To do so, follow the instructions below:

Steps:

  • Say, we have a column ‘A’ to divide by 20% which is situated inside the C5 cell. Now, at the result cell, put an equal sign (=) and divide A column data by the C5 cell using absolute reference. It will give you the result of 50.

How to Divide One Column by Another in Excel

  • Divide the values in the ‘A’ column by a constant number of 20% without using any cell references. It will likewise return a result of 50.

  • Moreover, 0.2 and 20% are the same things. If you divide the data in the ‘A’ column by 0.2, the result is also 50.

  • Furthermore, 20% and ( ⅕ ) are the same things. If you divide the data in the ‘A’ column by (1/5), you’ll get the same result as 50.

As a result, the entire column can be divided by either of these or by the percentage alone. This is what the result summary will look like. 👇

How to Divide One Column by Another in Excel

Read More: How to Divide a Value to Get a Percentage in Excel (5 Suitable Examples)


Things to Remember

It will display a #DIV/0! error if there is no value in the divisor cell. As a result, you must be cautious while dividing your columns. If this problem occurs, you’ll need to learn about the IFERROR function.


Conclusion

These are the several ways to divide one column by another in Excel. I hope you find this material to be informative and useful. If you have any further questions or suggestions, please do not hesitate to contact us. Visit exceldemy.com for many more articles like this.


Related Articles

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo