📊 Excel & Spreadsheets Prompts
Formulas, reports, pivot tables, charts, VBA macros
← All Categories
6 prompts
in Formulas & Functions
Nested IF Statements for Grading
Write a nested IF formula in Excel that assigns letter grades based on numeric scores: 90-100 = A, 80-89 = B, 70-79 = C, 60-69 = D, below 60 = F. Provide: the exact formula, a version using IFS function (Excel 365), explanation of the logic flow, and how to add plus/minus grades (A+, A, A-, etc.) with more conditions.
💡 IFS function is cleaner than nested IFs. Use it if your Excel version supports it.
Text Manipulation Formulas
Show me essential Excel text formulas with examples: 1) Extract first name from full name using LEFT+FIND, 2) Extract domain from email using MID+FIND, 3) Combine first and last name with CONCATENATE or ampersand, 4) Clean extra spaces with TRIM, 5) Convert text to proper case with PROPER, 6) Remove non-printable characters with CLEAN, 7) Extract numbers from mixed text using VALUE+SUBSTITUTE. Provide the exact formula for each with sample data.
💡 In Excel 365, use TEXTBEFORE and TEXTAFTER — they are much simpler than LEFT+FIND combinations.
VLOOKUP for Matching Data Between Sheets
Explain how to use VLOOKUP in Excel to match data between two sheets. Scenario: Sheet1 has employee IDs and names, Sheet2 has employee IDs and salaries. Write the exact VLOOKUP formula to pull salary data into Sheet1 based on matching employee IDs. Include: the formula, explanation of each parameter (lookup_value, table_array, col_index_num, range_lookup), common errors and how to fix them (#N/A, wrong results), and when to use FALSE vs TRUE for the last parameter.
💡 Use XLOOKUP instead if you have Excel 365 — it is more flexible and does not require the lookup column to be first.
INDEX MATCH as VLOOKUP Alternative
Show me how to use INDEX MATCH in Excel as a more powerful alternative to VLOOKUP. Create examples for: basic left-to-right lookup, reverse lookup (looking up values to the left), two-criteria lookup using INDEX MATCH MATCH, and case-sensitive lookup. For each example, provide the exact formula, explain how it works step by step, and show sample data.
💡 INDEX MATCH is faster on large datasets and works in any direction, unlike VLOOKUP.
SUMIFS with Multiple Criteria
Explain SUMIFS in Excel with 3 practical examples: 1) Sum sales where region is East AND month is January, 2) Sum expenses where amount is greater than 1000 AND category is Marketing, 3) Sum hours where employee name matches AND date falls within a specific range. For each example, provide the formula, sample data layout, and explain the difference between SUMIF (single criteria) and SUMIFS (multiple criteria).
💡 SUMIFS criteria ranges must all be the same size. The sum range comes first, unlike SUMIF.
Dynamic Date Formulas
Give me the most useful Excel date formulas for business: 1) Calculate age from birthdate using DATEDIF, 2) Find the last day of any month using EOMONTH, 3) Calculate business days between two dates using NETWORKDAYS, 4) Add 30 business days to a date using WORKDAY, 5) Extract year, month, day separately using YEAR/MONTH/DAY, 6) Calculate days until deadline. Provide exact formulas and sample usage for each.
💡 DATEDIF is a hidden function — it will not appear in autocomplete but it works.