Excel tip: How to skip weekends when autofilling dates

Printer-friendly versionPrinter-friendly versionSend by emailSend by emailPDF versionPDF version

Microsoft Excel has a powerful AutoFill feature that automates enumeration process. For instance, instead of typing down a column item numbers 1, 2, 3, and so on, autofill handles that automatically. It works well with known patterns like years, days of the week, and other incremented values.

How to skip weekends when autofilling dates in Excel

Suppose you have a worksheet with two columns "Date" and "Sales" for the month of November. You will then enter 11/01/2013 and then drag the fill handle down to autofill the cells below the Date column. But what if you only want to include weekdays in the date column?

There is a way to skip weekends when doing an autofill. Here are the steps:

Step 1: Locate the cell containing the date. Hover the mouse to the fill handle and you will notice that the mouse pointer will turn + sign.

Illustration of excel's fill handle

Step 2: Click on the fill handle with the left mouse button and drag it down to your desired number of rows and then release the mouse button. Dates will then automatically fill the cells.

Step 3: However, the dates include weekends. Locate and click on the AutoFill menu that appears below the last filled cell.

Illustration of excel's autofill menu

Step 4: In the context menu that appears, click on "Fill Weekdays".

"Fill Weekdays" shown in the Autofill menu

Step 5: Your list of dates will then be converted to weekdays only, skipping all weekends.




MS Office Software: