Autofill to Quickly Enter Lists

Jul 5, 2019 | Excel Hints and Tips

Did you know that standard lists can be created at speed in Excel without a lot of manual inputting?  If you are working with standard lists of information then autofill lists are something that could save you significant amounts of time.  Autofilling is performed by going to the bottom right corner of a cell until the cursor changes to a black cross and clicking and dragging.  This can be done either horizontally or vertically.  When autofilling is mentioned below assume it is with the left mouse button unless stated otherwise.

The smart tag gives extra options when autofilling takes place.  It appears as a floating box at the bottom right corner of the area autofilled.  The choices will vary depending on what is being filled and some of the most useful options are detailed below.

Autofill Text

The case is followed when autofilling is done so if the first word is uppercase then the rest will be uppercase too.

General text – repeats when you autofill

Days of the week – in either the full or abbreviated version they rotate round Mon, Tue, Wed etc.  The smart tag has the options of copy cells to repeat the same day of the week or fill weekdays only misses out weekends

Image to show text autofill options

Months of the year – in either full or abbreviated version they rotate round Jan, Feb, Mar etc

A word followed by a number – the word repeats and the number goes up

QTR or Quarter followed by a number – this is special and goes up to 4 then back to 1

Autofill Numbers

Single number – repeats when you autofill.  Clicking on the smart tag gives the option of fill series which makes the numbers increase by 1 each time

Image to show number autofill options

Enter 2 numbers, select both and autofill – Excel looks at the difference in the numbers and continues it. e.g. 2, 4 continues with 6 and 8

Autofill Dates

Single date – increases the date by one day at a time.  If the smart tag is clicked then options of copy cells, fill weekdays, fill months and fill years appear.  Formatting of the date may need to be changed to show the year particularly for the fill year option as all the dates may look the same.

Image to show smart tag for autofilling dates

Enter 2 dates, select both and autofill – Excel looks at the difference in the dates and continues it. This is great for week commencing dates.  Enter the Monday of the first week then the Monday of the second week and you can get the list continuing.

Autofill Calculations

Cell references in calculations move relative to the direction you are autofilling unless they have been fixed using absolute referencing.

If you found this useful see what Excel courses are available or email lara@laramellortraining.co.uk to discuss any training requirements.