This lesson covers two smart data-entry features that share a family resemblance: instead of writing formulas, you give Excel a hint about the result you want and it produces the rest. Flash Fill infers a transformation from one or two example values you type next to a source column, then applies that pattern to every remaining row. Linked Data Types enrich a cell by connecting its content to a live online record — typing AAPL and converting to a Stock data type attaches a card with dozens of fields (price, market cap, CEO) that you can then pull into adjacent columns on demand.
Where formulas describe how to transform data step by step, both of these features let Excel work out the what: pattern recognition for Flash Fill, online enrichment for Linked Data Types.
Think of Flash Fill itself as Excel's built-in regex-and-replace engine — without the regex syntax. Instead of writing a complex pattern, you simply type the result you want in one or two cells, and Excel infers the transformation rule and applies it to the rest of the column instantly. It is the fastest tool for one-time text cleanup tasks on real-world, dirty data.
Part 1: What Flash Fill Does
Flash Fill recognizes patterns between adjacent columns and uses them to extract, combine, or reformat data. It works with:
- Splitting data — separating "John Smith" into "John" and "Smith" in two columns
- Combining data — merging a First Name and Last Name column into "Smith, John"
- Reformatting text — converting "2024-01-15" into "January 15, 2024"
- Extracting substrings — pulling the domain out of "john@contoso.com" → "contoso.com"
- Changing case — converting "JOHN SMITH" → "John Smith"
Flash Fill does not understand your data semantically — it infers based purely on positional and character patterns.
Part 2: How to Trigger Flash Fill
Method 1: Keyboard shortcut (fastest)
1. In the column next to your source data, type the desired result for the first row.
2. Press Enter to move to the next cell.
3. Press Ctrl + E.
Method 2: Ribbon 1. Type the result for the first row. 2. Go to the Data tab > Data Tools group > Flash Fill.
Method 3: AutoFill handle
1. Type the result for the first row.
2. Begin typing the second row — Excel may show a gray preview of the full pattern.
3. Press Enter to accept.
Part 3: Practical Examples
Example 1 — Split Full Name into First and Last
| Column A (Source) | Column B (First Name) | Column C (Last Name) |
|---|---|---|
| John Smith | John | Smith |
| Maria Garcia | Maria | Garcia |
| David Lee | David | Lee |
Type John in B2, press Ctrl+E. Type Smith in C2, press Ctrl+E.
Example 2 — Reformat Phone Numbers
| Column A (Source) | Column B (Formatted) |
|---|---|
| 5551234567 | (555) 123-4567 |
| 8009876543 | (800) 987-6543 |
Type (555) 123-4567 in B2, press Ctrl+E.
Example 3 — Extract Email Domain
| Column A (Source) | Column B (Domain) |
|---|---|
| john@contoso.com | contoso.com |
| sarah@fabrikam.com | fabrikam.com |
Type contoso.com in B2, press Ctrl+E.
Part 4: Critical Limitations
Part 5: Linked Data Types (Stocks, Geography, Currencies)
Flash Fill and Linked Data Types are grouped together in this lesson because they are the two features Microsoft brands as smart data entry: both let you produce structured output from minimal user input, with Excel doing the heavy lifting of pattern recognition or record lookup. The difference is the source of the "smarts." Flash Fill works locally and offline by detecting patterns between adjacent cells in your workbook. Linked Data Types work online, querying Microsoft's data service to pull authoritative facts (stock prices, country populations, exchange rates) into your grid. Knowing both lets you cover the full spectrum from "reformat this messy text" to "fetch fresh figures from the internet" without leaving the cell you're standing in.
Where Flash Fill infers a transformation from text patterns, Linked Data Types enrich the cells themselves by connecting them to live, structured records pulled from an online data service. A cell that started as the string "AAPL" becomes a Stock record with dozens of attached fields — Name, CEO, Headquarters, Market Cap, 52-Week High — that you can pull into adjacent columns on demand.
What Linked Data Types Are
A linked data type is a special cell value backed by a record card. Behind the scenes, Excel stores the underlying data type identifier (e.g., the company's ticker) and queries Microsoft's data service when you expand the card or insert a field. The record card is refreshable: when the underlying data changes (a new closing price, a population update), a refresh pulls the latest values.
Built-in data types include:
- Stocks — Companies, exchanges, mutual funds, ETFs, currencies traded on public markets. Source: third-party financial data providers.
- Currencies — FX rate pairs (e.g.,
USD/EUR) with fields like Price, Change, 52-Week High/Low. - Geography — Countries, states/provinces, cities, postal codes. Fields include Population, Area, Capital, Largest City, GDP, Time Zone, Leader Names.
- Organization — Internal company directory data, surfaced from your Microsoft 365 organization (people, departments, reporting lines).
- Automatic — Excel attempts to detect which type to use based on the cell's content.
Inserting a Linked Data Type
- Type the recognisable identifier into a cell (e.g.,
AAPL,France,USD/EUR). - Select the cells you converted.
- Go to Data tab > Data Types group > pick Stocks, Geography, Currencies, Organization, or Automatic.
- Excel queries the service. On success, each cell shows a small icon to the left of the value (a building for stocks, a map pin for geography). On failure, a question-mark icon opens a Data Selector task pane where you pick the intended record from a list.
The Card Icon
Click the icon inside the cell (or press Ctrl + Shift + F2) to open the record card — a popover showing every field available for that record. The card is read-only; closing it leaves the cell unchanged.
The Insert-Data Icon
When a linked data type cell is selected, a small insert-data icon appears at the top-right corner of the selection (a tiny "Add Column" button). Click it to open the field list, then click any field to insert a new column to the right with that field's value for every selected row. Repeat to add more fields.
Cells: A2 = AAPL (Stock)
A3 = MSFT (Stock)
A4 = GOOG (Stock)
Click insert-data icon → "Market cap"
B2 = 3,200,000,000,000 ← live values pulled from the data service
B3 = 2,800,000,000,000
B4 = 1,800,000,000,000
Refreshing the Data
- Right-click the cell > Data Type > Refresh — refresh just that record.
- Data tab > Refresh All (
Ctrl + Alt + F5) — refresh every linked data type record (and every Power Query) in the workbook. - Refresh frequency is on-demand only — values do not update on a timer unless you wire one up via a macro.
Formula Reference into a Linked Data Type
Inside a formula, use the dot operator on the cell to pull a specific field:
=A2.Price The current price field for the stock in A2
=B5.Population The population of the geography in B5
=A2.[52 week high] Field names with spaces require square brackets
If the underlying data changes after a refresh, every formula that reads from .Price or .[Market cap] recalculates automatically.
Concrete Example: Stocks Workflow
| Step | Action |
|---|---|
| 1 | In A2:A4, type AAPL, MSFT, GOOG |
| 2 | Select A2:A4, click Data > Data Types > Stocks |
| 3 | Each cell now shows a building icon and the company's full name |
| 4 | With A2:A4 still selected, click the insert-data icon → pick Market cap |
| 5 | Click insert-data again → pick 52 week high |
| 6 | Two new columns of live financial data appear in B2:C4 |
| 7 | A week later, click Data > Refresh All to pull updated prices and market caps |