Create an Excel Pivot Table in Just Two Clicks
Many Pivot Tables focus on common questions, like “What are the total sales by salesperson”, or “What are the daily sales”.
When answering these common questions, you can create the Pivot Tables with just a few clicks by using the Recommended Pivot Tables feature located on the Insert ribbon.
This displays a panel on the right side of the screen showing thumbnail images of common Pivot Tables based on your specific data.
If you see a Pivot Table that meets your needs, you can click the “New Sheet” or “Existing Sheet” buttons to place the selected Pivot Table as needed.
These are fully formatted and ready to use Pivot Table. You can, of course, modify them as needed, or you can use them as-is.
Adjust Pivot Table Fields Settings and Layout
When working with the PivotTable Fields drag-and-drop interface, you may find it easier to work with by rearranging the field list and field wells.
This can be done by clicking the Tools (gear) icon in the upper right corner of the PivotTable Fields panel.
From here, you can rearrange the field list and field wells into one of four layouts.
Each layout has its pros and cons. Your screen size, number of fields, and personal preference will drive the layout choice that serves you best. Experiment with them all to see which one you prefer.
Also, when working with tables that have many fields, locating a specific field can be challenging. The field list is presented in what is called Data Source Order. In other words, fields are listed from top to bottom as they appear left to right in the data source.
It may be easier to sort the list in alphabetical order to locate fields more easily when you are not familiar with the data set’s order.
Featured Course
Excel Essentials for the Real World
Add a Timeline Slicer to a Pivot Table
Timeline Slicers are useful when working with Pivot Tables that can utilize dates to aggregate or filter data.
Timeline Slicers allow us to set a level by which to filter (such as years, quarters, months, days, etc.), and then use drag-and-drop controls to reduce or expand the start and end ranges or click specific intervals to filter.
Timeline Slicers can be created by selecting PivotTable Analyze (tab) – Filter (group) – Insert Timeline.
When creating the Timeline Slicer, any date-based fields in your Pivot Table will be listed for use by the control.
You can customize the Timeline Slicer’s color, as well as which controls and information you wish displayed for the user to utilize.
Using a Hidden Pivot Table for Dynamic Reports
Typically, the Pivot Table is the final link in the analytic chain. But it doesn’t have to be. Consider this use for a Pivot Table.
Since we can’t use Slicers to filter individual cell calculations, we can use a Slicer to filter a Pivot Table, then use the Pivot Table’s filter results to drive single-cell calculations. Here’s how this works:
We start with a Pivot Table that uses a field from the data (ex: Salesperson).
Create a Pivot Table Slicer that uses the Salesperson field.
Select a single entry from the Pivot Table Slicer to reduce the Pivot Table to but a single entry.
We can now use the single entry display in the Pivot Table (cell G3) in a variety of cell formulas.
Changing the Slicers to a different person changes the Pivot Table which then updates all the downstream calculations.
NOTE: Be aware that if the user selects multiple items from the Slicer, only the first entry in the Pivot Table will be used in the calculations. This is because the formulas are only looking at a single cell in the Pivot Table area.
Display the Top N Items in a Pivot Table (Top N Analysis)
Pivot Table results can get rather lengthy, especially when working with dozens, hundreds, or thousands of items in a field, like product names or employee names.
Suppose you only want to see the Top 3 (or whatever number you wish) items of the data set.
To do this, select the Filter dropdown for the Rows field (ex: Salesperson), and choose Value Filters.
In the Top 10 Filter dialog box, select the number of items you wish to display, along with the field that holds the number that the Top N will be based around.
If you are using a row-based hierarchy (ex: Year to Month to Salesperson), each month will be reduced to only show the top N salespersons based on the Order Total.
An added touch would be to right-click one of the Order Total values and sort from largest to smallest. This performs the sorting on a per-Top N basis.
Pivot Tables Customizing Labels and Headings
If you are working with data that has less than desirable labels for certain items, you can change the labels in the Pivot Table without changing the source data or breaking the link between the two.
For example, the labels “Qtr1, Qtr2, Qtr3…” should read as “Quarter 1, Quarter 2, Quarter 3…”.
To do this type directly into the Pivot Table cell that has the undesirable label and replace it with one that works for you.
Of course, the best option is to change the values in the source data, but if this is not possible, at least you have the option to change the labels in the Pivot Table report.
Does this mean that someone could type over the calculated values and compromise a report? No: this type of replacement is not allowed in Pivot Tables.
Featured Course
Black Belt Excel Package
Repeating or Centering Items in a Pivot Table
When working with lengthy Pivot Tables, it can be easy to lose focus of what category you’re examining when the list contains many rows, especially when printed data crosses to a new printed page.
To help maintain focus, the category items can be repeated to look more like a traditional tabular structure.
This is done by selecting a Pivot Table, then clicking Design (tab) – Layout (group) – Report Layout – Repeat All Item Labels.
If you don’t wish to repeat the heading labels for every heading, you can right-click a specific heading and click “Field Settings…”. In the Field Settings dialog box, on the Layout & Print tab, check the box labeled “Repeat Item Labels”.
Another way to display headings is to center them across their child-level items.
To do this, click a Pivot Table, then select PivotTable Analyze (tab) – PivotTable (group) – Options.
On the Layout & Format tab, in the Layout section, check the box labeled “Merge and center cells with labels”.
Enjoy your newly centered headings.
Pivot Table Custom Sort Items Using Dragging or Typing
Sorting data in Pivot Tables is straightforward. Right-clicking text allows you to sort alphabetically, numbers numerically, and dates chronologically.
But what if you want to sort by some unconventional, or preferential means? Suppose you want to keep an eye on a specific entry, so you want it to be at the top of the list regardless of any other conditions.
To reorder the list, select the desired cell, then click and hold the green border of the cursor and drag up or down to the needed position. A thick green bar will indicate the drop location for the item.
This will place the selected entry in that position throughout all grouping iterations of the report. In other words, if you place “Lily Code” at the top of the “January” cluster, “Lily Code” will appear at the top of ALL monthly clusters.
Another way to reposition an item in a Pivot Table list is to click where you wish the entry to appear and type the text for the entry being moved.
This technique may be faster and more efficient when working with long lists and you are familiar with the data.
Pivot Table Calculate Percentage Difference from a Previous Period
Take the following Pivot Table report as an example.
Suppose we need to calculate the percentage change from each month to the previous month. Or the percentage change from each month to the previous year’s month.
To do this, add the base value field (in this case, “Sales”) a second time to the Values field well.
Right-click one of the newly added values and select Show Values as – % Difference From…
In the following dialog box, select the Base Field (in this example, it is the Months field), and the Base Item, which would be “previous”.
We are presented with the percentage difference for each month compared to the previous month.
If we wanted to see the percentage difference for the same month of the previous year, we would set the Base Field to Years.
NOTE: Because there is no inherent time intelligence being utilized here (like would be if using DAX formulas), the word “previous” simply refers to the previous cell. This could be a previous product, person, country, etc.
Disable the Drill Down Feature in Pivot Tables
A great feature of Pivot Tables is the ability to double-click a calculated value and produce a separate table that is a filtered set of rows from the source data but made up of only rows that contribute to the clicked value. This is known as drilling down into the data.
But what if you don’t want your viewer to have the ability to examine the underlying data?
To disable this drill-down feature, click in the Pivot Table, then select PivotTable Analyze (tab) – PivotTable (group) – Options.
On the Data table, deselect the PivotTable Data feature labeled “Enable show details”.
If a user attempts to drill down into the data using a double-click maneuver, they are presented with a denial message.
Bonus Tip
If you’re using Pivot Tables for reporting, check out what’s in store – you can now use images inside Pivot Tables (currently available in Office Insiders) for greater visual impact!
Cheat Sheet
Download the handy Cheat Sheet summarizing all the tips:
Practice Workbook
Feel free to Download the Workbook HERE.
Featured Course
Power Excel Bundle
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
