AI_FILL
Learn from input-output examples and apply the same transformation to new data
=AI_FILL(examples, newInput)Retourne : string
Aperçu
AI_FILL brings the concept of few-shot learning into a Google Sheets formula. Provide a range of input-output example pairs (two columns where the first is the input and the second is the desired output), then give it new input, and the AI infers the transformation pattern and applies it. This is like teaching by example: show the AI three or four examples of what you want, and it figures out the rule and applies it to any new data you provide.
Paramètres
| Paramètre | Type | Requis | Description |
|---|---|---|---|
examples | range | Oui | A two-column range where the first column contains input values and the second column contains the desired output for each input. Provide at least 2-3 examples. |
newInput | string | Oui | The new input value to transform following the pattern learned from the examples. |
Exemples
Normalize company names
Learns a company name normalization pattern from examples and applies it to new entries.
=AI_FILL(A1:B4, C2)Entrée
| Apple Inc. | Apple |
| MICROSOFT CORPORATION | Microsoft |
| amazon.com, inc. | Amazon |
| Alphabet Inc (Google) | Alphabet |
Sortie
MetaGenerate product slugs from names
Learns URL slug formatting from examples and applies it to a new product name.
=AI_FILL(A1:B3, "Ultra HD Wireless Speaker Pro")Entrée
| Ergonomic Office Chair | ergonomic-office-chair |
| Bluetooth Noise-Canceling Headphones | bluetooth-noise-canceling-headphones |
| 4K Webcam with Ring Light | 4k-webcam-with-ring-light |
Sortie
ultra-hd-wireless-speaker-proExtract city from full addresses
Learns to extract just the city name from full US addresses.
=AI_FILL(A1:B3, "742 Evergreen Terrace, Springfield, IL 62704")Entrée
| 123 Main St, San Francisco, CA 94102 | San Francisco |
| 456 Oak Ave, Austin, TX 73301 | Austin |
| 789 Pine Rd, Portland, OR 97201 | Portland |
Sortie
SpringfieldConvert informal text to professional tone
Transforms casual messages into professional communication based on provided examples.
=AI_FILL(A1:B3, "hey can u send me the report asap?")Entrée
| gonna need those files by tmrw | I will need those files by tomorrow. |
| thx for the quick reply! | Thank you for your prompt response. |
| plz check this out when u get a chance | Please review this at your earliest convenience. |
Sortie
Could you please send me the report as soon as possible?Categorize expenses by description
Learns expense categorization rules from examples and applies them to new transactions.
=AI_FILL(A1:B5, "Annual Zoom Pro subscription")Entrée
| Uber ride to airport | Travel |
| Monthly AWS hosting bill | Software |
| Team lunch at restaurant | Meals & Entertainment |
| Google Workspace annual plan | Software |
| Office supplies from Staples | Office Supplies |
Sortie
SoftwareCas d'Usage
Data Cleaning and Normalization
Normalize inconsistent data entries — company names, addresses, product categories, job titles — by showing a few examples of the correct format and letting AI_FILL clean the entire column.
Automated Expense Categorization
Categorize bank transactions and expense entries into accounting categories by providing examples of each category. Process months of transactions in minutes instead of hours.
Customer Feedback Tagging
Tag customer feedback or support tickets with categories like "Bug Report", "Feature Request", "Billing Issue" by showing a few examples of each tag, then auto-tagging the rest.
Content Format Conversion
Convert content between formats — headlines to subheadlines, blog titles to social media posts, formal copy to casual tone — by showing the pattern through a few examples.
SKU and Code Generation
Generate consistent SKU codes, product IDs, or internal reference numbers from product descriptions by teaching the naming convention through examples.
Resume Parsing Standardization
Standardize how candidate information is formatted — converting varied name formats, phone number styles, and degree abbreviations into a consistent format across all applicant records.
Conseils Pro
Include edge cases in your examples. If you are normalizing company names, include examples with "Inc.", "LLC", and "Corp." suffixes, all-caps entries, and lowercase entries. Diverse examples produce robust transformations.
Use AI_FILL as a quick prototyping tool: show 3-5 examples to validate the pattern works, then apply to your full dataset. If results are inconsistent, add more examples before processing everything.
For classification tasks (like categorizing expenses), ensure your examples include at least one entry for each category. The AI cannot assign a category it has never seen in the examples.
Combine AI_FILL with data validation dropdowns. Set up AI_FILL to suggest a category, then use data validation to let users confirm or override the suggestion, building a human-in-the-loop workflow.
This function is incredibly versatile because the transformation can be anything the AI can understand from examples. Format phone numbers from one style to another. Convert full names to "Last, First" format. Translate informal text to formal tone. Extract the city from an address. Normalize inconsistent product categories. Convert dates between formats. The possibilities are endless, and you never have to write a formula, regular expression, or explain the rule — the examples speak for themselves.
AI_FILL is particularly valuable when the transformation is difficult to express as a traditional formula but easy to show by example. Consider normalizing company names: "Apple Inc.", "apple", "APPLE INC", and "Apple, Inc." should all become "Apple". Writing a formula to handle every variation is complex and fragile. With AI_FILL, you just show three examples of the normalization and it handles every new variation, including ones you did not anticipate. The function works with any number of examples (2-3 is usually enough for simple patterns, 5-10 for complex transformations), making it the fastest way to apply consistent data transformations across an entire spreadsheet.
Erreurs Courantes
#ERROR! Examples range must have exactly 2 columnsCause : The examples range does not have exactly two columns (input and output).
Solution : Ensure your example range has exactly two columns. The first column should contain input values and the second should contain the expected output. Example: A1:B5.
#ERROR! Not enough examplesCause : The examples range contains fewer than 2 rows of data.
Solution : Provide at least 2 input-output example pairs. Three to five examples is recommended for reliable pattern recognition.
#ERROR! Could not determine patternCause : The AI could not find a consistent transformation pattern in the provided examples.
Solution : Review your examples for consistency. Make sure they all follow the same transformation logic. Add more examples that clearly demonstrate the pattern, especially diverse ones covering different cases.
Questions Fréquentes
For simple, consistent patterns (like reformatting dates or extracting parts of text), 2-3 examples are usually sufficient. For complex or nuanced transformations (like tone adjustment or multi-step logic), provide 5-10 examples that cover different edge cases. More diverse examples produce better generalization.
AI_FILL works with any text-representable transformation. It can handle number formatting, date conversions, category assignments, and pattern-based generation. However, it returns text output, so you may need to convert the result to a number or date using VALUE() or DATEVALUE() for further calculations.
If examples suggest contradictory rules, the AI will try to find the most consistent pattern. Conflicting examples reduce accuracy. Review your examples to ensure they all demonstrate the same transformation rule. If you have multiple transformation types, create separate AI_FILL formulas for each.
AI_FILL learns by example without you needing to write a prompt describing the transformation. UNLIMITED_AI requires explicit instructions. AI_FILL is better when the pattern is hard to describe in words but easy to show (like data normalization). UNLIMITED_AI is better when you can clearly articulate the task (like "translate to Spanish" or "summarize in one sentence").
Yes, simply expand the example range. If your examples are in A1:B3 and you add more in rows 4-5, update the formula to reference A1:B5. More examples generally improve accuracy, especially if the new examples cover edge cases the original set missed.
The order has minimal impact on results. However, placing your most representative and clear examples first can help. If some examples are edge cases, put the common cases first to establish the base pattern.
Add the incorrect case as a new example with the correct output. This teaches the AI the specific edge case. Two to three additional examples targeting the failure mode usually resolve inconsistencies.
Fonctions Associées
Commencez à utiliser AI_FILL aujourd'hui
Installez Unlimited Sheets pour obtenir AI_FILL et 41 autres fonctions puissantes dans Google Sheets.
