Skip to main content

Entering data with Auto Fill and Flash Fill

To manually enter data

  1. Select an empty cell, such as A1, and then type text or a number.
  2. Press Enter or Tab to move to the next cell.

Use the fill handle to copy cells

Sometimes you don't need the numbers to change as you fill. Instead, you just want to copy values to other adjacent cells. Here's how to do that:

  • Click the cell with the word Produce. Rest your cursor on the lower-right corner of the cell until it becomes a cross, then drag down three cells.
  • Now select the cell with the word Fruit. Rest your cursor on the lower-right corner again, and when you get the cross, double-click. That's another way to fill down in case you ever need to fill a long column.

To fill data in a series

  1. Enter the beginning of the series in two cells: such as Jan and Feb; or 2018 and 2019.
  2. Select the two cells containing the series, and then drag the fill handle across or down the cells.

Excel can automatically fill some cells based on a series. For example, you can type Jan in one cell, and then fill the other cells with Feb, Mar, etc.

  1. Click the cell with the word Jan (B1).
  2. Rest your cursor on the lower-right corner of the cell until it becomes a cross, then drag right two cells. Excel detects a series, and fills in Feb and Mar for you.
  3. Now select the cell with Week 1 (A2).
  4. Rest your cursor on the lower-right corner again, and when you get the cross, double-click it.

Automatically fill a column with Flash Fill

Flash Fill automatically fills your data when it senses a pattern. For example, you can use Flash Fill to separate first and last names from a single column, or combine first and last names from two different columns. To turn Flash Fill on, go to File > Options > Advanced > Editing Options > check the Automatically Flash Fill box.

Combine a column of data with Flash Fill

Let's say column A contains first names, column B has last names, and you want to fill column C with first and last names combined. If you establish a pattern by typing the full name in column C, Excel's Flash Fill feature will fill in the rest for you based on the pattern you provide.

  1. Enter the full name in cell C2, and press ENTER.
  2. Start typing the next full name in cell C3. Excel will sense the pattern you provide, and show you a preview of the rest of the column filled in with your combined text.
  3. To accept the preview, press ENTER.
  4. If Flash Fill doesn't generate the preview, it might not be turned on. You can go to Home tab > Editing group > Fill > Flash Fill to run it manually, or press Ctrl+E.

Split a column of data with Flash Fill

  1. In the cells under First name, type the first names that are in the Email column: Nancy, Andy, and so on.
  2. When you see the faded list of suggestions, press Enter right away.
  3. This list of suggestions is called Flash Fill. Flash Fill detects when you type a consistent pattern, and provides suggestions to fill the cells with. When you see the faded list, that's your cue to press Enter.
  4. Try another way to Flash Fill: Click the cell with Smith.
  5. Click Home > Fill > Flash Fill. Or use shortcut: CTRL+E. Now the last names are in their own column.

Leave a comment

Your email address will not be published. Required fields are marked *

Format your code: <pre><code class="language-vba">place your code here</code></pre>