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

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!

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 9 comments
Rohan Shriyan - October 25, 2017

To copy numbers in sequence use Alt E I S

Reply
Rube - August 12, 2017

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.

Reply
Mezgebe - April 9, 2017

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

Reply
KC - April 7, 2017

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!

Reply
Iris - April 7, 2017

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

Reply
Lisa - April 7, 2017

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.

Reply
Carrie - April 7, 2017

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!

Reply
Eddie Pruitt - April 7, 2017

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

Thanks,
Eddie

Reply
Gregory C. Tocci - April 7, 2017

Jon,

Always look forward to your tips & tricks. Many thanks.

Greg.

Reply

Leave a Reply: