Flash filling the list of names

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

The Flash Fill in Excel is a unique and intelligent tool that saves time. If you enter the first row of data from the list in the left or right column with some modification of your choice and use the flash fill command, it will fill the rest with the data in a fraction of a second in the same format based on a pattern.

Video Tutorial on Flash Fill in Excel with examples:

1. Flash Fill as UPPER, lower, and Proper Case:

Let’s get started to flash fill as UPPER, Lower, and Proper Case with the following list of first and last names of the students.

  • Firstly, prepare the data as in the screenshot below.
Data preparation
Data preparation
  • To change the sentence case name as UPPERCASE, click and type the name in CAPITAL letters of the first student in the first right column, which is under the UPPERCASE header.
Changing Name in UPPERCASE
Changing Name in UPPERCASE
  • 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 as in the screenshot below.
Flash filling the list of names
Flash fills the list of names
  • To change the sentence case name as lower case, click and type the name in small letters of the first student in the first right column, which is under the “lower case “header.
  • 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 lower case in just a fraction of a section.
  • To change all the names of the students in Proper Case, click and start typing the first letter in the capital of each name or word in a blank column, which is under the “Proper Case “header.
  • 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 Proper Case in just a fraction of a section.

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

  • You can flash fill the first, middle, last names from the full names of the students list. 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 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, Rajputh, or Nayak and use flash fill command to apply the same rule quickly to all the students.
See also  Ways to Apply Accounting Number Format in Excel - 2022

3. 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.

4. Combine First, Middle, and Last Names of the Students:

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

Steps to Combine First, Middle, and Last Names of the Students:

  • 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 the 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.

5. Flash Fill any Name form 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.

6. Start with Short Initial Name and Remaining Full:

Start with the short initial name and remaining full.

  • Prepare the data as in the screenshot below.
  • Type the name of a person from the left column and make the initial as the short under “Name with Short Initial” header.
  • Carry out the flash fill command to apply the same rule for all.
See also  How to Add Zeros Before a Number in Excel - 2022's Best

7. 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 “Name with Personal Title” header.
  • Carry out the flash fill command to apply the same rule for all.

8. Framing Sentences using Flash Fill in Excel:

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 file.

  • First, prepare the student data as in the screenshot below.
  • Form a meaningful short sentence under “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 applied to all the rest students.

9. Separate Part of Dates using Flash Fill in Excel:

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 file.

10. Combine Part of Dates using Flash Fill in Excel:

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 file.

11. 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 file.

  • Prepare a list of date format as given in the screenshot below.
  • Pick the same date from the left column and type with 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.
See also  The Draw tab in Word, Excel, and Ppt - 2022's - Hidden

12. Creating an Email ID using Flash Fill in Excel:

You can create an Email ID for all the lists. An Email ID can be created with the First name, Middle name, Last name, or 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 list 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.

13. Adding Dialing Codes:

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

  • Prepare the data as in the screenshot below.
How to Use Flash Fill in Excel - 2022's Rich 1
Adding Dialing Codes Using Flash Fill in Excel
  • Type the mobile number starting with dialing code (For example, India’s dialing code is: +91) under “Mobile. Number with Dialing Code” header as it is from the left column.
How to Use Flash Fill in Excel - 2022's Rich 2
Adding Dialing Codes Using Flash Fill in Excel
  • After using the flash fill command, a list of all the students’ mobile number with dialing code will be filled as in the screenshot below.
Flash Fill mobile numbers with dialing code
Adding Dialing Codes Using Flash Fill in Excel

Read Also:

What is Flash Fill in Excel?

Flash fill is a unique and intelligent tool that saves time. If you enter the first row of data from the list in the left or right column with some modification of your choice and use the flash fill command, it will fill the rest of the data in a fraction of a second in the same format based on a pattern.

Flash fill keyboard shortcut

Ctrl + E is the Flash fill keyboard shortcut in excel

What is the flash fill formula in Excel?

There is no such formula or function for flash fill in excel. Though you have an option to use a shortcut key.

How to turn off flash fill in Microsoft Excel?

Navigate to the “File” menu ➜ click the “Options” ➜ Also click the “Advanced” tab in the Excel Options ➜ Lastly, uncheck the” Automatically Flash Fill” from the editing options.

What is the fill feature in Excel?

Flash Fill in Excel is also called the fill feature in Excel.

What is the Flash fill option in Excel?

You can choose many Flash Fill options to choose from.