Transcript excel - WVU CS101

EXCEL
PIVOT TABLE
CHAPTER 5
• A Pivot table is a powerful, interactive data mining feature that
enables you to summarize and analyze data, especially when working
with large datasets.
• We can easily and quickly rearrange the data, group data differently,
analyze data from different viewpoints and switch row and column
categories.
• Viewing the pivot table from different viewpoints helps you identify
different patterns and trends among variables in the data that might
not be obvious from looking at the data from only one viewpoint.
CREATING A PIVOT TABLE
• Select the range of cells.
• Click the insert tab and click the recommended pivot tables in the
tables group to open the recommended pivot tables dialog box.
• Click a thumbnail in the gallery on the left side of the dialog box to
see a preview of the pivot table on the right side.
• Click ok to create the desired pivot table.
AREAS OF PIVOT TABLE
• Values : Displays summary statistics, such as totals or averages
• Row Labels : Organizes and groups data into categories on the left
side of the PivotTable.
• Column Labels : Subdivides data into one or more additional
categories to add depth to PivotTable.
• Report Filter : Filters the aggregated data to displays the results
based on particular conditions you set.
ADDING FIELDS
• To row labels
• To column labels
• To values
• To Report Filter
MOIFYING A PIVOT TABLE
• Add or remove fields
• Rearrange fields
• Change the value field settings
• Change Data.
EXAMPLE FOR MODIFYING A PIVOT TABLE
• Remove the Edition field
• Move Copyright field from Row Labels to Column Labels area.
• Open Value Field Settings box, give custom name as ‘Sales by
Discipline’ and change the format to Accounting with 0 decimal
places.
• Replace Row Labels and Column Labels with Discipline and Copyright
Year.
• Change the cell J1 in Books Data worksheet to 125% and refresh the
pivot table.
APPLYING FILTERS
• Apply filters to show a subset of data in a PivotTable
• Two types of filters:
• A report filter sets the overall conditions for aggregating data
• A group filter filters out data based on a row or column category
• Filter by one or multiple items, as well as by entering a search
condition
EXAMPLE FOR USING FILTERS
• Drag Edition field to Report Filter area. Select multiple items check
box and select 1 and 2 check boxes.
• Copyright filters  Label filters >=2011
• Discipline filters  Check Introductory, Social
Problems, Social
Psychology check boxes.
SLICER
A small window containing one button for each unique item in a field
so that you can filter the PivotTable quickly.
Steps:
1. Insert Slicer
2. Click one or more field check boxes to display one or more slicers.
PIVOT CHART
• A PivotChart is a graphical representation of data in a PivotTable.
• You can create a PivotChart from a PivotTable or at the same time you
create a PivotTable.
• If you create a PivotChart from a PivotTable, the Row Labels area
changes to Axis Fields, and the Column Labels area changes to Legend
Fields when you select the PivotChart.
• A PivotChart is interactive, meaning changes made in the associated
PivotTable are immediately reflected in the PivotChart.
EXAMPLE TO CREATE A PIVOT CHART
• Create a PivotChart
Insert Tab -> Charts -> PivotChart
Select range from the Books Data sheet
Click OK
• Drag Discipline field to Axis Fields area
• Drag Total book sales field to Values area
• Drag Copyright field to Legend Fields area
• Change the Chart type and give a chart name