AutoFill



AutoFill is a very useful Excel feature. It allows you to create entire columns or rows of data that are based on the values from other cells. In other words, Excel compares the selected data and tries to guess about the next values you want to add.

 

AutoFill months and days

Example 1:

Look at the following example. In cell B2 is the word January. Excel automatically recognizes it as the first month. Click on this cell to activate it. Move the cursor to the bottom right corner so that it will change to a small black cross.

Figure 1 - AutoFill months.

 

AutoFill works both vertically and horizontally. As long as you hold down the mouse button, Excel shows you in a small rectangle which month will be inserted into the last cell.

Figure 2 - Filling months.

 

Release the key to insert the values into the cells.

Figure 3 - Filled with months.

 

Example 2:

You can use AutoFill, starting from any list item, not necessarily the first. See how it looks in the following example, with days of the week.

Figure 4 - Filled with days.

 

Notice that when the list reaches the end, Excel starts to insert new elements, starting from the beginning.

 

AutoFill numbers

Auto filling numbers is slightly different than filling data from defined lists. If you put a number and use AutoFill feature, Excel will fill all selected cells with the same value.

Figure 5 - Filled with the same number.

 

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 hold down the Ctrl key.

Figure 6 - Filled with numbers.

 

You can also use AutoFill to insert lists of odd numbers, even numbers, tens, etc. In this case, you must select at least two cells with the values.

Figure 7 - Odd numbers, even numbers and tens.

 

CAUTION

Unfortunately Excel can handle only simple examples. If you have more complex example, the result probably won't be the one you wanted.

 

Example:

Let's try a different example. Suppose that you want the next number to be the sum of all the previous. You have entered the following values: 1, 1, 2, 4, 8, 16. If you select these values and use AutoFill to create a longer list, you will see that Excel has treated them in a completely different way than you expected.

 

AutoFill hours

AutoFill hours is similar to AutoFill numbers. Look at the following example.

Figure 8 - Filling with time.

 

This time you also have to select at least two values to create the rest.

 

Creating Custom Lists

You might be in a situation when you often use a list of items that is not defined in Excel. For example a list of your employees.

To create such list in Excel go to FILE >> Options >> Advanced >> General >> Edit Custom Lists.

Figure 9 - Custom Lists.

 

After you add your custom list, you can use it in the same way as the one defined by Microsoft. Just type one of the values from the list, drag the mouse cursor and Excel will complete the rest.

Figure 10 - Custom Lists example.


Kurs Excel dla początkujących.