Tanjim Reza
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.
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.
1. Using Basic Mathematical Formula to Divide a Column by Another
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.
- 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.
- 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 completely. And the end outcome will be as follows.👇
Read More: How to Divide Without Using a Function in Excel
2. Applying Array Formula to Divide One Column by Another in Excel
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.
- Then, press Ctrl+Shift+Enter. Thus, you will see the formula is bracketed inside curly braces. This indicates this is an array 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.
- 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:
Read More: How to Divide for Entire Row in Excel
3. Inserting Excel QUOTIENT Function to Devide One Column by Another
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.
- Press the Enter Your desired result will appear in the specified cell. The formula can also be checked in the formula bar.
- 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:
Read More: How to Divide with Decimals in Excel
4. Dividing 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 (/)
- 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. Using Cell Absolute Reference Divided by a Constant Number
You can also divide a whole column using absolute reference 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 (/)
- 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.
- 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. 👇
6. Applying Paste Special Tool for Dividing an Entire Column
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.
- The Paste Special window should now appear. Put a radio button next to the Operation group’s Divide Click on the OK button.
As a result, the ‘Result’ column will now display the divided results. And the end outcome will be as follows. 👇
Read More: [Fixed] Division Formula Not Working in Excel
7. Dividing 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.
- 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. 👇
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.
Download Practice Workbook
You can download our practice workbook here.
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.
