askvity

How to Divide Cells by the Same Number in Excel?

Published in Excel Formulas 2 mins read

To divide a column of numbers by the same value in Excel, use a formula in the first cell and then copy that formula down the entire column. Here's how:

Steps:

  1. Choose the Divisor: Decide on the number you want to divide all other numbers by. Let's say you want to divide every number by 5.

  2. Enter Data: Assume the numbers you want to divide are in column A, starting from cell A1.

  3. Write the Formula: In the first empty cell of another column (e.g., B1), enter the following formula: =A1/5. Replace A1 with the first cell containing the number you want to divide, and 5 with your chosen divisor.

  4. Copy the Formula:

    • Click on cell B1 (or the cell containing your formula).
    • Hover your mouse over the small square at the bottom-right corner of the cell. Your cursor will change into a black plus sign (+).
    • Click and drag the plus sign down to the last cell in column A that contains a number. This will copy the formula down the column.

  5. Results: Column B will now display the results of dividing each corresponding number in column A by 5.

Example:

Column A (Original Values) Column B (Formula: A1/5)
10 2
25 5
50 10
100 20

Dividing by a Cell Reference (Best Practice):

Instead of hardcoding the divisor into the formula (e.g., /5), it's better practice to reference a cell containing the divisor. This allows you to easily change the divisor later without modifying every formula.

  1. Enter the Divisor: Place your divisor (e.g., 5) in a specific cell, such as D1.

  2. Modify the Formula: In cell B1, use the following formula: =A1/$D$1. The $ signs create an absolute reference to cell D1. This means that when you copy the formula down, the reference to D1 will not change.

  3. Copy the Formula: Copy the formula down column B as described above.

Example (Using Cell Reference):

Column A (Original Values) Column B (Formula: A1/$D$1) Column D
10 2 5
25 5
50 10
100 20

Now, if you change the value in cell D1, all the results in column B will automatically update.

Related Articles