How to Lock a Cell in Excel When Dragging a Formula (Bonus Calculation Example)

You've just created a perfect formula in Excel – perhaps calculating sales commissions, employee bonuses, or a financial projection. It works flawlessly for the first row. But when you try to drag that formula down to apply it to the rest of your data, disaster strikes: you get zeros, error messages, or completely wrong numbers. This common frustration occurs because Excel's default behavior shifts cell references, and you need to know how to lock a cell in Excel when dragging to prevent it.

The Common Problem: Your Formula Breaks When You Drag It Down

Imagine you have a list of sales representatives, their total sales, and a single cell containing the company's bonus rate (e.g., 10%). You write a formula in the first row to calculate the bonus: `=C3*G3`, where C3 is the sales figure and G3 is the bonus rate. It calculates correctly for the first rep.

However, when you drag this formula down to calculate bonuses for everyone else, you'll notice a significant issue. The subsequent bonus amounts might show up as zero or incorrect values. As the transcript highlights, "The problem is, I will get wrong answers if I drag this formula down... The reason for that is the cell reference."

What's happening? By default, Excel uses "relative references." When you drag a formula from row 3 to row 4, Excel automatically adjusts all cell references by one row. So, `C3` correctly becomes `C4` (pointing to the next sales figure), but `G3` also incorrectly becomes `G4`. If `G4` is an empty cell or contains irrelevant data, your bonus calculation breaks. The core issue, as noted in our course material, is that while "C3 became C4, which is absolutely correct, and G3 became G4... this G3, this cell should always point to G3."

The 2-Second Fix: Using the Dollar Sign ($) to Lock Your Cell

The solution to how to lock a cell in Excel when dragging is surprisingly simple: the dollar sign ($). This symbol creates an "absolute cell reference," telling Excel to keep a specific part of the cell reference fixed, no matter where you drag the formula. This is often referred to as an excel freeze cell in formula technique or using an excel absolute cell reference.

Step 1: Go back to your original formula.

Click on the cell where you first wrote the correct formula (e.g., the bonus calculation for the first employee).

Step 2: Identify the cell that should NOT change.

In our bonus example, the sales figure (C3) should change as you drag down (C4, C5, etc.), but the bonus rate (G3) should always remain G3. So, G3 is the cell we need to lock.

Step 3: Add a dollar sign before the row number to lock the row.

Edit your formula. For the cell G3, you need to lock the *row* reference because you are dragging the formula *down*. Place a dollar sign before the row number: `G$3`. So, your formula will change from `=C3*G3` to `=C3*G$3`. As explained in the course, "if I just put a dollar sign before the three now it's like freezing or locking the three." This ensures that when you drag the formula down, the '3' in `G$3` will not change to '4', '5', etc.

Step 4: Drag the formula down again and show the correct results.

Now, with your modified formula, drag the fill handle (the small square at the bottom-right corner of the cell) down the column. Alternatively, after selecting the cells where you want the formula, you can use the keyboard shortcut Ctrl+D to fill down. You'll notice that all your bonus calculations are now correct because the bonus rate cell (G3) remains fixed. This is the essence of how to copy formula down without changing cell reference.

When to Lock the Column vs. The Row

The dollar sign can be placed strategically to lock either the row, the column, or both:

• `G$3` (Lock the Row): This is what we used above. The row number (3) is locked. When you drag the formula horizontally, the column (G) will change, but when you drag it vertically, the row (3) will stay fixed. This is ideal for dragging formulas *down* a column.
• `$G3` (Lock the Column): Here, the column letter (G) is locked. If you were dragging your formula *across* rows (horizontally), the column would remain G, while the row number (3) would change. This is useful when you have a reference that needs to stay in a specific column.
• `$G$3` (Lock Both Row and Column): This locks both the column and the row. No matter where you drag the formula, it will always refer back to cell G3. This is useful for fixed constants that should never move.

A simple rule of thumb: If you're dragging your formula down, lock the row (`G$3`). If you're dragging your formula to the right, lock the column (`$G3`).

Go Beyond the Basics

Manually typing dollar signs can be quick, but Excel offers an even faster way to cycle through cell reference types. When your cursor is in or next to a cell reference in the formula bar, simply press the F4 key. Each press will cycle through the different options: `A1` (relative) → `$A$1` (absolute) → `A$1` (row locked) → `$A1` (column locked) → `A1` (back to relative).

Mastering cell referencing is a fundamental skill that significantly boosts your efficiency in Excel. If you're ready to master this and other powerful techniques to streamline your reports and calculations, Juno School offers a comprehensive free certificate course on Excel Cell Referencing and Conditional Formatting. This course delves deeper into these concepts, providing practical examples and hands-on exercises to transform you into an Excel pro.