The Autofill feature in Excel is a powerful tool that helps users automatically fill cells with data that follows a pattern or is based on existing data. When working specifically with dates, Autofill can save time by quickly generating series of dates in various formats and intervals.
🔧 How Autofill for Dates Works
When you enter a date in a cell and drag the fill handle (a small square at the bottom-right corner of the selected cell), Excel detects the pattern and fills the cells accordingly.
📅 Common Examples of Autofill with Dates
1. Daily Increment
- Start Date: 01-Jan-2025
- Drag Down → Excel fills:
- 02-Jan-2025
- 03-Jan-2025
- 04-Jan-2025
- …
2. Weekday Increment (Excludes Weekends)
- Type two dates manually: 03-Jan-2025 (Friday), 06-Jan-2025 (Monday)
- Select both, then drag down.
- Excel fills:
- 07-Jan-2025 (Tuesday)
- 08-Jan-2025 (Wednesday)
- (skipping weekends)
3. Weekly Increment
- Type two dates a week apart: 01-Jan-2025, 08-Jan-2025
- Select both, drag down:
- 15-Jan-2025
- 22-Jan-2025
- 29-Jan-2025
- …
4. Monthly Increment
- Type two dates a month apart: 01-Jan-2025, 01-Feb-2025
- Select both, drag down:
- 01-Mar-2025
- 01-Apr-2025
- …
5. Yearly Increment
- Type two dates a year apart: 01-Jan-2025, 01-Jan-2026
- Select both, drag:
- 01-Jan-2027
- 01-Jan-2028
- …
6. Custom Interval (e.g., Every 2 Days)
- Type two dates: 01-Jan-2025, 03-Jan-2025
- Select both, drag:
- 05-Jan-2025
- 07-Jan-2025
- …
7. Using Fill Series (Advanced Control)
- Go to Home > Fill > Series
- Choose options:
- Series in: Columns or Rows
- Type: Date
- Date unit: Day, Weekday, Month, Year
- Step Value: (e.g., 2 for every 2 days)
- Stop Value: (optional)
8. Autofill Day Names
- Type: Monday
- Drag:
- Tuesday
- Wednesday
- …
- Wraps around after Sunday
9. Autofill Month Names
- Type: January
- Drag:
- February
- March
- …
- December → loops back to January
10. Custom Date Formats
- If you format a date as
"ddd, dd-mmm-yyyy"and autofill, Excel still understands it’s a date and continues the correct series, maintaining the format:- Wed, 01-Jan-2025
- Thu, 02-Jan-2025
- Fri, 03-Jan-2025
- …
⚠️ Notes and Tips
- You must type a valid Excel date (not just text).
- To copy the same date without incrementing, hold Ctrl while dragging.
- Autofill works horizontally and vertically.
- Autofill can also be customized using the “Custom Lists” feature for non-standard sequences.
Watch Video for Autofill Date
