Tutorial Playlist

Excel Tutorial

Overview

Your One-Stop Solution to Know About Excel Worksheets

Lesson - 1

Your One-Stop Solution For Excel UserForms

Lesson - 2

The Best Guide on How to Send an Email in Excel

Lesson - 3

Your One-Stop Solution That Will Help You Learn How to Convert PDF to Excel

Lesson - 4

A One-Stop Solution for Acing the Excel MIS Report

Lesson - 5

All You Need to Know About Barcode in Excel

Lesson - 6

Your One-Stop Solution to Learn Mail-Merge in Excel and Its Implementation

Lesson - 7

A Comprehensive Guide on Excel Flash Fill

Lesson - 8

Slicers in Excel: The Ultimate Guide to Help You Design and Develop Excel Slicers

Lesson - 9

One-Stop Solution to Learn Everything About Excel Budget Template

Lesson - 10

All You Need to Learn About Match Function in Excel and Its Real-Time Implementation

Lesson - 11

Everything You Need to Learn on How to Create a Pivot Table From Multiple Sheets

Lesson - 12

The Major Fundamentals About XLookUp in Excel Along With Practical Examples

Lesson - 13

The Ultimate Guide to Learn How to Compare Two Columns in Excel

Lesson - 14

The Best Guide and Your One-Stop Solution to Master the Fill Series in Excel

Lesson - 15

What Is the DATE Function in Excel and How to Implement It?

Lesson - 16

Your One-Stop Solution to Designing and Implementing Hyperlinks in Excel

Lesson - 17

One-Stop Solution to Master Everything You Need to Know About Auto-Sum in Excel

Lesson - 18

DAX in Excel: The Complete Guide for DAX Functions and Formulas in Excel

Lesson - 19

A Perfect Guide for All You Need to Know About Data Formatting in Excel

Lesson - 20

The Perfect Guide to Learn How to Add Columns in Excel

Lesson - 21

The Perfect Guide That Will Explain to You How to Remove Page Break in Excel

Lesson - 22

The Perfect Guide to Explain to You How to Remove Blank Rows in Excel

Lesson - 23

The Perfect Guide to Learning How to Highlight Duplicates in Excel

Lesson - 24

One-Stop Solution Understand the Fundamentals of Indirect Function in Excel

Lesson - 25
A Comprehensive Guide on Excel Flash Fill

Excel Flash Fill comes in handy when using string manipulation functions like the trim() method to the entire column. Now, the process would not seem tedious for a couple of rows but can be a little hectic when you have thousands of rows. Excel Flash Fill can be employed to finish the time-consuming task within a couple of keystrokes. 

What Is Excel Flash Fill?

Excel Flash Fill is a feature of Microsoft Excel where Excel can sense a pattern in the cell and apply the logic to extract a similar resultant pattern out of the remaining cells in the table. 

The definition could be a little confusing, so go through an example.

PCP in Business Analysis

In partnership with Purdue UniversityVIEW COURSE
PCP in Business Analysis

Example for Excel Flash Fill

Now, assume that you have an automobile product data table. The table has one column that stores the geographical region code, manufacturer code, and automobile type code.

Example:

Let us assume that you have an automobile code as “NOR-HON-SUV”

Here, NOR is North for Geographical Code, HON is Honda for Manufacturer Code, and SUV is an automobile type code.

Imagine you need to identify these patterns individually and generate three different columns based on geographical region code, manufacturer code, and automobile type code from the original column.

This could be done using a bunch of functions like TRIM() etc. But, the process could seem a little complicated and time-consuming. What if you could do it with a few keystrokes?

That is precisely where Excel Flash Fill comes to play. You can press a set of keystrokes, and the data will be automatically split and segregated. 

Sounds interesting, right? Now, enter the next section and try to understand how exactly Excel Flash Fill works in real-time. 

How to Implement Excel Flash Fill?

In the following example, you will include product-related data. In column A, you have the product code, product serial number, assembly code written altogether. Now, the goal is to separate them into three different columns. As shown in the first row of the image.

Excel-Flash-Fill-Demo-1.

Now to do so, all you have to do is go to the next successive cell and press and hold the control key in Windows or Command key in Mac, then press the alphabet 'E.'

CTRL + E or CMD + E

Now, sometimes, very rarely, Microsoft Excel might fail to recognize the patterns; in such scenarios, you can help it by filling two or more rows to help Excel get familiarised with the pattern requirements. Then, you can press CTRL+E or CMD + E to fill all the successive cells.

A sample is shown in the image below.

Excel-Flash-Fill-Demo-2.

The final resultant data table would look like this in the image shown below.

Excel-Flash-Fill-Demo-3

This feature can be applied to any type of data, regardless of string length or data type.

For example, print the content in the 4th to 8th location of strings with different string lengths. 

FREE Business Analytics With Excel Course

Start your Business Analytics Learning for FREEStart Learning
FREE Business Analytics With Excel Course

Let us consider the example below. In this example, you have strings of different lengths, and also, you have included a cell with numerical data.

Excel-Flash-Fill-Demo-4

This example has filled the first two resultant cells so that excel could compile the expected pattern. Now, in the third cell, it has used the Excel Flash Fill shortcut, CTRL + E or CMD + E, and the remaining cells have gotten filled with data between the 4th to 8th location of strings as shown below. 

Excel-Flash-Fill-Demo-5

With this, you have come to an end of this tutorial on Excel Flash Fill. 

Looking forward to a career as a Business Analyst? Check out the Business Analytics Certification Training with Excel and get certified today.

Wrapping Up

Barcode in Excel can be your next step in learning data analytics. Barcodes are computer-readable information generated using a collection of black lines and white spaces. Using Excel, you can represent your data in the form of barcodes or even encode your confidential data.

Interested to learn more about Business Analytics? Or wish to get trained and certified to become a successful Business Analyst? Then feel free to reach out to the Business Analytics certification course from Simplilearn. Ranked among the five topmost business analytics courses. This Simplilearn program is offered in partnership with Purdue University is an outcome-driven training and certification program that helps you master the fundamental concepts of statistics and data analytics. 

Have any questions for us on this tutorial on "Excel Flash Fill"? If you do, or maybe you have doubts about our certification course, do reach out to us by sharing them as comments below. Our team of experts will address them and will be happy to answer them at the earliest. 

About the Author

Ravikiran A SRavikiran A S

Ravikiran A S works with Simplilearn as a Research Analyst. He an enthusiastic geek always in the hunt to learn the latest technologies. He is proficient with Java Programming Language, Big Data, and powerful Big Data Frameworks like Apache Hadoop and Apache Spark.

View More

Find Business Analytics with Excel in these cities

  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.