This chapter covers the autofill and fill series features in Excel, typically used for creating numbered lists, lists of dates, and so on.
Open a blank workbook for this chapter.
Excel can recognize and repeat patterns by automatically filling cells. Although one could type in each number by hand, the longer the list of numbers or data is, the longer it will take to input the information by hand. To use the autofill, use the following steps:
As long as the first few numbers in the desired pattern are put into a column, the autofill function will be able to recognize and continue the pattern for as long as you need it. Open a blank Excel workbook to practice making the following lists. To make a basic numbered list, use the following steps:
Now, select a new cell in an empty column. To make the list go by twos, use the following steps:
This function will also work with odd numbers (1, 3, 5, etc.), every five numbers (5, 10, 15, etc.), every ten numbers (10, 20, 30, etc.), and so on. It will repeat the pattern again and again, as long as it can recognize the pattern.
The autofill function also recognizes months and dates as patterns.
To create a monthly list, use the following steps:
To create a daily list, use the following steps:
To create a weekly list, use the following steps:
The autofill function will also recognize yearly patterns.
Excel’s autofill function will also recognize patterns made by the fill color within each cell. This allows the user to make the cells alternate colors as the list continues. To do this, use the following steps:
After you have done one of these select-and-drag autofills, a little option button will appear in the bottom-right corner of the selected pattern. This gives you the following options:
Fill Series is the default option, which is what happens when you click and drag the little square down through the cells to continue the pattern. Copy Formatting Only will copy the formatting of the selected cells, but not the formula or data in the cells. Fill Without Formatting will repeat the pattern of numbers, but not the formatting. For example, in Figure 2.6, this option would repeat the pattern of numbers, but not the fill color pattern. Flash Fill is what happens when you double-click the little square instead of dragging it down, which will fill the cells to the bottom of an adjacent list. (See Figure 2.7)
This only works if there is data to the left of the selected list so that Excel knows where to end the data.
Here’s a tip: If your data is in the column to the right of the adjacent cells you want to use the length of, you can select both the data you want to continue the pattern for and the empty cells that are adjacent to data of the desired length, then double-click to autofill down to your desired length. (See Figure 2.8)
Some lists may seem too long to put into Excel, but there’s a trick that can take care of this quickly. Let’s say we wanted to do a number list that goes up to 375,000. If we go by 100s (100, 200, 300, etc.) and start to drag down, getting to 375,000 is going to take a long time. Instead of clicking and dragging, we can use the Fill Series feature in Excel. To use the Fill Series feature for this list, use the following steps:
The Fill Series function is typically only used in place of the click and drag when you need to fill in large amounts of data. Reasonably short sets of data (a couple hundred cells or so) can typically be clicked and dragged down in a relatively short time.
The Fill Series function can also be used to create lists that grow exponentially.
To make a list that starts at 2 and grows to 100,000 exponentially use the following steps:
The list will multiply by two each cell down. You may notice in Figure 2.11 the data does not end with 100,000. Rather, it ends on the number immediately before 100,000. This is because if 65,536 were to be multiplied by two, it would result in a number greater than 100,000. If the data cannot get to the specific stop value, it will stop at the highest number it can get to below the stop value. The same selecting-and-dragging feature works on formulas as well. This will be covered in future chapters.