ChatGPT Prompts for Excel That Save Hours

I need an Excel lookup formula to find a value across sheets or tables. My Excel version is [Excel 365 / Excel 2019 / Excel 2016].

Data setup:
- Lookup value is in cell: [e.g., A2]
- Lookup table is in: [e.g., Sheet2!A:D or a named range]
- I want to return the value from column: [column number or header name]
- The lookup column in the table is: [column letter or position]

Specific requirements:
- Match type: [exact match / approximate match / wildcard match]
- If no match is found, return: [blank / 0 / "Not Found" / a default value]
- The lookup value might have: [leading/trailing spaces / different capitalization / partial matches]
- I need to look up based on: [single criterion / multiple criteria — if multiple, list all columns to match on]

Provide:
1. A VLOOKUP formula for backward compatibility
2. An XLOOKUP formula (if Excel 365) with error handling
3. If multiple criteria are needed, an INDEX/MATCH or XLOOKUP alternative that handles it
4. A brief explanation of why XLOOKUP is preferred over VLOOKUP for new spreadsheets
5. What breaks when columns are inserted or deleted, and how to make the formula resilient

Write a nested IF formula (or IFS formula for Excel 365) to categorize data based on multiple conditions.

Cell to evaluate: [e.g., B2 — contains a number, text, or date]
Data type in that cell: [number / text / date / percentage]

Conditions and outputs (list in priority order):
1. If [condition 1, e.g., B2 >= 90], return [output, e.g., "A"]
2. If [condition 2, e.g., B2 >= 80], return [output, e.g., "B"]
3. If [condition 3, e.g., B2 >= 70], return [output, e.g., "C"]
4. If [condition 4, e.g., B2 >= 60], return [output, e.g., "D"]
5. If none of the above, return [default output, e.g., "F"]

Additional requirements:
- The cell might be blank — handle blanks by returning: [value]
- The cell might contain errors — wrap in IFERROR: [yes/no]
- I need this to work with: [text comparisons / date comparisons / AND/OR logic across multiple columns]
- Number of conditions: [specify if more than 5]

Provide:
1. A nested IF formula
2. An IFS formula (Excel 365) as a cleaner alternative
3. A SWITCH formula if the logic is based on exact value matching
4. A CHOOSE approach if applicable
5. Explain the maximum nesting depth for IF statements and what to do when you exceed it

Guide me through setting up a pivot table to answer the following business question:

Business question: [e.g., "What is the total revenue by product category per quarter, and which sales rep contributes the most?"]

My source data:
- Data range: [e.g., A1:H5000]
- Columns available: [list all column headers — e.g., Date, Sales Rep, Product, Category, Region, Quantity, Unit Price, Revenue]
- Date column format: [e.g., MM/DD/YYYY or serial date numbers]
- Any columns with inconsistent data: [e.g., "Region has blanks and 'N/A' values mixed in"]

What I want to see:
- Row labels: [which fields in rows — e.g., Category, then Product as sub-rows]
- Column labels: [which fields in columns — e.g., Quarter or Month]
- Values: [what to aggregate — e.g., SUM of Revenue, COUNT of Orders, AVERAGE of Unit Price]
- Filters: [any slicers or report filters — e.g., filter by Region or Sales Rep]

Provide step-by-step instructions for:
1. How to prepare the data before creating the pivot table (formatting as a Table, removing blanks, standardizing values)
2. Exact steps to create the pivot table in Excel (Insert > PivotTable flow)
3. How to group dates by month/quarter/year in the pivot table
4. How to add calculated fields (e.g., profit margin = revenue - cost)
5. How to add conditional formatting to highlight top/bottom values
6. Recommended slicers to add for interactive filtering
7. Common pivot table mistakes that produce wrong totals (and how to fix them)

Write a VBA macro to automate the following repetitive task in Excel. I need complete, working code I can paste into the VBA editor (Alt+F11).

Task description: [describe the repetitive task in detail — e.g., "Every Monday I open 5 CSV files from a folder, copy column A and C from each file into a master sheet, add a column with the filename, and save the master sheet as a new file with today's date"]

Current manual process:
1. [Step 1 of what you currently do manually]
2. [Step 2]
3. [Step 3]
4. [Step 4]
5. [Step 5]

Technical details:
- Workbook structure: [how many sheets, their names, what's on each]
- File paths involved: [folder paths for input/output files]
- Data range: [fixed range like A1:D100, or dynamic — "varies each week, could be 50-500 rows"]
- Formatting requirements: [any specific formatting to apply — bold headers, currency format, column widths]
- Error scenarios: [what might go wrong — missing files, empty sheets, locked workbook]

Provide:
1. Complete VBA Sub procedure with comments on every section
2. Error handling with MsgBox alerts for common failures
3. A progress indicator (status bar update or MsgBox) for long-running macros
4. Instructions for assigning the macro to a button on the spreadsheet
5. How to set it to run automatically on workbook open (if applicable)
6. Security notes: what Macro Security settings are needed to run this

I have a messy Excel dataset that needs cleaning before analysis. Help me fix the following issues using formulas, Power Query, or step-by-step instructions.

Dataset details:
- Sheet name: [name]
- Data range: [e.g., A1:J2000]
- Number of rows: [approximate]

Issues to fix (check all that apply):
1. **Duplicates**: Column [X] has duplicate values. Rule for keeping: [keep first occurrence / keep last / keep the row with the most complete data]
2. **Extra spaces**: Column [X] has leading, trailing, or double spaces in text
3. **Inconsistent capitalization**: Column [X] has mixed case — e.g., "new york", "New York", "NEW YORK"
4. **Date formats**: Column [X] has dates in mixed formats — some as [MM/DD/YYYY], some as [DD-Mon-YY], some stored as text
5. **Number formatting**: Column [X] has numbers stored as text (green triangle in corner), or has currency symbols/commas embedded
6. **Blank rows/cells**: [scattered blanks that should be filled down / entirely blank rows to delete]
7. **Merged cells**: [which columns have merged cells that need unmerging]
8. **Special characters**: Column [X] has line breaks, non-breaking spaces, or invisible Unicode characters
9. **Inconsistent categories**: Column [X] has typos or variants of the same category — e.g., "US", "USA", "United States", "U.S.A."

For each issue, provide:
1. A formula-based fix (TRIM, CLEAN, PROPER, SUBSTITUTE, etc.)
2. A Power Query step-by-step alternative (if the dataset is large or this needs to be repeatable)
3. How to verify the fix worked (a validation formula or conditional formatting rule)

Write a SUMIFS or COUNTIFS formula with multiple conditions. My data is in [sheet name] and the range is [e.g., A1:F5000].

What I want to calculate:
- Function: [SUMIFS / COUNTIFS / AVERAGEIFS / MAXIFS / MINIFS]
- Sum/count range: [column letter and what it contains — e.g., "Column E: Revenue in dollars"]

Criteria (list all conditions):
1. Column [X]: [condition — e.g., equals "East", or is greater than 100, or contains "Pro"]
2. Column [X]: [condition — e.g., date is in Q1 2025, or date is >= a cell reference]
3. Column [X]: [condition — e.g., is not blank, or does not equal "Cancelled"]
4. Column [X]: [condition — e.g., matches a wildcard pattern like "*subscription*"]

Additional requirements:
- Date filtering: [by specific month / by quarter / by year / between two dates in cells]
- The criteria values come from: [hardcoded in formula / cell references / a dropdown list]
- I need to combine SUMIFS with: [division for averages / another SUMIFS for percentages / SUMPRODUCT for OR logic]
- Handling: [what if no rows match — should it return 0 or blank?]

Provide:
1. The complete SUMIFS/COUNTIFS formula
2. If OR logic is needed across criteria (SUMIFS doesn't natively support OR), provide a SUMPRODUCT alternative
3. A dynamic version where criteria reference cells instead of hardcoded values
4. How to verify the formula is correct by cross-checking with a filtered subtotal
5. Performance note: whether this formula will slow down on large datasets and alternatives if so

Set up conditional formatting rules in Excel to visually highlight important patterns in my data.

Data details:
- Range to format: [e.g., A2:F500]
- Column(s) to evaluate: [which columns contain the values to check]
- What each column contains: [numbers / percentages / dates / text / status values]

Formatting rules I need (list all):
1. [Rule 1: e.g., "Highlight rows where column D (Status) equals 'Overdue' in red background with white text"]
2. [Rule 2: e.g., "Apply a 3-color scale to column E (Revenue) — red for lowest, yellow for midpoint, green for highest"]
3. [Rule 3: e.g., "Add data bars to column F (Progress %) — blue bars, no negative axis"]
4. [Rule 4: e.g., "Highlight cells in column C where the date is within the next 7 days in yellow"]
5. [Rule 5: e.g., "Bold the entire row if column A (Priority) equals 'High'"]
6. [Rule 6: e.g., "Highlight duplicate values in column B (Email) in orange"]

Special requirements:
- Rules should apply to the entire row based on one column's value: [yes/no]
- Some rules should stop if true (don't apply lower-priority rules): [which ones]
- I need icon sets: [traffic lights / arrows / flags — for which column]
- The formatting should update automatically when data changes: [yes/no]

For each rule, provide:
1. Step-by-step: Home > Conditional Formatting > [exact menu path]
2. The custom formula if using "Use a formula to determine which cells to format" (with correct $ signs for row/column locking)
3. The exact colors/formatting to apply
4. The rule priority order (which rules should be evaluated first)
5. How to copy these rules to other sheets or ranges

Recommend the best Excel chart types for my dashboard and provide setup instructions for each one.

Dashboard purpose: [e.g., "Monthly sales performance dashboard for the leadership team"]
Audience: [who will view this — executives / managers / analysts / clients]
Update frequency: [daily / weekly / monthly / quarterly]

Data I want to visualize:
1. [Metric 1: e.g., "Monthly revenue trend over 12 months — show growth trajectory"]
2. [Metric 2: e.g., "Revenue by product category — show relative contribution"]
3. [Metric 3: e.g., "Actual vs. target performance — show gap clearly"]
4. [Metric 4: e.g., "Top 10 customers by revenue — ranked comparison"]
5. [Metric 5: e.g., "Regional breakdown — geographic distribution"]
6. [Metric 6: e.g., "Key KPIs — single numbers like total revenue, growth %, customer count"]

For each metric, provide:
1. Recommended chart type and why (not just "bar chart" — explain why a clustered bar beats a stacked bar for this data)
2. A second-choice chart type as an alternative
3. Step-by-step Excel instructions to create it
4. Formatting recommendations: colors, axis labels, data labels, legend placement
5. How to make the chart dynamic (linked to a dropdown or slicer for filtering)
6. Chart types to AVOID for this data and why (e.g., "don't use a pie chart for 15+ categories")

Also recommend:
- Dashboard layout: how to arrange 6 charts on one sheet for maximum readability
- A color palette (3-5 colors) that works for colorblind viewers
- How to add KPI summary cards (big numbers with sparklines) at the top of the dashboard

Write an INDEX/MATCH formula for a lookup that VLOOKUP can't handle. My Excel version is [Excel 365 / Excel 2019 / Excel 2016].

Lookup scenario: [describe what you're trying to find — e.g., "Find the sales rep name for the most recent order matching a specific product and region"]

Data structure:
- Data table range: [e.g., A1:G500]
- Column A: [header — e.g., Order ID]
- Column B: [header — e.g., Date]
- Column C: [header — e.g., Sales Rep]
- Column D: [header — e.g., Product]
- Column E: [header — e.g., Region]
- Column F: [header — e.g., Revenue]
- Column G: [header — e.g., Status]

What makes this lookup complex (check all that apply):
- [ ] Looking up a value to the LEFT of the match column (VLOOKUP can't do this)
- [ ] Multiple criteria lookup (match on 2+ columns simultaneously)
- [ ] Return the Nth match (not just the first)
- [ ] Case-sensitive matching required
- [ ] Lookup the last/most recent match in the list
- [ ] Two-way lookup (match both a row and column header)
- [ ] Return multiple results from one lookup

Provide:
1. The INDEX/MATCH formula with detailed comments explaining each part
2. If multiple criteria: the array formula version (Ctrl+Shift+Enter) and the non-array alternative
3. For Excel 365: a FILTER function alternative that's simpler
4. How to handle #N/A errors gracefully
5. Performance comparison: when INDEX/MATCH is faster than VLOOKUP and when it doesn't matter

Help me convert Excel data to [CSV / JSON / XML / SQL INSERT statements] format. I need both a manual method and an automated approach.

Source data:
- Sheet name: [name]
- Data range: [e.g., A1:F200, with headers in row 1]
- Column headers: [list them — e.g., id, name, email, date_joined, plan, revenue]
- Data types per column: [text, number, date, boolean — specify for each]

Target format: [CSV / JSON / JSON array / JSON nested objects / XML / SQL INSERT / Python dict]

Conversion requirements:
- Date format in output: [ISO 8601 YYYY-MM-DD / Unix timestamp / MM/DD/YYYY / custom]
- Number formatting: [no commas, 2 decimal places / integer only / keep currency symbol]
- Text encoding: [UTF-8 / ASCII / handle special characters like accented letters and emoji]
- Null/blank handling: [output as null / empty string / skip the field entirely / use a default value]
- Nested structure (for JSON): [flat object per row / nested — describe the nesting, e.g., "group orders under each customer"]

Provide:
1. A manual method using Excel's built-in Save As / Power Query export
2. A VBA macro that exports the data in the exact format needed
3. A formula-based approach: TEXTJOIN or CONCATENATE to build JSON/CSV strings row by row
4. How to handle commas, quotes, and line breaks inside cell values (CSV escaping rules)
5. A validation step to ensure the exported data matches the source (row count, spot-check values)

Help me build a financial model in Excel for [type of model]. I need formulas, structure, and best practices — not just a layout.

Model type: [choose one or describe]
- [ ] Revenue forecast (bottom-up or top-down)
- [ ] P&L / Income statement projection
- [ ] Cash flow forecast
- [ ] Unit economics model (CAC, LTV, payback period)
- [ ] SaaS metrics model (MRR, churn, expansion)
- [ ] DCF valuation
- [ ] Scenario analysis (base / bull / bear case)
- [ ] Budget vs. actual tracking

Time horizon: [monthly for 12 months / quarterly for 3 years / annual for 5 years]
Business type: [SaaS / ecommerce / services / marketplace / manufacturing / other]

Key inputs (assumptions I want to be able to change):
1. [Input 1: e.g., "Monthly new customers: starting at 50, growing 10% month-over-month"]
2. [Input 2: e.g., "Average revenue per customer: $49/month"]
3. [Input 3: e.g., "Monthly churn rate: 5%"]
4. [Input 4: e.g., "CAC: $150 per customer"]
5. [Input 5: e.g., "Monthly fixed costs: $25,000"]

Provide:
1. A recommended sheet structure (Assumptions, Calculations, P&L, Charts — with what goes on each sheet)
2. The key formulas for each line item, referencing the assumptions sheet
3. How to build scenario toggles (dropdown that switches between base/bull/bear assumptions)
4. Sensitivity analysis: a data table showing how output changes when 2 key inputs vary
5. A chart recommendation for presenting the model to investors or leadership
6. Best practices: cell color coding (blue for inputs, black for formulas), named ranges, and formula auditing

Write dynamic array formulas using FILTER, SORT, UNIQUE, SORTBY, and SEQUENCE to transform my data without pivot tables. I'm using [Excel 365 / Google Sheets].

Source data:
- Range: [e.g., A1:F500, with headers in row 1]
- Column A: [header and data type]
- Column B: [header and data type]
- Column C: [header and data type]
- Column D: [header and data type]
- Column E: [header and data type]
- Column F: [header and data type]

Transformations I need:
1. **FILTER**: Show only rows where [condition — e.g., "Column C (Region) = 'East' AND Column F (Revenue) > 1000"]
2. **SORT**: Sort the filtered results by [column, ascending/descending]
3. **UNIQUE**: Extract a unique list of values from [column — e.g., "all unique product names"]
4. **SORTBY**: Sort one column's output based on another column's values [describe the relationship]
5. **Combined**: Chain SORT + FILTER + UNIQUE together for [describe desired output — e.g., "unique customer names sorted alphabetically, but only for customers with orders > $500"]

Additional requirements:
- The output should start in cell: [e.g., H2]
- If filter returns no results, display: [custom message instead of #CALC! error]
- I need a dropdown-driven filter: [filter criteria comes from a cell with a data validation dropdown]
- Count of filtered results: [show "X results found" dynamically]

Provide:
1. Each formula individually with explanations
2. A combined mega-formula that chains multiple functions
3. How spill ranges work (the # operator) and how to reference them in other formulas
4. IFERROR/IFNA wrappers for empty result handling
5. Limitations: what happens when the spill range is blocked, and how to fix it