Skip to Main Content

Indiana University Indiana University


Tips for Populating Cells in Excel Using Auto Fill

Recently an Excel question came to me regarding using the fill handle to populate dates. The fill handle is a tiny dark square located in the lower right corner of a selected or active cell; when you place the cursor over this small square, it will take the shape of a small plus sign:

Not only can you copy formulas by dragging this fill handle, but you can quickly populate cells with a series of numbers or dates. The specific question was: How can you skip weekend dates and populate a contiguous range of cells with weekdays only? No problem! When you stop dragging the fill handle across a range of cells, an Auto Fill Options tag will appear. When you click on this tag, you will see:

 

 

 

 

 

 

 

 

All you have to do is choose Fill Weekdays.  You can choose to  fill cells with days, weekdays, months, or years, or fill the cells with formatting only. Remember that the date format that you begin with will be what Excel will use for the entire range. Notice also that you can choose to copy cells rather than using the Fill Series option.

Something else that you can do with this fill handle is to populate a range of cells with a particular numbering pattern. For example, what if you only wanted the contents of cells to be listed in this order: 1, 3, 5, 7, etc. Or what if you wanted to use a pattern of increased dollar amounts, such as $5, $10, $15, etc.?  All you have to do is to select the first few cells (two or more) that will tell Excel what the given pattern is, and then drag the fill handle across the desired range. Also, you can drag this fill handle any direction. If you drag up or to the left, the series will decrease rather than increase.


3 Responses to "Tips for Populating Cells in Excel Using Auto Fill" to “Tips for Populating Cells in Excel Using Auto Fill”

  1. Leslie Says:

    Can auto fill work for dates that are every two weeks? For example 09/02/11, 09/16/11, 09/30/11, 10/14/11, 10/28/11, etc.

  2. Susan Says:

    Leslie,
    Yes – Type 9/2/11 in one cell and 9/16/11 in another cell. Select both cells and drag down (or across) as far as you want. Excel knows the pattern of dates you want based on your selection, and will automatically fill in the dates for every two weeks.

  3. Ganesh Maharjan Says:

    This is really nice feature of Microsoft Excel. by the way can we use this kind of auto fill with other date format than English date, I have tried but I can’t fill if someone knows plz let me know, I want to use Nepali date in nepali date there is more than 28 days in second month of the year.
    Thankx

Leave a Reply

Get Training Tips via RSS Subscribe to Entries feed or Read Comments via RSS Subscribe to Comments feed.