How to Use Smart Fill in Google Sheets to Automatically Detect and Complete Data Patterns

Cleaning and formatting raw datasets is one of the most time-consuming chores in spreadsheet management. Splitting full names into separate first and last name columns, extracting postal codes from unformatted street addresses, extracting domain names from email addresses, or standardising telephone numbers typically requires writing complex nested formulas like REGEXEXTRACT, LEFT, MID, and FIND.

To eliminate this formula complexity for everyday users, Google integrated machine learning intelligence directly into spreadsheet cells through Smart Fill. Similar to Flash Fill in desktop spreadsheet apps, Smart Fill detects patterns in your manual data entry, predicts the underlying extraction logic, and automatically generates both the output values and the accompanying spreadsheet formula.

How Smart Fill Functions in Google Sheets

Smart Fill operates as an on-the-fly machine learning assistant. As you manually enter values in an adjacent column next to an existing dataset:

• Pattern Recognition: Smart Fill compares your manual entries against neighbouring columns to identify relationships (such as extracting the first word, pulling digits, or reordering text).
• Formula Generation: Rather than merely pasting static text strings, Smart Fill drafts the exact native formula (such as =SPLIT(), =UPPER(), or =INDEX()) required to calculate the result dynamically.
• Collaborator Transparency: Because it provides real formulas, other team members can inspect the calculation logic and the values will update automatically if source data changes.

Common Practical Use Cases for Smart Fill

Task	Source Data Example	Smart Fill Output	Generated Formula Type
Splitting Names	Sarah Jenkins	Sarah	=LEFT(A2, FIND(" ", A2)-1)
Extracting Domains	[email protected]	company.co.uk	=RIGHT(A2, LEN(A2)-FIND("@", A2))
Formatting Numbers	07911123456	+44 7911 123456	Text transformation string
Combining Attributes	Col A: Blue, Col B: Medium	Blue - Medium	=CONCATENATE(A2, " - ", B2)

Triggering and Accepting Smart Fill Suggestions

Using Smart Fill requires only typing a couple of sample examples:

1. Open your spreadsheet in Google Sheets. Ensure your source data resides in contiguous columns (e.g., column A contains full names).
2. Click into the adjacent empty column in row 2 (e.g., cell B2).
3. Type the desired extracted value manually (for example, if A2 is “David Miller”, type “David” in B2) and press Enter.
4. In cell B3, begin typing the next extracted name (e.g., “Jessica”).
5. Google Sheets instantly recognises the pattern. A translucent grey preview fills the entire column below your cursor, accompanied by an interactive suggestion bubble stating: Smart Fill: View formula.
6. Review the previewed values. If they look accurate, press Enter or click the checkmark button in the suggestion badge to apply the fill.
7. The entire column populates with values and underlying formulas instantly.

Verifying and Editing the Generated Formulas

Once accepted, click into any of the populated cells and inspect the formula bar (fx) at the top of your screen. Google Sheets displays the exact formula it synthesised. If a specific edge case in your dataset failed (such as a hyphenated last name or middle initials), you can tweak the formula directly in that specific row without affecting the rest of the column.

Enabling or Disabling Smart Fill

Smart Fill is enabled by default across all Google Workspace accounts. To verify that it is turned on, or to temporarily disable it if you prefer manual entry:

1. Click Tools in the top menu bar of Google Sheets.
2. Hover over Autocomplete.
3. Ensure that Enable Smart Fill suggestions is checked.
4. You can also toggle Enable formula suggestions from this same menu to manage automated math recommendations.

Best Practices for Flawless Pattern Recognition

• Keep Columns Adjacent: Ensure there are no completely blank separator columns between your source data and the column where you want Smart Fill to operate.
• Provide Two Consistent Samples: Typing two clear sample rows gives the machine learning model sufficient confidence to suggest patterns across thousands of subsequent rows.
• Standardise Source Data: Clean obvious typos or double spaces in your source column before triggering Smart Fill to ensure formulas parse delimiter spaces accurately.