Q

Excel tip: Fill Handle tricks

Microsoft Excel consultant Bill Jelen, aka 'MrExcel,' shows how to use the Excel Fill Handle to extend series.

Using the Fill Handle to extend a series is a common technique in Microsoft Excel. Although most people have used the Fill Handle to populate month names across a spreadsheet, there are many more hidden features.

Let's start with the basics. The primary use of the Fill Handle is for filling month names, weekday names and dates across the top or down the side of a report.

To do this, type the first item in a cell. Position the mouse cursor over the square dot in the lower corner of the active cell. When the mouse pointer changes to a + sign, drag right or down to fill other items in the list.

Fill HandleFigure 1

After you drag the Fill Handle, Excel fills items from the series. If you go beyond 12 cells for months, Excel starts over again with the first month.

Months columnFigure 2

Now let's take it further. Excel follows your lead on upper, lower or proper case. If you type all caps for the first entry, Excel will fill all caps. If you type lowercase, Excel will fill lowercase.

Excel will also follow the date formatting applied to the first cell, or use abbreviations for month or weekday names if you use them in the first cell.

date formattingFigure 3

If you type any text, followed by a number, Excel will correctly extend the series, even if there's a dash or a space before the number. This works great for entries such as Room 101, Pad 39, Building 36 or Area 51. However, it does not work when the number is in the middle.

extended seriesFigure 4

Excel was built for accountants, so it easily handles any possible variation of quarters. After Q4, Excel will go back to Q1. However, to have Excel correctly fill quarters and years, you have to use "1Q" instead of "Q1," and place the quarter before the year.

extended quarters seriesFigure 5

Annoyingly, Excel will not automatically extend 1 to 1, 2, 3, etc. But there are several workarounds. The easiest is to hold down the Ctrl key while dragging the Fill Handle. Excel will then extend the series.

Alternatives:

  • Select the 1 and a blank cell to the right of the 1 and fill.
  • Type 1 and then 2. Select both cells and fill.
  • Drag the Fill Handle to get a series of 1's. Use the icon to the bottom right of the series to change from 1, 1, 1 to 1, 2, 3.
Fill Handle buttonFigure 6

For dates, Excel will fill every date in a sequence. To have Excel fill only Monday-through-Friday dates, hold down the right mouse button while you fill. The tooltip will show all dates as normal. But after you release the mouse button, choose Fill Weekdays from the flyout menu and Excel will skip Saturdays and Sundays. Other options in this menu include Fill Months and Fill Years.

Fill Months and YearsFigure 7

Bill Jelen, aka MrExcel, has been a Microsoft Excel consultant for over twenty-five years. Read more of his expert Excel tips and tricks at his website, MrExcel.com.

This was first published in March 2014

Dig deeper on ERP System

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchManufacturingERP

SearchSAP

SearchOracle

SearchBusinessAnalytics

SearchContentManagement

SearchDataManagement

SearchCRM

Close