How to Use Excel to Create a Pattern

By David Weedmark

Create patterns in Excel 2013 from as little as two numbers. To change data to a different form, Excel can detect a pattern you specify to do that too.

Things You'll Need

  • Computer
  • Excel software

Typing an entire series of numbers or patterns of data can be tedious work when you need to fill up a spreadsheet. Using Auto Fill, you can have Excel 2013 create those patterns automatically. To create a pattern based on data that you already have, such as creating a list of email addresses for a new domain based on employee names, Excel's Flash Fill can create that pattern for you.

Using Auto Fill

Step 1

Enter any two numbers in the first two cells of a column, like 1 and 2, 5 and 10, or 3.1 and 3.2. Excel will create a pattern from these two numbers.

Step 2

Drag the small square to create a pattern.

Highlight the two cells by dragging the cursor over them. In the bottom-right corner is a small square just a few pixels in size, called a Fill Handle. Drag the Fill Handle down to cover the cells you want populated with the pattern. Note that as you pass each cell, a small icon appears showing you the value that will be placed in that cell.

Step 3

Excel created a pattern based on 5-unit increments.

Release the mouse button. Excel fills the cells with a pattern based on the two numbers you started with.

Step 4

Excel extends a series of numbers, but only repeats letters.

To repeat text instead, just enter text in the cells at the top of the column. For example, typing apples and peaches in the first two cells will repeat the pattern of these two words -- Excel won't give you more fruits. If you combine letters and numbers, like a1 and b1, Excel repeats the letters but extends the numbers in a pattern, such as a1, b1, a2, b2, a3, b3.

Using Flash Fill

Step 1

Enter an example of the change you want to make to the names layout.

Enter or copy a list into a column A in an Excel worksheet, such as a list of names. In cell B1, type a variation of the name in cell A1. For example, putting the last name first, separated with a comma. Press Enter.


Make sure the values in each cell are organized in the same way. If you're using names, for example, Flash Fill won't work well if some cells have the first name first, and others have the last name first.

Step 2

Click the Flash Fill icon.

Click the Home tab. In the Editing section of the ribbon, click the Fill icon with a down arrow, and then select Flash Fill. Alternatively, press Ctrl-E to use the Flash Fill keyboard shortcut. Excel automatically creates a pattern from your entry in cell B1 to fill the rest of the B column with the same variation for each of the names.

Step 3

Two names are missing initials based on this pattern.

Review the pattern Excel created to ensure there are no errors. For example, if some of the names include initials, they won't appear in the new list based on a last name-first name pattern.

Step 4

Give two examples of Flash Fill doesn't recognize the pattern you want.

Press Ctrl-Z if Flash Fill doesn't create the right pattern and then type one or two more examples in cells B2 and B3. In the example in this screenshot, creating a list of email addresses based on the names using a first initial and last name on the same domain wasn't a pattern Flash Fill recognized with only one example. It gave every person the same first initial, J. However, with two examples, it was able to create the desired pattern.