To autofill text patterns in Excel for Microsoft 365 on Windows, type one or two examples in the destination column, select the next blank cell, and press Ctrl+E to run Flash Fill. For results that must update when source text changes, use a formula instead; for text-to-result rules, use an IF formula or a lookup table. The right method depends on whether you are creating a text pattern or assigning a value based on existing text.
What You’ll Need
- A worksheet with the source text entered consistently, such as names, codes, or status labels.
- A blank destination column for the results. Leave the source data in place while setting up the fill.
- For formula-based rules, a clear description of what each text value should return. If you have many rules, prepare a small two-column mapping table.
- Microsoft Excel for Microsoft 365 on Windows for the steps below. Flash Fill and the formula methods are also available in many current Excel versions, though ribbon placement and shortcuts can vary slightly.
Step-by-Step
- Decide what “based on text” means in your sheet. Use Flash Fill when you want to extract, combine, or rearrange characters from text by showing Excel an example. For instance, you might turn a full name into a first name, combine a city and region, or extract the letters before a hyphen. Use a formula when the result must change automatically as the source text changes. Use an IF formula for a small number of rules, or a lookup table when many text labels have assigned results.
- Set up source and output columns. Put the original text in one column and reserve the adjacent column for the result. For example, put full names in A2:A20 and the desired first names in B2:B20. Add a heading to each column, such as Full Name and First Name. Avoid placing output examples inside the source column, because that can make Flash Fill interpret the wrong pattern.
- Use Flash Fill for a text pattern. In the first output cell, type the result you want for the first source row. With full names in A2:A20, for example, if A2 contains “Jordan Lee,” type “Jordan” in B2. Move to B3, then choose Data > Flash Fill, or press Ctrl+E on Windows. Excel examines the examples and fills the rest of the output column when it can detect a consistent pattern. Check several results, including rows with middle names, extra spaces, or different punctuation.
- Give Flash Fill more examples if the pattern is unclear. If Excel does not fill the column, or its preview is wrong, type a correct result for another source row. Choose Data > Flash Fill again. Two or more examples can show the intended rule more clearly, especially when names or codes have different formats. Flash Fill produces ordinary text values, not a live formula, so changing a source cell later will not automatically update its filled result. Run Flash Fill again or use a formula if the output needs to stay in sync.
- Use a formula for a simple text condition. Suppose A2:A20 contains payment statuses and you want “Complete” for Paid and “Review” for anything else. With the status in A2 and the result starting in B2, enter
=IF(A2="Paid","Complete","Review")in B2. This returns Complete when A2 exactly contains Paid; otherwise it returns Review. Press Enter, select B2, and drag the small square at its lower-right corner (the fill handle) down the column. Excel adjusts A2 to A3, A4, and so on. If the formula is copied across rows, each output remains linked to its source row. - Use a lookup table when several labels have different results. Create a mapping table with source labels in F2:F4 and their desired results in G2:G4. For example, F2:F4 could contain Paid, Pending, and Cancelled; G2:G4 could contain Complete, Waiting, and Closed. With the status to evaluate in A2 and the output formula in B2, enter
=XLOOKUP(A2,$F$2:$F$4,$G$2:$G$4,"Unmapped"). Paid returns Complete, Pending returns Waiting, and an unlisted label returns Unmapped. Fill the formula down. XLOOKUP is available in Microsoft 365 and Excel 2021 or later. The dollar signs keep the mapping ranges fixed as you copy the formula. - Check the fill and choose how to extend it. Compare the output against the source for the first, middle, and last rows. For formula results, confirm the cell references change by row while lookup ranges stay fixed. To fill a formula down quickly, select the formula cell and the blank cells below it, then press Ctrl+D on Windows. You can also double-click the fill handle when the neighboring source column has a continuous run of data. If you double-click, check that Excel filled exactly as far as you intended.
On Excel for Mac, Flash Fill is available from Data > Flash Fill; the shortcut may be Control+E depending on your keyboard and Excel settings. If that shortcut is unavailable, use the ribbon command. Excel for the web may offer Flash Fill depending on the current version and account; if the command is absent, use a formula or open the workbook in the desktop app. The fill handle is available in desktop Excel and can also be used in the web app.
Common Mistakes
Expecting a single text entry to create a sequence. Dragging the fill handle from one ordinary text cell usually copies the same text; it does not guess a new value. Enter two examples when you want Excel to continue a series, or use Flash Fill to demonstrate a transformation. Flash Fill and dragging the fill handle are different features.
Using Flash Fill for a rule that should stay live. Flash Fill writes fixed results. If A2 changes from Paid to Pending later, a Flash Fill result beside it remains what it was. Use an IF formula or lookup formula when outputs need to recalculate.
Giving inconsistent examples. Flash Fill may infer an unintended pattern if one example includes a middle initial and another does not, or if you type a space or punctuation inconsistently. Correct the examples first, then run Flash Fill again and review the entire output.
Forgetting exact text differences. “Paid,” “Paid ” with a trailing space, and “paid” may not behave as expected in a formula rule or lookup. Excel’s basic comparisons are generally not case-sensitive, but extra spaces and different spellings still matter. Clean or standardize the source values if needed.
Allowing a lookup table to move. If you copy a lookup formula down without dollar signs around the table ranges, the ranges can shift and return incorrect results. Use absolute references such as $F$2:$F$4 and $G$2:$G$4, then verify a few copied formulas.
Tips
Use Flash Fill when you need a quick, one-time transformation and the examples make the pattern obvious. It is particularly useful for splitting or combining text without building a formula. For repeated work, or a sheet that other people will update, formulas are usually easier to maintain because their logic is visible in the cells.
Make the rules explicit before filling. Decide what should happen to blanks, misspellings, and labels that are not on your list. For example, a lookup formula can return “Unmapped” for an unknown label, making exceptions easy to find. Filter the results for that marker and correct the source or add the missing mapping.
For a very simple repeatable task, convert your data range to an Excel table with Insert > Table. When you enter a formula in a table column, Excel commonly fills it through that column and applies it to new rows. Check the formula and table boundaries after converting, especially if the worksheet has blank rows or unrelated data nearby.
Keep source text and output text in separate columns, and label both. This makes it easier to audit the results and prevents a later paste from overwriting the original data. Before replacing an existing output column, consider copying the worksheet or saving a separate version of the workbook.
Troubleshooting
Flash Fill does not appear to do anything: Confirm you selected a cell below your example and that the source column is next to the destination column. Type another correct example and choose Data > Flash Fill again. If the command is unavailable, check whether you are using a version of Excel that supports it, or try the desktop app. Also check Excel’s Flash Fill setting under File > Options > Advanced; the setting is called Automatically Flash Fill and should be enabled for automatic suggestions. You can still invoke the ribbon command where available.
Flash Fill returns the wrong pattern: Undo immediately with Ctrl+Z, add clearer examples, and run it again. Do not assume a plausible-looking first result means every row is correct. Rows with inconsistent delimiters, extra words, or missing text may need individual correction or a more controlled formula.
The formula shows the formula text instead of a result: The cell may be formatted as Text, or the entry may have a leading apostrophe. Change the cell format to General, edit the formula, and press Enter. Also check that Show Formulas is not turned on; press Ctrl+` on Windows to toggle it. On Mac, the key combination can differ by keyboard layout, so use the Formulas tab’s Show Formulas command if needed.
A lookup returns Unmapped unexpectedly: Compare the source value with the mapping label, including spaces and punctuation. Check that the mapping ranges cover all table rows and that the formula uses the correct return range. If the source is intended to match a label but contains unwanted spaces, clean the data or use a deliberate cleanup step rather than hiding a mismatch.
The fill handle is missing: In Windows desktop Excel, open File > Options > Advanced and enable “Enable fill handle and cell drag-and-drop.” On Mac, check Excel > Settings (or Preferences, depending on version) > Edit for the fill-handle option. You can use Ctrl+D on Windows to fill down selected cells even when dragging is inconvenient.
Frequently Asked Questions
Can Excel autofill a word based on another cell’s text?
Yes. Use an IF formula for a small condition, or a lookup table and XLOOKUP for a list of labels and results. Flash Fill is for recognizing a text transformation from examples, not maintaining a changing rule.
How do I autofill only part of a text string?
Type the desired extracted result beside the first source value and use Flash Fill with Ctrl+E on Windows. If the text format is consistent and the result must update, use an appropriate text formula instead.
Will Flash Fill update when I edit the original text?
No. Flash Fill creates fixed values. Re-run it after source changes, or replace it with a formula if you need linked results.
Why does dragging the fill handle repeat the same text?
A single text value usually has no sequence for Excel to continue, so Excel copies it. Enter two values that show the sequence, use Flash Fill for a demonstrated text pattern, or use a formula for a calculated result.
Can I use Flash Fill in Excel for the web?
Availability can depend on the current web version and account. Look for Data > Flash Fill; if the command is not there, use a formula or open the workbook in desktop Excel. Formulas such as IF and XLOOKUP can be entered in supported web versions.
Which method should I choose for a long list of categories?
Use a two-column mapping table with XLOOKUP rather than a long chain of nested IF formulas. It is easier to add or correct labels in the table and to identify unmatched text.
Related
- Checkbox in Excel With Text: Two Practical Methods
- How to Add a Checkbox in Excel Ribbon
- How to Add Checkboxes to an Excel Table in Microsoft 365
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Column Q in Excel: Number, Location, and How to Use ItColumn Q in Excel is the 17th column, between P and R. To go straight to it, enter…
- How to Name a Dropdown List in Excel with a Named RangeTo name a dropdown list in Excel, give its source cells a name, then use that name as…
- Google Sheets: Count Dates in a Month with COUNTIFSTo count dates in a particular month in Google Sheets, use COUNTIFS with a start date and the…
- Import CSV Into Excel Without Losing Leading ZerosTo import a CSV into Excel without losing leading zeros, use Data > From Text/CSV in Excel for…
- How to Clear Cache Files in Outlook SafelyTo clear Outlook’s local cache on Windows, close classic Outlook, rename the account’s .ost file, then reopen Outlook…
- Google Sheets Dropdown: Show Only Selected ValuesYes. In Google Sheets, a dropdown cell normally displays only the value you selected after the menu closes;…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.