Microsoft Office 2016: The Complete Guide (2015)
HOW TO CHANGE THE DATA LAYOUT OF A PIVOT TABLE
After you have created a pivot table and inserted the fields for analysis, you have the option to alter the layout of the information for easier reading and analyzing. The layout of a PivotTable can be changed by choosing a different report layout.
Highlight any section in the PivotTable to bring up the PivotTable Tools on the ribbon.
Select Design then go to Report Layout.
Choose one of the following layout styles options:
Show in Compact Form- This option prevents the data that is related from spreading and reduces scrolling. When you design a PivotTable, the Show in Compact Form is the automatic layout form.
As shown in the picture above, items from different row area fields are in one column and the items from different fields are indented (like Qtr1 and Canada). Row labels take up less space in compact form, which leaves more room for numeric data. Expand and Collapse buttons are shown so you can display or hide details.
Show in Outline Form- This option summaries the information in the PivotTable.
Show in Tabular Form- This option presents all the data in a tabular format, providing ease of transfer of data to another workbook. This layout uses only a single column per field.
If you select outline or tabular form, you can also go to the Repeat All Item Labels under the Report Layout menu to display item labels for each item.
TIP After applying the layout you want, apply a style or banded rows to change the format of the PivotTable using the other options on the Design tab.
How to change how subtotals and grand totals are displayed
To further refine the layout of the data in your PivotTable, you can change the way subtotals, grand totals, and items are shown.
Click anywhere in the PivotTable to show the PivotTable.
On the Design tab, do one or more of the following:
Click Subtotals to change how they will be shown for groups of data.
Click GrandTotals to change how they will be shown for columns and rows.
Click Blank Rows to insert a blank row after each grouping in your PivotTable.
The new features found in the Excel 2016 PivotTables
The PivotTable feature in Excel has provided users with the ability to execute flexible and powerful analysis. Excel 2010 and Excel 2013 saw major improvements in the PivotTable feature with the addition of Power Pivot and the Data Model. Through this users have the option to design refined models across their data, enhance them with measures and KPIs, and run formulas on millions of rows. Excel 2016 has released a number of new features for the PivotTable, which will provide better management and cross referencing options.
With automatic relationship detection Excel is now able to ascertain and establish patterns among the tables used for your workbook’s data model. Excel 2016 picks up when examination needs two or more tables to be connected and alerts you. With one selection, it does the work to build the connection, so you can benefit straightaway.
The new forming, modifying and deleting custom measures can now be done right from the field lists in the PivotTable, which will save you a couple minutes when you need to make additional calculations for your examination.
The programmed time grouping feature will you to better manage your time-related fields such as year, quarter, and month in your PivotTable by, a programmed feature that will detect and cluster them for you.
The drill down buttons in the PivotTable allows you to peruse across the collections of time and other categorized structures within your data.
The Search in the PivotTable field list assists you with finding the fields that are central to you across your complete data set.
With the introduction of Smart rename users have the option to change the names tables and columns in their workbook’s data model. With each modification, Excel 2016 does an automatic update to any associated tables and calculations throughout your workbook, with the inclusion of all worksheets and DAX formulas.
There has been an introduction of several usability improvements. For example, deferred updating gives you the option to make edits in Power Pivot without having to wait until each is spread across the workbook. The modifications will be disseminated at once, the minute the Power Pivot window is closed.