The Commission Run Was 478.20 Out on 50,123.25, Because a Double-Click on the Fill Handle Stopped at the One Rep With No Sales

The workbook is called Commission_Q3.xlsx and it is rolled forward every quarter from the last one. Column A is the rep, column D is their net sales for the quarter, column E is commission, and the rate — 4.5% — sits on its own in J2. The person who prepares it pastes the new sales figures into column D, clicks E2, types =D2*$J$2, and double-clicks the little square at the bottom-right corner of the cell to send the formula down the column. It takes half a second and it has worked every quarter for four years.

This quarter one rep, Moreau, was on parental leave for the whole of the period. The sales export had no row for him, so his net-sales cell was left empty rather than filled with a zero.

The double-click filled E2:E13 and stopped. Rows 14 to 17 kept what was already in them, which — because the file is rolled forward rather than rebuilt — was last quarter's commission, pasted as values in July. Four people out of sixteen were paid against Q2 figures in a Q3 run.

The control was a tolerance: the preparer compared the run to the previous quarter and to the sales total, and 0.95% was well inside what a quarter's movement could explain. It was the net figure that was small. Underneath it, Moreau was paid 2,431.65 for a quarter in which he sold nothing, Pereira was short 1,876.50 on his best quarter of the year, Novak was short 875.70, and O'Rourke was over by 798.75. Two of the four noticed. Two did not, and one of those two was the one being overpaid.

Nothing in the file was broken. The formula in E2 was correct, the rate was correct, the sales figures were correct, and the total in E18 was a perfectly honest =SUM(E2:E17) of what the column contained.

What this covers. The fill handle, Ctrl+D/Ctrl+R and Ctrl+Enter are in every version of Excel, on Windows, Mac and the web. Custom lists are a desktop feature — Excel for the web will fill using them but has no dialog to edit them. ISFORMULA needs Excel 2013 or later; SUMPRODUCT, COUNTA, COUNTBLANK, MATCH, INDEX, ROWS, SUM and ROUND work everywhere. The example is a commission run because a commission run is the shape of problem where a part-filled column survives review: a column of numbers, all plausible, one per person, totalled once.

1) The Column That Stopped at Row 13

Here is the sheet as the file actually holds it, with the recomputation printed beside it.

Read column D down to row 14 and the whole thing is explained: D14 is empty. The double-click filled as far as the block of values next door went, and next door stopped at row 13.

The four rows underneath kept a number each. That is the part that makes this expensive rather than obvious. If the rows below the stop had been empty, the total would have been 39,492.45 against a sales figure of 1,113,850 — a commission rate of 3.55% on a 4.5% scheme, and anybody would have caught it. Instead they held stale values in the right shape, in the right format, of the right order of magnitude, and the total came out 0.95% away from the truth.

🎯 Scenario: Open any workbook where a formula column sits beside a data column and put =ROWS(E2:E1000)-SUMPRODUCT(--ISFORMULA(E2:E1000)) in a spare cell. It counts the cells in that column that are not formulas. If you believe the column is a formula column and the answer is not zero, the difference is the number of rows that are carrying something else.

2) What the Double-Click Actually Measures

The double-click is not filling "the column". It is filling as far as its neighbour goes.

When you double-click the fill handle, Excel looks at the column immediately to the left of the one you are filling. If that column is empty all the way down, it looks to the right instead. It then fills your formula down to the last row of the contiguous block of values it found there — and stops at the first genuinely empty cell.

Three consequences, and all three of them are in this file:

• The extent of your column is decided by somebody else's data. Column D is maintained by whoever pastes the sales export. Their gap becomes your gap.
• It stops silently. There is no dialog, no warning, no flag on the cell, nothing in the status bar. A fill that covers 12 rows and a fill that covers 1,200 look identical while you are doing them.
• It can also do nothing at all. If both neighbours are empty, the double-click has nowhere to measure, so Excel fills nothing — and the click feels exactly the same as one that worked.

A related trap, worth knowing before you go looking for the gap: a cell that looks empty is not always empty. A formula returning "", or a single space typed by an import, is content as far as Excel is concerned, so it does not stop the fill; it just fills straight past it. COUNTBLANK counts "" as blank, COUNTA does not, and the difference between those two counts over the same range is a list of the cells that are lying to you.

🎯 Scenario: Before you fill anything down a column, check the neighbour you are about to measure against. =COUNTA(D2:D17) against =ROWS(D2:D17) — 15 against 16 in this file — tells you there is a hole, and =COUNTBLANK(D2:D17) tells you how many. Find them first with Ctrl+G → Special → Blanks, which selects every one of them at once.

3) Copy, or Series? What the Handle Does to What Is in the Cell

The fill handle does not have one behaviour. It has a lookup table, and what comes out depends entirely on what was in the cell you dragged from.

The dangerous line in that table is INV-0001. Dragging a reference number down a column does not copy it; it manufactures new ones, in exactly the format of the real ones, that exist in no other system. They sort correctly. They look right in a printout. They match nothing in the ledger, and the only way to find out is to ask the ledger.

Three ways to take control of which behaviour you get:

• Hold Ctrl while you drag and the default inverts: a number that would have been copied becomes a series, and a date that would have become a series is copied instead.
• Right-drag the handle — drag with the right mouse button — and let go. You get a menu: Copy Cells, Fill Series, Fill Formatting Only, Fill Without Formatting, Fill Days, Fill Weekdays, Fill Months, Fill Years. This is the one to use when the answer matters, because you choose before anything is written.
• The AutoFill Options button appears at the bottom-right of the filled range after an ordinary drag, and offers the same choices retrospectively. It disappears the moment you do anything else.

🎯 Scenario: If a column of references, codes or invoice numbers has ever been filled rather than typed, check it against its source: =SUMPRODUCT(--ISNA(MATCH(A2:A200,Ledger!$A$2:$A$5000,0))) counts the ones that exist nowhere but this sheet. On a clean file the answer is 0. On a filled one it is the number of references Excel invented.

4) The Reference That Moved, and the One That Did Not

The formula in E2 is =D2*$J$2. Both halves of that matter in opposite directions.

D2 is relative: filled down to row 17 it becomes D17, which is what you want — each row multiplies its own sales. $J$2 is absolute: filled down it stays $J$2, which is also what you want, because the rate lives in one cell.

Write it as =D2*J2 instead and the fill turns the rate reference into J3, J4, J5 — empty cells, every one of them. An empty cell multiplied by a number is 0, not an error. So the column fills completely, in the right format, with the right number of rows, and every value in it is zero. It sums to zero, it prints, it charts, and it reads as "nobody earned commission this quarter" rather than as "this formula is broken" — which is a worse failure than the one in this article, because it is so complete that people look for the cause in the business.

The $ locks whatever it stands in front of. F4 on a selected reference in the formula bar cycles through all four. And a mixed reference is not an intermediate position between the other two — it is the right answer for anything filled in one direction and copied in the other, like a rate table filled down and across.

🎯 Scenario: A column of zeros where you expected values is almost always a lost anchor. Click the last cell of the column, not the first, and read the formula bar: =D17*J17 tells you the whole story in one glance. The first cell will look perfect, because the first cell always does.

5) Custom Lists: the Thing That Changes Both Filling and Sorting

Excel ships with four custom lists — Sunday to Saturday, Sun to Sat, January to December, Jan to Dec — and that is why typing Mon and dragging gives you a week rather than three copies of Mon.

You can add your own at File → Options → Advanced → General → Edit Custom Lists, and people do: a list of regions in board-pack order, a list of product families, a list of cost centres. It is genuinely useful, and it has two properties nobody expects.

First, a custom list changes sorting as well as filling. Sort → Order → Custom List is where it shows up, and once a sheet has been sorted that way, the order is baked into the rows. "North, South, East, West" is a perfectly good sort order and is not alphabetical, chronological or numerical. Anyone reproducing the report will get a different order and will not be able to work out why.

Second, custom lists live on the machine, not in the workbook. They are stored in your Excel settings, not in the file. Send the workbook to a colleague and the list does not travel with it. So a fill that produces a clean sequence of regions on your machine produces four copies of the same word on theirs, and a sort that came out in board order for you comes out alphabetically for them — from the same file, with no setting in it to point at.

And a smaller edge that bites data more often than it should: anything that starts a custom list fills as that list. A column of product codes where one of them is Mar, a surname May, a supplier abbreviated to Jun — drag any of those and Excel confidently continues the calendar.

🎯 Scenario: If a sheet's rows are in an order you cannot explain, open Edit Custom Lists on the machine that produced it before you look at anything else. And if a workbook depends on a custom order, put the order in the workbook — a small lookup table with a sort-key column, used with INDEX/MATCH or XLOOKUP — so it travels with the file instead of with the person.

6) Fill Without Formatting, and the Borders You Just Destroyed

The fill handle carries formatting along with the value, and that is a separate decision from copy-versus-series.

Drag a currency-formatted cell down over a column that was formatted as dates and you now have a column of dates displayed as currency: 46,266.00 where 01/09/2026 used to be. The underlying serial numbers are untouched — this is purely a display change — but a report that prints from those cells is now wrong in the only place anybody reads. The same applies to borders, shading, conditional-formatting inherited from the source cell, and the careful grey fill somebody used to mark input cells.

The fix is the AutoFill Options button, or the right-drag menu, and Fill Without Formatting: values and formulas go down, formatting stays as it was.

Ctrl+D (fill down) and Ctrl+R (fill right) are the keyboard versions, and they behave differently from the handle in one important way: they always copy — never a series — and they fill the range you have already selected rather than a range Excel works out for you. They do carry formatting. That is the trade, and for a formula column it is usually the one you want, because the thing you are filling is a formula and a formula has no series.

🎯 Scenario: After any fill that crossed a formatting boundary, select the filled range and check the Number Format box on the Home tab. If it shows blank — meaning the selection contains more than one format — you have either just created a mixture or just destroyed one. Ctrl+Z, then right-drag and choose Fill Without Formatting.

7) Filling Past the Gaps: the Version That Cannot Stop Early

The double-click's whole failure mode is that Excel chooses the range. So choose it yourself, and the failure mode is gone.

Name the range before you fill. Click the Name Box — the small box to the left of the formula bar that normally reads A1 — type E2:E17, press Enter. The whole range is now selected, gaps in the neighbouring column or not. Press Ctrl+D and the formula in the top cell fills every row of it. There is no measuring step for anything to go wrong in.

Or type it once into a selection. Select E2:E17 the same way, type =D2*$J$2, and press Ctrl+Enter instead of Enter. Excel writes that formula into every cell of the selection at once, adjusting the relative references per row. This is the single most useful key combination in this article: it cannot stop early, because the range was decided before a character was typed.

And for filling in the gaps themselves — the shape you get from a unpivoted export or a de-merged column, where a label appears once and the rows underneath are blank:

1. Select the column.
2. Ctrl+G → Special → Blanks → OK. Every empty cell in the column is now selected.
3. Type =, press the Up arrow — the formula bar reads =A3 or whatever is above the first blank.
4. Ctrl+Enter.

Every blank now carries the value above it, each one pointing at its own predecessor. Convert to values with a paste-special if the file is going anywhere else.

🎯 Scenario: Retrain the reflex. Any time you are about to double-click a fill handle on a column that matters, use the Name Box and Ctrl+D instead. It is two seconds slower and it removes an entire class of error, because the range came from you rather than from the shape of the data next door.

8) The Version That Fills Itself

The permanent fix is not a better fill. It is not needing one.

Select the data and press Ctrl+T to make it a Table. Type a formula into any cell of a column and Excel turns it into a calculated column: it propagates to every row of the Table immediately, and — the part that matters for a file that is rolled forward or appended to — to every row added afterwards. Paste 200 new rows on the bottom and the commission column is already populated on all 200.

Gaps stop mattering, because the Table's extent is the Table. It is defined by the object, not inferred from the neighbouring column, so D14 being empty simply produces a 0 in E14 like any other empty cell, in a column that runs the full height of the Table.

Written as structured references it also reads as what it is:

=[@[Net sales]]*$J$2

Two things to know before you commit to it. A calculated column is meant to be one formula: type an exception into a single cell and Excel marks the column inconsistent and offers to restore it, which is a feature — it is telling you that somebody has hand-edited a row. And the Table's Total Row uses SUBTOTAL(109,…), which follows the filter, so a filtered Table's total is the total of what you can see. A hand-written =SUM(E2:E17) two cells below it is not, and that disagreement is its own article.

🎯 Scenario: Take the one workbook you roll forward most often and convert its main data block to a Table. Then check what broke: any formula elsewhere that pointed at a fixed range like E2:E17 is now pointing at a range that will not grow with it. Repoint those at the Table's column — Commission[Commission] — and the whole file stops caring how many rows arrive next quarter.

9) Five Checks Before You Trust a Filled Column

All five are one cell each, and all five are computed against this file's E2:E17.

1. Is the whole column actually formulas?

=ROWS(E2:E17)-SUMPRODUCT(--ISFORMULA(E2:E17))    →  4

Sixteen rows, twelve formulas. Any answer but 0 means the column is part formula and part something else.

2. Where does it stop?

=MATCH(TRUE,INDEX(NOT(ISFORMULA(E2:E17)),0),0)   →  13

The thirteenth row of the range — sheet row 14, because the header takes row 1. That is Moreau, and one look at D14 explains the rest.

3. Does the column agree with its own inputs in total?

=SUM(E2:E17)          →  50,601.45
=SUM(D2:D17)*$J$2     →  50,123.25

Two routes to the same number, 478.20 apart. This check costs one cell and would have caught the whole thing before the payment run.

4. Which rows disagree?

=SUMPRODUCT(--(ROUND(E2:E17-D2:D17*$J$2,2)<>0))  →  4

The ROUND is not decoration: comparing a stored number to a recomputed one without rounding will find differences in the fifteenth digit that are not differences at all.

5. Is there a hole in the neighbour?

=COUNTBLANK(D2:D17)   →  1

Run this one before you fill. The gap is the cause; everything above is the symptom.

10) Twelve Traps

1. The double-click measures the column next door. A gap in data you do not own decides how far your formula goes.
2. It stops in silence. No dialog, no flag, no status bar message. A 12-row fill and a 1,200-row fill look identical while you do them.
3. Rows below the stop keep what was there. In a file that is rolled forward, that is last period's numbers — the right shape, the right format, the right order of magnitude, and wrong.
4. A double-click with both neighbours empty does nothing at all, and feels exactly like one that worked.
5. A cell containing "" or a space is not empty, so the fill runs straight past the gap you were looking for. COUNTBLANK and COUNTA disagree about those cells, which is how you find them.
6. One number copies; two selected numbers make a series. The difference is what you selected before dragging, not how you dragged.
7. Trailing digits in text increment. INV-0001 fills to INV-0002, and the fabricated references are indistinguishable from the real ones until you match them against the source.
8. A date fills as a day series by default, so a column of identical "as at" dates quietly becomes a calendar.
9. Ctrl while dragging inverts the default — number copy becomes series, date series becomes copy — which is useful when you mean it and baffling when you do not.
10. J2 where you meant $J$2 returns a column of zeros, not errors. Zeros sum, format, print and chart, and they send people looking for the cause in the business.
11. Custom lists live on the machine, not in the workbook, so the same file fills and sorts differently for different people, with nothing in it to explain why.
12. The handle carries formatting with the value. Use Fill Without Formatting, or use a Table's calculated column and never drag anything again.

Nobody in this story was careless. The preparer used the fastest correct method in Excel for sending a formula down a column, and it is correct — it is the method Microsoft built for exactly this, and it works on every well-formed block of data. The reviewer compared the run to the previous quarter and to the sales total, which is the right control, and it returned 0.95%. The rep on parental leave had no row in the sales export, which is what should happen when somebody has no sales.

What made it expensive is that the fill handle answers a question about layout — how far does the block next door go — while everybody using it believes they are answering a question about logic: apply this formula to these rows. Those two questions have the same answer on a rectangular sheet with no holes in it, which is most sheets, most of the time, which is why the habit survives for years before it costs anything. The moment a single cell goes empty, they separate, and nothing on screen says so.

So the discipline is small. Choose the range yourself — Name Box, then Ctrl+D, or a selection and Ctrl+Enter — rather than letting a double-click infer it. Count the formulas in any column you are about to trust: =ROWS(range)-SUMPRODUCT(--ISFORMULA(range)) should be zero. Total the column a second way, from its own inputs, and put the two totals next to each other on the sheet. And when a file is going to be used again next quarter, make the data a Table, so the column fills itself and the question never comes up.