2.2 Flash Fill
Flash Fill watches one or two examples you type and fills the rest of the column with the same pattern. Available in Excel 2013 and later.
Steps in Excel
- Put the source data in column A (e.g.
ravina.patil@example.com– see example below). - In B2 type the result you want for row 2 (e.g.
ravina). - Go to B3 and press Ctrl + E (or Data › Data Tools › Flash Fill, or Home › Editing › Fill › Flash Fill).
- Check the results. If a few are wrong, correct one of them and Flash Fill again.
Worked example. Store codes in our export are combined like PUN-Kothrud-01. Shraddha wants City code and Area separately.
| Store Code (A) | City Code (B – typed first row, Ctrl+E) | Area (C – typed first row, Ctrl+E) |
|---|---|---|
| PUN-Kothrud-01 | PUN | Kothrud |
| NSK-College Road-01 | NSK | College Road |
| NGP-Sitabuldi-02 | NGP | Sitabuldi |
| KOP-Tarabai Park-01 | KOP | Tarabai Park |
Flash Fill also combines: type Kothrud, Pune in a new column from Area and City, press Ctrl + E.
Ravindra Bagale's Tip
Remember, the result of Flash Fill is not a formula – it is only values. If the source data changes, the Flash Fill column does not update. For data that changes every day, use a formula (TEXTBEFORE, LEFT, MID – Module 3) or Power Query; Flash Fill is best for one-time cleaning.
Ravindra Bagale's Tip – मराठी
लक्षात ठेवा, Flash Fill चा result formula नसतो – त्या फक्त values असतात. Source data बदलला तर Flash Fill column update होत नाही. रोज बदलणाऱ्या data साठी formula (TEXTBEFORE, LEFT, MID – Module 3) किंवा Power Query वापरा; Flash Fill one-time cleaning साठी best आहे.
Ravindra Bagale's Tip – हिंदी
याद रखो, Flash Fill का result formula नहीं होता – वह सिर्फ़ values होती हैं. Source data बदला तो Flash Fill column update नहीं होता. रोज़ बदलने वाले data के लिए formula (TEXTBEFORE, LEFT, MID – Module 3) या Power Query इस्तेमाल करो; Flash Fill one-time cleaning के लिए best है.
Practice task
From a column of customer e-mails like shahrukh@example.com, use Flash Fill to extract the name part with the first letter capital (Shahrukh). Then combine Area and City into "Area, City".