Tell AutoFill What To Do And It Will Oblige

Mon 24th January 2011

AutoFill is a useful function in Excel that many of us are familiar with. It saves time by automatically inserting predictive data, such as numbers, months or days. But there is more to AutoFill than entering the straightforward, as this article shows.

You are probably familiar with the AutoFill function in Excel. If you type in a number or other predictive data, such as days of the week or months of the year, and pull down the handle at the bottom right of the cell, then AutoFill will automatically fill the cells with what you require. This is at its most basic but it can perform many more intricate tasks than entering the days of the week. But before we look at those here are a few points about the basics of AutoFill.

If you were to type the number 1 into a cell and drag down the AutoFill handle, the resulting cells would all contain the same number because Excel has not been told that there is an increment in the following cells, so it merely copies the original one. If you are entering an increment if one, then after entering your number 1 into a cell, hold down the Shift key and pull down the AutoFill handle. This time you will get numbers ascending in increments of one.

This will only work with increments of one, which is obviously the most commonly used form of numbering. If you were to insert a number 2 and hold down Ctrl while you pull the handle down, the increment would still be one. In order to go up by two (or by any other number) you would have to tell Excel the increment. To do this, type in the number 2 and then 4 in the cell below. Highlight both of these (they will be bordered by a heavy line) and drag the handle down and your increment of two will appear.

For days of the week or months of the year you need only make a single entry and AutoFill will recognise this and fill in the following cells accordingly. You can choose to enter your days or months fully (Monday, January) or as abbreviations (Mon, Jan) and AutoFill will follow your instructions.

Incidentally, I refer to dragging the handle down but you can achieve the same effect by dragging the handle across to AutoFill rows rather than columns. Indeed you can even select a day or month and drag the handle upwards and the preceding days or months will appear.

So these are some basic AutoFill functions, but what else can it do?

It can do quite a lot actually, if you take things a step further. For example, if you want to leave a blank row or column between each entry, then, just as with the number increments, you only have to tell Excel what you want it to do. When dealing with different increments such as this, however, you have to pay attention to detail.

I say this because if you were to type Jan, then a blank cell, then Feb and select these three cells, when you pulled the handle down the months would not appear evenly spaced. The reason for this is that you did not tell Excel that you wanted a space after Feb as well. So to correct this you would type Jan, empty cell, Feb, empty cell and select all four, then drag down a perfectly spaced list of months.

Another useful way to use AutoFill is when you want to list specific dates of the year, say when a regular payment has to be made, or to keep track of appointments that are held on the same day every week.

The procedure is exactly the same as before in that you tell Excel what you want and it will oblige. Say you have a regular bill to pay each fortnight, starting in February. Enter the date of your first payment, say Tuesday February 1st as 01 Feb 2011, making sure that your dates are not entered in the US format, and then in the next cell type in 08 Feb 2011. Select these two cells and drag the AutoFill handle to display each date in sequence for as long as you like.

AutoFill is one of Excel's great features. As I have shown above, once you have grasped how it operates then you can try telling it to do other things to see if they work. There is a world of exploration and experiment waiting at the drag of a handle, so why not find out more about this wonderful function?

Author is a freelance copywriter. For more information on excel training london, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-1420-tell-autofill-what-do-and-it-will-oblige.html