How Do I Insert A Pivot Chart In Excel 2016 For Mac

Lesson 27: Intro to PivotTables

/en/excel2016/inspecting-and-protecting-workbooks/content/

Introduction

When you have a lot of data, information technology can sometimes be difficult to clarify all of the information in your worksheet. PivotTables can help make your worksheets more manageable by summarizing your data and allowing you to manipulate it in different ways.

Optional: Download our practice workbook.

Watch the video below to learn more virtually PivotTables.

Using PivotTables to reply questions

Consider the example below. Let'south say nosotros wanted to answer the question What is the corporeality sold by each salesperson? Answering it could exist time consuming and difficult; each salesperson appears on multiple rows, and we would need to full all of their different orders individually. We could use the Subtotal command to assistance notice the total for each salesperson, but we would still have a lot of data to piece of work with.

worksheet of sales data

Fortunately, a PivotTable can instantly calculate and summarize the data in a mode that volition make it much easier to read. When we're done, the PivotTable will await something like this:

sales total by salesperson

Once y'all've created a PivotTable, you can use it to reply dissimilar questions past rearranging—or pivoting—the data. For case, permit's say nosotros wanted to answer What is the total amount sold in each month? We could alter our PivotTable to look similar this:

sales total by month

To create a PivotTable:

Select the table or cells (including column headers) yous want to include in your PivotTable.
From the Insert tab, click the PivotTable control.
The Create PivotTable dialog box will appear. Choose your settings, then click OK. In our case, nosotros'll use Table1 as our source data and place the PivotTable on a new worksheet.
A blank PivotTable and Field List will announced on a new worksheet.
Once you create a PivotTable, you lot'll demand to decide which fields to add. Each field is simply a column header from the source information. In the PivotTable Field List, bank check the box for each field you desire to add. In our example, we want to know the total amount sold by each salesperson, and then we'll check the Salesperson and Society Amount fields.
The selected fields will be added to one of the iv areas below. In our instance, the Salesperson field has been added to the Rows surface area, while Club Amount has been added to Values. Alternatively, you can drag and drib fields directly into the desired expanse.
The PivotTable volition calculate and summarize the selected fields. In our example, the PivotTable shows the corporeality sold by each salesperson.

Merely like with normal spreadsheets, you can sort the data in a PivotTable using the Sort & Filter control on the Home tab. You can too utilise any type of number formatting y'all desire. For case, you may desire to change the number format to Currency. All the same, be aware that some types of formatting may disappear when you change the PivotTable.

sorted and formatted

If you lot change any of the information in your source worksheet, the PivotTable will not update automatically. To manually update it, select the PivotTable and then become to Analyze Refresh.

Pivoting data

I of the best things about PivotTables is that they can quickly pivot—or reorganize—your data, assuasive you to examine your worksheet in several ways. Pivoting data tin can help you answer different questions and even experiment with your data to detect new trends and patterns.

To add together columns:

So far, our PivotTable has only shown one column of data at a time. In social club to show multiple columns, you'll need to add a field to the Columns area.

Elevate a field from the Field Listing into the Columns area. In our case, we'll use the Month field.
The PivotTable will include multiple columns. In our example, there is at present a column for each person's monthly sales, in addition to the grand total.

To alter a row or column:

Changing a row or cavalcade can requite you a completely different perspective on your data. All y'all have to do is remove the field in question, then supervene upon it with another.

Drag the field you desire to remove out of its current area. You can as well uncheck the appropriate box in the Field List. In this instance, we've removed the Calendar month and Salesperson fields.
Drag a new field into the desired area. In our instance, we'll place the Region field under Rows.
The PivotTable will adjust—or pivot—to evidence the new information. In our case, it now shows the amount sold past each region.

To learn more

Once you're comfortable with PivotTables, check out our Doing More than with PivotTables lesson for even more than ways to customize and dispense them.

Challenge!

Open our practice workbook.
Create a PivotTable in a separate sheet.
Nosotros want to answer the question What is the total amount sold in each region? To practice this, select Region and Order Amount. When y'all're finished, your workbook should wait like this:
In the Rows area, remove Region and supervene upon information technology with Salesperson.
Add Calendar month to the Columns area.
Alter the number format of cells B5:E13 to Currency. Annotation: You lot might have to make columns C and D wider in order to see the values.
When you're finished, your workbook should look like this: