The PivotTable Options dialog box appears. You can set the PivotTable Options according to your preferences. You can set your preferences for the field вЂ“ Region. On the Ribbon, in the Active Field group, in the Active Field box, Region will be displayed.Д¬lick on Field Settings. Suppose you want to set the options for the Region field.Д¬lick on East. Consider the example of sales data that we used earlier in this chapter. You can set a field options by clicking on a value of that field. If you want to ungroup this grouping, you can do as shown earlier, by clicking Ungroup in the group вЂ“ Group on the Ribbon. The HireDate field values will be grouped into Quarters, nested in Years. To select / deselect multiple items, keep the Ctrl-key pressed. Select Quarters and Years in the box under By. Set the dates for вЂ“ Starting at and Ending at.

Click Group Field in the group вЂ“ Group.

Click on a Date item in the PivotTable.

Suppose you want to group this data by the HireDate field that is a Date field into years and quarters. The items вЂ“ South and West of the field Region will be grouped under the name Group2.Д¬onsider the following PivotTable, wherein you have the employee data summarized by Count of EmployeeID, hiredate wise and title wise. Select the South and West items of the Region2 field in the PivotTable, along with the nested Region and Salesperson field items. You can also observe that a new field вЂ“ Region2 is added in the PivotTable Fields list, which appears in the ROWS area. In addition, a new South is created under which South is nested and a new West is created under which West is nested. The items вЂ“ East and North will be grouped under the name Group1. Select the East and North items of the Region field in the PivotTable, along with the nested Salesperson field items.Д¬lick Group Selection in the group вЂ“ Group. For example, you might want to know the data combining East and North regions. You can group and ungroup field values to define your own clustering.

Click Expand Field in the Active Field group.ДЄll the items of the field Region will be expanded.

Click on any of the items of the field вЂ“ Region.

If you want to expand all the items of a field at once, do the following в€’ Click Collapse Field in the Active Field group.ДЄll the items of the field Region will be collapsed.Click any of the items of the field вЂ“ Region.If you want to collapse all the items of a field at once, do the following в€’ The item East of the field Region will expand. If you want to collapse any of them, repeat the steps that you have done for East.Д¬lick on the symbol to the left of East. The item East of the field Region will collapse.ДЄs you can observe, the other items - North, South and West of the field Region are not collapsed. If you have nested fields in your PivotTable, you can expand and collapse a single item or you can expand and collapse all the items of the active field.Д¬onsider the following PivotTable, wherein you have Salesperson field nested under Region field.Д¬lick the symbol to the left of East. The commands on the Ribbon of ANALYZE tab include the following в€’ You will learn the DESIGN commands in the Chapter - Aesthetic Reports with PivotTables. You will learn the ANALYZE commands in this chapter. The DESIGN tab commands will be useful to structure the PivotTable with various report options and style options. The ANALYZE tab has several commands that will enable you to explore the data in the PivotTable. In the worksheet containing a PivotTable, the Ribbon will contain the PivotTable Tools, with ANALYZE and DESIGN Tabs.

How to use pivot tables in excel on mac