Excel pivot tables, scenario and solver:
Refresh PivotTable data
When you need to make changes to the data source associated with a PivotTable,
you must make them in the source itself and then manually refresh the PivotTable.
This keeps the PivotTable in sync with the data source.
At the bottom of the Excel window, click the Sales PivotTable worksheet tab.
You recently changed two of the values in column E in the Regional Sales
worksheet. You want to update the associated PivotTable to reflect this change. To
do so, you first have switched to the Sales PivotTable worksheet.
In the Sales PivotTable worksheet, click cell A3.
The PivotTable is selected, and the PivotTable Tools tabs appear on the ribbon.
Click the PivotTable Tools Analyze tab on the ribbon.
In the Data group, click the Refresh button.
Excel updated the pivot table. The value in cell D5 changed from 134,910 to
140,000, and the value in cell D6 changed from 78,500 to 99,500. Excel also
updated the Grand Total in cell D18.
Remove a field from an area of the PivotTable Fields pane
A PivotTable is a handy tool that lets you rearrange data quickly and easily,
providing numerous ways to view and analyze the data. When you decide you no
longer want a field to be included, however, you can just as easily remove the field
from the pivot table by dragging it out of the PivotTable Fields pane.
Click cell A4.
The PivotTable is selected, and the PivotTable Tools tabs appear on the ribbon. The
PivotTable Fields task pane appears at the right of the worksheet window.
In the Filters area of the PivotTable Fields pane, move the pointer over the Region
field button, press and hold the left mouse button, and then drag the Region field to
the left, into a blank area of the worksheet. Release the mouse button.
When you drag the Region field over the worksheet, an X appears on the pointer
shape, indicating that the field will be deleted. When you release the mouse button,
Excel removes the field and its associated filter in row 2 of the PivotTable. The
Filters area of the pane is now empty, and the check box next to the Region field in
the top half of the pane is unchecked.
Filter data in a PivotTable using a Slicer
Adding a slicer to a PivotTable provides a visual filter that displays buttons for each
item in the field for which the slicer is created. You can click to select any number of
the buttons, and the PivotTable will display only data for those items.
In the Region slicer, click the Southern button.
Excel displays the data for PivotTable records with the Region field value of
Southern. Note that Excel highlights the Southern button in the slicer and removes
highlighting from the remaining buttons. You also want to display records for the
,Western region.
Press and hold the CTRL key, click the Western button, then release the CTRL key.
Excel highlights the Western button in the slicer and expands the PivotTable display
to include records for the Western region.
Add a calculated field to a PivotTable
Adding a calculated field to your PivotTable lets you perform a calculation on fields
already in the PivotTable. For example, you can quickly add quarterly sales to your
PivotTable based on the existing monthly sales values.
Click cell F5.
The PivotTable is selected, and the PivotTable Tools tabs appear on the ribbon. The
PivotTable Fields task pane appears at the right of the worksheet window.
Click the PivotTable Tools Analyze tab.
In the Calculations group, click the Fields, Items, & Sets button.
The Fields, Items, & Sets menu opens.
Click Calculated Field.
The Insert Calculated Field dialog box opens. The default text in the Name text box
is highlighted. You will enter a name for the calculated field here.
Type Q1 in the Name text box.
In the Fields list, double-click January.
Excel re-enters the = (equal) sign to indicate you are starting a formula, then begins
to enter the field names that you select, starting with January.
Type + (plus) in the Formula text box.
In the Fields list, double-click February.
Type + (plus) in the Formula text box.
In the Fields list, double-click March.
The Q1 calculated field will total the values in the January, February, and March
fields.
Click OK.
Excel inserts the Q1 calculated field into the PivotTable, renaming the field Sum of
Q1 in reference to the calculation it performs. The Sum of Q1 field also appears in
the VALUES area of the PivotTable Fields pane.
Insert a slicer into a PivotTable
A pivot table is a handy tool that provides numerous ways to view and analyze data.
Adding a slicer makes it easy to filter data visually by displaying buttons for each
item in the field to be filtered.
Click cell A4.
The Regional Sales PivotTable is selected, and the PivotTable Tools tabs appear on
the ribbon.
Click the PivotTable Tools Analyze tab on the ribbon.
In the Filter group, click the Insert Slicer button.
, The Insert Slicers dialog box opens. A slicer will be created for each item you select
in the dialog box.
In the Insert Slicers dialog box, click the Region check box to select it.
The slicer will filter the PivotTable by Region.
Click OK.
Excel adds a slicer to the PivotTable with a button for each category in the Region
field. The slicer is selected, and the Slicer Tools Options tab appears on the ribbon.
Because the slicer is an Excel object, you can move, resize, and format the slicer as
desired.
Show field headers in a Pivot Table
Show PivotTable field headers so you can filter and sort PivotTable fields using
commands on the field headers.
Click cell A4.
The PivotTable is selected, and the PivotTable Tools tabs appear on the ribbon.
Click the PivotTable Tools Analyze tab.
Note that in the Show group, the Field Headers button is not highlighted, indicating
that the field headers are not currently displayed in the PivotTable.
In the Show group, click the Field Headers button.
Excel toggles the Field Headers button to the on position and displays the field
headers in the PivotTable. Note that the width of the second column increases to
accommodate the field header text.
Add a field to the COLUMNS area of the PivotTable Fields task pane
Display numeric data in PivotTable columns. When you drag the field buttons to the
COLUMNS location in the pane, you can preview, and quickly change, the layout of
your PivotTable.
Click cell A4.
The PivotTable is selected and the PivotTable Tools tabs appear on the ribbon. The
PivotTable Fields pane is currently hidden. You need to open the pane before you
can add a field to the Columns area.
Click the PivotTable Tools Analyze tab.
In the Show group, click the Field List button.
The PivotTable Fields pane opens at the right of the Excel window.
In the PivotTable Fields pane, in the Choose fields to add to report area, move the
pointer over the Branch field button. Press and hold the left mouse button, and then
drag the Branch field to the Columns area. Release the mouse button.
Excel places the Branch field in the Columns area of the PivotTable Fields pane, and
displays the field values in the PivotTable as columns.
Apply a style to a PivotTable
Refresh PivotTable data
When you need to make changes to the data source associated with a PivotTable,
you must make them in the source itself and then manually refresh the PivotTable.
This keeps the PivotTable in sync with the data source.
At the bottom of the Excel window, click the Sales PivotTable worksheet tab.
You recently changed two of the values in column E in the Regional Sales
worksheet. You want to update the associated PivotTable to reflect this change. To
do so, you first have switched to the Sales PivotTable worksheet.
In the Sales PivotTable worksheet, click cell A3.
The PivotTable is selected, and the PivotTable Tools tabs appear on the ribbon.
Click the PivotTable Tools Analyze tab on the ribbon.
In the Data group, click the Refresh button.
Excel updated the pivot table. The value in cell D5 changed from 134,910 to
140,000, and the value in cell D6 changed from 78,500 to 99,500. Excel also
updated the Grand Total in cell D18.
Remove a field from an area of the PivotTable Fields pane
A PivotTable is a handy tool that lets you rearrange data quickly and easily,
providing numerous ways to view and analyze the data. When you decide you no
longer want a field to be included, however, you can just as easily remove the field
from the pivot table by dragging it out of the PivotTable Fields pane.
Click cell A4.
The PivotTable is selected, and the PivotTable Tools tabs appear on the ribbon. The
PivotTable Fields task pane appears at the right of the worksheet window.
In the Filters area of the PivotTable Fields pane, move the pointer over the Region
field button, press and hold the left mouse button, and then drag the Region field to
the left, into a blank area of the worksheet. Release the mouse button.
When you drag the Region field over the worksheet, an X appears on the pointer
shape, indicating that the field will be deleted. When you release the mouse button,
Excel removes the field and its associated filter in row 2 of the PivotTable. The
Filters area of the pane is now empty, and the check box next to the Region field in
the top half of the pane is unchecked.
Filter data in a PivotTable using a Slicer
Adding a slicer to a PivotTable provides a visual filter that displays buttons for each
item in the field for which the slicer is created. You can click to select any number of
the buttons, and the PivotTable will display only data for those items.
In the Region slicer, click the Southern button.
Excel displays the data for PivotTable records with the Region field value of
Southern. Note that Excel highlights the Southern button in the slicer and removes
highlighting from the remaining buttons. You also want to display records for the
,Western region.
Press and hold the CTRL key, click the Western button, then release the CTRL key.
Excel highlights the Western button in the slicer and expands the PivotTable display
to include records for the Western region.
Add a calculated field to a PivotTable
Adding a calculated field to your PivotTable lets you perform a calculation on fields
already in the PivotTable. For example, you can quickly add quarterly sales to your
PivotTable based on the existing monthly sales values.
Click cell F5.
The PivotTable is selected, and the PivotTable Tools tabs appear on the ribbon. The
PivotTable Fields task pane appears at the right of the worksheet window.
Click the PivotTable Tools Analyze tab.
In the Calculations group, click the Fields, Items, & Sets button.
The Fields, Items, & Sets menu opens.
Click Calculated Field.
The Insert Calculated Field dialog box opens. The default text in the Name text box
is highlighted. You will enter a name for the calculated field here.
Type Q1 in the Name text box.
In the Fields list, double-click January.
Excel re-enters the = (equal) sign to indicate you are starting a formula, then begins
to enter the field names that you select, starting with January.
Type + (plus) in the Formula text box.
In the Fields list, double-click February.
Type + (plus) in the Formula text box.
In the Fields list, double-click March.
The Q1 calculated field will total the values in the January, February, and March
fields.
Click OK.
Excel inserts the Q1 calculated field into the PivotTable, renaming the field Sum of
Q1 in reference to the calculation it performs. The Sum of Q1 field also appears in
the VALUES area of the PivotTable Fields pane.
Insert a slicer into a PivotTable
A pivot table is a handy tool that provides numerous ways to view and analyze data.
Adding a slicer makes it easy to filter data visually by displaying buttons for each
item in the field to be filtered.
Click cell A4.
The Regional Sales PivotTable is selected, and the PivotTable Tools tabs appear on
the ribbon.
Click the PivotTable Tools Analyze tab on the ribbon.
In the Filter group, click the Insert Slicer button.
, The Insert Slicers dialog box opens. A slicer will be created for each item you select
in the dialog box.
In the Insert Slicers dialog box, click the Region check box to select it.
The slicer will filter the PivotTable by Region.
Click OK.
Excel adds a slicer to the PivotTable with a button for each category in the Region
field. The slicer is selected, and the Slicer Tools Options tab appears on the ribbon.
Because the slicer is an Excel object, you can move, resize, and format the slicer as
desired.
Show field headers in a Pivot Table
Show PivotTable field headers so you can filter and sort PivotTable fields using
commands on the field headers.
Click cell A4.
The PivotTable is selected, and the PivotTable Tools tabs appear on the ribbon.
Click the PivotTable Tools Analyze tab.
Note that in the Show group, the Field Headers button is not highlighted, indicating
that the field headers are not currently displayed in the PivotTable.
In the Show group, click the Field Headers button.
Excel toggles the Field Headers button to the on position and displays the field
headers in the PivotTable. Note that the width of the second column increases to
accommodate the field header text.
Add a field to the COLUMNS area of the PivotTable Fields task pane
Display numeric data in PivotTable columns. When you drag the field buttons to the
COLUMNS location in the pane, you can preview, and quickly change, the layout of
your PivotTable.
Click cell A4.
The PivotTable is selected and the PivotTable Tools tabs appear on the ribbon. The
PivotTable Fields pane is currently hidden. You need to open the pane before you
can add a field to the Columns area.
Click the PivotTable Tools Analyze tab.
In the Show group, click the Field List button.
The PivotTable Fields pane opens at the right of the Excel window.
In the PivotTable Fields pane, in the Choose fields to add to report area, move the
pointer over the Branch field button. Press and hold the left mouse button, and then
drag the Branch field to the Columns area. Release the mouse button.
Excel places the Branch field in the Columns area of the PivotTable Fields pane, and
displays the field values in the PivotTable as columns.
Apply a style to a PivotTable
