Flash Fill in Office 2013 is a smart version of Excel’s Auto Fill

Working with Excel in a Flash

Excel recently introduced a few new tools, including Flash Fill, with the rollout of Office 2013. Flash Fill looks for patterns in an adjacent column of cells and allows you to populate another column with information based on the recognized pattern.

Sounds a lot like Auto Fill so far, right? While it’s true that the Auto Fill feature is able to copy a pattern, it is limited to repeating a simple pattern over and over. Flash Fill is a smarter tool: It has the capability to foresee which adjustments within a pattern the user is attempting to make within a spreadsheet.

Let’s take a look at a few examples of how the Flash Fill feature can be useful.

Example 1:

In the example pictured below, I have a column of names, with first and last names combined into one cell. I want to create two separate columns to the right, separating the first and last names. I’ve already labeled the new columns B and C as “First” and “Last.”

flash fill
Flash Fill requires the user to define the pattern initially. In our example, type the correct data into the first cell of each column (in cell B2 type first name “Terri” and in C2 type the last name “Snider,” based on the value in A2, “Terri Snider”). Once you have established the pattern, Flash Fill will do the rest—no need for formulas or splitting the data manually to make this occur.

  • Start in the next blank cell of the column where the pattern is needed (specifically B3 in our example).
  • Click the Data
  • Choose Flash Fill from the menu’s Data Tool group. Excel looks for the attempted pattern and will add all first names into the adjacent column.
  • To finish this example, select blank cell C3 and select Flash Fill once again. This will populate column C with last names pulled from column A.

flash fill

Example 2:

In column C, I want to display email addresses for each person listed in column A. If the email addresses follow a structure, I won’t have to resort to keying in each email address manually—Flash Fill can do the work.

flash fill

(In this example, notice that all users have an email address on the same domain. Flash Fill would not recognize a pattern if the email addresses had varying domains.)

  • Type in the first couple of email addresses to allow Excel to determine the pattern. (Note that entering only the first email address before using the Flash Fill tool would work—but the program would misperceive the pattern and would place the letter “t” in the beginning of all email addresses, thinking that the pattern is “the letter ‘t’ plus the last name, ‘at’ the domain xyz.com.” Keying in the second email address allows Flash Fill to recognize that the pattern is “first initial, then last name, followed by the domain.”)
  • With your cursor in the B4 cell, select Flash Fill to complete the remainder of the column. All cells in column B should now populate with the correct email addresses.

Example 3:

Flash Fill can merge data from two cells into one cell. You might know how to use a formula to make this possible, but with Flash Fill, no formulas here!

flash fill

  • Given a spreadsheet with “First” and “Last” names in columns A and B, type in the first full name in cell C2.
  • Select blank cell C3 below, use the Flash Fill tool, and watch the magic happen.

Jump in a New Document in Office 2013

Each application within Office 2013 has a start screen that is enabled by default, intending to encourage end users to make use of certain features. The start screen has some value, but many users find that it can become a nuisance upon launch of an application.

Here are example start screens in PowerPoint, Word, and Excel:

flash fill startscreens

One of those features is the list of recently-opened documents. It is handy to have the list displayed, but in May’s Tips & Tricks article I discussed the options to pin items into your taskbar to make better use of this feature.

Another part of the start menu is a graphic display of templates. These are documents designed with end users in mind: Why recreate common documents when Microsoft has done the job for us? But as wonderful as they are, these templates are always accessible on the File tab in every application.

When opening an application, most users want to jump into a new document without extra clicks needed to make that occur. Effectively, they want to disable the start menu of the Microsoft Office application they’re using.

To Disable the Start Menu of your Office Application

  • Open the application.
  • Select the File tab and choose
  • From the General category, uncheck Show the Start screen when this application starts.
  • Test and adjust within each application.

flash fill
Customization options reside individually within Microsoft Office applications. After making adjustments within an application, you’ll jump right into a new document when you open that application in the future.

Related Posts