Copy Dates & Fill Series of Numbers with the Fill Handle in Excel

Bottom line: Learn how to use the Fill Handle in Excel to copy down dates and fill a series of numbers.

Skill level: Beginner

Fill Handle Hacks

What's the Fill Handle?

The Fill Handle is a great tool that allows us to quickly copy values or formulas down an entire column.  We can hover our mouse over the bottom right corner of the selected cell/range until the cursor turns into a + symbol.

At this point we have two options:

  1. Drag it up/down or right/left to copy the cell contents to the new cells.  Left-click and hold to drag the fill handle.
  2. Double left-click the mouse to fill the formula or value down an entire column.  This fill down only works when there are cells in the column directly to the left of the fill handle.

How to Use the Fill Handle in Excel GIF

Note: Double-clicking the fill handle will only extend down to the last non-blank cell in the current region.  So if your column to the left contains a lot of blanks, the Fill Handle might not be the best option for copying down.

The fill handle has different behavior depending on the contents of the cell.

  • If the cell contains a value or a formula, then the fill handle will Copy down the value.
  • If the cell contains a date, then the fill handle will fill the series and increment the date by one for each cell.

How to Copy Dates Instead of Fill Series

In last week's blog post on Ctrl+Enter, Lisa left a great tip about using Ctrl+Enter to copy down dates because she did not want the fill handle to increment them.

Let's say we have entered today's date in cell B2 (keyboard shortcut Ctrl+;), and we want to copy the date down to all the cells in column B of the current region (used range).

If we double-click the fill handle, the fill series option will be used and increment each date by one day.

Double Clicking the Fill Handle Creates Series of Dates

But, we wanted to COPY the date down, not increment it!  Here are few ways to go about this using the fill handle to copy down.

Ctrl+D to Copy Down Dates

After double-clicking the fill handle, press Ctrl+D to copy the date down.

How to Copy Down with the Fill Handle Instead of Fill Series

Ctrl+D is the keyboard shortcut to copy down the value/formula in the selected range.  The value in the active cell (usually the first cell in the selected range) is copied down with Ctrl+D.

Bonus tip: Ctrl+R is the shortcut to copy across.  I remember this by D for down, R for row.

Bonus tip #2: Ctrl+D can also be used to duplicate shapes, charts, and slicers.  I shared this tip on my video on how to create the Progress Doughnut Chart.

Auto Fill Options Menu to Copy Cells

Another option is to use the mouse.  After double-clicking the fill handle, the Auto Fill Options button appears below the last selected cell.

Clicking the button will open the Auto Fill Options menu.  In the example with the date value, the Fill Series radio button will be selected.  Click the Copy Cells radio button instead.  The date value will then be copied down.

Auto Fill Options Menu to Copy Cells for Dates

Windows Context Menu Key Keyboard Post ThumbBonus tip: The Auto Fill Options Menu can be opened with the keyboard shortcut: Alt+Menu Key.  Then press the underlined letter of the option in the Auto Fill Options menu.  So, the keyboard shortcut to change the Auto Fill option to Copy Cells is Alt+Menu Key, C.

Ctrl+D is faster than this, but it's good to know the keyboard shortcut exists.  If your Menu Key is feeling neglected, then check out my article on keyboard shortcuts for the Menu key.  Also checkout my article on the best keyboards for Excel keyboard shortcuts.

How to Create a Sequential List of Numbers

Another common use of the fill handle is to create a sequential list of numbers. 1,2,3,4,…

If we enter a 1 in a cell and then double-click the fill handle, the Copy Cells option is used in the Auto Fill Options, instead of Fill Series.  Again, this is the opposite of what we want to happen in this case.

Double Clicking the Fill Handle Copies Numbers Down

We can use the Auto Fill Options menu to change the fill from Copy Cells to Fill Series.  The keyboard shortcut is: Alt+Menu Key, S.

We can also use the Fill Series menu that is on the Home tab of the ribbon.  Keyboard shortcut is Alt, h, f, i, s.  The old school Excel 2003 shortcut is a little shorter: Alt, e, i, s.  That still works in modern versions of Excel.

Ctrl Key + Auto Fill

We can also use the Ctrl key to help us with filling a series (sequence) of numbers.

Create Series of Numbers with the Fill Handle and Ctrl Key

Here are the steps to fill the series of numbers:

  1. Enter a 1 in the first cell and press Ctrl+Enter to keep the cell selected.
  2. Press & hold the Ctrl key.
  3. Use the fill handle to drag the number down one cell.  A 2 will be placed in the next cell.
  4. Double-click the fill handle to perform the fill series on the column.

When we enter a 1 in the first cell and a 2 in the second cell, then double-click the fill handle, Excel recognizes this as a series of numbers, and completes the fill as a series.

Holding the Ctrl key while dragging the fill handle will do the opposite of whatever the fill handle does.  Holding the Ctrl key will copy dates and fill series on numbers.  One of the many quirks of Excel… 🙂

We can also use the fill handle to copy Table formulas across in the Totals Row of a Table to keep the references relative.

How Do You Fill a Sequence of Numbers in Excel?

There are many other techniques for filling a series of numbers in a column.  How do you perform this task?  Please leave a comment below with your preferred method, or any questions.  Thank you!


Your email address will not be published. Required fields are marked *

  • Hi Jon,
    I do enjoy your post. When I use Ctrl+D my workbook closes without messages or saving. Any ideas?


  • I used to type 1, 2, 3 or so and then autofill the rest down, but it doesn’t work with a table to the right. One could argue that you can do that IN the table, but in this case I was making a paper log for our field techs, and I was only using the table for formatting with a list of items to the left. I can’t remember for the life of me where I saw this tip, and I’ve only used it once so far, but it works fairly well.

    Assuming that the first row contains headers, and the table starts in column B, so that you can make an automatic list of sequential numbers in column A as the table grows:



  • Here’s another trick to enter the same date into a range of cells:
    1. Select the range of cells you want to fill with the same date
    2. Key in the date you wish to use or press keyboard shortcut Ctrl+; for today’s date
    3. Press Ctrl+Enter
    This works for contiguous and noncontiguous ranges.

  • Hi jon,
    I really love to learn your tips and tricks it really helps me a lot. Looking for more tips and tricks and thank you…you’re the best!!!


  • Thank you, Jon. I was just wondering about how to copy down without filling in as a series today.

    Is there a secret to double clicking and having values to fill across columns, that is fill to the right? I know CTRL R fills right, but I would have to click and drag to select the area before filling using CTRL R, isn’t that correct?

    I need something quick like the double click to send values down a single column trick.

    Thank you!

  • I prefer Ctrl + D to copy Date rather than Alt + Menu Key + C

    However, to fill series of numbers I prefer to use Alt + Menu Key + S

  • I don’t understand why you used the ctrl + enter step in the “CTRL Key + Auto Fill” section. I tried without that step and it still seems to work fine.

    • I have this series that I would like to try this with:
      A-01-01-A, A-01-02-A, A-01-03-A…ETC
      A-01-01-B, A-01-02-B, A-01-03-B…ETC

      Any idea how to do this type?

  • A-01-01-C A-01-02-C A-01-03-C A-01-04-C
    A-01-01-B A-01-02-B A-01-03-B A-01-04-B
    A-01-01-A A-01-02-A A-01-03-A A-01-04-A

    How do I do this series, it goes for 24 sections across and 10 top to bottom

  • Hello sir,

    How are you. i hope your will be fine. this is your new friend.i am working as a DOCUMENT CONTROLLER in SAUDI ARABIA. i want to learn excel very well. but i need your help.

    Thanks & Warm Regards
    numan khan dawar

      • I’ve tried hard to find the keyboard equivalent to double clicking the fill handle lower right. Ctrl-D only does ONE cell, or the selected range.

        Is there a keyboard shortcut to fill down to the bottom of the adjacent column? As in exactly as double clicking the fill handle?

        Surely this must be a common request, and I see many requests, but seems no simple shortcut.

        I’ve tried the Ribbon fill series, which works, but you have to specify the stop row. How can we use shortcuts to say fill down as far as the last value on the column to the left?

      • So I JUST discovered Shift-Ctrl-END will select down to the last value in the column to the left. Hence the two step combination works


        Thank goodness. It’s a shortcut I have wanted for years, and never found. But I still wonder if there is a simpler way yet, other than a macro. Anyway, this seems to be the answer I see many asking for.

  • Is anyone know a shortcut copy with increment 1 from the cell above (Like you do with CTRL+D, which just copy the same value from the above cell?

  • Very helpful, thanks! I wanted to fill a column with sequential numbers. But there were over 20,000 rows to number, way too much for the click-and-drag method which is all I could find until I somehow found your site. I have bookmarked it!

  • Hello,

    My issue is that I want the Auto Fill to continue the designated pattern (which I know it can do). If I want the increment to be by 1, (ex: 1, 2, 3, 4, 5, 6…) it works perfectly. If I want the increment to be by… say… 2 (ex: 2, 4, 6, 8, 10) it won’t work. I will type in a few of the first values to initiate the pattern but when I try to drag it down to more cells in the column, it will automatically overwrite all the data and copy the cells instead. It will change all the values I have to the first number so all the selected cells now have the number 2 in them. I then click in the lower right hand corner to change from “copy cells” to “fill series”. It then changes my pattern to “1, 2, 3, 4, 5, 6, 7, 8…” instead of the correct pattern I want. So how do I use AutoFill to continue the designated pattern that is NOT just +1 number.

    Thank you so much for your time and help as I know this is probably a quick and simple fix, I am just doing something wrong.

    • type your starting number in your desired cell. then select this and all the other cells that you want your pattern to extend to.

      Then on the home ribbon, click fill, then series, a dialog box will appear. change the step value from 1 to 2, and it will extend the pattern you’re after.

    • Try the new SEQUENCE function if you have it:
      100 rows, one column, start at 1 , and increment by 2.

      Before SEQUENCE I used to enter 1 in first cell (say A1) then enter =A1+1 in A2, copy down, drag right and back to convert to values.

  • I used to be able to copy, fill series etc with the fill handle in the latest version of Excel. However for the past few months, without prior notice, when I use the fill handle I get a menu with options such as Formatting, Charts, Totals, Tables and Sparklines. Why is this, and what can be done about it? This is very frustrating. Thanks.

  • As for creating numerical series, I just fill one cell with the first value, then I press and drag the fill handle down. Works like a charm, even for creating horizontal series, by dragging the fill handle rightwise.

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter