Flash Fill in Excel – Shiksha Online

Flash Fill in Excel – Shiksha Online

6 mins read666 Views Comment
clickHere
Rashmi
Rashmi Karan
Manager - Content
Updated on Dec 8, 2023 13:15 IST

Flash Fill is a powerful and time-saving feature in Microsoft Excel that can transform how you work with data. It allows you to extract, combine, or reformat information quickly and efficiently. The article highlights the use of flash fill in cleaning and managing huge data sets. Learn how to use flash fill in Excel through examples.

Flash Fill in Excel

 

Flash Fill is a highly flexible tool integrated into MS Excel 2013. It offers a quick way to enter and manage new data. In order to effectively use Flash Fill, ensure that –

  • The source data has a consistent pattern.
  • You refresh the Flash Fill results every time you update the source data.

Content

How Does Flash Fill Work?

Flash Fill is used to fill in values automatically. All you need to do is enter a couple of examples you want as output in the column you want to fill in. Excel will automatically detect the pattern in the data and fill the rest of it. You can use FlashFill to extract data, join names, reformat the numbers, and much more.

Flash Fill is among the most straightforward Excel functions. Here is how you can use it: 

  • Enter your data set in the spreadsheet
  • Next to your data, enter what you aim to achieve using Flash Fill.
  • Go to Data, and under the Tools section, Choose Flash Fill to complete the list.

 You can run it manually to Data > Flash Fill or use Ctrl + E as a shortcut.

Flash Fill in excel

Read MS Excel Tutorials

Flash Fill Keyboard Shortcut

2022_09_image-111.jpg
Most Popular and Powerful Formulas in Excel
Most Popular and Powerful Formulas in Excel
Excel has various functions ready to process your information to get the desired results. Familiarize yourself with each of them. Once you master various functions, you can create advanced MS...read more
Average Function in Excel – Formula and Examples
Average Function in Excel – Formula and Examples
The AVERAGE function is a popular Excel function used for various purposes, such as calculating average sales, average student scores, average monthly expenses, etc. It provides a quick and efficient...read more
MIN and MAX Functions in MS Excel
MIN and MAX Functions in MS Excel
Learn about MIN and MAX functions in MS Excel in this blog.

Adding Flash Fill to the Quick Access Toolbar

Add the Flash Fill button to the Quick Access Toolbar to make the work more convenient.

The Flash Fill button is now added to the quick access toolbar.

Extract Data – Flash Fill Example 1

Flash Fill is used to extract a defined string from a cell – In the dataset below, we have employee codes from which we have to segregate the employees’ names, phone numbers and user IDs. We will use Flash Fill to extract these specific strings from the cells.

To segregate the names mentioned in Column A, start typing the names in Column B. If you drag the name to fill the rest of the columns below, then this won’t work, and the name in the B1 will get copied to the rest of the Columns.

Go to the Data tab and click Flash Fill.

You will see that the rest of the columns are now filled with the first name data.

We will follow the same process for the rest of the columns and sort our data.

Top MS Excel Interview Questions
Top MS Excel Interview Questions
Microsoft Excel is an indispensable tool that empowers professionals to manage, analyze, and visualize data with unparalleled efficiency. Master your Excel interview with confidence using our curated list of top...read more
Introduction to MS Excel – A Beginner’s Guide
Introduction to MS Excel – A Beginner’s Guide
Microsoft Excel has been around for decades and is one of the most desired skills if you are considering a career in data science or analysis. But there are still...read more
How to Create MIS Report in Excel?
How to Create MIS Report in Excel?
A Management Information System (MIS) report in Excel is a vital tool for businesses and organizations, offering valuable insights by consolidating and presenting data in an organized and comprehensible format....read more

Join Names – Flash Fill Example 2

Flash Fill in Excel is used to join the names and create email addresses for the rest of the cells. We have the first name in Column A, and the last names in Column B. Note that we have used lowercase to create the email address in C2. Flash Fill will read this pattern and apply the same format against the rest of the names.

Click Flash Fill to generate email IDs for the remaining cells.

TRIM Function in Excel – Shiksha Online
TRIM Function in Excel – Shiksha Online
If you are dealing with datasets from Excel, you know that you have to deal with characters that you’d rather have removed, such as unnecessary spaces. This happens especially if...read more
How To Calculate Percentage In Excel
How To Calculate Percentage In Excel
Percentages are a very powerful tool for data analysis. They can be used to make data more concise, easier to understand, and more comparable. In this tutorial, we will learn...read more
Sum Function in Excel – Learn Through Examples
Sum Function in Excel – Learn Through Examples
In continuation to our previous tutorial on MIN and MAX functions in Excel, we move on to another important basic function, the SUM function. Learn about the SUM function in...read more

Separate Names – Flash Fill Example 3

You can also perform the vice versa action using Flash Fill. It can separate names from the given email addresses. 

We will pick the first and last names from the email and write them down in Columns B and C, respectively. First, we will select B2, click on Flash Fill, and then select C2 and Flash Fill.

If you select both B2 and C2 simultaneously, you will see that the Flash Fill option gets deactivated. So choose them separately.

Reformat the Numbers – Flash Fill Example 4

Flash Fill can reformat the numbers. Here, we want to convert the given numbers into social security numbers. We will follow the same path – Apply flash fill and achieve the desired number format.

Click Flash Fill.

We now have the re-formatted data.

Limitations of Flash Fill

It is an easy tool, but Flash Fill still has certain limitations. In the example below, each statement in Column A contains a data point. We aim to pick those data points from the text strings and place them separately. To start with, we will mention the data from A2 in B2.

Apply Flash Fill.

You can see that Flash Fill did not correctly extract the decimal numbers. It could only pick digits after the decimal point.

You would need to provide a little help to identify the pattern. Change the first instance of the incorrect data, and Excel will automatically correct all the remaining instances. In this case, we changed the entry from 0.74 to 74, updating the instances where Excel picked data incorrectly. However, you can still see that values remained unchanged for B5 and B10.

How to Turn Off Flash Fill

Sometimes, you don’t want the Flash Fill function to interrupt while working with your datasets. Even the previews sometimes interrupt. 

In such cases, you can disable or turn off Flash Fill. Please note that you can enable or disable Flash Fill anytime you want. Here is how to turn off Flash Fill in Excel.

Go to the Ribbon, and click on File —> Options

Click on Advanced. You will be redirected to a new Dialogue Box

Locate Advanced and click it

Scroll down and uncheck the “Automatically Flash Fill” box. Click OK.

Conclusion

Flash Fill offers great flexibility when it comes to managing data. However, it does limit you if there are inconsistencies in the data. Another point to remember is that it will not automatically update your results in case your source data changes.


Top Trending Articles in MS Excel:

Most Useful Excel Formulas | Min Max Functions in Excel | Average Functions in Excel | Introduction to MS Excel | Financial Modelling in Excel | MS Excel interview questions | Sum Function in Excel | Trim Function in Excel | Pivot Table in Excel | Percentage in Excel | Vlookup in Excel | Median Function in Excel | Types of Charts in Excel | Count Function in Excel | MS Excel Vs. Google Sheet | Remove Duplicates in Excel | Create Graph in Excel

FAQs

How do I enable flash fill in Excel?

To enable Flash Fill in Excel, follow the below path - Click File ==> Options. Click the Advanced in the left pane. The Automatically Flash Fill box should be checked. Click OK. Close and restart Excel

How to fill an empty cell in Excel?

The Ctrl + Enter key combination fills the selected cell range with the current entry from the formula bar. In the case of having a formula with relative references, it will make the necessary changes when copying the formula.

How to fill blank cells with F5?

Select and fill empty or blank cells Select the range you want to complete including the cells where the names you want to repeat are. Press the F5 key to open the Go To box. Click Special. Choose the Blank Cells option. Click OK.

How to fill empty cells with color in Excel?

To fill all blank/non-blank cells with colors - Go To the Special function Select the specified range where you will shade all blank cells (or non-blank cells ). Click Home > Find & Select > Go To Special... ... Click OK.

About the Author
author-image
Rashmi Karan
Manager - Content

Rashmi is a postgraduate in Biotechnology with a flair for research-oriented work and has an experience of over 13 years in content creation and social media handling. She has a diversified writing portfolio and aim... Read Full Bio

Comments