Excel Tip - Dates and AutoFill, a Powerful Trio!

So, you want to create a row or column of sequential dates, but you don’t want to include weekends.  Do you painstakingly go through the long list deleting the Saturday and Sunday dates?  No, let AutoFill do it for you…

The AutoFill tool has long been a favourite of Excel users, saving time when completing spreadsheets, either by copying data or formulas.  It’s particularly helpful for creating a list of sequential dates, to compile a calendar or schedule.  But, did you know that it can help even further by, for example, missing out weekends, or by just increasing the month or year?  It’s that strange little icon that appears after we’ve clicked and dragged, and is often overlooked.  Don’t ignore it, the AutoFill Options icon could save you a great deal of time.

Using the AutoFill Options

  • In a cell, type a date in a format recognised by Excel and use the fill handle to drag to the required column or row
  • When you release the mouse, you’ll see the AutoFill Options icon to the right and just underneath the last cell

  • Click the icon to view the options

  • Choose Fill Weekdays to omit Saturdays and Sundays

  • Choose Fill Months to increment the month only

  • Choose Fill Years to increment the year only

What a time saver!

Pin it