Splitting a column using Flash Fill in Excel
MS Excel has introduced a new feature in 2023 called Flash Fill. It helps in splitting a column using Flash Fill when a pattern is identified. It works similar to Auto Fill but with a slight difference.
Advantages of using Flash Fill
Flash Fill helps in splitting a single column into two columns or combining two columns into one. For e.g., if a column contains both First Names and Last Names, it can be split into two different columns displaying the First Names and Last Names separately.
Flash Fill can also be used to combine the values contained in separate columns into one.
Splitting a column using Flash Fill in Excel
To understand the working of Flash Fill, we will use the following data:
The data shown above contains First Name and Last Name in one cell. It will be split and inserted using Flash Fill in the blank First Name and Last Name columns.
- Enter the first name (Adam) in the First Name column.
- Using the mouse, right-click on the cell and drag it down to the remaining cell and select Flash Fill from the options.
Excel will identify a pattern and will fill the remaning cells with all the first names.
- On the Last Name column, enter the first Last Name “Smith”.
- Right-click on the cell and drag it down. Select Flash Fill from the options.
Excel will identify a pattern and fill in the Last Names in all the cells.
In this way, using Flash Fill we can split a column into two separate columns.
Flash Fill allows us to fill in large amount of data based on a pattern.
For a step-by-step approach to learning more advanced features and functions in Excel, take a look at the book Excel Basics to Advanced.
To practice more Excel techniques visit MS Excel practice questions.
