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 that should be inserted.

 

AutoFill months and days

Example 1:

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.

Figure 1 - AutoFill months.

 

Drag your cursor to cell F2. 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.

 

TIP

AutoFill works both vertically and horizontally

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 works 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 of the list.

 

AutoFill numbers

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.

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 and 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 ones, the result probably won't be the one you expected. So be careful when you use this feature.

 

Example:

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 other cells, you will see that Excel has treated them in a completely different way.

 

AutoFill hours

Using AutoFill on hours works in the same way as using it on numbers. Look at the following example.

Figure 8 - Filling with time.

 

This time you also have to select at least two values to fill the rest of the cells.

 

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 the list of your employees.

To create such a 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 ones 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.