How to create a summary table in Excel with AI

Simplify analysis for large datasets and find totals and patterns faster with the right spreadsheet tools. Summary tables, also known as PivotTables in Microsoft Excel, are a practical way to turn detailed data into organized summaries, so they’re easier to review.

Creating summary tables from scratch is a manual, detailed process, but Copilot in Excel helps make it easier by reducing steps, for both beginners and experienced users. Generate a ready-to-review table using AI to find a quick starting point for analysis, with just a few simple instructions and minimal effort. Read on to learn how summary tables work, easy steps to create them in Microsoft Excel, and Copilot tips to get started.

How do summary tables work in Excel

Summary tables condense datasets to highlight the most useful information, while a PivotTable is the Excel tool that creates these types of tables. The tool groups rows of values and presents totals, counts, and averages in an easy-to-read format, using a simple workflow.

1. Group data: Excel splits and organizes information into relevant categories, such as defined time periods, regions, average spends, or sales.

2. Calculate values: Excel then performs calculations using SUM, COUNT, or AVERAGE, to calculate values for each group or as per the task.

3. Display summary: the result is a concise table that is easier to scan than the original data.

This process works best for tasks that involve cleaning up and categorizing large volumes of information to compare groups for smooth decision-making. If a revenue spreadsheet lists sales by region, a summary table can show values for each region side by side, making it easy to spot local trends and decide where to focus next.

With Copilot, creating summary tables can change from being a lengthy workflow into an easy one. Copilot can also provide a brief analysis of emerging patterns and insights from the table to help with strategic planning.

How to create PivotTables in Excel with AI

1. Upload or open a spreadsheet in Excel for the web.

2. Organize the dataset into rows and columns with clear headers, and check that no rows or columns are blank.

3. Locate the Copilot in Excel icon to initiate a chat. Explain the task and required summaries using simple phrases.

4. Review the suggested summary table that Copilot creates. Check if data is grouped correctly, for example, by region or month, and that suggested calculations like total or averages match the task.

5. Once the table is ready, Excel may place the table in a new or existing sheet.

6. Adjust the table if needed by changing fields, updating calculations (for example, sum to average), or applying filters and sorting.

7. Ask follow-up prompts in Copilot to refine the table or to explain more insights.

8. Save and share the summary for review using a secure online link, or export as a PDF to send via email.

Tip: refresh the PivotTable when the source data changes to keep the summary up to date.

Try these Copilot in Excel prompts to get started

Calculate revenue in sales

Try this Copilot prompt"Create a PivotTable to outline average spend per customer for every region and provide key insights to help standardize pricing."Click to copy this Copilot prompt

Compare budgets with spending

Try this Copilot prompt"Build a PivotTable to summarize spending compared to income to better manage personal expenses and increase monthly savings."Click to copy this Copilot prompt

Review business expenses

Try this Copilot prompt"Produce a PivotTable to show expenses by category across the month and highlight areas with higher spending."Click to copy this Copilot prompt

Summary tables bring structure to data and reduce the need for manual setups by grouping rows and showing key values in one place. Using a few simple prompts in a Copilot chat, finalize data and make progress on action points.

Explore more ways Copilot in Excel can help with the process of analyzing spreadsheets and optimize everyday tasks with the online formula generator in Microsoft Excel.