Software

Tip: Learn how to customize the Fill Weekdays option in Excel

I heard from a reader the other day who was trying to fill a range with consecutive dates for just weekdays (Monday through Friday). She entered six values, Monday through Friday and then repeated Monday, thinking Excel would recognize the series. Instead, the fill handle repeated Monday:

Monday Tuesday Wednesday Thursday Friday Monday Monday Tuesday Wednesday Thursday Friday Monday Monday

Excel sees the second Monday as just another member of the list. It doesn’t recognize the series on its own. Fortunately, you can tell it to enter just weekdays using the fill handle and you need enter only one day, not five—that gives you a bit of flexibility in just where you want to start the list. You don't have to start the list with Monday.

Now, to see how Excel creates a list of weekdays, enter Monday (or any weekday) into cell A1. Then, use the fill handle to fill several cells (at least 10 or 12 so you can see the pattern). By default, Excel will fill the range with all seven days of the week, but you can change that. Find the Auto Fill Options icon and click its dropdown arrow to view the fill options. To fill the selected range with just weekdays, click the Fill Weekdays option. That’s all there is to it. The key to this feature is simply knowing its there. Familiarize yourself with the different options so you can take advantage of them in the future.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks