How To Customize Excel Pivot Table Fields And Layout

A pivot table is a form of table representation of the data source. The data in the data source table can be easily edited and modified, but the data in the pivot table cannot be edited. This article will introduce the fields and layout operations of the Pivot Table.

1. Add Pivot Table Fields.

1. Open an Excel sample file financial_sample.xlsx, you can download it to your local machine and open it.
2. Select the Units Sold column, then click the Home —> Sort & Filter —> Sort Largest to Smallest menu item to sort the rows.
3. Select the first 10 rows and 5 columns, and then click the Insert —> PivotTable icon on the top toolbar to create an excel pivot table.
4. Now it will create a new worksheet, and create an empty pivot table in the worksheet.
5. On the right side of the worksheet is the PivotTable Fields panel.
6. You can find all the source data column names in the FIELD NAME list. In this example, the filed names are Segment, Country, Product, Discount Band, Units Sold.
7. To add a pivot table filed is very easy, just check the checkbox before the field name, and you can drag the field name to the Rows or Columns list pane.
8. In this example, I drag the Product, Units Sold filed to the Rows list pane, and drag the Country field to the Columns list pane.
9. I also drag the Units Sold field to the ∑ Values list pane. Then you can get the pivot table.
10. You can swap the rows fields and columns fields according to your needs.
11. You can also drag a field ( for example Country ) to the Filters list pane.
12. Then it will add a row at top of the pivot table, and the A column’s value is Country, the B column is a filter drop-down list,  you can filter the pivot table rows with the drop-down list.

2. Delete Pivot Table Fields.

1. Uncheck the checkbox in the pivot table fields pane to remove a field in the pivot table.
2. You can also drag a field name out of the Rows or Columns list pane to remove them in the pivot table.
3. You can also click the drop-down triangle on the end of a field name in the pivot table fields Rows, Columns, Filters, ∑ Values pane, then click the Remove Field menu item in the popup menu list to remove them. There are also other menu items in the popup menu to operate the selected field.

3. Set Pivot Table Layout.

1. Click the PivotTable Tools —> Analyze —> Options —> Options menu item on the excel top toolbar to open the PivotTable Options dialog.
2. There are 6 tabs in the options dialog, they are Printing, Data, Alt Text, Layout & Format, Totals & Filters, Display.
3. You can set the options as you need.

4. Pivot Tabe Field Settings.

1. Click one row in the pivot table to display the PivotTable Fields pane on the right side.
2. Click the drop-down triangle at the end of any field name in the Rows, Columns, Filters, ∑ Values pane, then click the Field Settings menu item in the popup menu list.
3. It will pop up the Field Settings dialog. You can re-enter the field name in the Custom Name text box. You can also config the Subtotal & Filters, Layout & Print in this dialog.

5. Pivot Table Value Field Settings.

1. Right-click a cell in the pivot table rows, then click the Value Field Settings… menu item in the popup menu list.
2. Then the Value Field Settings dialog box pops up.
3. In the Summarize Values By tab, there are different calculation types, such as Sum, Count, Average, Max, Min, etc. you can change them as needed.
4. In the Show Values As tab, you can set the display method of the cell value.