To change the numeric format, click the Number Format button in the lower-left corner.Īlthough Excel 2016 offers a Live Preview feature for many formatting settings, the Format Cells dialog does not offer one. From the Analyze tab, select Field Settings from the Active Field group.Īs shown in Figure 3.3, the Value Field Settings dialog is displayed. Select any cell in the Values area of the pivot table.Click the drop-down to the right of the Sum of Revenue field in the areas of the PivotTable Fields list and then select Value Field Settings from the context menu.Right-click a number in the Values area of the pivot table and select Value Field Settings.You have three ways to get to this dialog: The right way to solve the problem is to use the Number Format button in the Value Field Settings dialog. Either of these methods temporarily fixes the problem, but you lose the formatting as soon as you move a field in the pivot table. You will be tempted to format the cells using the tools on the Home tab. You will be tempted to format the numbers using the right-click menu and choosing Number Format. Check the Sector, Customer, and Revenue fields, and drag the Region field to the Columns area. To create this pivot table, open the Chapter 3 data file. You need to make a few changes to almost every pivot table to make it easier to understand and interpret. Other options-Review some of the obscure options found throughout the Excel interface.Advanced calculations-Use settings to show data as a running total, percent of total, rank, percent of parent item, and more.In a pivot table that defaults to Count of Revenue, change it to default to Sum of Revenue instead. Summary calculations-Change from Sum to Count, Min, Max, and more.Major cosmetic changes-Use pivot table styles to format a pivot table quickly.Layout changes-Compare three possible layouts, show/hide subtotals and totals, and repeat row labels.The fact that you must correct these defaults in every pivot table that you create is annoying. Minor cosmetic changes-Change blanks to zeros, adjust the number format, and rename a field.Rather than cover each set of controls sequentially, this chapter covers the following functional areas in making pivot table customization:
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.
These tweaks range from making cosmetic changes to changing the underlying calculation used in the pivot table. In such cases, you can use many powerful settings to tweak pivot tables.
0 kommentar(er)
