It is easy to say that Pivot Tables and Power Pivot are the two most powerful features in Excel. If you are a consultant looking for clues or an analyst looking for insight, Pivot Tables and PowerPivot are your tools to the next higher level.
I don’t mean time saving as with Excel VBA macros, but rather they are powerful because the decisions made from Pivot Table (and Power Pivot) results drive key business decisions.
I’ve built tons of Pivot Tables and skimmed through tons of top Pivot Table tips. This list contains some of the top tips. The list starts with fundamentals and works its way down to esoteric, but powerful. If you are a new to Pivot Tables, this list will get you up-to-speed by starting at #1 and working down. If you are an experienced Pivoter, then I recommend skimming through the list to see if there is something critical you need to put in your toolkit.
There is great free training available on the basics of Pivot Tables. If you haven’t had comprehensive training or you learned on your own in an “as needed” mode, then you may have missed some of the basic concepts and features. The links below are some of my favorites for brevity and clarity.
Goodwill Industries creates free courses combining text and short videos that can help anyone keep their job skills up-to-date. Foundation work skills include many versions of Microsoft Office products as well as topics like writing, email, and other office and job skills.
Excel Easy has some of the cleanest, most straightforward text tutorials on Excel topics.
Debra has written three books on Pivot Tables and is a recognized expert on the topic. At Contextures she has an index of valuable Pivot Table features and tips.
The prolific Bill Jelen has posted 70 well-done Pivot Table videos on YouTube. There’s some good stuff here.
Use the Pivot Table to COUNT a field containing text data to see how many rows are in the dataset. Use a field that you are sure will be completely filled, for example, CustomerID. Does the dataset have as many records as you expected?
Are there empty cells in records that should have data? Are the date ranges you need available and contiguous? Use the following keystrokes to zoom through the dataset and check for empty cells.
|Up/Down||Ctrl + Up/Down Arrow|
|Left/Right||Ctrl + Left/Right Arrow|
|Top left of worksheet||Ctrl + Home|
|Bottom right of worksheet||Ctrl + End|
|Around corners of dataset (first select a cell in dataset)||Ctrl + * then,
Ctrl + . to move around corners
Create simple Pivot Tables with fields such as Product Name, Region, SalesRep, etc. as row headers. Scroll through these row or column headers to look for mistyped entries. If data is entered manually, then it is almost guaranteed that some data entry errors will occur. This causes rows or columns to summarize data separately in the Pivot Table rather than together. For example, if a field is “Fruit” and someone manually types the fruit data incorrect entries could produce like,
The first four entries should all be the same, “Apple”. However, “Applr” was a typo and “Apfel” and “Manzana” are in German and Spanish. For valid summaries in the Pivot Table you want them all spelled as “Apple.”
When you find an incorrect entry, use Ctrl+F to open a Find dialog box or Ctrl+H to open a Search and Replace dialog box to make the correction in the dataset. Check the Options button for fine control of search and replace. If the data comes through an external link, contact IT to correct the data source so you maintain data integrity.
Whenever possible opt for “one version of the truth.” Connect your Pivot Tables directly to the data source on a network. To connect to a networked data source, choose Insert, then click PivotTable in the Table group. In the Create PivotTable dialog box, select the Use an external data source, then click Choose Connection. You can connect to data sources in the same workbook, in a network, or on the same computer.
To import data from the same workbook in Excel 2013,
To import data from a network,
Pivot Tables don’t really work on the data you see or connect to. They work on a second dataset that Excel creates in cache (memory). This cache stores data in a special structure that allows extremely fast summation. (This is blindingly fast in PowerPivot.)
If you need to send a smaller worksheet containing the Pivot Table and the users do not need to recreate the Pivot Table or charts, then you can delete the worksheet containing the dataset. The Pivot Table(s), slicers, and charts will continue to work for the viewers, but they cannot update data, nor will they have access to the source data. The file should be considerably smaller if the dataset was in a worksheet.
If your dataset is in a worksheet you may want to turn it into an Excel Table. Lists or databases defined as an Excel Table automatically extend down and across when you add data. This size increase is automatically recognized by a Pivot Table that references the Table. There are many other advantages to making lists into Tables such as automatic formatting, automatic column naming (similar to range names), and more. For more information, checkout this article at TechRepublic.
You don’t have to use the field names that come from the dataset. If you want a different name than what shows in the Pivot Table column or row heading, just right-click the existing name and choose Field Settings. At the top of the Field Settings dialog box in the Custom Name text box enter the custom name you want to appear.
There are a few limits. You can’t create a new name that is the same as an existing field name; however, there are a few tricks you can use to get around that such as using hyphens or underlines between words or adding extra spaces (beware this can confuse humans trying to type a name in a formula).
If field name has been renamed, as described in tip #7, then the field name shown in the Pivot Table no longer matches the field name used in the dataset. On a small dataset this usually isn’t a problem, but on large data sets with many columns and similar appearing data it can cause confusion when you try to find the data that matches the renamed field name.
It isn’t hard to find the field name in the data set for a renamed field. Right-click the renamed field name in the Pivot Table and select Value Field Settings. At the top left of the Value Field Settings dialog box you will see Data Source Name followed by the name of the original data field.
Well formatted and laid out Pivot Tables are more attractive and easier to read. You can use Excel’s built-in Pivot Table formats or create your own custom formats. With at least once cell selected in the Pivot Table, choose Pivot Table Tools, then in the PivotTable Style group, click the drop-down and select one of the pre-defined styles.
If you want to create your own Pivot Table Style, click New PivotTable Style at the bottom of the pre-defined styles. You can define a new style for each element in a PivotTable.
You don’t have to put up with a blank or error value in your Pivot Table. Right-click a cell in the Pivot Table and choose PivotTable Options. In the PivotTable Options dialog select and set how you want to display error values and blanks.
If the data you are analyzing (the field in the center of the Pivot Table) is text, then your Pivot Table summary will show the Count of text items. If your data is numeric, then the summary will show Sums. But, what if you want to calculate a different type of summary?
Right-click inside the Pivot Table and choose Value Field Settings. In the Value Field Settings dialog box, select the Summarize Values By tab and select how you want the data summarized.
Keep Excel from driving you crazy when it automatically reformats cells or column widths each time you recalculate the Pivot Table. To turn this madness off, right-click in the Pivot Table and choose PivotTable Options. In the Layout and Format tab, unselect the Autofit Columns on Update. To keep formatting you’ve applied select the Preserve Cell Formatting on Update checkbox.
Don’t format numeric values inside the Pivot Table. Instead, format the column heading and the numeric format will continue to apply to that field throughout Pivot Table operation. To apply the format, right-click on the field heading and select Value Field Settings. Select the Number Format button at the bottom of the Value Field Settings dialog box and apply the numeric format of your choice.
Pivot Tables are frequently used to show totals and their corresponding percentages, for example, a column might have total sales with the percentage of total by region in the adjacent column. This is easy to do if you drag the field name, Sales, twice in to the row area.
When you drag in a duplicate field name into the row area of the PivotTable fields panel it will show the total or count just the same as the original field name. This is because they are both using the same calculation. To change the calculation for the second field to show percentage, right-click the second field name and change Show Values As from No Calculation to % of Grand Total. The duplicate field’s results will now show percentage of grand total. Use the earlier tip to edit the duplicate heading to reflect that this is a percentage.
Remove grand totals on rows or columns by right-clicking in the PivotTable and selecting PivotTable Options. In the Options dialog box, in the Totals & Filters tab, select or deselect the row or column grand totals.
If field names in the rows or columns aren’t in the order you want just drag them where you want them. For example, to move a field name that is a row header move the mouse pointer to the top or bottom of the border around the field name. When you see the four-pointed arrows, drag the header up or down. Use similar steps to drag field names above columns to the left or right.
Be careful. If you use the drop-down menu to resort the fields you will lose your manual sort.
If you want a custom sort order, and one that doesn’t change when sorted, then create a custom sort list as described in tip #17.
Excel will automatically sort field names in headers into alphabetical or date order. However, sometimes you need a custom order, for example, if you want the most products with greatest brand visibility first in the sort, or if you have geographic regions you want in a custom order.
To create a custom sort order you must create a custom list that shows Excel how to sort. To create a custom list, choose File, Options. In the Excel Options dialog, select the Advanced tab, then scroll down to the General group and click the Edit Custom Lists button.
The Custom Lists dialog appears. You can type a custom list in the List Entries box separating list items with a comma and then click Add to add it to the Custom Lists. If you have a list you have created in a worksheet you can use the Import button to select and import that list. Click Ok when you are finished.
Now, when you need to enable custom list sorting for Pivot Tables. Right-click in a Pivot Table and select PivotTable Options. In the PivotTable Options dialog select the Totals & Filters tab, then select the Use Custom Lists When Sorting check box. If you have an existing Pivot Table, you need to refresh it. If you create a new Pivot Table it will sort using your custom lists.
It is a simple matter to group items in a Pivot Table, for example, grouping together products in the same brand or grouping countries in a region. The Pivot Table analytics you use on the items also apply to the groups.
To group items, select the field names you want in one group. Hold down Ctrl to select multiple non-adjacent field names. Then, right-click one of the selected items and choose Group from the menu. The Group will be given a name like Group1.
To rename that Group name, right-click the Group name, choose Field Settings, and in the Custom Name text box enter the custom name you want for the group.
For more on grouping, check tip #5 in this article at Skilled Up.
Grouping items by numeric value is useful when you need to see items with related pricing, size, populations, etc. Grouping data by numeric values is also an excellent way to reduce large databases into histograms that group data into numeric ranges or bins, for example, bins that show 1 to 10, 11-20, 21-30, etc.
If your version of Excel does not have Timelines, refer to tip #26 to learn how to create custom date segments for use with Slicers.
To insert a Timeline, click in the Pivot Table you want to filter, then choose PivotTable Tools, select the Analyze tab, and click Insert Timeline.
Timelines are a visual date filter. Moving each end of the slider defines the begin and end dates for the time filter. If the date field selected can be segmented into different time units, for example, Years, Quarters, Months, Days, then you can select at the top right of the Timeline which date units you want on the Timeline. The figure shows that Months are the current unit.
If you want to filter with custom date units, such as Week Start Date or custom Fiscal Quarters, then see tip #26 to learn how to create custom date units .
Slicers are one of the great features added to Excel in recent versions. Slicers are boxes containing buttons with field names. Clicking one or more buttons in a slicer filters the Pivot Table for the field(s) selected. They act like the drop-down filters, but they are much more accessible.
To add a slicer, click in the Pivot Table so the Field List displays. (If it is hidden, click in the Pivot Table and choose Analyze, Show, Field List). In the Field List, right-click on the field for which you want a slicer. Choose Add a Slicer. When the slicer appears, drag it where you want on the worksheet.
Align slicers perfectly with the corners of cells by hold down the Alt key as you drag a corner of the slicer. While the Alt key is held down the slicer edges will “snap” to align with cell corners and edges.
Slicers have options that most Pivot Table users aren’t familiar with. To see Slicer options, right-click a Slicer’s heading. In the Slicer Settings dialog you can change the name of the Slicer, select how it sorts, and more.
Some users get confused when the make a selection in one Slicer that affects the choices in another Slider. For example, selecting a year and month in one Slicer, but the other Slicer continues to show all Starting Weeks, although some are greyed. A more eloquent way of creating slicers to set your Slicer options so unavailable items are hidden in subordinate slicers rather than being greyed. In the following two images of Slicers they both have 2015 and Feb selected, but one shows unavailable Start Week dates, while the other completely hides the unavailable Start Week dates. (For more information on how to create custom dates for Slicers, like Start Week, refer to tip #26.)
To make Slicers hide the items that are not available, select the Hide Items with No Data checkbox in the Slicer Settings dialog box. (Shown above.)
Control which Pivot Tables a Slicer filters with the Slicer Report Connections dialog box.
When you create a Slicer it will control the Pivot Table that was active when it was created. However, you may want one Slicer to control multiple Pivot Tables. To do that, right-click the Slicer heading and choose Report Connections. In the Report Connections dialog box you can turn on or off the Pivot Tables that Slicer controls.
Excel 2016 and Excel 365 automatically create date fields such as Quarter for you to use. However, if you are using an earlier version or want a custom date field then you can use custom date calculations like these to create data for custom date Slicers.
If you need custom dates for Slicers or you are using Excel 2013 or previous versions, use the tricks below.
Learn more about Time Group Enhancements in Excel 2016 in this article.
The following functions and formulas use a date in each row (record) of the dataset to calculate a new date term, for example, Quarter or Week Start. To make these custom dates available for use in a Slicer just put the formula or function in a cell at the rightmost column of your dataset. Copy the formula or function down and expand the range of the dataset to include the new column.
After adding a custom date field to your dataset, refresh your Pivot Table to see the new field in the field list.
Many analytics need smaller date segments than a month. For these try using a week segment and displaying the Week Start in a slicer. This formula calculates the first day of the week for each record in the data. The formula shown uses the term -1 to calculate the Monday beginning each week. Change the -1 term to calculate a different start day for the week.
A custom slicer using the first Monday of each week could look like this,
Here are some date functions you may want to use in calculating custom date fields.
You can join numbers, text, and dates together using the & concatenation function. In this example, the FORMAT function is used to format the date as the three letter text abbreviation for a month.
Note that “00” was used as the month format so that the months 01 to 09 would sort ahead of 10 – 12. The result will look like,
The ExcelJet website has an easy formula for calculating a Quarter. Use this formula,
A great feature for the inquisitive is the ability to drill down in a Pivot Table and see the detail underlying the summary. For example, double-clicking on US Sales Total, could open a new tab that shows all the rows of US states or regions that went into the US Sales Total.
This is a great feature for cross-checking how data is being calculated for the Pivot Table result. However, an accidental double-click can cause confusion with managers or reviewers who aren’t with this feature. Inexperienced users will get lost when the new worksheet opens or they will close the entire workbook when they think they are closing the new worksheet.
To turn off drill-down, right-click in the Pivot Table then select PivotTable Options, in the Data tab, deselect the Enable Show Details check box.
You can create custom field calculations that are not part of the Pivot Tables standard calculations. These are not row-wise calculations. Custom field calculations perform calculations on the results of other fields. For example, how one field’s results compared to another field’s results. Or you can create conditionals to only show results when they exceed a total value.
Note: In many cases people want to add a new field that calculates using data from the same row of the dataset. This is not a Calculated Field. To create a row-wise calculation, create a new column at the edge of the dataset, enter the row-wise formula down the column, and then expand the dataset referenced by the Pivot Table. Refresh your Pivot Table and you will see the new field name in the Field List.
In the following figure, results for the Units Met Limit are zero unless a specific result is greater than 50, then the result appears in the Pivot Table.
To create a new calculated field, select inside the Pivot Table, then select Analyze (Options for Excel 2010 or earlier), Calculations and click Fields, Items, Sets, then choose Calculated Field to display the Insert Calculated Field dialog box.
In the Name text box type the name for the calculated field. This is the name that will appear in the Field List. In the Formula text box, type = and then the formula. Enter Field names in the formula by selecting them from the Fields list and clicking Insert Field. Enter a formula using the same syntax as you would in the formula bar. Remember! The Fields you are specifying here are the Pivot Table result for that field. Click Add when you are complete. This adds the formula to the Fields List.
Be cautious when creating calculated fields. Results do not behave as you would expect for row-wise calculations. For more information on the unique differences of calculated fields, refer to Debra Dalgleish’s website at www.Contextures.com.
The confusing, but powerful GETPIVOTDATA function will appear in your worksheet when you click to create a link into a Pivot Table. For example, type an = in a cell, then click in the Pivot Table result and press Enter. You will get a function like,
This function retrieves data from within the Pivot Table based on the criteria specified in the function. What is very powerful is that you can change the text in quotes for cell references that contain other text. For example, replacing “Gill” with $T$16 and then typing Jones in T16 will give you the Units total for Jones in January.
The book, “Balanced Scorecards & Operational Dashboards with Microsoft Excel, Second Edition” contains an entire chapter on how to use drop-downs and scrolling lists to create dynamic Pivot Tables that change when the user makes a selection from a drop-down or scrolls through dates. I have included many examples of using drop-down lists in this blog.
Where this is extremely helpful is in creating dashboards that show Pivot Table results in executive presentations. For example, executives may not want to see all the details of a Pivot Table so it can be on a hidden sheet, but the executives could still see dynamic tables change when they select regions from a drop-down that retrieves the data.