Additional Resource Videos
Flash Fill

Additional information


The first new feature is a feature called Flash Fill. Now, this is a very simple feature that allows you to extract data that matches a specific pattern from another set of cells. In this particular spreadsheet, notice that the asset numbers on the left hand side of the screen begin either CL or BW, CL is a color printer, BW for a black and white printer. What we want to do now is show which items are color or black and white. 

To do this, I'm simply going to type CL, which represents the first two letters of the asset number in that particular row. I'm going to hit enter, click on the cell and I'll use the auto fill handle to take it down as far as it can go. As a tip, you can double click this box and it will go down as far as it needs. Now as you'll see, the words CL are repeated down the set of cells selected. At the bottom of the cells, you'll notice the auto fill options smart tab has appeared. If this is clicked, there are a number of options, including one at the bottom called Flash Fill, I'll click this and notice what it's done. It's found where the first two letters match the items in the appropriate asset number and displays them accordingly in the column that I have used. 

Let's click on the location code column and notice that the asset numbers for the location are represented by four letters in the middle of the number. For example, in row two, READ represents reading and in row three, BRIS represents Bristol. What I want to do is extract those letters and place them in the location code column. to do this, I'm going to type READ into the location code. This time, to be slightly different, I'm going to highlight all of the cells that I wish to place the Flash Fill in to, I'll then click the data tab and towards the right of the data tools area is a button called Flash Fill. Press this and as you'll see, it's looked at all the information shown in the asset number and displays that in the location code column.

About the Author
Learning Paths

A world-leading tech and digital skills organization, we help many of the world’s leading companies to build their tech and digital capabilities via our range of world-class training courses, reskilling bootcamps, work-based learning programs, and apprenticeships. We also create bespoke solutions, blending elements to meet specific client needs.