Flash Fill Times
Flash Fill is a marvelous feature that is designed to fill values down based on an example in an adjacent column. That is, you have a column of values and then you enter an example of a derived value in an adjacent column. Flash Fill attempts to recognize the pattern from your example and then fill that pattern down. This is difficult to visualize with words, so, let’s walk through an example that converts a column of text strings into valid times.
I needed to import a bunch of events into my calendar, and the data source showed the event times in a format that wasn’t able to be imported. For example, the first few rows in the data source looked something like this:
In order to import the events into my calendar, I needed to split the Time column into Start and End columns, like this:
Excel provides many options to accomplish this, for example, I could have used formulas or Power Query. But, I just wanted something fast and easy to use. So, I used Flash Fill.
Let’s walk through the steps together.
We’ll walk through these steps:
- Get Time Start
- Get Time End
Let’s do it.
Get Time Start
The first step was to get the Time Start values from the Time column. At this point, the worksheet looked like this:
The idea with Flash Fill is that you enter an example of the desired values in the adjacent column …
… and then you click the Data > Flash Fill command (or Ctrl+E) to fill that pattern down.
When I did, I got some unexpected results:
When I inspected the Time column more closely, I noticed that the time was expressed using a text string of a dot and no space (1.00PM) instead of time format with a colon and a space (1:00 PM). As such, Flash Fill didn’t detect my intended pattern.
I was asking Flash Fill to extract the start time from the Time column, plus convert the text representation of the start time into a valid time data type. One solution is to break the sequence down into smaller incremental steps. That way, we only ask Flash Fill to concentrate on one step (transformation) at a time.
But, we can also assist Flash Fill by providing additional examples. So, I gave Flash Fill two examples instead of one:
This extra example is exactly what Flash Fill needed to understand my desired results. When I clicked the Flash Fill command this time … bam:
All that remained was to populate the Time End column.
Get Time End
Once again, I entered an example value:
And Flash Filled it down:
Again, it didn’t quite understand my desired pattern as it gave me 1:00 AM for the second row instead of 1:00 PM as expected. So, I entered an additional example:
And this time … bam, it worked:
As you can see, Flash Fill provided an easy way to split the Time column into Start and End columns, including converting them into time values. Brilliant!
Flash Fill is a wonderful feature of Excel that often provides a quick way to transform data columns without the need of something fancy like formulas or Power Query. If this were a recurring workbook, I’d probably take the time to automate this process with Power Query. However, for a one-time project, Flash Fill is a great option!
If you have any other fun Flash Fill tips, please share by posting a comment below … thanks!
Sample File: FlashFillTimes.xlsx
Excel is not what it used to be.
You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.
Want to learn Excel?
Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.