Click on the PivotTable button and select Create Manual PivotTable from the popup menu. A Create PivotTable window should appear. Select the range of data for the pivot table and click on the OK button. In this example, we've chosen cells A1 to D13 in Sheet1. Next, select where you wish to place the PivotTable.
In Excel, you can pivot data in a PivotTable or PivotChart by changing the field layout of the data. By using the PivotTable Field List, you can add, rearrange, or remove fields to show data in a PivotTable or PivotChart exactly the way that you want.
By default, changes you make in the PivotTable Field List are automatically updated in the report layout. To improve performance when you are accessing a large amount of external data, you can temporarily switch to manual updating.
Working with the PivotTable Field List
When you create a PivotTable, Excel displays the PivotTable Field List so that you can add fields to the PivotTable, rearrange and reposition them as needed, or remove them from the PivotTable. By default, the PivotTable Field List displays two sections:
A field section at the top for adding fields to and removing fields from the PivotTable
A layout section at the bottom for rearranging and repositioning fields
You can dock the PivotTable Field List to either side of the Excel window and horizontally resize it. You can also undock the PivotTable Field List, in which case, you can resize it both vertically and horizontally.
If you don't see the PivotTable Field List, make sure that you click anywhere in the PivotTable.
If you close the PivotTable Field List, you can display it again. Right-click the PivotTable, and then click Show Field List. You can also click Field List on the Ribbon (PivotTable Tools, Options tab, Show group for a PivotTable; PivotChart Tools, Analyze tab, Show/Hide group for a PivotChart).
If you don't see the fields that you want to use in the PivotTable Field List, refresh the PivotTable or PivotChart to display any new fields, calculated fields, measures, calculated measures, or dimensions that you have added since the last operation (PivotTable Tools, Options tab, Data group).
How the PivotTable Field List works
It's important to understand how the PivotTable field List works and the ways that you can arrange different types of fields so that you can achieve the results that you want when you create the field layout of a PivotTable or PivotChart.
1. An external data source contains structured data organized as one or more fields (also called columns) that are displayed in the Field List.
Manual Pivot Table On Mac Excel 2008 Page Break Preview
2. Move a field to the Report Filter area in the Field List, which simultaneously moves the field to the Report Filter area in the PivotTable.
3. Move a field to the Column Label area in the Field List, which simultaneously moves the field to the Column Label area in the PivotTable.
4. Move a field to the Row Label area in the Field List, which simultaneously moves the field to the Row Label area in the PivotTable.
5. Move a field to the Values area in the Field List, which simultaneously moves the field to the Values area in the PivotTable.
Guidelines for moving fields in the PivotTable Field List
To create a field layout, use the following guidelines for moving Value, Name, and Dimension fields from the field section to the four report areas in the layout section.
Value fields If you select a check box only for a numeric field, by default, it is moved to the Values area.
Row and Column fields You can add a field only once to either the Report Filter, Row Labels, or Column Labels areas, whether the data type is numeric or non-numeric. If you try to add the same field more than once — for example, to the Row Labels and the Column Labels areas in the layout section — the field is automatically removed from the original area and put in the new area.
Measures In an Online Analytical Processing (OLAP) data source, there are often many fields (or measures) organized in a hierarchy of different dimensions, hierarchies, and levels. Click the expand and collapse buttons until you find the fields that you want.
You can move only hierarchies, attributes, and named sets to the Row Labels, Column Labels, and Report Filters areas.
You can move only measures, calculated measures, and Key Performance Indicators (KPIs) to the Values area.
Changing the PivotTable Field List view
The PivotTable Field List has five different views that are designed and optimized for different types of PivotTable tasks.
To change the view, click the Field List views button at the top of the PivotTable Field List.
In the list, select one of the following views:
Fields Section and Areas Section Stacked | This is the default view, and it is designed for a small number of fields. |
Fields Section and Areas Section Side-By-Side | This view is designed for adding and removing fields when you have more than four fields in each area. |
Fields Section Only | This view is designed for just adding and removing many fields. |
Areas Section Only (2 by 2) | This view is designed for just rearranging many fields. |
Areas Section Only (1 by 4) | This view is designed for just rearranging many fields. |
Tip In the Fields Section and Areas Section Stacked and Fields Section and Areas Section Side-By-Side views, you can adjust the width and height of each section by resting the pointer on the section divider until the pointer changes to a vertical double arrow or horizontal double arrow , by dragging the double arrow up or down or left or right to where you want it, and then either clicking the double arrow or pressing ENTER.
Add fields to a PivotTable or PivotChart
After you create a PivotTable or PivotChart, you are ready to add the fields that contain the data you want to display in the report. You typically select one field for each area in the layout section. However, to see different values for a specific field, you can also add multiple copies of a field to the Values area.
If the PivotTable is connected to an external data source that contains a lot of data, you can filter one or more fields before you add fields to the report, which can help reduce the time it takes to update the report.
Add the fields you want to display
In the PivotTable Field List, you can add fields to the PivotTable or PivotChart by doing one or more of the following:
To place a field in a designated area of the layout section, in the Choose fields to add to report box, select the check box of the field that you want. You can then rearrange fields later if you want.
Note By default, nonnumeric fields are added to the Row Labels area, numeric fields are added to the Values area, and Online Analytical Processing (OLAP) date and time hierarchies are added to the Column Labels area.
To place a field in a specific area of the layout section, in the Choose fields to add to report box, right-click the field name, and then click Add to Report Filter, Add to Column Label, Add to Row Label, or Add to Values.
To drag a field to an area in the layout section, in the Choose fields to add to report box, click and hold a field, and then drag it from the field section to the layout section that you want.
Add multiple copies of a field in the Values area
In a PivotTable that is based on data in an Excel worksheet or external data from a non-Online Analytical Processing (OLAP) data source, you may want to add the same field more than once to the Values area. You can do this whether the data type is numeric or non-numeric. For example, you may want to compare calculations side-by-side, such as gross and net profit margins, minimum and maximum sales, or customer counts and percentage of total customers.
In the PivotTable Field List, in the Choose fields to add to report box, click and hold a field, and then drag it to the Values area in the layout section.
Repeat step 1 to create as many copies of that field that you want to display in the Value area.
In the PivotTable, change the summary function or custom calculation the way you want in each copy of the field.
Tip You can also change the name of the copied fields in the PivotTable itself.
Notes
When you add two or more fields to the Values area, whether they are copies of the same field or different fields, the Field List automatically adds a Values Column label to the Values area. You can use this field to move the field positions up and down in the Values area. You can even move the Values Column Label to the Column Labels area or Row Labels areas. However, you cannot move the Values Column label to the Report Filters area.
You can add a field only once to either the Report Filter, Row Labels, or Column Labels areas, whether the data type is numeric or non-numeric. If you try to add the same field more than once — for example, to the Row Labels and the Column Labels areas in the layout section — the field is automatically removed from the original area and put in the new area.
Another way to add the same field to the Values area is by using a formula (also called a calculated field) that uses that same field in the formula.
Filter data before you add fields
In the PivotTable Field List, in the Choose fields to add to report box, rest the pointer on a field name, and then click the filter drop-down arrow next to the field name.
On the Filter menu, select the filter options that you want.
Rearrange fields in the PivotTable or PivotChart
In the PivotTable Field List, you can rearrange existing fields or reposition those fields by using one of the four areas at the bottom of the layout section:
PivotTable | Description |
|---|---|
Values | Use to display summary numeric data. |
Row Labels | Use to display summary numeric data. |
Column Labels | Use to display fields as columns at the top of the report. A column lower in position is nested within another column immediately above it. |
Report Filter | Use to filter the entire report based on the selected item in the report filter. |
PivotChart | Description |
|---|---|
Values | Use to display summary numeric data. |
Axis Field (Categories) | Use to display fields as an axis in the chart. |
Legend Fields (Series) | Use to display fields in the legend of the chart. |
Report Filter | Use to filter the entire report based on the selected item in the report filter. |
To rearrange fields in a PivotTable, click the field name in one of the areas, and then select one of the following commands:
Move Up | Moves the field up one position in the area. |
Move Down | Moves the field down position in the area. |
Move to Beginning | Moves the field to the beginning of the area. |
Move to End | Moves the field to the end of the area. |
Move to Report Filter | Moves the field to the Report Filter area. |
Move to Row Labels | Moves the field to the Row Labels area. |
Move to Column Labels | Moves the field to the Column Labels area. |
Move to Values | Moves the field to the Values area. |
Value Field Settings, Field Settings | Displays the Field Settings or Value Field Settings dialog boxes. For more information about each setting, click the Help button at the top of the dialog box. |
Tip You can also click and hold a field name, and then drag the field between the field and layout sections, and between the different areas.
Remove fields from the PivotTable or PivotChart
To remove a field, in the PivotTable Field List, do one of the following:
In the Choose fields to add to report box, clear the check box of the field you want to remove.
Note Clearing a check box removes all instances of the field from the report.
In a layout area, click the field that you want to remove, and then click Remove Field.
In a layout area, click and hold the field that you want to remove, and then drag it outside the PivotTable Field List.
Switch between automatic and manual updating of the report layout
By default, changes you make in the PivotTable Field List are automatically updated in the report layout. To improve performance when you are accessing a large amount of external data, you can temporarily switch to manual updating mode. Manual updating mode allows you to quickly add, move, and remove fields in the PivotTable Field List. However, you cannot use the report until you switch back to automatic updating mode.
To enable manual updating of the PivotTable, at the bottom of the PivotTable Field List, select the Defer layout update check box.
Caution After you set the report layout to manual updating, closing the PivotTable Field List, changing to Fields only view, or exiting Excel discards all layout changes that you have made to the PivotTable without confirmation.
In the PivotTable Field List, make the field layout changes, and then click Update to manually update the layout in the PivotTable.
To return to automatic updating after you finish changing the report layout in the Field List, clear the Defer layout update check box.
Note A PivotTable starts with automatic updating each time that you open the workbook.
Working with the PivotTable Field List
When you create a PivotTable, Excel displays the PivotTable Field List so that you can add fields to the PivotTable, rearrange and reposition them as needed, or remove them from the PivotTable. By default, the PivotTable Field List displays two sections:
A field section at the top for adding fields to and removing fields from the PivotTable
A layout section at the bottom for rearranging and repositioning fields
You can dock the PivotTable Field List to either side of the Excel window and horizontally resize it. You can also undock the PivotTable Field List, in which case, you can resize it both vertically and horizontally.
If you don't see the PivotTable Field List, make sure that you click anywhere in the PivotTable.
If you close the PivotTable Field List, you can display it again. Control-click the PivotTable, and then click Show Field List. You can also click Field List on the PivotTable Analyze tab.
If you don't see the fields that you want to use in the PivotTable Field List, refresh the PivotTable to display any new fields, calculated fields, measures, calculated measures, or dimensions that you have added since the last operation: PivotTable Analyze > Refresh.
Guidelines for moving fields in the PivotTable Field List
To create a field layout, use the following guidelines for moving Value, Name, and Dimension fields from the field section to the four report areas in the layout section.
Value fields If you select a check box only for a numeric field, by default, it is moved to the Values area.
Row and Column fields You can add a field only once to either the Report Filter, Row Labels, or Column Labels areas, whether the data type is numeric or non-numeric. If you try to add the same field more than once — for example, to the Row Labels and the Column Labels areas in the layout section — the field is automatically removed from the original area and put in the new area.
Measures In an Online Analytical Processing (OLAP) data source, there are often many fields (or measures) organized in a hierarchy of different dimensions, hierarchies, and levels. Click the expand and collapse buttons until you find the fields that you want.
You can move only hierarchies, attributes, and named sets to the Row Labels, Column Labels, and Report Filters areas.
You can move only measures, calculated measures, and Key Performance Indicators (KPIs) to the Values area.
Add fields to a PivotTable or PivotChart
After you create a PivotTable or PivotChart, you are ready to add the fields that contain the data you want to display in the report. You typically select one field for each area in the layout section. However, to see different values for a specific field, you can also add multiple copies of a field to the Values area.
If the PivotTable is connected to an external data source that contains a lot of data, you can filter one or more fields before you add fields to the report, which can help reduce the time it takes to update the report.
Add the fields you want to display
Manual Pivot Table On Mac Excel 2008 Filter
:max_bytes(150000):strip_icc()/calculate-current-age-with-excel-datedif-function-4178612-Final-fd9352a957f64920ac5cf1d418e8d064.png "Manual pivot table on mac excel 2008 page break preview")
In the PivotTable Field List, you can add fields to the PivotTable or PivotChart by doing one or more of the following:
To place a field in a designated area of the layout section, in the PivotTable Fields box, select the check box of the field that you want. You can then rearrange fields later if you want.
Note By default, nonnumeric fields are added to the Row Labels area, numeric fields are added to the Values area, and Online Analytical Processing (OLAP) date and time hierarchies are added to the Column Labels area.
To drag a field to an area in the layout section, in the PivotTable Fields box, click and hold a field, and then drag it from the field section to the layout section that you want.
Add multiple copies of a field in the Values area
In a PivotTable that is based on data in an Excel worksheet or external data from a non-Online Analytical Processing (OLAP) data source, you may want to add the same field more than once to the Values area. You can do this whether the data type is numeric or non-numeric. For example, you may want to compare calculations side-by-side, such as gross and net profit margins, minimum and maximum sales, or customer counts and percentage of total customers.
In the PivotTable Field List, in the PivotTable Fields box, click and hold a field, and then drag it to the Values area in the layout section.
Repeat step 1 to create as many copies of that field that you want to display in the Value area.
In the PivotTable, change the summary function or custom calculation the way you want in each copy of the field.
Tip You can also change the name of the copied fields in the PivotTable itself.
Notes
When you add two or more fields to the Values area, whether they are copies of the same field or different fields, the Field List automatically adds a Values Column label to the Values area. You can use this field to move the field positions up and down in the Values area. You can even move the Values Column Label to the Column Labels area or Row Labels areas. However, you cannot move the Values Column label to the Report Filters area.
You can add a field only once to either the Report Filter, Row Labels, or Column Labels areas, whether the data type is numeric or non-numeric. If you try to add the same field more than once — for example, to the Row Labels and the Column Labels areas in the layout section — the field is automatically removed from the original area and put in the new area.
Another way to add the same field to the Values area is by using a formula (also called a calculated field) that uses that same field in the formula.
Remove fields from the PivotTable or PivotChart
To remove a field, in the PivotTable Field List, do one of the following:
In the PivotTable Fields box, clear the check box of the field you want to remove.
Note Clearing a check box removes all instances of the field from the report.
In a layout area, click and hold the field that you want to remove, and then drag it outside the PivotTable Field List.
Working with the PivotTable Field List
When you create a PivotTable, Excel displays the PivotTable Field List so that you can add fields to the PivotTable, rearrange and reposition them as needed, or remove them from the PivotTable. By default, the PivotTable Field List displays two sections:
A field section at the top for adding fields to and removing fields from the PivotTable
A layout section at the bottom for rearranging and repositioning fields
To open the PivotTable Field list, right-click the PivotTable, and then click Show Field List.
If you don't see the fields that you want to use in the PivotTable Field List, refresh the PivotTable or PivotChart to display any new fields, calculated fields, measures, calculated measures, or dimensions that you have added since the last operation. To do that, right-click the PivotTable and select Refresh.
Guidelines for moving fields in the PivotTable Field List
To create a field layout, use the following guidelines for moving Value, Name, and Dimension fields from the field section to the four report areas in the layout section.
Values If you select a check box only for a numeric field, by default, it is moved to the VALUES area.
Rows and Columns You can add a field only once to either the FILTERS, ROWS, or COLUMNS areas, whether the data type is numeric or non-numeric. If you try to add the same field more than once — for example, to the ROWS and the COLUMNS areas in the layout section — the field is automatically removed from the original area and put in the new area.
Add fields to a PivotTable or PivotChart
After you create a PivotTable or PivotChart, you are ready to add the fields that contain the data you want to display in the report. You typically select one field for each area in the layout section. However, to see different values for a specific field, you can also add multiple copies of a field to the Values area.
If the PivotTable is connected to an external data source that contains a lot of data, you can filter one or more fields before you add fields to the report, which can help reduce the time it takes to update the report.
Add the fields you want to display
In the PivotTable Field List, you can add fields to the PivotTable or PivotChart by doing one or more of the following:
To place a field in a designated area of the layout section, in the Choose fields box, select the check box of the field that you want. You can then rearrange fields later if you want.
Note: By default, nonnumeric fields are added to the ROWS area, numeric fields are added to the VALUES area, and Online Analytical Processing (OLAP) date and time hierarchies are added to the COLUMNS area.
To place a field in a specific area of the layout section, in the Choose fields box, rest the pointer on a field name, and then click the red drop-down arrow next to the field nameclick the field name, and then click Move to Report Filter, Move to Column Label, Move to Row Label, or Move to Values.
To drag a field to an area in the layout section, in the Choose fields to add to report box, click and hold a field, and then drag it from the field section to the layout section that you want.
Add multiple copies of a field in the Values area
In a PivotTable that is based on data in an Excel worksheet or external data from a non-Online Analytical Processing (OLAP) data source, you may want to add the same field more than once to the Values area. You can do this whether the data type is numeric or non-numeric. For example, you may want to compare calculations side-by-side, such as gross and net profit margins, minimum and maximum sales, or customer counts and percentage of total customers.
In the PivotTable Field List, in the Choose fields box, click and hold a field, and then drag it to the Values area in the layout section.
Repeat step 1 to create as many copies of that field that you want to display in the Value area.
In the PivotTable, change the summary function or custom calculation the way you want in each copy of the field.
Tip: You can also change the name of the copied fields in the PivotTable itself.
Notes:
When you add two or more fields to the Values area, whether they are copies of the same field or different fields, the Field List automatically adds a Values Column label to the Values area. You can use this field to move the field positions up and down in the Values area. You can even move the Values Column Label to the Column Labels area or Row Labels areas. However, you cannot move the Values Column label to the Report Filters area.
You can add a field only once to either the Report Filter, Row Labels, or Column Labels areas, whether the data type is numeric or non-numeric. If you try to add the same field more than once — for example, to the Row Labels and the Column Labels areas in the layout section — the field is automatically removed from the original area and put in the new area.
Another way to add the same field to the Values area is by using a formula (also called a calculated field) that uses that same field in the formula.
Filter data before you add fields
In the PivotTable Fields list, in the Choose fields box, rest the pointer on a field name, and then click the red drop-down arrow next to the field name, and select Filter.
Rearrange fields in the PivotTable or PivotChart
In the PivotTable Fields or PivotChart Fields list, you can rearrange existing fields or reposition those fields by using one of the four areas at the bottom of the layout section:
PivotTable | Description |
|---|---|
FILTERS | Use to filter the entire report based on the selected item in the report filter. |
COLUMNS | Use to display fields as columns at the top of the report. A column lower in position is nested within another column immediately above it. |
ROWS | Use to display summary numeric data. |
VALUES | Use to display summary numeric data. |
Description | |
|---|---|
FILTERS | Use to filter the entire report based on the selected item in the report filter. |
LEGEND (SERIES) | Use to display fields in the legend of the chart. |
AXIS (CAEGORIES) | Use to display fields as an axis in the chart. |
VALUES | Use to display summary numeric data. |
To rearrange fields in a PivotTable, click the field name in one of the areas, and then select one of the following commands:
Move Up | Moves the field up one position in the area. |
Move Down | Moves the field down position in the area. |
Move to Beginning | Moves the field to the beginning of the area. |
Move to End | Moves the field to the end of the area. |
Move to Report Filter | Moves the field to the Report Filter area. |
Move to Row Labels | Moves the field to the Row Labels area. |
Move to Column Labels | Moves the field to the Column Labels area. |
Move to Values | Moves the field to the Values area. |
Value Field Settings, Field Settings | Displays the Field Settings or Value Field Settings dialog boxes. |
Tip: You can also click and hold a field name, and then drag the field between the field and layout sections, and between the different areas.
Remove fields from the PivotTable or PivotChart
To remove a field, in the Field List, do one of the following:
In the Choose fields box, clear the check box of the field you want to remove.
Note: Clearing a check box removes all instances of the field from the report.
In a layout area, click the field that you want to remove, and then click Remove Field.
In a layout area, click and hold the field that you want to remove, and then drag it outside the PivotTable Field List.
Need more help?
You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.