Don't let serial dates outsmart you when Microsoft Excel's fill handle can handle even complex series with just a few clicks.
You probably use Excel's fill handle frequently—I know I do. But if you're using it for basic input tasks, such as copying formulas, text, or generating a simple series, you're not taking full advantage of this feature. Excel offers lots of shortcuts for entering dates and times. In this article, we'll discuss how to use Excel's fill handle to create a series of dates; in a future article, we'll learn how to work with time series.
LEARN MORE: Office 365 Consumer pricing and features
First, we'll use the fill handle to create a series of days and months to review the basic feature. Then, we'll move on to skipping days (or months) and then on to date series.
I'm using Office 365 Excel desktop, but you can use earlier versions. Everything works in the browser edition except for the right-mouse button submenu. There's no demonstration file; you won't need one.
Most users rely on AutoFill to copy formulas and complete any number of lists. In this section, we'll use the same feature to create a serial list of months. Simply enter any month abbreviation or full name and use the fill handle to complete the series. We'll work through one quick example.
- Enter Mar (or March) in cell A1.
- Grab the fill handle—the tiny square in the bottom-right corner—click and drag down to extend the selection for several rows (Figure A). As you drag the fill handle, notice a little tip that displays the value for each cell. This tip can be helpful; it's a visual clue that you can use to determine when you've completed the series.
- Release the mouse when you've completed the series.
That's it! You've saved a lot of time and prevented potential errors (typos).
This series is built-in—Excel knows what to do. There are several built-in series: Days of the week, abbreviated and full; months, abbreviated and full; dates, quarters, both Q1 and Quarter 1; 1st, 2nd, 3rd, and so on. There are a few things you should know.
- If you use an abbreviation, all values in the series will be abbreviated. The same thing is true with full names.
- You must use with the pre-defined values. For instance, if you type Sept instead of Sep for September, the fill handle will copy Sept instead of creating a series of months. Sept isn't part of a built-in series but Sep is. Abbreviations must be three characters. May is the one exception to the rule because its full name stands in as an abbreviation in the pre-defined list. If you start with May, you will get a full-name series, not abbreviations.
- Series are case-sensitive.
- Series work in rows, dragging up, and even dragging to the left, as you can see in Figure B. If you drag up, the series continues, but decreases each by one day or month. For instance, if you start with Nov, and drag up, the series will add Oct, Sep, Aug, and so on. Similarly, if you drag left, the series goes backward.
- If you want to repeat the day or month instead of creating a series, hold down Ctrl while dragging.
- To create a series that skips days (or months), enter enough values to create the pattern and then use the fill handle to create the skipping series. Figure C shows the results of entering Mon and Wed, selecting both values, and then dragging.
A series of days of the week or months is great, but the fill handle is much more flexible; it also recognizes dates and times. Let's start by creating an easy date series.
- Enter the first day in the series. If that happens to be the current date, press Ctrl+;.
- Use the fill handle to drag down; each cell will increase by one day (Figure D).
If you drag up or to the left, the dates decrease by one day. Almost everything you learned about days of the week and months works similarly with dates (and times). If you don't get the series you expected, review the original date(s); most likely you didn't specify the series correctly. If the series copies the date, make sure the first date is a valid date.
You can even create a series of the same day of the week (or month) by date. For example, to create a series of Mondays, you could enter the first two Monday dates in the series—say, May 27 and June 3—select both dates and drag. Figure E shows the resulting series. If you select both cells and drag up, the handle will enter the previous Monday dates.
Now, let's suppose you want to create a monthly series. Again, enter the first date of the first two months, select both dates, and drag. The series will generate a series of the same day of the month. Now, what if you want the last day of the month? You could enter the last day of the first two months, but there's another way.
Let's suppose you want to create a series of the last day of the month, beginning with Jan 31. To do so, enter Jan 31, but don't enter Feb 28 in the cell below. Instead, select the cell with Jan 31, hold down the right mouse button, and drag. This is a little different. When you release the right mouse button, Excel displays a submenu of series possibilities, as shown in Figure F. (To the right, you can see the series.) Because the first date is the last day of the month, all of the entries will be the last day of the month. To create a series of Jan 31 from year to year, you'd choose Fill Years. To create a series of dates that ignore weekends, choose Fill Weekdays. Check out all the possibilities on that menu; there are several that used creatively will help you create even more complex series.
You learned some neat fill handle tricks for generating date series. In a future article, we'll expand on the fill handle's capabilities and create time series.
Send me your question about Office
I answer readers' questions when I can, but there's no guarantee. Don't send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. I'm not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at firstname.lastname@example.org.
- How to add horizontal lines to a Word 2016 document (TechRepublic)
- How to combine Excel VLOOKUP() and PivotTable for simple solutions (TechRepublic)
- How to add a drop-down list to an Excel cell (TechRepublic)
- 10 Excel time-savers you might not know about (free PDF) (TechRepublic)
- Cost comparison calculator: G Suite vs. Office 365 (TechRepublic Premium)
- You've been using Excel wrong all along (and that's OK) (ZDNet)
- It takes work to keep your data private online. These apps can help (CNET)
- Get more must-read Microsoft coverage (TechRepublic on Flipboard)