Five ways to create and analyze an Excel spreadsheet with AI

Enhance productivity with Copilot in Microsoft Excel. Whether you’re managing business budgets, tracking inventory, or assessing timesheets, chat with Copilot to speed up and automate these tasks. Beginners can learn from the AI-powered assistant that suggests formulas and guides through basic Excel tasks, while advanced users can discover new workflows, automations, and time-saving shortcuts.  

Learn five useful AI actions and how to prompt Copilot to organize a sheet, analyze data, and make visualizations with ease. 

1. Ask for the right formula

Let Copilot suggest the perfect formula for the spreadsheet task you need to complete. Just describe what you want to calculate and let Copilot execute it for you after analyzing the data and tables to generate the right formula. Beginners can kickstart working with numbers with this feature, and confident users can save time from searching for the right syntax or discover a smarter workflow. Easily execute SUMIFS, AVERAGE, VLOOKUP, or even more advanced functions like XLOOKUP or TEXTSPLIT by chatting with Copilot.

Copilot in Excel prompt examples:

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

“Create 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 makes it easy to clean up large spreadsheets and get them ready for analysis and sharing. Ask Copilot to scan the Excel sheet and suggest improvements to formatting, headers, number formats, and more. This feature is specifically useful in spreadsheets with typos, when copy-pasting goes wrong, or when mixed and unstructured data is imported from CSV files. Chat with Copilot to create order from the chaos, and focus time on analyzing the spreadsheet 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

Spotting trends from large spreadsheets manually can be a time-consuming effort. Automate the use of TREND and FORECAST functions in Excel with Copilot, which can apply functions to quickly to uncover patterns for you. Copilot can additionally spot seasonal patterns, correlations, or issues that require additional further analysis. Chat with Copilot and find trends even from unstructured data points.

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

Need to turn raw data into clear charts and graphs to showcase insights in a digestible way? Copilot in Excel can generate compelling charts in a click once it chooses the best format for the data, including line graphs, pie charts, scatter plots, PivotCharts, and more. Use this feature to create a dashboard or report, and impress your colleagues, clients, and executive leaders with clear insights. Chat with Copilot to help you highlight trends and compare sheet items in an eye-catching manner.

Copilot in Excel prompt examples:

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

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

“Generate a pie chart of expenses by category.”

5. Set up macros and automation

Level up productivity in Excel with this underrated functionality and save hours. Chat with Copilot to identify the best workflow to automate tasks you keep repeating using Visual Basic for Applications (VBA), all without writing any code yourself. This feature can make custom macros that can consistently handle repetitive tasks inside large data-heavy spreadsheets. Format tables, create reports, and build more sequences for efficiency using Copilot. 

Copilot in Excel prompt examples:

“Create a macro to generate invoices from customer order data.” 

“Automate payroll calculations based on hourly rates and hours worked this month.” 

“Build a macro to update balance sheet totals across all departments.” 

Work smarter with Copilot and transform the way you work with spreadsheets. With AI in Excel, there’s no need to manually search for formulas, fix formatting, or worry about how to complete data analysis again. 

Continue learning how to draft and maintain business spreadsheets in Excel.