Copilot in Excel: Data Analysis

Overview

Government agencies manage enormous amounts of data in spreadsheets — budget tracking, procurement records, personnel rosters, program metrics, compliance logs. Turning that raw data into actionable insights traditionally requires expertise in formulas, pivot tables, and chart building. Copilot in Excel changes this by letting you ask questions about your data in plain English and get answers, insights, and visualizations without writing a single formula.

This video walks you through using Copilot in Excel to analyze data, identify trends, create charts, and understand what your spreadsheets are telling you.

What You’ll Learn

• Prerequisites: What you need before Copilot works in Excel
• Data Questions: How to ask Copilot questions about your data in natural language
• Insights and Trends: How to surface patterns and outliers in your data
• Visualizations: How to create charts and graphs with simple prompts
• Capabilities: What Copilot can and cannot do with your Excel data

Script

Hook: Your data has answers — ask for them

How many spreadsheets are sitting on your SharePoint right now with data that nobody has time to analyze? Budget trackers with twelve months of spending data. Procurement logs with hundreds of line items. Program dashboards that get updated but never truly examined.

Government agencies collect data constantly but turning it into insight takes time, formulas, and expertise that is not always available. What if you could just ask your spreadsheet a question and get an answer?

Copilot in Excel does exactly that. In the next ten minutes, you will learn how to use Copilot to analyze data, find trends, and create visualizations — all by asking in plain English.

Prerequisites and data setup

Before Copilot works in Excel, your data and environment need to meet a few requirements.

First, you need a Microsoft 365 Copilot license. Copilot in Excel is part of the paid Copilot experience.

Second, your data must be formatted as an Excel table. This is the most common stumbling block. Copilot needs structured table data — it cannot analyze a random range of cells. To convert your data to a table, select any cell in your data range, go to Insert, and click Table. Confirm the range and check “My table has headers.” That is it. Your data is now in a format Copilot can work with.

Third, AutoSave must be enabled, which means your file needs to be saved to OneDrive or SharePoint. Copilot does not work with files saved only to your local drive.

For government cloud environments, Copilot in Excel is fully supported in GCC, GCC High, and DoD tenants. Once your file meets these requirements, click the Copilot button in the Home tab of the ribbon to open the Copilot panel.

Asking questions about your data

With the Copilot panel open, you can start asking questions about your data in natural language. This is the most immediate and practical feature.

Type a question like “What is the total budget across all programs?” and Copilot reads your table, identifies the relevant column, and calculates the answer. Ask “Which department has the highest spending?” and Copilot sorts through the data and tells you. Ask “How many records have a status of Pending?” and Copilot counts and responds.

These are not canned queries. You can ask any question that relates to the data in your table. Copilot understands your column headers and uses them to interpret your questions. If your table has columns named “Department,” “Amount,” “Date,” and “Status,” Copilot uses those labels to map your question to the right data.

Follow-up questions work as well. After asking about total spending, you can ask “Break that down by quarter” or “Which quarter had the highest total?” Copilot maintains context within the conversation.

Here is a government scenario. You manage a procurement tracking spreadsheet with columns for vendor, contract value, award date, status, and agency. You ask Copilot, “What is the total contract value for awards made in the last quarter?” Then follow up with, “Which vendor has the most awards?” and “How many contracts are still pending?” In two minutes you have answers that would have taken ten minutes of filtering and formula building.

Tips for better results: use the exact column names from your table in your questions. Be specific — “What is the average processing time for requests submitted in January?” works better than “Tell me about processing times.” The more precise your question, the more accurate the answer.

Getting data insights and trends

Beyond answering specific questions, Copilot can analyze your data for patterns you might not think to ask about.

Ask Copilot to “Analyze this data” or “Show me insights about this table.” Copilot examines your data and returns observations — trends over time, comparisons between categories, outliers that stand out, and correlations between columns. These insights are presented as plain-English descriptions that you can review and act on.

For trend analysis, ask “Show me the spending trend over the last twelve months” or “How has the number of open requests changed quarter over quarter?” Copilot identifies the time-based patterns and describes them clearly.

For comparisons, ask “Compare spending across departments” or “Which regions have the fastest processing times?” Copilot groups and compares the data and highlights meaningful differences.

Here is a government scenario. You have a quarterly spend report with data from four program offices across three fiscal years. Ask Copilot, “Which program office shows the largest increase in spending year over year?” Copilot calculates the growth rates and identifies the answer. Follow up with “Is there a seasonal pattern in the spending?” and Copilot checks for quarterly fluctuations.

One important point: Copilot works with the visible data in your table. If rows are filtered out or data is on another sheet, Copilot may not include it. Make sure the data you want analyzed is visible and in the active table.

You should also verify Copilot’s insights against your own understanding. Copilot is excellent at pattern detection, but you know the context behind the numbers. If Copilot flags an outlier, check whether it is a genuine anomaly or has a known explanation.

Creating visualizations with Copilot

One of the most useful features in Copilot for Excel is chart creation. Instead of manually selecting data ranges, choosing chart types, and formatting axes, you describe what you want and Copilot builds it.

Ask “Create a bar chart showing spending by department” and Copilot generates the chart. Ask “Show me a line chart of monthly revenue over the past year” and Copilot plots the trend. Ask “Create a pie chart of contract status distribution” and Copilot builds the breakdown.

Copilot selects an appropriate chart type based on your data, but you can override it. If you ask for a bar chart and decide you want a line chart instead, just tell Copilot, “Change that to a line chart.” You can also refine the chart: “Add data labels” or “Sort the bars from highest to lowest.”

Once Copilot generates a chart, you can insert it directly into your workbook. The chart becomes a standard Excel chart that you can further customize, resize, and format using Excel’s built-in chart tools.

Here is a government scenario. You are preparing a quarterly performance review and need visuals for your briefing. Ask Copilot to “Create a bar chart comparing on-time delivery rates across all program offices.” Then ask for “A line chart showing the trend in open tickets over the past six months.” In five minutes you have two presentation-ready charts that would have taken considerably longer to build manually.

Tips for better chart prompts: specify the chart type when you have a preference. Name the exact columns you want on each axis. Include any filters, such as “only include records from fiscal year 2026.” The more detail you provide, the closer the first result will be to what you need.

Understanding Copilot’s data analysis capabilities

To use Copilot effectively, it helps to understand what it can and cannot do.

Copilot can summarize data, calculate totals and averages, compare groups, identify trends over time, highlight outliers, sort and filter data, and create charts. It works with the structured data in your Excel table and uses your column headers to understand what each data point represents.

Copilot cannot connect to external data sources, modify your source data automatically, or work with data that is not in an Excel table format. It operates within the boundaries of your current workbook and the active table. If you need to combine data from multiple sources, you will need to bring it into the table first.

For best results, keep your data clean. Consistent column headers, no merged cells, no blank rows in the middle of your data, and consistent data types within each column. Clean data leads to better answers from Copilot.

For government environments, sensitivity labels and data protection policies apply to Copilot outputs just as they apply to the data itself. If your spreadsheet has sensitivity labels, Copilot respects those labels in its responses. This ensures that data governance extends to AI-generated insights.

Close: Let Copilot work your data

Let us recap what you have learned. Copilot in Excel lets you ask questions about your data in plain English and get immediate answers. It identifies trends, patterns, and outliers that might take hours to find manually. And it creates charts and visualizations from simple descriptions, so you can go from raw data to a presentation-ready visual in minutes.

Here is what to do next. Open a spreadsheet you work with regularly. Make sure the data is formatted as an Excel table and saved to OneDrive or SharePoint. Click the Copilot button and ask a simple question — “What is the total for this column?” or “Which category has the most entries?” Start simple and build from there.

Your data already has answers. Copilot helps you find them. Start asking.

Sources & References

• Get started with Copilot in Excel — Prerequisites and getting started guide for Copilot in Excel
• Analyze data in Excel with Copilot — Data analysis capabilities and prompting techniques
• Copilot adoption resources — Adoption guidance and government cloud availability