Happy New Year ,

Let's kick off the year with some tips to get you working more efficiently in Excel. First, our tip lets you in on a little known secret to navigate to the previously selected cell:

Note: if the image above isn't animated, you'll find it on our social media channels:

Excel Fill Series

And this week's tutorial focuses on some hidden tips for using the Fill Series tool.

The Fill Series tool is like having a bundle of tricks at your fingertips. It can help with one of the most common tasks we do in Excel, which is to create a list of dates. Whether it’s a list of days, months or years, the Fill Series tool can easily handle them all in its stride.

Watch the Video

Download Workbook with Notes and Examples

Excel Fill Series Options

For example let’s say we want to create a list of dates from January 1, 2023 to December 31, 2023. Simply enter the first date in cell A1 (or whichever cell you want to start your list in).

BTW, my dates are formatted dd/mm/yyyy.

Option 1: Mouse Mayhem

Left click and drag the fill handle for a minute or so doing the scroll dance until you reach December 31, 2023.

Yes, it’ll take a minute or so because it’s difficult to stop the mouse at exactly the right cell! That’s why this option is called “Mouse Mayhem”.

The only thing more frustrating than having an out of control mouse is watching someone else with an out of control mouse. Mastering keyboard shortcuts for navigating the worksheet is essential to working efficiently in Excel, but that’s a post for another day.

Option 2: Excel Fill Series

The solution to mouse mayhem is to use the Fill Series tool and there are two ways to access it, either via the Ribbon: on the far right of the Home tab:

Or if you can get your fingers to co-operate you can click and hold the RIGHT-mouse-button on the fill handle, drag down one cell and then back up to cell A1, then release the mouse button. This will display a few options with ‘Series’ at the bottom:

Now, you might find the right-click and drag technique feels a bit weird! You know the kind or ‘weird’ that’s similar to right-handed people writing with their left hand, and vice versa.

It’s worth it though as there are is a secret menu here which includes a range of options, including the ‘Series’ tool:

The Series dialog box contains loads of options but most of them are automatically selected because the value in cell A1 is a date. Let’s take a look:

1. I want to fill my dates down column A, so I need ‘Columns’ selected.
2. Excel has detected that I’m using Dates because I have a date in cell A1.
3. It has also assumed I want to increase my series by days, as opposed to weekdays, months or years.
4. And the step value is one day at a time.
5. All I need to do is enter the Stop value, which is the last date in my series.

Click OK and you’ll have a list of dates from January 1, 2023 to December 31, 2023 in a few clicks.

Excel Fill Series Tools

We’ve seen the Fill Series tool create a list of dates but if you take a closer look at the dialog box you’ll notice there is a huge range of options for automatically creating series of values.

1. Notice if the value in the first cell is a number then you’ll see the ‘Date unit’ options are greyed out:

1. If you select more than one cell the Series will be limited to those cells irrespective of what Stop Value you enter.
2. The right-click and drag on the fill handle also gives you some default options for filling series, including Fill Weekdays, Fill Months, Fill Years etc.:

Note: Flash Fill in the menu above is only available in Excel 2013 onwards.

Create a Series of Bi-monthly Dates

Enter the first two dates > right-click and drag the desired number of cells > Fill Months:

Credit: I learnt this last tip in Bob Umlas’ book Excel Outside the Box. It’s full of clever tips like this. You can get an electronic copy for next to nothing, here.

DISCLOSURE: If you purchase Bob's book I make a couple of dollars, but that's not why I recommend it. It's a great book. I have my own copy and even after using Excel for 20+ years, I still learnt a load of tricks from his book.

Have a great day,

Mynda

Co-founder My Online Training Hub