Analyzing all of the data in a worksheet can help you make better decisions. Creating a
PivotTable from sheet data is a great way to summarize, analyze, explore, and present your data by freely moving and filtering data fields.
PivotCharts help you visualize this data. While a PivotChart shows data series, categories, and chart axes the same way as a standard chart, it also gives you the same interactive and dynamic controls you get from a PivotTable.
Convert Your Data into a Table
- Click the top left-most cell in your data array.
- In the
Styles group under the
Home tab, click the
Format as Table button and select any style.
- In the
Format As Table dialogue, confirm the area of your table and check the
My table has headers box.
- Confirm that the marching border includes your whole data array.
- Click
OK.
- Your new table will appear in the style that you selected at Step 2.
Convert Your Table into a PivotTable
- Click any cell inside of the new table.
- Click the
Pivot Table button in the
Tables group under the
Insert tab.
- In the
Create PivotTable dialogue, click the
New Worksheet radio to place the PivotTable on a separate worksheet.
- Click
OK
Pivot Your Data
- The
PivotTable Report appears on the left side of the new worksheet.
- The
PivotTable will be empty until
Fields are selected from the
Fields Pane on the right.
- A
Field is created from each of the
column headers in your original table.
- In the
Fields Pane on the right, you will find the
Field List at the top and the Field Area divided into four areas below.
- Click and drag desired fields into any quadrant of the
Field Area.*
- Drag
Fields freely from quadrant to quadrant to change the layout of the
PivotTable (on the left).
- This is what it means to “pivot.”
-
Pivot your
Fields in the
Fields Areas until your
PivotTable meets your needs and is easy to analyze.
-
Remove a Field from the
Report by unchecking its box in the
Field List above.
Insert a PivotChart
- Click any cell in the PivotTable.
- Click the
Insert tab in the ribbon.
- In the
Charts group, select the chart style you’d like.
- The new
PivotChart will insert on the same sheet as the
PivotTable.
- To avoid crowding, move the PivotChart to a new sheet.
- Click the
PivotChart.
- In the
Design tab of the
PivotChart Tools, click the
Move Button in the
Location group.
- In the
Move Chart dialogue, click the
New Sheet radio.
- Optionally, change the name of the new sheet.
- Click
OK
Filter the PivotChart Fields
The PivotChart will be found on a new sheet apart from any other tables in the workbook. Like the PivotTable, the PivotChart can be updated by pivoting fields in the Pivot Pane on the right side of the sheet.
- PivotChart tools are found in contextual ribbon tabs. These tabs are only visible with the chart is actively selected.
- Like tables, charts are dynamic and can be changed by pivoting Fields in the Field Pane.
-
Fields in tables and charts can be filtered to show only the data you want to see in the
Report.
- Hover over the
Field on which you’d like to apply the filter.
Example: Field = Company - Click the dropdown arrow that appears to the right of the Field.
- Apply any of three types of filter:
-
Selection – Use check boxes to hide items.
-
Rule – Set criteria either to
Label (field name) or
Value
(data) -
Search – Type a word, or phrase to display only items that match the typed text.
- A funnel icon will will display next to the Field with the Filter.
- Click
Clear Filter From “Field” to remove the filter.
If you have any questions or comments regarding the steps outlined in this document, please contact UHD TLS Training Services by calling (713) 221-8200, or by sending an email to
ttlctraining@uhd.edu.