Drag to fill duplicates values

Question

I want to create a sequence of dates that populate a column.

A
2023-12-31
2023-12-30
2023-12-29
...

Format Cell is set to Date.
Calculation Options is set to Automatic
Enable fill handle and cell drag-and-drop is enabled.

I have tried using a reference cell with the current date and then a formula, =$A$1-1; =$A$1-2; =$A$1-3; ..., as this has worked for other types of values that had the same issue, to no avail.

If I manually enter the correct value in the first cell and then drag to fill, it duplicates this value in every cell.

2023-12-31
2023-12-31
2023-12-31
...

If I manually enter the correct values in the first two cells and then drag to fill, it duplicates both of these values in the same sequence.

2023-12-31
2023-12-30
2023-12-31
2023-12-30
2023-12-31
2023-12-30
...

So on and so forth.

Microsoft Office Home and Student 2021

Answer 1

In A2 use the formula =A1-1 and then drag it down the column.

         A   
   +------------+
 1 | 2023-12-31 |
   +------------+
 2 | =A1-1      |
   +------------+

which will create

         A   
   +------------+
 1 | 2023-12-31 |
   +------------+
 2 | =A1-1      |
   +------------+
 3 | =A2-1      |
   +------------+
 4 | =A3-1      |
   +------------+
 5 | =A4-1      |
   +------------+
 6 | =A5-1      |
   +------------+
 7 | =A6-1      |
   +------------+
 8 | =A7-1      |
   +------------+
       etc.

The reason this works is that A1 is a relative cell reference, a reference without the $ sign in the row and column coordinates. When you copy a formula that contains a relative cell reference, that reference will change relative to the position of the original cell and the new cell. An absolute cell reference will not.

Address	Relative Formula	Absolute Formula	Mixed (v1) Formula	Mixed (v2) Formula
Copy B10 to C20	=B5 becomes =C15	=$B$5 remains =$B$5	=B$5 becomes =C$5	=$B5 becomes =$B15

 

More Info:   Switch between relative, absolute, and mixed references