Learn how to use PivotTables using Copilot in Excel

Greetings, we continue our series of posts this week that show how you can get help from copilot with the chat helper. Today we will show how you can use it to learn how to use PivotTables. We’ll show this by asking Copilot in Excel the following question:

 Can you tell me the basics for creating a PivotTable?

Based on the question, Copilot in Excel returns a step-by-step guide for how to create a PivotTable in Excel:

Picture of Copilot in Excel pane showing the above prompt with a step-by-step guide for creating a PivotTable.

 

"Certainly! Here's a step-by-step guide to creating a PivotTable in Excel:

1. Select Your Data: Click on any cell within the range of data you want to use for your PivotTable. Make sure your data has column headings and no blank rows or columns.
2. Insert PivotTable: Go to the Ribbon, click on the "Insert" tab, and then click on "PivotTable". Excel will automatically select the data for the PivotTable or you can select the range manually.
3. Choose Where to Place the PivotTable: In the dialog box that appears, you can choose to place the PivotTable in a new worksheet or in an existing worksheet. Select your preference and click "OK".
4. Arrange Fields: Drag and drop the fields from your data (listed in the PivotTable Field List) into one of the four areas:
• Filters: To apply filters that affect the entire PivotTable.
• Columns: To show data horizontally across the top of the PivotTable.
• Rows: To display data vertically on the side of the PivotTable.
• Values: To aggregate data (sum, count, average, etc.) in the body of the PivotTable.
5. Customize Your PivotTable: You can further customize your PivotTable by sorting, filtering, and formatting the data. You can also change the summary function and refresh the data if the source data changes.
6. Refresh PivotTable: If the source data changes, you can update the PivotTable by right-clicking anywhere in the PivotTable and selecting "Refresh".

Remember to check that your data is well-organized, with no empty rows or columns, and that each column has a unique header. This will ensure that your PivotTable works correctly."

 

The response is a good overview. If you want to go a bit deeper and get more detail, you could use the following prompt:

Can you give me a more detailed example?

 

Copilot in Excel returns more information, using a sales data set as an example and shows how a PivotTable may layout the data:

Picture of Copilot in Excel pane showing the above prompt with a detailed example for creating a PivotTable.

 

"Certainly! Let's say you have a sales dataset with the following columns: Date, Product, Region, Salesperson, and Sales. Here's how you would create a PivotTable to analyze total sales by product and region:

1. Select Your Data: Click on any cell within your dataset. Ensure that each column has a unique header and there are no blank rows or columns in your range.
2. Insert PivotTable: Go to the "Insert" tab on the Ribbon and click on "PivotTable". Excel will select the data for the PivotTable or you can adjust the range if needed.
3. Choose PivotTable Location: In the Create PivotTable dialog box, choose to place the PivotTable in a new worksheet or an existing worksheet. Click "OK".
4. Arrange Fields: In the PivotTable Field List, drag and drop the Product field to the Rows area, the Region field to the Columns area, and the Sales field to the Values area. This will create a matrix with products listed down the rows, regions across the columns, and the sum of sales in the body of the PivotTable.
5. Customize Your PivotTable: You can sort the products or regions, apply filters, or change the summary function from sum to count, average, etc., by using the dropdown menus in the PivotTable.
6. Refresh PivotTable: If your source data changes, right-click anywhere in the PivotTable and select "Refresh" to update the data.

Here's an example of what the PivotTable could look like:

        | Region 1 | Region 2 | Region 3 | Grand Total

-----------------------------------------------------

Product A | $5,000   | $7,000   | $4,000   | $16,000

Product B | $3,000   | $8,000   | $2,000   | $13,000

Product C | $6,000   | $2,000   | $3,000   | $11,000

-----------------------------------------------------

Grand Total | $14,000  | $17,000  | $9,000   | $40,000

This PivotTable shows the total sales for each product in each region, as well as the grand totals for each product and each region."

 

Over the coming weeks I will be sharing more examples of what you can do with Copilot in Excel.

 

Thanks for reading,

Microsoft Excel Team

 

*Disclaimer: If you try these types of prompts and they do not work as expected, it is most likely due to our gradual feature rollout process. Please try again in a few weeks.