Contents
Recommended pivot tables
On the Advanced tab Insert (Insert) button appeared Recommended pivot tables (Recommended Pivot Tables), which offers several suitable pivot tables of various types to choose from, depending on the type of source data:
For those who know exactly what they want to see in their report and will do it themselves – a useless thing, but for novice users – a nice help. It is sometimes easier to choose from ready-made options.
Filtering with the Timeline
The ability to filter pivot tables with slicers, which appeared in Excel 2010, was added Timeline (Timeline) – an interactive graphical time axis that can be inserted on the tab Analysis (Analysis) . By selecting any time interval on the axis, we will get the results for the selected period in the summary attached to it:
New mechanism for calculating pivot tables (Data Object Model)
Excel 2013 introduces for the first time the ability to build PivotTables that are not based on the classic cache mechanism, as in all previous versions before. Now, when building a pivot table, you can set an inconspicuous checkmark at the bottom of the dialog Add this data to the data model (Add to Data Object Model) and, thus, use Power Pivot to build a summary “engine”, i.e. in fact, a full-fledged database built into Excel:
It is the object model that allows you to do one of the most impressive tricks of this version – to build a summary for several data ranges at once.
Relationships and summary across multiple tables
On the Advanced tab Data (Date) button appeared Relations (Relationships), which allows you to link several tables by key fields (columns) using the following dialog:
The nuance is that the subordinate (second) table must contain only the unique values of the elements in the key field – otherwise the relationship will not be created. If you first link two tables, and then start building a summary for one of them, then the list of fields will display the column headings not only of the current table, but of both linked tables:
If you try to throw fields from different tables into one summary, then a hint about a possible missing link that needs to be created will appear in the list of fields:
Function to count the number of unique elements
If you are building a summary using the Data Object Model, then in the list of functions performed on the data (right-click on the field – Field Options) one more new one will be added – Number of distinct elements (Distinct Count). It will not calculate the total number of non-empty elements like a regular function Account (Count), and the number of non-repeating representatives.
The example above counts the total number of items sold and the number of unique items for each city for comparison.
Express View
Those who work with pivot tables know a simple but extremely useful feature: if you double-click on any cell with a numeric result in the value area, then details on this cell will be displayed on a separate sheet. This allows you to quickly check the original data and understand where such a result came from.
The logical continuation and development of this idea was a new function Express View (Quick Explore) in Excel 2013 PivotTables.
When you select any cell in the pivot with a numeric result, a smart tag appears next to it Express View. By clicking on it, you can select the table we need (if the summary is based on several tables) and the field of interest to us for detailing. So, for example, if I select the cell with the manager’s sales results Ivanova and select field Customer for detail:
… then I will get a new pivot table with detailed sales results exactly Ivanova for all customers:
Disadvantages
From what I saw in the course of work:
- Grouping does not work in pivot tables built on the basis of the Object Model. It’s not critical, but sad. Let’s hope they fix it.
- Sometimes the names of the columns in the field list do not correspond to reality, i.e. are not taken from the cells of the cap, but it is not clear where. It helps to pre-format source ranges as “smart” tables.
- To unload the source data from the Object Model, you have to use the command Data – Connections (Data — Connections).