Excel Skills on Demand — Without the Googling
20 ChatGPT prompts for formulas, VBA macros, pivot tables, data cleaning, dashboards, and automations that turn hours of Excel work into minutes.
In short: This page contains 20 copy-paste ready prompts, organized into 4 categories with a description and pro tip for each. The first 5 prompts are free instantly, no signup needed. Drafted with AI writing assistance and checked against our editorial policy.
Formulas
5 promptsFormula Generator
1/20✨ What it does
ChatGPT writes an Excel formula for [describe what I want to calculate], with exact syntax, a part-by-part explanation, and sample outputs. Fill in your columns and edge cases, then paste the formula into a spare cell and check the result.
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.
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✨ What it does
ChatGPT diagnoses [paste formula], names what broke, and rewrites it with a safer alternative. Add the error plus [paste 3-5 rows], then test the fixed formula on that sample before you trust the sheet.
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.
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✨ What it does
ChatGPT builds a nested Excel formula for [describe complex calculation] using IF, IFS, LET, or a lookup, and says when a pivot or Power Query is cleaner. List [list each rule/case] and the [ranges/cells], then paste the readable version into your sheet.
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.
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✨ What it does
ChatGPT translates your slow [paste formula] into Power Query M steps, with why it is faster and the exact Editor clicks. Note [rows x columns], then rebuild the query and time it against the old formula.
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).
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✨ What it does
ChatGPT writes a dynamic array formula for [describe output I want] using FILTER, SORT, UNIQUE, LET, or LAMBDA, and explains how it spills. Say whether you need a [single value, row, column, or 2D range], then lock the inputs and watch the spill.
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.
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.
VBA & Macros
5 promptsVBA Macro Generator
6/20✨ What it does
ChatGPT writes a complete VBA macro for [describe what I need it to do], with comments, On Error handling, and screen updating turned off for speed. Follow the editor paste steps, then run it on a copy of your workbook first.
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.
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✨ What it does
ChatGPT debugs [paste code], explains the root cause, and returns corrected VBA with comments plus extra edge cases. Add the [exact error message or unexpected behavior], then step through the fix on a backup of your file.
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.
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✨ What it does
ChatGPT designs a VBA UserForm with named controls, Initialize code, validation, and submit or cancel logic for the fields in [list]. Build the form from that spec, then try Submit on dummy data before you wire it live.
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.
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✨ What it does
ChatGPT converts [paste VBA] into an Office Script in TypeScript for Excel on the web, noting APIs that will not translate and how to trigger it. Run the script in Excel Online, then drop anything from your macro that needed a UserForm.
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.
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✨ What it does
ChatGPT reviews [paste code] for hardcoded paths, version lock-in, and risky Shell or SendKeys calls, then says how to package it for teammates or clients. Strip the local paths, then share the cleaned file only after you agree with the security notes.
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.
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 promptsData Cleaning Plan
11/20✨ What it does
ChatGPT builds an ordered data-cleaning plan with a formula, Power Query, VBA, or manual method per step and a final QC checklist. Note the issues and size in [rows], then run the steps in your order and tick every check.
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).
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✨ What it does
ChatGPT designs a pivot table for [describe what insight I need], including field placement, value settings, calculated fields, and slicers. Map [what should appear in rows, columns, values, filters], then build the pivot on your data and add the slicers it names.
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.
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✨ What it does
ChatGPT writes conditional formatting rules for [describe purpose and data], each with a formula, apply range, format, and priority order. Apply them from the top down on your range, then hide any rule that fights another.
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.
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✨ What it does
ChatGPT picks IQR, Z-score, or MAD for your sheet, gives flagging formulas, and suggests what to do with outliers plus a chart idea. Drop [describe columns, sample data], then mark the flagged rows and decide which ones stay.
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.
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✨ What it does
ChatGPT sets data validation per column, including lists, custom formulas, input messages, and cascading dropdowns. Fill [describe columns and what each should contain], then lock the sheet after you test a bad entry.
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.
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.
Like these prompts? There are full tutorials behind them.
Learn the workflows, not just the prompts. Easy-to-follow tutorials inside AI Academy, and the library keeps growing.
Dashboards & Reporting
5 promptsDashboard Blueprint
16/20✨ What it does
ChatGPT designs an Excel dashboard for [describe purpose] with 5-7 front KPIs, chart picks, a one-screen layout, and slicers for [executive, team, self-use]. Place the charts as drawn, then hide anything on your dashboard that does not fit one screen.
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.
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✨ What it does
ChatGPT builds a monthly report for [describe what's being reported] with auto-updating formulas, variance vs last month and vs target, and a refresh checklist. Swap in this month's data, then walk that checklist before you send it.
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.
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✨ What it does
ChatGPT picks a chart type for [describe data, what I want to communicate, audience], plus what to strip from Excel defaults and how to color and annotate. Build that chart, then delete the gridlines and leftover legend it told you to cut.
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.
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✨ What it does
ChatGPT builds a what-if model for [describe decision I'm evaluating] with validated inputs, formulas, a scenario table, and a visual summary. Enter [list with ranges], then flip the scenarios and keep the one you would actually run.
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.
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✨ What it does
ChatGPT designs a KPI tracker for [describe business/project] with an input sheet, green/yellow/red thresholds, sparklines, and an archive. Add [list 5-10] on a [daily, weekly, monthly] cadence, then log this period before you glance at the sparkline.
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.
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.
Free tool
Formula Genie
Describe what you need and get the exact Excel or Google Sheets formula.
Frequently Asked Questions
Prompts are the starting line. Tutorials are the finish.
A growing library of hands-on tutorials on ChatGPT, Claude, Midjourney, and many other AI tools. New tutorials are added regularly.
7-day free trial. Cancel anytime.
Related guides