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!

18 comments

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

  • I have noticed you don’t monetize excelcampus.com, don’t waste your traffic, you can earn extra cash every month with new monetization method.
    This is the best adsense alternative for any type of website (they approve all sites), for more details simply search in gooogle: murgrabia’s tools

  • 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!

  • 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?

  • 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
    KSA.JEDDAH

  • 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
    Thanks

    • 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?
      Thanks

  • 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 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

  • 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!

  • 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!!!

    Iris

  • 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.

  • 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:

    =IF(B2=””,””,(ROW(B2)-1))

    Cheers!

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

    Thanks,
    Eddie

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly