Auto Fill and Fill Series

This chapter covers the autofill and fill series features in Excel, typically used for creating numbered lists, lists of dates, and so on.

Practice Spreadsheet

Open a blank workbook for this chapter.

Basics of Autofill

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:

  1. Select a cell, and type 1.
  2. Move your cursor to the little square in the bottom-right corner of the selected cell.
    1. Your cursor will turn into a black cross.
  3. Select and drag this little square down over the empty cells below.
    1. When you release, Excel will autofill each of those cells, putting a 1 in each cell. (See Figure 2.1)
      BUS115_image_2.1.png
      Figure 2.1
       

Creating Numbered Lists

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:

  1. Select a cell, and type 1.
  2. Press Enter to select the next cell down and type 2.
  3. Select both of these cells. 
  4. Move your cursor to the little square in the bottom-right corner of the selected cells.
    1. Your cursor will turn into a black cross.
  5. Select and drag this little square down over the empty cells below.
    1. When you release, Excel will autofill each of those cells, following the numerical pattern it has recognized (1, 2, 3, 4, etc.). (See Figure 2.2)
      BUS115_image_2.2.png
      Figure 2.2
       

Now, select a new cell in an empty column. To make the list go by twos, use the following steps:

  1. Select a cell, and type 2.
  2. Press Enter to select the next cell down and type 4.
  3. Press Enter to select the next cell down and type 6.
  4. Select all three of these cells. 
  5. Move your cursor to the little square in the bottom-right corner of the selected cells.
    1. Your cursor will turn into a black cross.
  6. Select and drag this little square down over the empty cells below.
    1. When you release, Excel will autofill each of those cells,
       following the numerical pattern it has recognized (2, 4, 6, 8, 10, etc.). (See Figure 2.3)

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.

BUS115_image_2.3.png
Figure 2.3
 

Months and Dates

The autofill function also recognizes months and dates as patterns. 

To create a monthly list, use the following steps:

  1. Select a cell, and type January.
    1. Excel will automatically recognize this as a pattern.
  2. Move your cursor to the little square in the bottom-right corner of the selected cells.
    1. Your cursor will turn into a black cross.
  3. Click and drag this little square down over the empty cells below.
    1. When you release, Excel will autofill each of these cells, following the monthly pattern it has recognized (January, February, March, etc.).

To create a daily list, use the following steps:

  1. Select a cell, and type a date (for example, 4-Jul-00).
    1. Excel will automatically recognize this as a pattern.
  2. Move your cursor to the little square in the bottom-right corner of the selected cells.
    1. Your cursor will turn into a black cross.
  3. Click and drag this little square down over the empty cells below.
    1. When you release, Excel will autofill each of these cells, following the daily pattern it has recognized (4-Jul-00, 5-Jul-00, 6-Jul-00, etc.).

To create a weekly list, use the following steps:

  1. Select a cell, and type a date (for example, 4-Jul-00).
  2. Press Enter to select the next cell down and type the date a week from the first entry (for example, 11-Jul-00). 
  3. Select these two cells.
  4. Move your cursor to the little square in the bottom-right corner of the selected cells.
    1. Your cursor will turn into a black cross.
  5. Click and drag this little square down over the empty cells below.
    1. When you release, Excel will autofill each of these cells, following the weekly pattern it has recognized (4-Jul-00, 11-Jul-00, 18-Jul-00, 25-Jul-00, etc.). 

The autofill function will also recognize yearly patterns.

Fill 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:

  1. Select a cell, and type 1.
  2. Press Enter to select the next cell down and type 2.
  3. While the second cell is still selected, select the Home tab from the toolbar, then select the Fill Color icon, and select the desired color. (See Figure 2.4)
    BUS115_image_2.4.png
     Figure 2.4
     
  4. Select both of these cells. 
  5.  Move your cursor to the little square in the bottom-right corner of the selected cells.
    1. Your cursor will turn into a black cross.
  6. Select and drag this little square down over the empty cells below.
    1. When you release, Excel will autofill each of these cells, continuing the pattern of numbers and alternating colors every other cell. (See Figure 2.5)
BUS115_image_2.5.png
Figure 2.5
 

More Options

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)

BUS115_image_2.6.jpg
Figure 2.6
 

BUS115_image_2.7.png
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)

BUS115_image_2.8.png
Figure 2.8
 

Fill Series with Longer Lists

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:

  1. Select a cell, and type 100.
  2. Press Enter to select the next cell down and type 200.
  3. While the cell containing 100 is selected, select the Home tab, select Fill from the toolbar, and select Series. (See Figure 2.9)
    BUS115_image_2.9.jpg
    Figure 2.9
     
  4. Under Series In, select Columns
  5. Type 100 in Step Value. 
  6. Type 375000 in Stop Value. (See Figure 2.10)
    1. Do not include a comma in 375000.
  7. Select OK
    1. Excel will fill the cells in that column from 100 to 375,000.
BUS115_image_2.10.png
Figure 2.10
 

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.

 

Fill Series with Exponential Growth

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:

  1. Select an empty cell.
  2. Select the Home tab, select Fill from the toolbar, and select Series. (See Figure 2.9)
  3. Under Series In, select Columns
  4. Under Type, select Growth. (See Figure 2.11)
  5. Type 2 in Step Value.
  6. Type 100000 in Stop Value.
    1. Do not include a comma in 100000.
  7. Select OK.
    1. Excel will create a list from 2 to 100,000 that grows exponentially by multiplication. 
BUS115_image_2.11.jpg
Figure 2.11
 

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.

Supplemental Resource

This content is provided to you freely by BYU-I Books.

Access it online or download it at https://books.byui.edu/bus_115_business_app/auto_fill_and_fill_s.