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.
About the Author
Chandraish Sinha has 20+ years of experience in Information Technology. He is an accomplished author and has published 11 books covering Business Intelligence related topics such as, Tableau, Power BI and Qlik. Checkout his Amazon Author profile.
His latest book Excel Basics to Advanced covers all the aspects of MS Excel and provides exercises for self-learning.
Similarly, his recent book, Dashboarding with Tableau, covers all the features in Tableau and includes exercises for self-learning.
He has implemented IT solutions in different domains viz. Pharmaceutical, Healthcare, Telecom, Financial and Retail.
He blogs regularly on various IT topics. Check them out in the links given below:
