Tanjim Reza
At times, we need to divide numbers from two different columns in Excel. Now, if the columns are lengthy, it becomes tiring and time-consuming for us to complete the entire calculation. So, it would be easier and handier for us, if we can divide the entire column in Excel. In this article, I will show you 7 such quick and easy tricks to divide in Excel for the entire column.
Download Practice Workbook
You can practice here by downloading our workbook.
7 Quick Tricks to Divide in Excel for Entire Column
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 used for the result. Here, I will show you 7 quick tricks to divide in Excel for entire columns A & B.
1. Divide Two Columns of Excel by Copying a Formula
You can divide two columns in Excel by writing the formula in the formula bar and copying it through the entire column by the fill handle. You can follow the steps given below to do so:
Steps:
- Firstly, click on the cell where you want your result. Now, put an equal (=) sign on the cell. Because this sign is a must to write any formula. Then, select the data which you want to divide. Remember, in Excel, the division sign is the forward-slash (/) symbol.
- Now, hit the Enter button. You will see the result output at the selected cell. You will see the formula used at the formula bar which is situated at the top of the column headings.
- Now, put your cursor at the bottom right position of the cell. You will see the cursor will change its appearance and will convert into a black fill handle. Press the left-click button of the mouse and drag it below and through the cells where you want to get the same formula.
- 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 columns are divided. And, the result will look like this. 👇
Read More: Division formula in Excel for multiple cells
2. Divide Two Columns Using Array Formula
You can also divide two columns using the array formula. Follow the given steps to do so:
Steps:
- Firstly, click on the cell where you want your result. Now, put an equal (=) sign on the cell. Because this sign is a must to write any formula.
- Now, select your entire range of data and use the forward-slash (/) sign between the column ranges to divide the two columns.
- Then, press Ctrl+Shift+Enter. Thus, you will see the formula is bracketed inside curly braces. This means this is an array formula.
- Now, put your cursor at the bottom right position of the cell. You will see the cursor will change its appearance and will convert into a black fill handle. Press the left-click button of the mouse and drag it below and through the cells where you want to get the same formula.
- Now, all the cells will have the same formula. You can also double-click on the fill handle to copy the formula to the bottom of the dataset.
Finally, the entire columns are divided. And, the result will look like this. 👇
Read More: How to Divide for Entire Row in Excel (6 Simple Methods)
3. 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:
- Say, we have to divide column ‘A’ by 2. Now at the result cell, put an equal (=) sign. Then, select the B5 cell and divide by 2. Use forward-slash (/) to perform division.
- Now, hit the Enter button. You will see the desired result on the result cell. And, you can also check the formula from the formula bar situated on top of the column heading.
- Now, put your cursor at the bottom right position of the cell. You will see the cursor will change its appearance and will convert into a black fill handle. Press the left-click button of the mouse and drag it below and through the cells where you want to get the same formula.
- Now, all the cells will have the same formula. 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. 👇
4. Divide an Entire Column by a Constant Number Using Cell Absolute Reference
You can divide an entire column by a constant number using absolute cell reference. An absolute cell reference is denoted by the dollar ($) sign. Absolute cell reference means even if you drag your formula, the absolute cells will not change their reference according to the drag. But, if the cell is not absolute, the cell reference will change automatically with the dragging. Follow the given steps to do so:
Steps:
- Say, we have to divide column ‘A’ by 2 which is placed inside the C5 cell. Now, at the result cell, put an equal (=) sign. Then, select the B5 cell and divide it by the C5 cell. Then press F4. It will make the C5 cell absolute. Use forward-slash (/) to perform division.
- Now, hit the Enter button. You will see the desired result on the result cell. And, you can also check the formula from the formula bar situated on top of the column heading.
- At this time, put your cursor at the bottom right position of the cell. You will see the cursor will change its appearance and will convert into a black fill handle. Press the left-click button of the mouse and drag it below and through the cells where you want to get the same formula.
- Now, all the cells will have the same formula. 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. 👇
5. Divide in Excel for Entire Column by Paste Special Option
You can divide the entire column of an Excel file by using Paste Special option. Follow the given steps to do so:
Steps:
- Say, we have to divide column ‘A’ by 2 which is placed inside the C5 cell.
- Now, Copy the range of column ‘A’.
- Then, Paste it on the ‘Result’ column cells where you want the divided results.
- 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.
- Now, the Paste Special window will open. Put the radio button on the Divide option from the Operation group. Click on the OK button.
Thus, the ‘Result’ column will show you the divided results now. And, the result will look like this. 👇
Read More: How to Divide Without Using a Function in Excel (with Quick Steps)
6. Divide an Entire Column by Excel QUOTIENT Function
You can also use the QUOTIENT function to divide an entire column. This function will return only the integer part of the division. This function will trim the remainder. You can use this method to divide using the following steps:
Steps:
- Select the cell where you want to get the desired division. Now, put an equal sign (=) and write QUOTIENT. Then, select the two cells that you want to divide. These cells will be separated by a comma (,).
- Hit the Enter button. You will see your desired result at the selected cell. You can check the formula on the formula bar too.
- Now, put your cursor at the bottom right position of the cell. You will see the cursor will change its appearance and will convert into a black fill handle. Press the left-click button of the mouse and drag it below and through the cells where you want to get the same formula.
- Finally, 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.
Thus, the entire column is now divided. For using the QUOTIENT function, it will show 2 at the D7 cell instead of 2.5. And, the result will look like this. 👇
Read More: How to Divide with Decimals in Excel (5 Suitable Examples)
7. Divide an Entire Column by Using Percentage
You can divide your entire column by percentage too. Follow the steps below to do so:
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 as 50.
- Now, divide the ‘A’ column data by the constant number of 20% without using any cell reference. It will also give you the result as 50.
- Furthermore, 20% and 0.2 are the same things. So if you divide ‘A’ column data by 0.2, you will get the same result as 50.
- Moreover, 20% and (⅕) are the same things. So if you divide the ‘A’ column data by (1/5), it will still show you the same result as 50.
Thus, the entire column can be divided by any of these or using just the percentage. The result summary will look like this. 👇
Read More: How to Divide a Value to Get a Percentage in Excel (5 Suitable Examples)
Things to Remember
If you don’t have any value on the divisor cell, it will show you #DIV/0! error. So, you have to be careful about the columns which you are dividing. And, if somehow this error happens, you have to learn about the IFERROR function.
Conclusion
These are the several ways to divide in Excel for an entire column. I hope you find this information to be useful and educational. Please feel free to contact us if you have any further queries or recommendations. For many more articles like this, visit exceldemy.com.
