Prompt Library

Excel Skills on Demand — Without the Googling

20 copy-paste prompts

20 ChatGPT prompts for formulas, VBA macros, pivot tables, data cleaning, dashboards, and automations that turn hours of Excel work into minutes.

Formulas

5 prompts

Formula Generator

1/20

Write an Excel formula that [describe what I want to calculate]. My data structure: [describe columns and rows, or paste a small sample]. Constraints: [any edge cases like blank cells, errors, specific text matching]. Requirements: (1) give me the formula in exact Excel syntax, (2) explain what each part does, (3) note which Excel version it requires (especially for newer functions like XLOOKUP, LET, LAMBDA), (4) provide an alternate formula for older Excel/Google Sheets if needed, (5) show expected output for 2-3 sample inputs.

Generates Excel formulas with syntax explanation, version requirements, and sample outputs.

💡

Pro tip: Always share your Excel version or specify "compatible with Excel 2019+" — functions like XLOOKUP, FILTER, and LET don't work in older versions. Nothing worse than a perfect formula that returns #NAME? error.

Formula Debugger

2/20

Debug this Excel formula for me: [paste formula]. Error I'm getting: [#VALUE!, #REF!, #N/A, wrong result, etc.]. What I'm trying to achieve: [describe]. Sample data: [paste 3-5 rows]. Analyze: (1) identify the exact source of the error, (2) rewrite the formula correctly, (3) explain what was wrong and why it failed, (4) suggest a simpler alternative if my formula is overly complex, (5) recommend how to bulletproof it against similar errors in the future.

Diagnoses Excel formula errors, rewrites correctly, and suggests bulletproof alternatives.

💡

Pro tip: Excel errors almost always come from mismatched data types, missing lookup values, or reference drift. Share the actual error message — #N/A vs #VALUE! vs #REF! each points to completely different root causes.

Nested Formula Builder

3/20

Build a nested Excel formula to [describe complex calculation]. The conditions: [list each rule/case]. Data location: [ranges/cells]. Use the appropriate mix of IF, IFS, AND, OR, LET (if available), or a lookup function. Deliver: (1) the formula with clean structure (use LET or helper columns if it gets unreadable), (2) a plain-English breakdown of each condition, (3) a simpler alternative if my logic can be flattened, (4) whether a pivot table or Power Query would actually be better.

Builds complex nested conditional formulas or recommends when Power Query/pivot tables beat a mega-formula.

💡

Pro tip: If your formula needs more than 3 nested IFs, you probably want IFS() or a lookup table. Stacked IFs become unreadable and error-prone. Readability saves you hours when you come back in 3 months.

Formula-to-Power-Query Translator

4/20

I'm using this Excel formula across a large dataset: [paste formula]. Dataset size: [rows x columns]. It's slow. Translate this to Power Query (M language): (1) the equivalent Power Query step or transformation, (2) why Power Query is faster for this use case, (3) exact step-by-step in the Power Query Editor UI, (4) the M code equivalent, (5) when to keep the formula approach instead (quick ad-hoc tasks).

Translates slow array/lookup formulas into Power Query steps for performance on large datasets.

💡

Pro tip: Formulas recalculate on every change. Power Query runs once per refresh. On datasets over 10,000 rows, Power Query is usually 10-100× faster. Learn the 5-10 most common transformations and never look back.

Array Formula Architect

5/20

Write an Excel dynamic array formula (Excel 365/2021+) to [describe output I want]. Inputs: [describe]. Expected output shape: [single value, row, column, or 2D range]. Use FILTER, SORT, UNIQUE, SEQUENCE, LET, LAMBDA as appropriate. Provide: (1) the formula with comments via LET variable names, (2) how it spills, (3) what happens when input ranges change size, (4) common gotchas (spill errors, mixed data types), (5) a non-array fallback for older Excel.

Architects dynamic array formulas using FILTER/SORT/UNIQUE/LET for clean spill-based calculations.

💡

Pro tip: Dynamic arrays are the biggest Excel upgrade in 20 years. FILTER + SORT + UNIQUE can replace most VLOOKUP + helper column combos. If you're on Excel 365, stop writing VLOOKUP — XLOOKUP and FILTER are better in every way.

Prompts get you started. Tutorials level you up.

A growing library of 300+ hands-on AI tutorials. New tutorials added every week.

Start 7-Day Free Trial

VBA & Macros

5 prompts

VBA Macro Generator

6/20

Write a VBA macro that [describe what I need it to do]. Context: [describe my workbook — sheet names, data location, output location]. Requirements: (1) complete VBA code with comments, (2) error handling with On Error, (3) screen updating off/on for speed, (4) instructions on how to paste into the VBA editor, (5) how to attach it to a button, (6) a test scenario to verify it works. Assume I have basic Excel knowledge but limited VBA experience.

Generates complete VBA macros with error handling, performance optimizations, and setup instructions.

💡

Pro tip: Always include `Application.ScreenUpdating = False` at the start and `True` at the end of any macro that touches cells. It can 10-100× macro speed. Users don't want to watch cells flicker while macros run.

VBA Debugger

7/20

Debug this VBA macro: [paste code]. Error I'm seeing: [exact error message or unexpected behavior]. What it should do: [describe]. Analyze: (1) what's causing the error, (2) the corrected code with inline comments on the fix, (3) why the original failed, (4) what other edge cases to watch for, (5) how to add error handling so it fails gracefully. Flag any performance issues while you're at it.

Debugs VBA code, explains the root cause, adds error handling, and flags performance issues.

💡

Pro tip: VBA's most common bugs: implicit activation (.Select, .Activate), missing sheet qualification (Range vs Sheets("X").Range), and missing .Value on object references. Always qualify everything — lazy VBA breaks at the worst moments.

UserForm Designer

8/20

Design a VBA UserForm for [describe the input I need to collect — e.g., expense entry, customer record, survey response]. Fields needed: [list]. Create: (1) the UserForm design — list each control (TextBox, ComboBox, ListBox, OptionButton, etc.) with names and positions, (2) initialization code (UserForm_Initialize), (3) validation logic for each field, (4) the submit button code that writes data to a specific sheet, (5) a cancel button that cleanly closes. Include exact control names so I can build it quickly.

Designs complete VBA UserForms with controls, initialization, validation, and submit/cancel logic.

💡

Pro tip: UserForms transform Excel from a spreadsheet into an app. If people enter data directly into sheets, you'll always have data-quality issues. A 30-minute UserForm build saves hours of cleanup every month.

VBA to Office Script Converter

9/20

Convert this VBA macro to Office Scripts (TypeScript) for Excel on the web: [paste VBA]. I need to run this in Excel Online/Teams. Provide: (1) the equivalent Office Script, (2) key API differences to be aware of, (3) what won't translate 1:1 (e.g., some UserForm/Automation features), (4) how to schedule or trigger it via Power Automate, (5) testing steps in Excel Online.

Converts VBA macros to Office Scripts for Excel on the web, including Power Automate integration paths.

💡

Pro tip: Office Scripts are the future of Excel automation — they work in Excel Online, trigger from Teams, and integrate with Power Automate. VBA still wins for complex UserForms, but for cross-platform automation, Office Scripts are the direction.

Macro Security & Portability

10/20

Review this VBA macro for portability and security: [paste code]. Target users: [describe — teammates, clients, public]. Check: (1) hardcoded paths, sheet names, or cell references that'll break elsewhere, (2) dependencies on specific Excel versions or add-ins, (3) security concerns (SendKeys, Shell, file system writes), (4) how to package it (workbook vs add-in vs digitally signed), (5) a README to include for end-users with install steps. Make the macro safe to distribute.

Reviews VBA for portability issues, security red flags, and packaging recommendations for distribution.

💡

Pro tip: Macros that "work on my machine" often fail elsewhere — hardcoded file paths, missing references, or version-specific functions. Before sending a macro to anyone, open it on a clean Excel install and confirm it runs. 10 minutes of testing saves 10 support emails.

Data Cleaning & Analysis

5 prompts

Data Cleaning Plan

11/20

Create a data cleaning plan for this dataset: [describe or paste sample — columns, types, common issues]. Size: [rows]. Quality issues I've noticed: [describe]. Plan: (1) list every cleaning step in order, (2) for each step: recommended Excel approach (formula / Power Query / VBA / manual), (3) validation checks after each step, (4) common pitfalls for this data type, (5) a final QC checklist before the data's ready for analysis. Prioritize repeatable steps (so I can rerun next month).

Creates a sequenced data cleaning plan with method recommendations, validation checks, and a final QC checklist.

💡

Pro tip: Clean data once, analyze many times. If you find yourself cleaning the same way every month, move it to Power Query — one-click refresh beats repeating 15 cleanup steps. Your future self will thank you.

Pivot Table Designer

12/20

Design a pivot table for this analysis: [describe what insight I need]. Source data: [columns and sample rows]. Output requirements: [what should appear in rows, columns, values, filters]. Deliver: (1) exact pivot table configuration — fields in each area, (2) value field settings (sum, count, average, % of total, etc.), (3) whether to use calculated fields or helper columns, (4) conditional formatting rules that make insights pop, (5) how to build a slicer dashboard from it.

Designs a complete pivot table configuration with value settings, calculated fields, and slicer dashboards.

💡

Pro tip: Pivot tables are underused because most people don't know about "Show Values As" options (% of column total, running total, difference from, etc.). 5 minutes learning these options unlocks analysis that used to require formulas.

Conditional Formatting Rules

13/20

Create conditional formatting rules for this spreadsheet: [describe purpose and data]. Rules I want: [list]. Deliver: (1) each rule with exact formula (use =formula: syntax if needed), (2) which cells to apply to, (3) format to apply (fill, font, border, icon set), (4) order of rules (top rule wins), (5) visual hierarchy tip — don't over-format or it all blends. Bonus: suggest 2-3 rules I didn't think of that would highlight useful insights.

Creates a prioritized set of conditional formatting rules with formulas, formats, and visual hierarchy guidance.

💡

Pro tip: Conditional formatting reveals patterns instantly. But over-formatting kills it — if 80% of cells are colored, none stand out. Use color for exceptions (top 10%, outliers, overdue items), not for the norm.

Outlier Detector

14/20

Help me find outliers in this Excel dataset: [describe columns, sample data]. Approach: (1) recommend the right statistical method — IQR (interquartile range), Z-score, MAD (median absolute deviation) — based on the data's distribution, (2) provide the exact formulas to flag outliers, (3) suggest what to do with outliers (investigate, remove, winsorize, keep), (4) a formula to tag outliers with a category (mild, severe), (5) how to visualize with conditional formatting or a box plot.

Detects outliers using appropriate statistical methods and flags them with category labels and visualization suggestions.

💡

Pro tip: Outliers are not always errors — sometimes they're the most interesting data points. Always investigate before removing. A "weird" customer might be your best sales opportunity hiding in the noise.

Data Validation Rules

15/20

Set up data validation rules for this spreadsheet: [describe columns and what each should contain]. Create: (1) exact data validation settings per column (list, whole number, date, custom formula), (2) custom input messages and error alerts, (3) drop-down lists (including dependent/cascading ones if needed), (4) how to handle existing bad data, (5) protection strategy — what cells to lock, what to leave editable. Goal: make it hard to enter bad data.

Sets up column-level data validation rules including cascading dropdowns, custom formulas, and protection strategies.

💡

Pro tip: Data validation prevents 80% of data quality issues before they happen. Bad data in = bad analysis out. Take 15 minutes upfront to validate inputs — it saves hours of cleanup later.

Dashboards & Reporting

5 prompts

Dashboard Blueprint

16/20

Design an Excel dashboard for [describe purpose]. Audience: [executive, team, self-use]. Data source: [describe]. Create: (1) the top 5-7 KPIs that should be front-and-center, (2) recommended charts per KPI (bar, line, gauge, heatmap), (3) layout design — where each element goes on a single screen, (4) slicer/filter design for interactivity, (5) color scheme and visual hierarchy rules, (6) a "tell me a story" narrative for how a user should read the dashboard. Single screen, no scrolling.

Designs an executive Excel dashboard with KPI selection, chart recommendations, layout, and narrative flow.

💡

Pro tip: Great dashboards answer one question in 5 seconds: "Is everything okay or not?" If someone has to hunt for the answer, the dashboard failed. Start with the decision the dashboard enables, then work backward.

Monthly Report Builder

17/20

Build a monthly report in Excel for [describe what's being reported]. Data inputs: [describe]. Audience: [describe]. Create: (1) the exact report structure — sections, headers, visuals, (2) formulas/pivot tables that auto-update when new data is added, (3) a variance analysis section (vs last month, vs target, vs last year), (4) an executive summary template at the top, (5) a refresh checklist — what to do each month to update the report in under 10 minutes. Make it reusable.

Builds a monthly recurring Excel report with auto-updating formulas, variance analysis, and a reusable refresh checklist.

💡

Pro tip: Monthly reports should take 10 minutes to refresh, not 3 hours. If you're manually retyping numbers every month, you've built a report, not a system. Automate the refresh once — benefit forever.

Chart Selection & Design

18/20

Help me pick and design the right chart for this data: [describe data, what I want to communicate, audience]. Recommend: (1) the best chart type and why (and 1-2 alternatives), (2) what to remove from Excel's default chart (gridlines, chart title, legend placement), (3) how to order categories, (4) color choices that match the message (highlight one series, use gray for the rest), (5) annotations that call out key insights, (6) chart types to avoid for this data and why. Clarity over cleverness.

Recommends chart type with design cleanup, color choices, and annotations that communicate the insight clearly.

💡

Pro tip: Excel's default charts are visual noise. Delete gridlines, move axis labels closer, pick one color to highlight, gray out everything else. 10 minutes of chart cleanup does more than any fancy chart type ever will.

What-If Scenario Modeler

19/20

Build a what-if scenario model for [describe decision I'm evaluating]. Variables I want to test: [list with ranges]. Output metrics I care about: [list]. Create: (1) a structured input section with data validation on ranges, (2) the calculation logic (formulas), (3) a scenario table using Data Tables or SUMPRODUCT, (4) a visual summary chart showing how output changes as inputs change, (5) a sensitivity analysis — which variables matter most. Make it safe to stress-test.

Builds a what-if scenario model with structured inputs, scenario tables, sensitivity analysis, and visual summaries.

💡

Pro tip: What-if models that only test "best/middle/worst" cases miss the real insight: which variables drive the outcome. A good sensitivity analysis shows you where to focus attention — and where not to waste time.

KPI Tracking System

20/20

Design a KPI tracking system in Excel for [describe business/project]. KPIs to track: [list 5-10]. Update frequency: [daily, weekly, monthly]. Create: (1) input worksheet with validation for each KPI, (2) target and threshold logic (green/yellow/red), (3) trend sparklines for at-a-glance patterns, (4) a summary dashboard with current status + trend, (5) an archive system so historical data isn't overwritten, (6) alerts or conditional formatting when a KPI crosses a threshold.

Builds a complete KPI tracking system with input validation, thresholds, trend sparklines, and historical archiving.

💡

Pro tip: The KPIs you track shape behavior. Track vanity metrics (revenue, follower count) and people chase vanity. Track leading indicators (activation rate, time-to-first-value) and people build value. Pick KPIs that drive the right behavior.

Frequently Asked Questions

Yes, very reliably — ChatGPT is excellent at generating formulas for VLOOKUP, XLOOKUP, SUMIFS, INDEX/MATCH, dynamic arrays, and complex nested conditionals. Always specify your Excel version (Excel 365, 2019, 2016, Google Sheets) because functions like XLOOKUP, FILTER, and LET don't work in older versions. Test the formula with sample data before deploying.
Power Query for anything data transformation — cleaning, reshaping, combining sources. It's faster, more maintainable, and refreshes with one click. VBA for everything Power Query can't do — UserForms, user interactions, email automation, complex logic. Rule of thumb: if the task is "every month, transform this data the same way," use Power Query. If it's "when user clicks, do X," use VBA.
Top speed killers: (1) volatile formulas (NOW, TODAY, OFFSET, INDIRECT) — replace where possible, (2) array formulas on large ranges — use dynamic arrays or Power Query instead, (3) excessive conditional formatting — simplify or use tables, (4) external file links — import the data instead, (5) too many sheets — consolidate or split into multiple workbooks. A 100MB file becoming 10MB is normal after optimization.
ChatGPT can generate functional VBA macros for common tasks — loops, ranges, UserForms, basic automation. For complex projects (multi-workbook automation, API calls, custom add-ins), you'll need to review, test, and often refactor the code. Always include error handling (`On Error`), screen updating toggles, and qualified object references (Sheets("X").Range) — ChatGPT sometimes skips these in quick-and-dirty drafts.
Excel for speed, interactivity, and business-user accessibility. Python for datasets over 1 million rows, repeatable data pipelines, and statistical analysis. Use both — Power Query to pull/clean data into Excel, then Python (via a tool like xlwings or Power Query's Python integration) for heavy computation. Most business analysts don't need Python for 90% of their work.

Prompts are the starting line. Tutorials are the finish.

A growing library of 300+ hands-on tutorials on ChatGPT, Claude, Midjourney, and 50+ AI tools. New tutorials added every week.

Start 7-Day Free TrialExplore AI for Productivity courses

7-day free trial. Cancel anytime.

Browse All Prompt Collections