AI Prompts for Excel: How Consultants Can Save Hours of Work

Key Insights:

AI Boosts Efficiency: Consultants can save hours by using AI prompts to generate formulas, clean data, and build models.
Tailored for Consulting: Prompts focus on real consulting tasks like financial modeling, sensitivity analysis, and executive-ready charts.
Excel Evolves with AI: Combining new Excel functions with AI guidance creates faster, cleaner, and more client-focused deliverables.

Management consultants live and breathe Excel. It is the backbone of financial models, market analyses, and client-ready deliverables. A consultant’s ability to structure data, uncover insights, and communicate results in Excel is often what separates average performers from high-impact analysts. But now, with artificial intelligence, consultants have a powerful partner that can accelerate the process. By using the right prompts, consultants can delegate repetitive tasks, generate formulas on demand, and even enhance the storytelling impact of their work. Consulting firms are actually hiring fewer entry level consultants because of AI. The expectation is that incoming consultants would know how to leverage AI to increase their productivity.

In our articles on How Consultants Use Excel and New Excel Functions, we highlighted why Excel mastery is essential for a consulting career. The reality is that Excel is no longer just about manual formulas and pivot tables. Consultants who combine Excel with AI unlock an entirely new level of efficiency. Below, we share specific AI prompts that consultants can use across the most common categories of work, helping them save time and deliver client-ready output faster.

Data Cleaning and Preparation

Consultants rarely receive clean data. Whether it is market data from a client’s internal system, competitor research, or survey results, raw files usually contain duplicates, missing values, and inconsistent formats. Manually fixing these issues can take hours, but AI can guide you to solutions instantly.

Data Cleaning and Preparation AI Prompts:

Take this messy dataset [paste data] and show me the formulas or Power Query steps I need to clean duplicates, standardize formats, and split full names into first and last.
Write me a step-by-step Excel approach to clean survey data with inconsistent date and currency formats.
Generate an Excel formula to combine multiple sheets into one summary table without VBA.

These types of prompts help consultants create structured, analysis-ready data with minimal manual work.

Financial Modeling

Financial modeling is the lifeblood of consulting analysis. Clients look to consultants for clear projections and scenario testing. AI can help consultants build models faster, reduce errors, and apply more advanced techniques.

Financial Modeling AI Prompts:

General 3 Statement Financial Model:
- Build me a 3-statement financial model template (income statement, balance sheet, cash flow) in Excel using formulas, not macros.
Calculate CAGR, IRR, and NPV:
- Write formulas to calculate CAGR, IRR, and NPV for this dataset [paste sample cash flows].
Sensitivity Table for Revenue Growth vs. Margin in Excel:
- Act as an expert in Excel financial modeling. Create a sensitivity table that shows how changes in revenue growth rate (across rows) and EBITDA margin (across columns) impact net income in a 5-year financial model. Write the exact Excel setup, including input cells, formulas, and the Data Table configuration. Present the answer in a consulting-style format with clear instructions that I can copy directly into Excel.
3 Statement Financial Model for SAAS Company:
- Act as a senior financial analyst and build a dynamic 5-year, 3-statement financial model in Excel for a growth-stage SaaS startup with $10 million in annual recurring revenue (ARR). Break revenue into customer cohorts, including new customers, churned customers, and upsell/expansion. Build expenses at a detailed level (COGS, R&D, Sales & Marketing, and G&A) with a headcount-driven approach where appropriate.

Make all major assumptions flexible using input cells, including ARR growth rate, churn, ARPU, gross margin, and headcount growth. Add a sensitivity table to test changes in churn and ARR growth. Incorporate financing elements such as equity raises and optional debt schedules.

Include valuation outputs with a simple discounted cash flow (DCF) analysis and a multiples-based approach. Present the model in a consulting-style format with a clearly structured assumptions tab, clean formatting, standardized colors, and an executive summary dashboard of key KPIs (ARR, EBITDA, cash balance, valuation).

Use modern Excel functions like XLOOKUP, UNIQUE, FILTER, LET, and LAMBDA where they simplify formulas, but ensure compatibility so a consultant could run this in standard Excel. Provide step-by-step build instructions along with the formulas.

Startup Valuation:
- Build a startup valuation model using the Discounted Cash Flow (DCF) method. Use the provided financial projections and explain the assumptions for discount rate and terminal value.

These prompts give consultants templates and formulas that form the backbone of client-ready deliverables, saving hours of model-building time.

Revenue and Expense Forecasting

Revenue and expense forecasting is one of the most critical skills in consulting. Clients rely on consultants to not only interpret historical performance but also to project what the future may hold under different scenarios. Accurate forecasts allow leadership teams to anticipate cash needs, plan investments, and evaluate the impact of strategic decisions. For consultants, this means building forecasts that are both data-driven and flexible, incorporating assumptions about growth, costs, and market conditions while presenting results in a format executives can act on.

Revenue & Expense Forecasting AI Prompts:

Revenue and Expense Forecasting:
- Act as a senior financial analyst. Analyze our historical monthly revenue and expense data for the past six months [insert data]. Forecast the next quarter’s revenue and expenses over a 3-month horizon. Break down results by product line and region for added granularity.Use a driver-based methodology with assumptions for market growth, churn, pricing changes, and seasonality. Adjust for any missing or irregular data points and exclude one-time expenses. Provide three scenarios — base case, best case, and worst case — and include sensitivity analysis for revenue growth and cost inflation.Incorporate external benchmarks such as industry growth rates or competitor performance where relevant. Model expenses as a mix of fixed and variable costs, with headcount-driven detail where applicable.Present the output as an Excel-ready table and a trend chart. Format it in a consulting-style executive summary that highlights the key drivers of performance, risks to the forecast, and recommended actions for leadership.
Cash Flow Projection:
- Act as a senior financial analyst. Generate a 12-month monthly cash flow forecast [insert historical data if available]. Use the past 12–24 months of inflows and outflows to identify trends, seasonality, and payment cycles.Break results into detailed categories including revenue inflows, operating expenses, payroll, vendor payments, capital expenditures, and debt servicing. Apply assumptions for customer payment terms, collection delays, and percentage of bad debt. Reflect vendor payment cycles, recurring vs. one-time expenses, and planned CapEx (whether upfront, phased, or maintenance).Incorporate financing assumptions such as interest payments, principal repayments, and potential new financing inflows. Build three scenarios — base, best, and worst — to test sensitivity around revenue growth, expense timing, and payment cycle shifts.Present the output in an Excel-ready cash flow statement and a visualization (such as a waterfall or line chart) that highlights key KPIs: free cash flow, ending cash balance, and projected runway. Summarize findings in a consulting-style executive commentary focused on risks, assumptions, and strategic implications.

Analytical Tasks

Beyond financial modeling, consultants use Excel to run quick analyses on markets, customers, and operations. With new Excel functions like XLOOKUP, FILTER, and UNIQUE, consultants can generate insights faster than ever. AI helps by showing how to combine these functions for maximum efficiency.

Analytical Task AI Prompts:

XLOOKUP, FILTER, and UNIQUE:
- Explain how to use XLOOKUP, FILTER, and UNIQUE together to quickly segment data by consultant, client, and industry.
Top 5 Customers by Revenue:
- Write the formula to find the top 5 customers by revenue across this dataset [paste sample data].
Flag Anomalies:
- Create a formula that flags anomalies where quarterly growth is more than 2x the historical average.

Using AI in this way ensures consultants move from raw data to insights without wasting time figuring out complex formulas from scratch.

Visualization and Storytelling

Data only has an impact if it can be communicated clearly. Excel remains one of the primary tools consultants use to tell stories through charts and tables. AI can help consultants design these visualizations in ways that match the clean, persuasive style expected in boardrooms. You can even incorporate The Pyramid Principle into your prompts to help present the data to present the recommendation front and center.

Visualization and Storytelling AI Prompts:

Waterfall Chart in Excel to Show Revenue Bridge:
- Act as an Excel visualization expert. Create a waterfall chart that shows the revenue bridge from [YEAR] to [YEAR], with starting revenue, growth drivers (price, volume, new products), negative impacts (churn, discounts), and ending revenue. Provide the full Excel setup including the sample dataset, formulas to calculate the bridge components, and the exact chart configuration so I can copy it directly. Format it in a consulting-style presentation-ready layout.
Best Chart Type:
- Suggest the best chart types in Excel to compare market share across regions and track changes over time.
VBA Code:
- Write VBA code to automate formatting of consulting-style charts (consistent fonts, colors, and labels).

With these prompts, consultants can elevate raw charts into polished client-ready visuals, speeding up the process of creating executive presentations.

Productivity Shortcuts

Every consultant knows the pressure of late nights and tight deadlines. Time-saving hacks in Excel are invaluable, and AI can surface them instantly. Instead of memorizing dozens of formulas and shortcuts, consultants can simply ask AI to generate what they need on the spot.

Productivity AI Prompts:

Excel Keyboard Shortcuts:
- List 10 time-saving Excel keyboard shortcuts every consultant should know, and show me how to use them in practice.
Dynamic Excel Formula:
- Give me a dynamic Excel formula that pulls the last 3 months of data automatically without needing manual updates.
Count Unique Values:
- Write a formula to count only unique values in a filtered list.
Pivot Tables Prompts:
- Prompt 1:
  Act as an Excel Power Query expert. Show me how to combine multiple monthly sales files from different regions into a single clean dataset. Each file has the same columns (Region, Client, Revenue, Month). Provide the exact Power Query steps to append them, clean duplicates, and standardize date formats for analysis.
- Prompt 2:
  Act as a data transformation coach. Demonstrate how to use Power Query to split a “Client Information” column into three fields: Client Name, Industry, and Country. Then show how to remove null values and apply consistent capitalization across all fields. Provide the full M code.
- Prompt 3:
  Act as a consulting analyst. Build a Power Query process that loads raw financial data from multiple sheets (Revenue, Expenses, Headcount), transforms them into structured tables, and merges them into one master dataset. Include the steps for applying business rules (e.g., flagging negative revenue as anomalies) and prepare it for pivot table analysis.

The more consultants incorporate these prompts, the faster they can move from data wrangling to analysis and recommendations.

Using New Excel Functions

Microsoft continues to roll out new Excel functions that make analysis more intuitive. Functions like LET, LAMBDA, TEXTSPLIT, and SEQUENCE can simplify previously complex tasks. Consultants who understand how to apply them gain an edge in speed and clarity.

New Excel Functions AI Prompts:

LET and LAMBDA Functions:
- Show me how to use the LET and LAMBDA functions to simplify a complex nested IF formula.
TEXTSPLIT Function:
- Demonstrate how to use the TEXTSPLIT function to break a consultant’s weekly schedule into daily tasks.
Combine:
- Combine SORT, FILTER, and SEQUENCE to create a ranked list of top-performing products.

These new tools, combined with AI, allow consultants to build cleaner and more efficient spreadsheets that can adapt quickly to client needs.

The chances are that the above prompts will not fit your situation exactly. But what they can do is help you to think about how to approach Excel in the AI age. You might be able to use AI in ways that you never really thought about before. Use the above examples as launch points for your own prompts. Iterate, experiment, and polish prompts that you could use over and over for subsequent projects!

Final Thoughts

Excel is still the consultant’s most important analytical tool, but it is no longer just about manual formulas and pivot tables. AI expands Excel’s potential by offering real-time coaching, formula generation, and visualization support. By leveraging AI prompts, consultants can save hours of work, reduce errors, and focus on what really matters: developing insights that drive client decisions.

If you are preparing for consulting interviews, remember that firms expect you to demonstrate Excel proficiency alongside case interview skills. Consider joining our Black Belt Program for expert coaching, case practice, and technical skill-building that will give you a competitive edge.