AI Prompts for Excel Automation: Formulas, Macros, Dashboards

Published: July 2026 | 8 min read

Excel is still the most widely used analytics tool in the world, and AI can make you dramatically faster at it. From complex formulas to VBA macros to interactive dashboards, these prompts help you build Excel solutions that work — not just look impressive in a demo.

Formulas & Functions

1. Complex Formula Builder

Role: Excel formula expert
Task: Write a formula to [describe what you need to calculate].
Data layout: [Describe the sheet structure — columns, rows, data types, sheet names]
Requirements:
1. What to calculate: [precise description]
2. Input cells/ranges: [which cells contain the inputs]
3. Output cell: [where the formula goes]
4. Error handling: [what to show for blank, text, error, #N/A]
5. Performance: [is this a single cell or thousands of rows? array formula needed?]
Provide:
1. Formula (exact Excel syntax, ready to paste)
2. How it works (step by step, each function explained)
3. Edge cases handled (empty cells, text in numeric columns, div/0)
4. Alternative approaches (if a simpler or more efficient formula exists)
5. Compatibility note (does this work in older Excel? need dynamic arrays?)
Format: Formula with explanation, ready to use.

2. VLOOKUP to XLOOKUP Migration

Role: Excel modernization specialist
Task: Convert these VLOOKUP/INDEX-MATCH formulas to XLOOKUP or dynamic arrays.
Current formulas: [Paste the formulas]
For each formula:
1. Original formula
2. XLOOKUP equivalent (with explanation of improvements)
3. If applicable, dynamic array alternative (FILTER, SORT, UNIQUE)
4. Benefits of the new approach (faster? more flexible? spill range?)
5. Compatibility note (does this require Excel 365? will older versions break?)
Also recommend:
- Named ranges for readability
- Table references (instead of A1 ranges)
- Data validation to prevent errors
Format: Before/after comparison with migration guidance.

3. Conditional Formatting Rules Generator

Role: Excel formatting specialist
Task: Create conditional formatting rules for [describe what to visualize].
Data: [Column/range, data type, value range]
Rules needed:
1. Highlight [condition] — what color, what condition
2. Data bars — for which column, what settings
3. Color scale — gradient or threshold-based, colors
4. Icon sets — which icons, what thresholds
5. Custom formula rules — if standard rules are not enough
For each rule:
1. Rule type (cell value, formula, data bar, color scale, icon set)
2. Condition/formula (exact Excel formula)
3. Format (fill color, font, border, icon)
4. Applies to (range)
5. Stop if true? (priority handling)
6. Why this rule (what insight does it surface?)
Format: Complete formatting specification ready to implement.

VBA Macros & Automation

4. VBA Macro Generator

Role: Excel VBA developer
Task: Write a VBA macro to [describe what to automate].
Worksheet: [Describe — sheet names, data layout, any existing named ranges]
Requirements:
1. Trigger (button, shortcut, auto-open, auto-change)
2. Input: [what data to read, from where]
3. Processing: [what to do with the data]
4. Output: [where to write results, what format]
5. Error handling: [what if data is missing? wrong format? sheet does not exist?]
Code requirements:
- Option Explicit (declare all variables)
- Variable declarations with types
- Error handling (On Error GoTo, meaningful error messages)
- ScreenUpdating = False during execution, True after
- Input validation
- Progress indication for long operations
- No Select or Activate (use direct references)
- Comments explaining non-obvious logic
- Cleanup (close objects, restore settings)
Output: Complete VBA module ready to paste into the VBA editor.
Include: Instructions for adding the macro and assigning to a button/shortcut.

5. Data Cleaning Macro

Role: Excel VBA automation specialist
Task: Write a VBA macro to clean and standardize data in [sheet/range].
Cleaning tasks:
1. Remove duplicate rows (which columns define uniqueness?)
2. Trim extra spaces (leading, trailing, multiple internal)
3. Standardize case (PROPER, UPPER, or LOWER — per column)
4. Fix date formats (convert text dates to real dates)
5. Remove special characters (which ones?)
6. Handle missing values (leave blank, fill with default, flag)
7. Standardize text (e.g., "USA" and "United States" -> "US")
8. Validate data types (numbers are numbers, dates are dates)
9. Remove empty rows/columns
10. Sort by [column] ascending/descending
For each task:
1. What it does
2. VBA code
3. What can go wrong (and how the code handles it)
Output: Complete VBA module with all cleaning operations, ready to run.
Include: Summary report (how many rows changed, what was cleaned).

6. Report Generation Macro

Role: Excel reporting automation developer
Task: Write a VBA macro to generate a formatted report from raw data.
Source data: [Sheet name, data layout, row count range]
Report requirements:
1. Report sheet (create new or clear existing)
2. Layout (header, data section, summary section)
3. Formatting (fonts, colors, borders, column widths)
4. Calculations (sums, averages, counts, percentages)
5. Charts (which type, what data, what title, what position)
6. Print setup (margins, orientation, fit to page, print area)
7. Save options (save as new file? PDF export? email?)
Macro flow:
1. Validate source data exists and is correct
2. Clear/create report sheet
3. Build header (title, date, company info)
4. Copy/transform data to report sheet
5. Apply formatting
6. Add formulas/calculations
7. Create charts
8. Set print area
9. Save/export
10. Show completion message
Output: Complete VBA module ready for production use.

Dashboards & Data Analysis

7. Interactive Dashboard Design

Role: Excel dashboard designer
Task: Design an interactive dashboard for [data source and audience].
Data: [What data is available, sheet structure, refresh frequency]
Audience: [Who will use the dashboard, what decisions they make]
Dashboard elements:
1. KPI Summary (top 4-5 metrics with current value vs target, sparkline)
2. Time series chart (trend over [period], with trend line)
3. Comparison chart (by category/segment)
4. Detail table (filterable, top 10 rows)
5. Filters/slicers (date range, category, region, etc.)
For each element:
1. Data source (which sheet, which range, which formula/table)
2. Chart type (line, bar, column, donut, combo, scatter)
3. Formatting (colors, fonts, gridlines, data labels)
4. Interaction (what slicers affect it, what happens on click)
5. Dynamic range handling (named ranges or tables so it auto-updates)
Layout:
- Overall size (how many columns wide?)
- Arrangement (KPIs on top, charts middle, table bottom)
- Color scheme (brand colors? semantic colors?)
- Print/screenshot considerations
Output: Dashboard specification with formulas, chart settings, and layout instructions.

8. Pivot Table Automation

Role: Excel pivot table expert
Task: Create a pivot table setup guide and automation for [data].
Data: [Sheet, range/columns, data types, row count]
Analysis needs: [What questions does the pivot answer?]
For each pivot table:
1. Source data (table or range — recommend Excel Table for dynamic updates)
2. Rows (which fields, in what order, grouping needed?)
3. Columns (which fields)
4. Values (which fields, what aggregation: sum/count/avg/min/max)
5. Filters (which fields, what selections)
6. Calculated fields (if needed — formula and rationale)
7. Sorting (which field, ascending/descending)
8. Grouping (by date: month/quarter/year? by numeric range?)
9. Slicers (which fields, what to connect)
10. Timeline (if date field exists)
11. Refresh strategy (manual, on open, VBA triggered)
Output: Pivot table configuration guide ready to build.
Include: VBA snippet to auto-create or refresh the pivot table.

Power Query & Advanced

9. Power Query M Code Generator

Role: Power Query (M) developer
Task: Write Power Query M code to [describe the transformation].
Source: [Excel file / CSV / database / web / folder]
Transformation needed: [step by step what to do with the data]
For each step:
1. M code (exact syntax)
2. What it does
3. What the data looks like after (column names, sample row)
Complete query:
1. Source step (connect to data)
2. Navigation (select table/sheet/range)
3. Promotion (headers, if needed)
4. Type conversion (set proper data types)
5. Cleaning (remove nulls, trim, filter rows)
6. Transformation (merge, append, unpivot, pivot, group)
7. Calculated columns (add custom columns with M formulas)
8. Final output (load to sheet, data model, or connection only)
Output: Complete M query ready to paste into Advanced Editor.
Include: Performance notes (folded queries, buffer, enable/disable refresh).

10. Power Pivot DAX Generator

Role: Power Pivot DAX specialist
Task: Write DAX measures for [data model and analysis needs].
Data model: [Tables, relationships, key columns]
Measures needed:
1. Basic aggregations (sum, count, average, min, max)
2. Time intelligence (YTD, MTD, prior year, rolling 12 months)
3. Ratios (margin %, growth %, achievement vs target %)
4. Ranking (by category, dynamic)
5. Filters (CALCULATE with specific conditions)
6. Running totals (cumulative, by category)
For each measure:
1. Measure name (descriptive, follows naming convention)
2. DAX formula (exact syntax)
3. What it calculates (plain English)
4. Which table it belongs to
5. Format string (currency, percentage, number with decimals)
6. Potential issues (BLANK handling, divide by zero, filter context)
Output: DAX measures ready to add to the data model.
Include: Calculation group recommendations if there are many variations.

11. Data Validation Setup

Role: Excel data validation specialist
Task: Create data validation rules for [describe the input form/sheet].
For each cell/range that needs validation:
1. Cell/range reference
2. Validation type:
   - List (dropdown — what items? from a range or typed?)
   - Whole number (min, max, or between)
   - Decimal (min, max)
   - Date (range or relative)
   - Text length (min, max)
   - Custom (formula — what condition?)
3. Input message (what the user sees when they select the cell)
4. Error alert (style: stop/warning/info, title, message)
5. Circle invalid data (should we highlight existing invalid entries?)
Output:
1. Validation specification for each cell
2. VBA code to set up all validations at once (faster than manual)
3. Helper sheet for dropdown sources (named ranges)
Format: Ready-to-implement validation setup with VBA automation.

12. Excel-to-PowerPoint Automation

Role: Excel VBA automation developer
Task: Write VBA to export Excel charts and data to PowerPoint.
Source: [Excel charts/tables to export, sheet names, chart names]
Destination: [PowerPoint template, slide layout, where each element goes]
Automation flow:
1. Open PowerPoint (or use existing instance)
2. Open template (or create new presentation)
3. For each export item:
   a. Select target slide
   b. Copy chart/table from Excel
   c. Paste to PowerPoint (paste special as chart/image/HTML)
   d. Position and size the pasted object
   e. Update title text (if dynamic)
4. Save presentation
5. Optional: export to PDF
6. Close or leave open
For each chart/table:
1. Excel source (sheet, chart name, range)
2. PowerPoint target (slide number, position, size)
3. Paste type (chart, picture, linked object)
4. Post-paste formatting (if needed)
Output: Complete VBA module that automates the Excel-to-PowerPoint workflow.
Include: Error handling for missing slides, closed PowerPoint, template not found.

Best Practices

For more productivity content, see our ChatGPT prompts for presentations and AI prompts for data analysis.