How to use bluetooth headset. How to get a function to remove all subtotals and totals within the pivot table tabular form (office 2011 for mac) like it is available in the office 2011 for windows? Adding and Removing Subtotals. Subtotals are an essential feature of pivot table reporting. Sometimes you might want to suppress the display of subtotals, and other times you might want to show more than one subtotal per field.

In This Chapter • Making Common Cosmetic Changes • Making Report Layout Changes • Customizing a Pivot Table’s Appearance with Styles and Themes • Changing Summary Calculations • Adding and Removing Subtotals • Changing the Calculation in a Value Field Although pivot tables provide an extremely fast way to summarize data, sometimes the pivot table defaults are not exactly what you need. In such cases, you can use many powerful settings to tweak pivot tables.

These tweaks range from making cosmetic changes to changing the underlying calculation used in the pivot table. In Excel 2016, you find controls to customize a pivot table in myriad places: the Analyze tab, Design tab, Field Settings dialog, Data Field Settings dialog, PivotTable Options dialog, and context menus. Rather than cover each set of controls sequentially, this chapter covers the following functional areas in making pivot table customization: • Minor cosmetic changes—Change blanks to zeros, adjust the number format, and rename a field. The fact that you must correct these defaults in every pivot table that you create is annoying.

• Layout changes—Compare three possible layouts, show/hide subtotals and totals, and repeat row labels. • Major cosmetic changes—Use pivot table styles to format a pivot table quickly. • Summary calculations—Change from Sum to Count, Min, Max, and more.

In a pivot table that defaults to Count of Revenue, change it to default to Sum of Revenue instead. • Advanced calculations—Use settings to show data as a running total, percent of total, rank, percent of parent item, and more. • Other options—Review some of the obscure options found throughout the Excel interface. Making Common Cosmetic Changes You need to make a few changes to almost every pivot table to make it easier to understand and interpret. Shows a typical pivot table. To create this pivot table, open the Chapter 3 data file. Select Insert, Pivot Table, OK.

Check the Sector, Customer, and Revenue fields, and drag the Region field to the Columns area. A typical pivot table before customization. This default pivot table contains several annoying items that you might want to change quickly: • The default table style uses no gridlines, which makes it difficult to follow the rows and columns across and down.

• Numbers in the Values area are in a general number format. There are no commas, currency symbols, and so on. • For sparse data sets, many blanks appear in the Values area. The blank cell in B5 indicates that there were no Associations sales in the Midwest.

Most people prefer to see zeros instead of blanks. • Excel renames fields in the Values area with the unimaginative name Sum of Revenue. You can change this name. You can correct each of these annoyances with just a few mouse clicks.

The following sections address each issue. TIP Excel MVP Debra Dalgleish sells a Pivot Power Premium add-in that fixes most of the issues listed here. This add-in is great if you will be creating pivot tables frequently. For more information, visit. Applying a Table Style to Restore Gridlines The default pivot table layout contains no gridlines and is rather plain.

Fortunately, you can apply a table style. Any table style that you choose is better than the default. Follow these steps to apply a table style: • Make sure that the active cell is in the pivot table. • From the ribbon, select the Design tab. Three arrows appear at the right side of the PivotTable Style gallery.

• Click the bottom arrow to open the complete gallery, which is shown in. NOTE For more details about customizing styles, see “Customizing a Pivot Table’s Appearance with Styles and Themes,” p. Changing the Number Format to Add Thousands Separators If you have gone to the trouble of formatting your underlying data, you might expect that the pivot table will capture some of this formatting.