Five ways to create and analyze an Excel spreadsheet with AI

Make sense of data faster with Copilot in Excel. Get formula suggestions, clean up common inconsistencies, or surface simple trends for data ranges by prompting AI. Copilot can support a variety of actions, for any experience level, from easing beginners into understanding Excel to assisting advanced users with refining workflows.

Read on to learn five AI actions in Excel to work faster and smarter.

1. Ask for the right formula

Use Copilot to get helpful formula recommendations. Highlight cells, describe a calculation, and AI may present options based on the content in the selected range. Quickly identify common issues in large workbooks such as inventory lists or expense trackers where consistent calculations are key, with Copilot.

Copilot in Excel prompt examples:

“What formula to use to add up all the revenue?”

“Insert a VLOOKUP formula to match employee names with ID numbers.”

“Find the average monthly spend per customer from column B and C.”

2. Clean large data sets

It can be tough to make sense of messy data, but Copilot in Excel can assist with basic cleaning up of spreadsheets like contact lists or event logs where simple inconsistencies add up. Get AI to help with manual editing tasks such as removing duplicate rows, fixing capitalization errors in text, or turning simple ranges into tables. With Copilot taking on small formatting tasks, spend more time analyzing spreadsheets and identifying trends with clarity.

Copilot in Excel prompt examples:

“Remove duplicate rows from this table.”

“Fix inconsistent capitalization in column A.”

“Standardize number formatting across all columns in the balance sheet.”

3. Analyze spreadsheet trends

Manually spotting trends from large spreadsheets can be a time-consuming effort. Copilot can suggest formulas like TREND or FORECAST.LINEAR, which help identify patterns in data sets easily. AI may also surface top-level trends to call out changes in structured data, such as month‑to‑month differences in expenses, sales, or inventory. To get findings report-ready, Copilot summarizes how selected values have changed over time, streamlining analysis.

Copilot in Excel prompt examples:

“Are there any trends in this data?”

“Show me which weeks had the highest overtime hours from the timesheet.”

“Compare monthly marketing spend across quarters.”

4. Visualize data

Showcase complex data as clear, digestible charts and graphs with AI in Excel. Ask Copilot to visualize sheet information in formats such as line and column graphs, scatter plots, or pie charts for selected ranges. Helpful for presenting survey responses, include these visuals in a report to clearly outline insights for stakeholders.

Copilot in Excel prompt examples:

“Create a line graph showing patient admissions by month.”

“Create a column graph showing stock levels by product category to track inventory.”

“Generate a pie chart of expenses by category.”

5. Summarize data

Summarize large workbooks to extract key data points faster. Use AI summaries in Excel to quickly pull insights for planning budgets or team projects. AI can also add simple headers or create basic charts from available data to complement summaries. Make long lists such as survey imports or timesheet records less overwhelming by prompting Copilot to describe what appears in a data set.

Copilot in Excel prompt examples:

“Summarize the changes in income for the previous financial year.”

“Give a brief overview of the time taken for project completion.”

“Outline what this dataset appears to include.”

Use Copilot in Excel to help bring structure to data, create graphs and charts, and assist with simple calculations across any workbook.

Explore templates like inventory tracker and invoice generator within Excel to get started.