AutoFill and inserting rows in Excel — fill handle, weekdays only, and a total that won't grow

The fill handle — what you drag and what you get

Select a cell and a small square appears at the bottom right corner of the border. That's the fill handle. Point at it until the pointer turns into a thin plus (+), then drag down (or across). The practice file is a made-up class schedule with only the first row filled in the No., Date, Day and Week columns.

You can also fill without dragging: select from the top cell down and press Ctrl+D (Home > Fill > Down) — the top cell is copied into the cells below. On this PC, =A1+1 filled with Ctrl+D became =A2+1 · =A3+1 · =A4+1.

Weekdays only, or one month at a time — Home > Fill > Series…

Dragging a date fills Saturdays and Sundays too. For class days you usually want weekdays only: select from the first date down to where the list ends, then Home > Fill > Series…. The Series box opens; with date cells selected, Type was already set to Date on this PC. Pick a Date unit:

Trap 1 — the dates become weekdays, the Day column doesn't

A Day column filled by dragging 'Mon' is just the list Mon → Tue → Wed … typed out. So after the dates were re-filled as weekdays, row 7 (Mon Nov 9) said 'Sat' and row 8 (Nov 10) said 'Sun'. Excel shows no error for this.

1. 1Type =TEXT(B2,"ddd") in C2. It returned 'Mon' (format code ddd = short weekday name).
2. 2Drag C2's fill handle to row 11: Mon · Tue · Wed · Thu · Fri · Mon · Tue · Wed · Thu · Fri — always matching the date.
3. 3Change a date and its weekday changes with it.

Trap 2 — a row inserted right above the total is left out of it

To add a class at the bottom, a row was inserted right above the total row (row 12). The total moved down to row 13, but the formula stayed =SUM(F2:F11) — with 20 students typed in the new row the total was still 120 (it should be 140). The new row sits outside the end of the SUM range (row 11).

Excel's error checking marked the total with a green triangle in its top-left corner. Reading the cell's error flags on this PC, 'formula omits adjacent cells' (xlOmittedCells) was on. Change the total to =SUM(F2:F12) and it reads 140.

Inserting the row above the last data row (row 11) instead put it inside the range, and the total widened to =SUM(F2:F12) by itself — 140.

Inserting several rows and renumbering

Drag across row headers 5, 6 and 7 and insert: three rows go in at once. On this PC the No. column then read 1 · 2 · 3 · (three blanks) · 4 · 5 …, and the total widened to =SUM(F2:F14) by itself (the rows were inside the range).

To renumber, select A2:A3 (1, 2) and drag the fill handle to the last row: 1 to 13 again. Do this once after each insert.

Common snags

• No fill handle: Microsoft's help says to turn on File > Options > Advanced > 'Enable fill handle and cell drag-and-drop'
• The number just copies: only one cell was selected — select 1 and 2 together and drag
• Weekends sneak into the dates: dragging fills every day — use Series… with Date unit Weekday
• Days don't match the dates: the Day column was dragged as text — use =TEXT(B2,"ddd")
• Inserted a row but the total didn't change: it went right above the total, outside the range — widen the range or insert above the last data row

Practice

Open the practice file (No., Date, Day and Week filled on the first row only) and try this:

1. 1Type 2 in A3, select A2:A3 and drag the fill handle to row 11 → No. 1 to 10.
2. 2Select B2:B11, Home > Fill > Series… > Date unit Weekday → Nov 2 to Nov 13 with no weekends.
3. 3Type =TEXT(B2,"ddd") in C2 and fill to row 11. In column D, five rows of Week 1 and five of Week 2.
4. 4Insert a row above row 12 (the total) and add a new class (11 · 2026-11-16 · Mon · Week 3 · Korean · 20). If the total says 120, that's trap 2 — change it to =SUM(F2:F12) for 140.

The answer file is the filled schedule with one new class added and the total fixed to =SUM(F2:F12) (140).

FAQ