How to Divide One Column by Another in Excel – 7 Methods

This is the sample dataset.

To divide column A by column B:

Method 1 – Using Basic Mathematical Formula to Divide a Column by Another

Steps:

• Select a cell to see the result.
• Enter =.
• Select the data you want to divide. Remember that the forward-slash (/) symbol is the division sign in Excel.

• Press Enter.

The result will be displayed in the selected cell and the formula in the formula bar.

• Drag down the Fill Handle to see the result in the rest of the cells.

• You can also copy the formula by double-clicking the fill handle.

This is the output.

Read More: How to Divide in Excel for Entire Column

Method 2 – Applying an Array Formula to Divide One Column by Another in Excel

Steps:

• Select a cell to see the result.
• Enter =.
• Select the data you want to divide. Remember that the forward-slash (/) symbol is the division sign in Excel.

• Press Ctrl+Shift+Enter. This is an array formula.

• Drag down the Fill Handle to see the result in the rest of the cells.

• You can also copy the formula by double-clicking the fill handle.

This is the output.

Read More: Division Formula in Excel for Multiple Cells

Method 3 – Inserting an Excel QUOTIENT Function to Divide One Column by Another

Use the QUOTIENT function. Only the integer component of the division will be returned.

Steps:

• Choose a cell to see the division.
• Enter QUOTIENT with an equal sign (=).
• Select the two cells you want to divide. Use a comma (,) to separate these cells.

• Press Enter.

• Drag down the Fill Handle to see the result in the rest of the cells.

• You can also copy the formula by double-clicking the fill handle.

This is the output.

Method 4 – Dividing an Entire Column by a Constant Number Using a Formula

Steps:

To divide column ‘A’ by two:

• Enter = sign in the result cell.
• Select B5 and divide it by two. Use the forward-slash (/) sign.

• Press Enter.

• Drag down the Fill Handle to see the result in the rest of the cells.

• You can also copy the formula by double-clicking the fill handle.

This is the output.

Method 5 – Using the Absolute Cell Reference Divided by a Constant Number

You can also divide a whole column using an absolute reference by a fixed integer. The dollar ($) symbol indicates an absolute cell reference.

Steps:

To divide column ‘A’ by two.

• Enter = in the result cell.
• Select B5 cell and divide it by C5.
• Press F4.  C5 cell will be an absolute reference. To divide it, use the forward-slash (/) sign.

• Press Enter.

• Drag down the Fill Handle to see the result in the rest of the cells.

• You can also copy the formula by double-clicking the fill handle.

This is the output.

Read More: How to Divide a Group of Cells by a Number in Excel

Method 6 – Applying the Paste Special Tool to Divide an Entire Column

Use the Paste Special option.

Steps:

To divide column ‘A’ by two:

• Copy the range in column ‘A’.

• Paste it into the ‘Result’ column to see the results.

• This is the output.

• Copy C5 again.

• Select the ‘Result’ column range and right-click.
• Click Paste Special.

• In the Paste Special window, in Operation, choose Divide.
• Click OK.

This is the output.

Read More: How to Divide for Entire Row in Excel

Method 7 – Dividing One Column by Another Using a Percentage

Use percentages to divide an entire column.

Steps:

To divide column ‘A’ by 20%:

• In the result cell, enter =.
• Divide column A by C5 cell using an absolute reference.

The result is 50.

• Divide the values in column by a constant number of 20% without using cell references. It will return 50.

• If you divide column A by 0.2, the result is also 50.

• If you divide column A by (1/5), you’ll get the same result: 50.

This is the output.

Things to Remember

A #DIV/0! error will be displayed if there is no value in the divisor cell. If this problem occurs, use the IFERROR function.

Download Practice Workbook

Download the practice workbook here.

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