AutoFill is a very useful Excel feature. It allows you to create entire columns or rows of data which are based on the values from other cells. In other words, Excel compares the selected data and tries to guess the next values that will be inserted.
AutoFill months and days
Look at the following example. In cell B2, there is the word- “January”. Excel automatically recognizes it as the first month. Click this cell to activate it. Move the cursor to the bottom right corner so that it will change to a small black cross.
Drag your cursor to cell F2. As long as you hold down the mouse button, Excel shows you which month will be inserted into the last cell in a small rectangle.
AutoFill works both vertically and horizontally
Release the key to insert the values into the cells.
You can use AutoFill, starting from any list item- not necessarily the first. See how it works in the following example, with days of the week.
Notice that when the list reaches the end, Excel starts to insert new elements, starting from the beginning of the list.
Auto filling numbers is slightly different than filling data that is saved in the defined lists. If you put a number and use the AutoFill feature, Excel will fill all selected cells with the same value.
If you want each next number to be incremented by one- compared to the previous one, you can perform the same operation as before, but this time holding down the Ctrl key.
You can also use AutoFill to insert lists of odd and even numbers, tens, etc. In this case, you must select at least two cells with the values.
Unfortunately, Excel can handle only simple examples. If you have more complex ones, the result probably won’t be the one you expected. So be careful when you use this feature.
Look at the following example. Suppose that you want each next number to be the sum of all the previous. You entered the following values: 1, 1, 2, 4, 8, 16. If you select these values and use the AutoFill feature to fill the other cells, you will see that Excel has treated them in a completely different way.
Using AutoFill on hours works in the same way as using it on numbers. Look at the following example.
This time, you also have to select at least two values to fill the rest of the cells.
Creating Custom Lists
You might often use a list of items that are not defined in Excel. For example the list of your employees.
To create such a list in Excel, go to FILE >> Options >> Advanced >> General >> Edit Custom Lists.
After you add your custom list, you can use it in the same way as the ones defined by Microsoft. Just type one of the values from the list, drag the mouse cursor and Excel will complete the rest.
Skillshare’s usually $10 a month, but because you’re an OfficeTuts reader you can get your first month free by clicking here. That’s unlimited access to over 18,000 classes on Excel, VBA, and much more.