Excel Formula Prompt Templates
AI prompt templates for Excel formulas. Create complex formulas, VLOOKUP, pivot tables, and more.
Overview
Excel formulas can get complicated fast. These prompts help you build formulas step by step, troubleshoot errors, and learn why formulas work the way they do. Whether you're stuck on a VLOOKUP or trying to build a complex nested formula, these templates will get you to a working solution.
Best Practices
Describe your data layout clearly: which column has what, where headers are located
Include actual cell references when you can, not just descriptions
Mention your Excel version since some functions aren't available in older versions
Explain what result you expect so the AI can verify the formula works
Ask for explanation of how the formula works if you want to learn
Prompt Templates
1. VLOOKUP and XLOOKUP Builder
I need a [VLOOKUP/XLOOKUP] formula. Here's my setup: Lookup value: [CELL_OR_VALUE] Lookup table: [TABLE_RANGE] Column structure: [DESCRIBE_COLUMNS] I want to return: [WHAT_TO_RETURN] What if no match: [ERROR_HANDLING] Excel version: [VERSION]
I need an XLOOKUP formula. Lookup value: Cell A2 (product ID). Lookup table: Sheet2!A:D. Column structure: A=Product ID, B=Product Name, C=Category, D=Price. I want to return: The price. What if no match: Show 'Not Found'. Excel version: Microsoft 365.
=XLOOKUP(A2, Sheet2!A:A, Sheet2!D:D, "Not Found") This searches for the product ID in A2, looks through Sheet2 column A to find it, and returns the corresponding price from column D. If the ID doesn't exist, it shows 'Not Found' instead of an error. Tip: XLOOKUP is better than VLOOKUP here because it can search in any direction and has cleaner error handling built in.
When you need to pull data from another table based on a matching value
- •Use XLOOKUP over VLOOKUP when possible. It's more flexible and easier to read
- •Specify if you need approximate match for things like tax brackets or grade ranges
2. Conditional Formula Builder
I need a formula that returns different values based on conditions: If [CONDITION_1]: return [RESULT_1] If [CONDITION_2]: return [RESULT_2] [ADDITIONAL_CONDITIONS] Otherwise: return [DEFAULT] Data is in: [CELL_REFERENCES] Prefer: [IF_STATEMENTS/IFS/SWITCH/CHOOSE]
I need a formula that returns different values based on conditions: If sales >= 100000: return 'Platinum'. If sales >= 50000: return 'Gold'. If sales >= 25000: return 'Silver'. Otherwise: return 'Bronze'. Data is in: B2 (sales amount). Prefer: whatever is cleanest.
=IFS(B2>=100000, "Platinum", B2>=50000, "Gold", B2>=25000, "Silver", TRUE, "Bronze") IFS is cleaner than nested IF statements here. It checks conditions in order and returns the first match. The TRUE at the end acts as the 'else' case. Alternative with IF: =IF(B2>=100000,"Platinum",IF(B2>=50000,"Gold",IF(B2>=25000,"Silver","Bronze"))) but this gets messy with more conditions.
When you need to categorize data or return different results based on criteria
- •Order conditions from most restrictive to least restrictive
- •IFS needs Excel 2019 or later. Use nested IF for older versions
3. Array Formula and SUMIFS
I need to [SUM/COUNT/AVERAGE] values that meet multiple criteria: Values to aggregate: [VALUE_COLUMN] Criteria 1: [COLUMN] must be [CONDITION] Criteria 2: [COLUMN] must be [CONDITION] [ADDITIONAL_CRITERIA] Data range: [RANGE] Headers in row: [ROW_NUMBER]
I need to SUM values that meet multiple criteria: Values to aggregate: Column D (sales amount). Criteria 1: Column A (region) must be 'West'. Criteria 2: Column B (product) must be 'Widget'. Criteria 3: Column C (date) must be in March 2024. Data range: A1:D500. Headers in row 1.
=SUMIFS(D2:D500, A2:A500, "West", B2:B500, "Widget", C2:C500, ">=2024-03-01", C2:C500, "<=2024-03-31") This adds up all sales in column D where region is West, product is Widget, and date falls in March 2024. Note: For dates, you need two criteria (>= start and <= end) to define a range. If you're referencing cells for the date range, use: =SUMIFS(D2:D500, A2:A500, "West", B2:B500, "Widget", C2:C500, ">="&E1, C2:C500, "<="&E2) where E1 and E2 contain your dates.
When you need to sum, count, or average based on multiple conditions
- •In SUMIFS, the sum range comes first. In SUMIF, it comes last. Don't mix them up
- •Use COUNTIFS for counting and AVERAGEIFS for averages with the same pattern
4. Formula Troubleshooter
My Excel formula isn't working. Help me fix it. Formula: [YOUR_FORMULA] Expected result: [WHAT_YOU_EXPECTED] Actual result: [WHAT_HAPPENED] Error message (if any): [ERROR] Cell values involved: [SAMPLE_VALUES] What I'm trying to do: [GOAL]
My Excel formula isn't working. Formula: =VLOOKUP(A2, B:D, 3, FALSE). Expected result: The price from column D. Actual result: #N/A error. Error message: #N/A. Cell values: A2 contains 'SKU-001', Column B has 'SKU-001' in row 5. What I'm trying to do: Look up a product SKU and return its price.
The issue is likely one of these: 1. Extra spaces: 'SKU-001' might have trailing spaces in one location but not the other. Fix: =VLOOKUP(TRIM(A2), B:D, 3, FALSE) 2. Case sensitivity: VLOOKUP isn't case-sensitive by default, so this probably isn't it, but check if one has special characters. 3. Number stored as text: If SKU-001 is stored as text in A2 but as a number in column B (or vice versa), they won't match. To diagnose: In a helper cell, use =A2=B5 to see if Excel thinks they're equal. If FALSE, there's a hidden difference. Try =TRIM(A2)=TRIM(B5) to check for spaces. Quick fix that usually works: =VLOOKUP(TRIM(A2), B:D, 3, FALSE) handles the most common cause (extra spaces).
When a formula returns an error or unexpected result
- •Include the actual cell values, not just descriptions
- •#N/A usually means the lookup value wasn't found. Check for spaces, typos, or type mismatches
5. Date and Time Calculations
I need a formula for date/time calculations: What I have: [INPUT_DATA] What I need: [DESIRED_OUTPUT] Date format in my sheet: [FORMAT] Workdays only? [YES/NO] Specific requirements: [DETAILS]
What I have: Start date in A2, end date in B2. What I need: Number of business days between them, excluding US holidays. Date format in my sheet: MM/DD/YYYY. Workdays only? Yes. Specific requirements: I have a list of holiday dates in Sheet2!A:A.
=NETWORKDAYS(A2, B2, Sheet2!A:A) This counts workdays (excludes weekends) between start and end dates, and also excludes any dates listed in your holiday column. The dates in Sheet2!A:A should be actual date values, not text. If you need to exclude only specific weekends (like if Saturday is a workday in your region): =NETWORKDAYS.INTL(A2, B2, "0000011", Sheet2!A:A). The string "0000011" means Mon-Fri are workdays (0) and Sat-Sun are weekends (1). Customize as needed. Note: Both dates are inclusive in the count.
Calculating durations, deadlines, aging, or any date math
- •NETWORKDAYS.INTL lets you define custom weekend days for different regions
- •Ensure your holiday list contains actual dates, not text that looks like dates
Common Mistakes to Avoid
Not describing the data layout clearly, leading to formulas with wrong column references
Mixing up similar functions like SUMIF and SUMIFS, which have different argument orders
Forgetting to specify Excel version, resulting in formulas that use unavailable functions
Frequently Asked Questions
Excel formulas can get complicated fast. These prompts help you build formulas step by step, troubleshoot errors, and learn why formulas work the way they do. Whether you're stuck on a VLOOKUP or trying to build a complex nested formula, these templates will get you to a working solution.
Related Templates
SQL Query Prompt Templates
AI prompt templates for writing SQL queries. Create SELECT, JOIN, aggregate, and complex queries.
Data Analysis Prompt Templates
AI prompt templates for data analysis. Extract insights, identify patterns, and interpret results.
Data Visualization Prompt Templates
AI prompt templates for data visualization. Create effective charts, dashboards, and visual reports.
Have your own prompt to optimize?