How to Use Flash Fill in Excel – 2022’s Rich

Flash Fill in Excel is a unique and intelligent time-saving tool. When you input data in the first row of a list in either the left or right column, making the modifications you desire, and then execute the command, it swiftly populates the remaining column with data in a matter of seconds, following the same formatting.

To put it simply, Flash Fill is a data manipulation feature within Microsoft Excel that autonomously populates values in a column by recognizing patterns inherent in existing values. This functionality empowers users to rapidly and effortlessly extract, split, merge, or format data, all without necessitating the creation of intricate formulas or macros.

Please refer to the accompanying screenshots for each step, which will aid in comprehending the process easily.

Flash Fill in Different Cases

Let’s begin by using Flash Fill to convert the first and last names of the students into UPPER case, lower case, and Proper Case. Here is how you can do it step by step:

UPPER CASE:

• First, prepare any data as in the screenshot below.

• To change the sentence case name to UPPERCASE, click and type the name in CAPITAL letters of the first student in a new adjust column, which is under the UPPERCASE header.

• Excel will recognize the pattern and automatically suggest the transformation for the remaining names.
• Then press the flash fill shortcut (CTRL+E) or click on the “Fill”, then click “Flash Fill” to onfirm the Flash Fill suggestion, and the entire column will be populated with the names in UPPER case as in the screenshot below.

lower case

• In another new adjacent column, type the first name in lower case.
• Excel will again identify the pattern and propose the transformation for the remaining names.
• PThen press the flash fill shortcut (CTRL+E) or click on “Fill”, then click “Flash Fill” to accept the Flash Fill suggestion, and the entire column will be filled with the names in lower case.
• Proper Case:

Proper Case

• For Proper Case, start by typing the first name with the appropriate capitalization.
• Excel will detect the pattern and provide a suggestion for the rest of the names.
• Confirm the Flash Fill suggestion by pressing “Ctrl + E,” and the entire column will be populated with the names in Proper Case.

Pick First, Middle, or Last Names from the Full Names:

• You can flash-fill the first, middle, and last names from the full names of the student’s lists. For example, In the screenshot below, the full names of the students are separated as first name, middle name, and last name by flash-filling them in the right-side columns.
• In other words, you can separate and fill in the part of the name you want. For example, If the full name of a student is Omkar Rajputh Nayak, you can type any part of the name such as Omkar, Rajput, or Nayak and use the flash fill command to apply the same rule quickly to all the students.

Flash Fill Full Names of the Students as Fist, Middle, or Last Name:

• Click right to the full names of the students from the beginning under the first name header and type the first name of the student from the full name of the student which is given in the left column.
• Then press the flash fill shortcut (CTRL+E) or click on the “Fill”, then click “Flash Fill” to fill a column with all the names of the students in UPPERCASE in just a fraction of a section.

First, Middle, and Last Names of the Students:

You can combine and fill in the parts of the full names. For example, If 3 parts of the names of the students are Omkar, Rajput, and Nayak are separated into 3 columns, You can combine all the 3 parts into full names in one column. By using flash fill in excel, this rule can be applied to all the students.

Steps:

• Click the beginning column, which is to the right of the first, middle, and last names of the students and under the full name of the header, then type the first, middle, and last name of the student, which is given towards the left column.
• Then press the flash fill shortcut (CTRL+E) or click on the “Fill”, then click “Flash Fill” to fill a column with all the names of the students in UPPERCASE in just a fraction of a section.

Any Name from Full Name:

You can flash fill any name such as first name, middle name, or last name from the full names of the list of all the students.

Start with Short Initial Name and Remaining Full:

Start with the short initial name and remain full.

• Prepare the data as in the screenshot below.
• Type the name of a person from the left column and make the initial the short under “Name with Short Initial” header.
• Carry out the flash fill command to apply the same rule for all.

Start Every Name with Mr., Ms., Mrs., or Miss.

You can add Mr, Ms, Mrs, or Miss before all the names at a time.

• Prepare the data as in the screenshot below.
• Type the name of a person from the left column and add Mr, Ms, Mrs, or Miss under the “Name with Personal Title” header.
• Carry out the flash fill command to apply the same rule for all.

Framing Sentences:

You can also use words to form sentences you like. For example, from the following screenshot, each column gives information about each student. So, using the information of the first student you can create meaningful short sentences as you like in the next column, which is empty and apply the same rule to all the students through the flash fill.

• First, prepare the student data as in the screenshot below.
• Form a meaningful short sentence under the “Full Information” header for the first student and apply Flash Fill as given in the screenshot below.
• After applying flash fill, the above-formed sentence rule was applied to all the rest students.

Separate Parts of Dates:

You can separate dates into months, days, years and vice versa. For example, from the following screenshot, under the date header, a list of dates is presented. You can separate them as days, months, and years in the right column, which is empty and apply the same rule to all through the flash fill.

Combine Parts of Dates:

You can combine months, days, years, and vice versa from the date. For example, from the following screenshot, under the “day”, “month”, and “year” a list is presented. You can combine them as dates in the right column, which is empty, and apply the same rule to all through the flash fill.

Change Text/Date Format:

You can change the date format you like. For example, the date formats include 01/02/2021, 2021/02/01, 02/01/2021, 01/ Feb/2021, 2021/Feb/01, etc. You can change them in any format from the right column to the left empty column. And apply the same rule to all the dates through the flash fill.

• Prepare a list of date format as given in the screenshot below.
• Pick the same date from the left column and type with a different format under “Date Format”. For example, change 01/02/2021 to 01/Feb/2021.
• Now use flash fill to apply the same rule to the list of all dates.

Creating an Email ID:

You can create an Email ID for all the lists. An Email ID can be created with the First name, Middle name, Last name, full name, or vice versa with or without a slight difference.

• Prepare the data as given in the screenshot below. In this screenshot, first, middle, and last name lists were created
• Pick any part of the name such as first, middle, last, or full name and type it under the “Email Id” header with any changes you want to be followed by any email such as Gmail.

Adding Dialing Codes:

You can add dialling codes before all the mobile numbers at a time.

• Prepare the data as in the screenshot below.

• Type the mobile number starting with the dialling code (For example, India’s dialling code is: +91) under the “Mobile. Number with Dialing Code” header as it is from the left column.

• After using the flash fill command, a list of all the students’ mobile numbers with dialling codes will be filled as in the screenshot below.