Function Problems

Fix Excel Fill Handle Not Continuing Repeated Number Sequences

Camila Milosovich Sep 27, 2026 872 views

Question details

The user needs to generate a sequence with repeated values (e.g., November 1, November 1, November 2, November 2) for a shift tracking worksheet, but the fill handle is copying the values inconsistently.

How to Fix Excel Fill Handle Not Continuing Repeated Number Sequences

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating and managing an employee shift tracker where rows are frequently added or deleted, requiring a stable, repeating date or number sequence.
Observed behavior: The Excel AutoFill handle fails to accurately predict and continue the repeated pattern, resulting in inconsistent data. Irregular rows and borders make the incorrect sequence even more problematic.

Before you start

Before proceeding, ensure that automatic calculation is enabled in your workbook by navigating to Formulas > Calculation Options > Automatic, and verify there are no merged cells interrupting your target column.

Solution 1Recommended

Use a Formula and Paste as Values

Ideal for static lists, this method uses a temporary formula to build the repeated sequence and converts it to hardcoded text so it won't break if rows change.

Excel's default AutoFill tool often struggles to recognize patterns that contain repeating duplicate elements. By mathematically defining the pattern with a simple formula, you force Excel to generate the correct sequence. Converting it to values afterwards ensures that adding or deleting employee shifts won't cause #REF! errors.

Enter the base sequence manually

Type your initial duplicate values in the first block of cells. For example, type 'November 1' in cell A1 and 'November 1' in cell A2.

Apply an increment formula

In cell A3, enter a formula that adds a specific value to the previous sequence block. For a sequence that repeats twice, type '=A1+1'.

Drag the fill handle

Select cell A3, click and hold the small green square at the bottom-right corner of the selection, and drag it down the column to generate the remainder of your sequence.

Paste as values

With the newly generated sequence still selected, press Ctrl+C to copy. Right-click the selected area and choose 'Paste Values' (the clipboard icon displaying '123') to remove the formulas while keeping the generated text intact.

Pro Tip: Pasting as values completely removes the underlying formula. This is the safest way to ensure your shift tracking dates remain accurate even if you delete an employee's row above it.

Solution 2

Format Data as an Excel Table for Dynamic Sequences

Best for shift trackers where rows are constantly added or deleted, ensuring your sequence formulas expand automatically without breaking.

Advanced Data Management

Manage Shift Trackers Effortlessly with WPS Spreadsheet

WPS Spreadsheet provides a highly compatible and user-friendly platform that easily handles complex data fills, formulas, and dynamic tables for all your shift tracking needs.

1. Open your tracker: Launch WPS Spreadsheet and open your existing shift tracking document.
2. Enter the formula: Use a simple formula like '=A1+1' to establish your repeating sequence.
3. AutoFill the column: Drag the fill handle down to populate the remaining employee rows seamlessly.
4. Convert to values: Copy the populated cells and right-click to select 'Paste as Values', securing the data against row deletions.

Fully compatible with Microsoft Excel (.xlsx and .xls) file formatsAdvanced drag-and-fill capabilities for repeating dates and complex number patternsSeamless support for Paste Values to lock in generated sequencesFree to use with a familiar, intuitive interface

microsoft office alternative - wps office

Frequently Asked Questions

Why is my AutoFill copying the exact same number instead of creating a sequence?

If your fill handle is only duplicating cells, click the AutoFill Options icon that appears at the bottom right after dragging. Choose 'Fill Series' from the dropdown menu to apply a sequence instead of a direct copy.

Can I make AutoFill repeat a sequence like 1, 1, 2, 2 without formulas?

Native AutoFill struggles to predict duplicate sequences just by dragging. The most reliable workaround is to use a simple formula (like referencing the cell two rows above and adding 1) and dragging it down, or manually adding the pattern via File > Options > Advanced > Edit Custom Lists.

How do I prevent sequence numbers from changing when deleting rows?

Formulas recalculate automatically when rows are modified or deleted. To prevent this, select the column containing your generated sequence, press Ctrl+C to copy, then right-click and use 'Paste Special > Values' to convert the dynamic formulas into static text.

Excel & Data Problems