How to Make Excel Divide by the Same Cell?

To divide a range of cells by a single, fixed cell in Excel, use absolute referencing. This ensures the divisor remains constant while the dividend changes for each row or column.

Step-by-Step Instructions with Examples

Here's how to do it, with examples to illustrate the process:

1. Identify the Dividend and Divisor Cells: Determine which cell contains the number you want to divide (the dividend) and which cell contains the number you want to divide by (the divisor).

2. Enter the Formula with Absolute Referencing:

   • Select the cell where you want the result of the division to appear.
   • Type the equals sign (=) to start the formula.
   • Enter the cell reference for the dividend, which is the cell you want to divide. For example, A1.
   • Type the division operator (/).
   • Enter the cell reference for the divisor, which is the cell you want to divide by. Crucially, use absolute referencing for the divisor. You achieve this by adding dollar signs ($) before the column letter and row number. For example, if your divisor is in cell B1, you would enter $B$1.
   • Press Enter to calculate the result.

3. Apply the Formula to Other Cells:

   • Click and drag the fill handle (the small square at the bottom-right corner of the cell containing the formula) down or across to apply the formula to other cells in the column or row. Because of the absolute referencing ($B$1 in our example), the divisor (B1) will not change as you drag the fill handle, while the dividend (A1) will change relative to its new row/column.

Example

Let's say you have the following data:

	A	B	C
1	Value	Divisor	Result
2	10	2	=A2/$B$2
3	20		=A3/$B$2
4	30		=A4/$B$2

In this example:

• Column A contains the values you want to divide (the dividends).
• Cell B2 contains the divisor (2).
• Column C will contain the results of the division.

In cell C2, you would enter the formula =A2/$B$2. The $ signs before B and 2 in $B$2 make it an absolute reference. When you drag the fill handle from C2 down to C3 and C4, the formula in C3 becomes =A3/$B$2 and in C4 becomes =A4/$B$2. The divisor (B2) remains constant for all calculations.

Why Use Absolute Referencing?

Absolute referencing is essential when you want to keep a cell reference constant while applying a formula to multiple cells. Without absolute referencing, Excel would automatically adjust the divisor cell reference as you copy the formula, which would likely result in errors.

Alternative Scenario: Dividing by a Constant Number Directly

If you want to divide by a specific number (e.g., 4) instead of a cell reference, you can simply include that number directly in the formula: =A1/4. However, using a cell reference (and absolute referencing) is best practice because it allows you to easily change the divisor by simply changing the value in that cell without needing to edit all of your formulas.