Fill Cells With Data Automatically in Excel

If you have a large amount of repetitive data to type into your worksheet, save time by learning to fill cells with data automatically in Excel. AutoFill and Flash Fill are two powerful features in Excel that not only fill cells with data automatically but will sort, manipulate, and organize it however you want. This tutorial will walk you through using both features in detail.

Using AutoFill

Whether you need to enter a sequence of numbers, copy a formula to multiple cells, split or merge data, AutoFill can be extremely useful.

Copying a value into cells

To copy a value into other cells, do the following:

  1. Enter a value into a cell.
  2. Select the cell and hover the mouse pointer over the fill handle until it changes into a cross icon.
    Add a series of data automatically in Excel
  3. Press down the left mouse button and drag the fill handle through as many rows as you need.
    Add series of data automatically in Excel
  4. Release the left mouse button. Excel automatically fills the cells with the value.
    Add series of data automatically in Excel
  5. To repeat a value into columns instead of rows, enter the value into a cell and drag the fill handle across the worksheet through as many columns as you need.
    Enter data automatically in Excel

Creating a sequence of numbers

To create a sequence of numbers, do the following:

  1. Enter the first two values of the sequence into the starting cells.
  2. Select both cells. (Select both cells by holding down the Shift key and clicking in both cells.)
    Using Auto Fill in Excel
  3. Hover the mouse pointer over the fill handle of the last cell until it changes into a cross icon.
    Using Auto Fill in Excel
  4. Hold down the left mouse button and drag through as many cells as you need.
    Using Auto Fill in Excel
  5. Release the left mouse button and Excel automatically completes the sequence.
    Using Auto Fill in Excel

You can use multiples of a number. For example, you can create a sequence of even numbers such as 2, 4, 6, 8, 10, or odd numbers as with 3, 6, 9, 12, 15.

To create a sequence of numbers in multiples of 5, do the following:

  1. Enter the first two values of the sequence into the starting cells and select both cells.
  2. Place the mouse pointer over the fill handle of the last cell until you see a cross icon.
    Add series of data automatically in Excel
  3. Hold down the left mouse button and drag the fill handle through as many cells as you need to use.
    Using Auto Fill in Excel
  4. Release the left mouse button, and Excel fills cells with the remaining values of the sequence.
    Add series of data automatically in Excel

Entering a range of dates

Sometimes you need to enter a large amount of repetitive data into your worksheet, such as a range of dates. This can be very tedious, time-consuming work. Automate this process with Auto Fill.

  1. Type the starting date into a cell and select it.
    Using Auto Fill in Excel
  2. Hover the mouse pointer over the fill handle of the cell until it changes into a cross icon.
    Using auto fill in Excel
  3. Hold down the left mouse button and drag the fill handle through the cells you want to fill.
    Using Auto Fill in Excel
  4. Release the left mouse button. Excel finishes filling in the range of dates automatically.
    Using auto fill in Excel

Entering the days of the week

Let’s enter the days of the week using AutoFill and use columns instead of rows.

  1. Type the first value into a starting cell.
  2. Select the cell and hover the mouse pointer over the fill handle.
    Use Auto Fill in Excel
  3. When the pointer changes into a cross icon, hold down the left mouse button and drag the fill handle horizontally through as many cells as you need to complete the sequence.
    Add series of data automatically in Excel
  4. Release the left mouse button, and Excel automatically finishes the sequence.

Using Flash Fill

Flash Fill is a powerful feature in Excel that can automatically fill cells with data based on a pattern that you establish.

Merging two columns of data into one

Let’s say you have a worksheet that contains a list of first names in column A and a list of last names in column B, and you want to merge the two columns into one column of full names.

  1. Type the first full name in the cell adjacent to the first two columns (C2 in the example) and press Enter.
    Fill cells with data automatically in Excel
  2. Start typing the next full name in the cell below the first one. As you type, Excel displays a preview of the rest of the column based on the pattern you’ve established.
    Using Flash Fill in Excel
  3. To accept the preview, press Enter. Excel merges the contents of the first and second columns into one column.
    Using Flash Fill in Excel

Manipulating data

Sometimes you have a lot of data that is not in the format you want. In the example below, we have a list of names of the format: last name, middle name, first name. We want to create a new list of names in a separate column of the format: first name, last name, leaving out the middle name, and capitalizing the first letter of each person’s first and last name.

It would be time consuming and tedious to go through all the names in the list and manually type each name in the new format, especially if the list were extremely large. Fortunately, Flash Fill can do all of this for us instantly.

Fill cells with data automatically in Excel

  1. In an adjacent cell, type the first name in the new format and press Enter.
    Fill cells with data automatically in Excel
  2. Start typing the next name and Excel instantly displays a preview of the rest of the column.
    Fill cells with data automatically in Excel
  3. To accept the preview, press Enter. Excel fills the remaining cells.
    Fill cells with data automatically in Excel

Both AutoFill and Flash Fill are powerful tools in Excel that save time and effort. Learning to use these features will increase your productivity, particularly when working with large amounts of data.

Related Topics