Ever struggled to mask hundreds of phone numbers or ID numbers with asterisks (*)? Spending too much time memorizing complex formulas like REPLACE or SUBSTITUTE? You can fill an entire column automatically by typing just one example and pressing a single keyboard shortcut.
Quick Summary
- Mask sensitive data and extract text without formulas using the shortcut Ctrl + E
- Supported versions: Excel 2013, 2016, 2019, 2021, Microsoft 365
- How to use: Type an example in the first row next to the source data, then press Ctrl + E on the row below
- Precautions: Do not leave blank columns between source and target columns; re-run shortcut if source data changes
How Does Flash Fill Work Without Formulas?
You do not need to memorize complex formulas to mask or split text in Excel. Excel 2013 and later versions (Excel 2013, 2016, 2019, 2021, and Microsoft 365) include Flash Fill, an intelligent feature that automatically recognizes patterns.
It works like a cookie cutter. Enter your desired format in the cell directly adjacent to your source data, and Excel will detect the pattern and populate the remaining rows accordingly.
You can reformat large datasets in seconds without using functions like REPLACE, SUBSTITUTE, MID, or CONCAT.
How to Mask Data in 1 Second Using ‘Ctrl + E’
The process is straightforward. In the column directly next to your source data, type the desired masked format in the first row. Then, select the cell right below it and press Ctrl + E on your keyboard to instantly fill the rest of the column with the same pattern.
Alternatively, you can click the [Flash Fill] icon under the [Data] tab on the ribbon menu.
| Task Type | Source Example | First Row Example | Shortcut Result (Ctrl + E) |
|---|---|---|---|
| Phone number masking | 010-1234-5678 | 010-****-5678 | Masks middle 4 digits across all rows |
| National ID / SSN masking | 900101-1234567 | 900101-1****** | Masks last 6 digits across all rows |
| Name masking | Hong Gil-dong | Hong G*l-dong | Masks middle letters across all rows |
| Extract username from email | gildong@company.com | gildong | Extracts usernames only across all rows |
3 Things to Check When Flash Fill Fails or Shows an Error
First, ensure there are no empty columns between the source data and the working column. Flash Fill only reads patterns from directly adjacent columns. If the shortcut does not respond, go to [File] > [Options] > [Advanced] > [Editing options] and verify that [Enable AutoComplete for cell values] and [Automatically Flash Fill] are turned on.
Second, irregular data with varying character lengths may cause pattern recognition to miss with only one sample. In such cases, manually enter examples for 2 to 3 rows so Excel can accurately detect the pattern. (There is no official rule on the exact number of rows required for irregular data.)
Third, Flash Fill outputs static text values, not dynamic formulas. If the source data changes, the results will not update automatically, so you must press Ctrl + E again. When sharing files externally, remove the original unmasked column or copy only the masked column to a new document to keep sensitive data secure.
Try typing a single masked example in your current Excel sheet and press Ctrl + E right now.
References
1-Minute Video Summary
Related Articles
- PC Screen Frozen While Gaming or Working? How to Reset Your Graphics Driver in 1 Second with Win + Ctrl + Shift + B
- Stop Searching for Symbols and Emojis: Open the Windows Character Panel in 1 Second with Win + Period (.)
- How to Hide the Taskbar Weather Widget and Search Box on Windows 11 in 10 Seconds
답글 남기기