Exploring Excel 2013 for BI Tip #3: Flash Fill

DataOnWheels, 2013-03-15

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

Flash Fill

Flash Fill is new functionality in Excel 2013 that allows you to manipulate data and fill a column based on actions you took.  I like to think of it as somewhere between programming a macro and using the black cross to pull down formulas.  I am going to illustrate a couple of ways to use this then you will have to take it from there.

First, you will need a set of data that this makes sense with.  So let’s start with something obvious.  You have a column of data that has a customer’s name formatted as Firstname Lastname.  But you really need to format them as Last name, First name. 

Here is the dataset we will use (it is the Indiana Hoosier players from the 80-81 season who were drafted).

Year Round Pick Player NBA_Club Key
1981 1 2 Isaiah Thomas Detroit Pistons 1
1981 1 18 Ray Tolbert New Jersey Nets 2
1981 5 115 Glen Grunwald Boston Celtics 3
1981 8 180 Steve Risley Phoenix Suns 4
1982 10 225 Landon Turner Boston Celtics 5
1983 1 22 Randy Wittman Washington Bullets 6
1983 2 40 Jim Thomas Indiana Pacers 7
1983 2 41 Ted Kitchel Milwaukee Bucks 8
1983 4 78 Steve Bouchie Detroit Pistons 9
1983 7 141 Tony Brown Indiana Pacers 10

So in our first illustration, we will take the Player column and separate and reorder the name – Last name, First, name.

The Flash Fill function is located on the DATA tab as shown below.


Follow the next steps carefully to reproduce what I am doing.

  1. Highlight the Isaiah Thomas last name and copy it to an empty cell.
  2. Add a comma.
  3. Copy his first name over.  This should have a column where you see, Thomas, Isaiah.
  4. Highlight the cell with the new value and click Flash Fill.

You are done.  You now have a column that is formatted the way you wanted it to be.  Cool, huh?

imageThe next set of data I am going to work with is the Hoosiers 2011-2012 season.  The data includes a Record column which has the conference record part of the way through the set.  What I want is to have separate columns for the overall and conference records.

This time I am going to select the cell on row 15 which has both records in it.  The first column I will create will have the overall record.  This is done by copying only the conference record over and flashfilling the column.  Works great.  The first row will have no data because the dash does not fit the pattern.  The next column, I only copy the section in the parenthesis.  However, this did not work.  For the rows without a set of data in parenthesis, it copied that anyway.  Let’s clear the column and try one other way.

After many attempts that did not imagework, then next operation needed more patterns to follow.  For rows 13 and 14, neither of which has a conference record, I placed an empty set of parenthesis (), Then for the conference record, I copied the conference record in parenthesis over.  My results look like the table to the right.  As you can see, it is pattern matching and sometimes it does not get you the results you would expect.  However, it is a great tool and will get you most if not all of the way to the result you need with very little effort.  Enjoy.





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis


1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren


1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren


360 reads