Ravindra BagaleCourses & study guides मराठी Track your progress

2. Data Entry Tools

2.2 Flash Fill

By Last updated:

Flash Fill with example — in 30 seconds

What: Flash Fill copies a pattern you type once. Excel guesses the rest of the column.

Why: Splitting first names by hand for 500 customers takes an hour. Flash Fill does it in one key press.

Tiny example: Type "Shraddha" next to "Shraddha Bagale" and press Ctrl + E. Excel fills Ruhi, Ravindra and Zoya.

First look at the data (before → after):

Flash Fill with example: before and after tables, fictional FreshGo Maharashtra data

Expected result — After Flash Fill

A: Customer B: First name
Shraddha Bagale Shraddha
Ruhi Bagale Ruhi
Ravindra Bagale Ravindra
Zoya Zoya

About this data: Teaching sample for a fictional shop “Orbit Logistics”. Figures and names (e.g. Vikram Rao) are made up for this lesson on flash fill.

Before table and formula as text

Before — Customer column, B is empty

A: Customer B: First name
Shraddha Bagale —
Ruhi Bagale —
Ravindra Bagale —
Zoya —

About this data: Demo table only — not real customer or company data. Built to practise flash fill.

Formula / click path

B2: type Shraddha → Enter
Data › Flash Fill   (or Ctrl + E)

Do it step by step:

  1. In B2, type the answer for the first row: Shraddha.
  2. Press Enter. You are now in B3.
  3. Press Ctrl + E (or Data › Flash Fill).
  4. Read every filled value. Flash Fill is a guess, not a formula.
  5. If one row is wrong, type that one correctly and press Ctrl + E again.

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

  1. Put the source data in column A (e.g. ravina.patil@example.com – see example below).
  2. In B2 type the result you want for row 2 (e.g. ravina).
  3. Go to B3 and press Ctrl + E (or Data › Data Tools › Flash Fill, or Home › Editing › Fill › Flash Fill).
  4. 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

About this data: Made-up rows so you can copy the pattern for flash fill. Currencies may use ₹, £, € or $.

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.

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

Loading practice tasks...

  1. Total quantity (Easy): Add up the "Quantity" column. (sheet "Data")
  2. Average unit price (Easy): Average of the Unit price ($) column, rounded to 2 decimals. (sheet "Data")
  3. Rows in Website (Easy): How many rows have Channel "Website"? (sheet "Data")
  4. Largest quantity (Easy): The largest value in the "Quantity" column. (sheet "Data")
  5. Clean name (Easy): Trim the "Name" value in the first row. (sheet "Text")
  6. Proper case (Easy): the "Name" value in the first row trimmed and in proper case. (sheet "Text")
  7. Code prefix (Easy): First 3 characters of the "Channel" value in the first row. (sheet "Text")
  8. Code number (Easy): Last 4 characters of the "Channel" value in row 3. (sheet "Text")
  9. Quantity for Website (Medium): Total of the Quantity column where Channel is "Website". (sheet "Data")
  10. Website in Denver (Medium): Total of the Quantity column where Channel is "Website" and City is "Denver". (sheet "Data")
  11. City part (Medium): Characters 5 to 7 of the "Channel" value in row 4. (sheet "Text")
  12. Name length (Medium): Length of the "Name" value in row 3. (sheet "Text")

See all practice tasks in the Task Hub →

Worked example — flash fill

Why

Meera Joshi in Pune and Carlos Rivera abroad both need a clear, repeatable way to use flash fill on a CityBasket Retail sheet without breaking totals.

What

Flash Fill is the Excel idea this page teaches. We will walk one tiny example end to end.

How

  1. Open a blank sheet. In A1 type a short label for flash fill.
  2. Enter a small made-up value for Meera Joshi using £ where money appears.
  3. Add a second value for Carlos Rivera using € so you see mixed currencies stay numeric.
  4. Apply the method from this lesson step by step (menu or formula as shown above).
  5. Check the result still works inside SUM or a simple check cell.

Formula / action

Follow the formula or ribbon steps shown in this lesson (keep values numeric).
Result: The demo cell shows the expected result for CityBasket Retail's sample row.
Meaning: Once flash fill is correct on one row, copy the same pattern down the column.

Quick revision cards

Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.

Revision card 1: What is Flash Fill?
Card 1: What is Flash Fill? · Download PNG
Revision card 2: Tiny example — Flash Fill
Card 2: Tiny example — Flash Fill · Download PNG
Revision card 3: Checklist — Flash Fill
Card 3: Checklist — Flash Fill · Download PNG