Pivot tables are awesome! They’re one of Excel’s most powerful features, they allow you to quickly summarize large amounts of data in a matter of seconds. This collection of awesome tips and tricks will help you master pivot tables and become a data ninja! Show
You’re gonna learn all the tips the pros use, so get ready for a very very long post! Download the example file with the data used in this post to follow along. Your Source Data Needs to be in Tabular FormatWhen using a pivot table your source data will need to be in a tabular format. This means your data is in a table with rows and columns.
Use a Table for Your Source DataWhen creating a pivot table it’s usually a good idea to turn your data into an Excel Table. When adding new rows or columns to your source data, you won’t need to update the range reference in your pivot tables if your data is in a Table. Without a table your range reference will look something like above. In this example, if we were to add data past Row 51 or Column I our pivot table would not include it in the results. To create and name your table.
Now when you create a pivot table you can reference it with a name instead of a range. When you add data to the table, you won’t need to update the range in your pivot table. Just refresh it and the new data will appear in your results. Change Source DataOk, if you decide not to use a table for some reason, then you’re going to have to update the range when you add any new rows or columns outside the original range selected. Select your pivot table and go to the Analyze tab and press the Change Data Source button then select Change Data Source from the menu. Update your range accordingly in the following Change PivotTable Data Source pop up dialog box. Undock the PivotTable Fields WindowTo undock the PivotTable Fields window pane hover your mouse cursor over the title until it turns into a four way arrow, then right click and drag it to your desired location. You can either leave it floating somewhere in the spreadsheet or dock it to the left side by dragging it to the very left edge. Quickly Dock the PivotTable Fields WindowTo quickly dock the PivotTable Fields window pane hover your mouse cursor over the title until it turns into a four way arrow, then double right click. It will dock to the last docked location (either to the right or left side). Hide or Unhide the PivotTable Fields WindowYou can get more screen real estate by hiding the PivotTable Fields window. Select a cell in your pivot table and then go to the Analyze tab in the ribbon. Press the Field List button in the Show section to toggle the PivotTable Fields window on or off. You can also close the window using the X in the upper right corner. You can also show or hide the PivotTable Fields window with a right click anywhere inside your pivot table then select Show Field List or Hide Field List (depending on the current state of your PivotTable Fields window). Change the Default Arrangement of the PivotTable Fields WindowClick on the gear wheel with a downward arrow to change default appearance of the PivotTable Fields window. There are five different available options you can select from.
Change the Sort Order of Your Field ListThe list of data fields will show in the same order as the source data by default. You can change this to show in alphabetical order (A to Z) if you prefer. Left click on the options menu in the PivotTable Fields window to access the option. Select the Sort A to Z option in the menu. Your fields will now display in descending order! Move, Resize and Close the PivotTable Fields WindowRight click on the small downward arrow to the right of the PivotTable Fields title to move, resize or close the window.
PivotTable and PivotChart Wizard Keyboard ShortcutUse the keyboard shortcut Alt + D + P to open the PivotTable and PivotChart Wizard. This will take you through the steps to set up either a pivot table or pivot chart, select your data and the location for your new pivot table or chart. Create a PivotTable With a Keyboard ShortcutUse the ribbon command keyboard shortcut Alt + N + V to quickly create a pivot table. Show Details Behind a ValueDouble right click on a value inside a pivot table to quickly see the data behind that aggregated value. A new sheet will be created with only the data relating to that value. You can also access this feature by right clicking on any value then selecting Show Details. Turn Off Show Details to Avoid Accidental Double ClickIf the ability to show the detailed data behind a pivot table result doesn’t interest you, then you can turn this feature off. This means you and can avoid creating new sheets with bits of data in them because of accidental double clicks. Select your pivot table and go to the Analyze tab in the ribbon. Press the Options button in the PivotTable section to open the options menu. In the PivotTable Options menu go to the Data tab and uncheck the Enable show details box to disable this feature. Replace Blank CellsThis pivot table contains blank cells because our source data does not contain any records for those combinations of dimensions. For example, there is no data for Arthur James and France so the intersection of the Arthur James row and France column is blank. We can change the settings to display something such as a zero or some text saying “N/A” instead of a blank. Left click anywhere in the pivot table then select PivotTable Options. In the PivotTable Options menu
Now the previously blank cells have been replaced by zeros. Show Items with No DataIn this example we have create a pivot table with Customer Name and Product Sold in the Rows area. Notice that under each customer, not all the possible products are listed. Only those which we have a transaction in our data are listed. We can change this so that we see all items even when there is no data. Right click and select Field Settings from the menu. Check the Show items with no data box and press the OK button. Now we can see all the available items in the Product Sold field even when there is no data. Remove Items from a Filter Using a Keyboard ShortcutHighlight items in a row or column and press Ctrl + – to remove them from the filter. You can select non-adjacent cells by holding Ctrl and then clicking on the cell. Add the Current Selection to the FilterYou can use the Search from within a pivot table filter to add items to your previously selected items. This is essentially like using an OR condition in your filtered item searches.
Use the Select All Filter ToggleQuickly select or deselect all items in the filter by using the Select All filter toggle. This can be very handy when dealing with a long list of items. You can quickly deselect all and then manually select a small number of items or quickly select all and manually deselect a small number of items. Defer Layout UpdateYou can defer updating the pivot table while you make changes in the PivotTable Fields window. This is generally only useful if your table is connected to a very large data source and you need to make many changes to the layout. This option is more useful for connections to external data sources as pivot tables with any data you can fit into Excel should be pretty responsive.
Add or Remove Fields Using the Check BoxYou can quickly add fields to your pivot table by using the check box next to the field name from the field list in the PivotTable Fields window. This can save time if you have a lot of fields to add instead of dragging and dropping each item. Fields containing text data will be added to the Rows section and fields containing numeric data will be added to the Values section when using the check box. Filter Fields from the PivotTable Fields WindowYou can filter items in a field from the field list in the PivotTable Fields window. The filter will only apply when the field is added to the filters, columns or rows area. Hover over the desired field and click on the small downward arrow to the right of the field name to open the filter menu. Rename Any LabelYou can rename any label in a pivot table simply by selecting the cell and typing over it. You can change item names in a field, row headings, column headings, filter labels, totals or grand total labels. The only conditions are you can’t rename it to something that already exists in your source data and you can’t type over a value. This doesn’t change the source data, it just changes how the item is labelled. Rename A Label With A Trailing SpaceOne thing you may want to do is change a column heading like our “Total” column that appears as “Sum of Total” to just show “Total” in the pivot table. Unfortunately, this can’t be done, since “Total” already exists in the source data. If you try to do this you will get a warning pop up saying “PivotTable field name already exists“. We can get around this by adding a space character to the end of the name. This will count as a different name but visually it will look the same as the old field name. Group Together Items in a FieldYou can group items in a field together to further summarize your data. Highlight the items and then right click and select Group from the menu. You can select multiple non-adjacent field items by holding the Ctrl key while making your selection. By default, the grouped name for a set of items will be Group1, Group2, Group3 etc… But you can change these to something more meaningful. You can also ungroup a grouped field. Select it and right click then choose Ungroup from the menu. You will notice a new field in appear which has the same name as the grouped field but with a number appended to the end. This is the newly created grouped field and you can use it just like any other field in your data. You can move it to the Filter, Row, Column area or remove it completely from the pivot table. Note that removing it from the pivot table will not ungroup the field. Group Together Items in a Field Using a Keyboard ShortcutYou can quickly group together items in a field by highlighting the items you want to group then pressing Alt + Shift + Right Arrow key. Ungroup Grouped Items Using a Keyboard ShortcutYou can quickly ungroup grouped items by highlighting the grouped item and then pressing Alt + Shift + Left Arrow key. Group DatesGrouping dates works a little differently than grouping items in a field. When you add a date field into either the rows or columns area, Excel will assume you probably want to view the data by Month, Quarter or Year and will automatically group the dates like this. If you actually wanted the view by date, you will need to right click on it and choose Ungroup from the menu. I’ve added the Order Date into the rows area and we can see it’s been grouped by year, quarter and month. Just like when grouping items in a text field, Excel creates new fields which can be use like any other field. You can remove the original date field without affecting the year or quarter fields. When you right click on the date field and select Group from the menu, you will be presented with a variety of grouping options.
Group Numbers into RangesExcel can also group numerical fields. This can be handy if you want to know something like “How much of my sales are from orders less than $50?“. If I place the Total field in both the Rows and Values area, I don’t get anything that useful. If you right click on the row, this numerical grouping menu will open and you can select a Starting and Ending point along with the interval length. Now it’s easy to see what range most of the sales are in. Search the PivotTable Fields ListIf your source data has a lot of fields then using the search box can help to narrow down the list to find what you’re looking for. Give Your Pivot Table a Different StyleQuickly change the style of any of your pivot tables using the preset PivotTable Styles. Go to the Design tab in the ribbon and click on the small downward arrow in the PivotTable Styles section to reveal a full selection of pivot table styles available. Note, the Design tab is only visible when the active cell cursor is in a pivot table. Explore Different Style OptionsToggle different PivotTable Style Options on or off. Go to the Design tab in the ribbon and look for the PivotTable Style Options section. Each option can be independently turned on or off to add a particular style element to your pivot table.
Refresh Your DataYou will need to refresh your pivot table when you add to or change your source data if you want to see these changes reflected in your pivot table results. You can do this from several locations. Select a cell in your pivot table to activate the PivotTable Tools tabs.
You can also refresh with a Right Click anywhere inside a pivot table and selecting Refresh from the menu. Refresh with a Keyboard ShortcutRefresh the connection to the active pivot table’s source data by using the Alt + F5 keyboard shortcut. Refresh All with a Keyboard ShortcutRefresh All data connections for all pivot tables in the workbook by using the Ctrl + Alt + F5 keyboard shortcut. Automatically Refresh Data when Opening Your WorkbookIf you want to make sure you’re always looking at the latest data in your pivot tables, you can set the workbook to refresh all pivot tables connected to particular data source. This is especially useful with external data sources. Select one of the pivot tables connected to your data source then go to the Analyze tab and press the Options button found in the PivotTables section. From the PivotTable Options menu, go to the Data tab and check the Refresh data when opening the file box. This will refresh all pivot tables in the workbook which are connected to the same data source. Select the Entire Pivot TableIf you’re like most people, you’ll probably end up making several copies of a pivot table in order to have different views of the data at the same time. If your pivot table is large or has items in the filter area, it can be tricky to select all of it in order to copy and paste. This is when Select Entire PivotTable comes in handy. Go to the Analyze tab and press the Select command under the Actions section then choose Entire PivotTable. This will select all of the pivot table including any filter elements above the table. You can also choose to select only the Labels or the Values area from here. Clear All FiltersIf you have multiple filters engaged on your pivot table you can quickly clear them all without going into each individual filter menu and selecting the Clear Filter From option. Select a cell in the pivot table which you want to clear filters from to activate the PivotTable Tools tabs in the ribbon.
Your pivot table will revert back to a completely unfiltered state showing results based on all source data. Clear Your Entire Pivot TableYou can clear your pivot tables entirely back to the initial blank state if you want to start over completely with your pivot table analysis.
Your pivot table will now be in its initial blank state with all fields and filters removed. Clear Old Field ItemsYou might have seen this happen before. You delete old data and then add in the new data, but you still see items from the old data after you refresh the pivot table. These items are still stored in the pivot cache and displayed in filter selections even if there is no data for it at all. It can be very confusing when it happens. You can change the settings so that your pivot cache doesn’t retain any of the old field items when you refresh your data. Go to the Analyze tab and press the Options button found under the PivotTable section to open the PivotTable Option. Then go to the Data tab and select None under the Number of items to retain per field option. Now when you refresh, the old phantom items will no longer appear. Format NumbersUnfortunately, number formatting from source data does not transfer into your pivot tables. You may want to format your numbers to make them more readable. To format a given field, Right Click on any number in that field and select Number Format from the menu. The familiar Format Cell dialog box will open with only the Numbers tab available and you will be able to format the numbers in your field the same as any other cell in your workbook. The cool thing is that applying your number formats this way will be dynamic. Even when you move the field around in the pivot table, add other fields or filter on items the formatting will remain applied to the entire field in the pivot table. Expand or Collapse Field HeadingsIf your pivot table has multiple dimension fields in a row or column you can expand or collapse the outer fields to show more or less detail. Right click on the field you want to expand or collapse and select Expand/Collapse from the menu.
Double Click to Expand or Collapse Field HeadingsYou can expand or collapse fields with a double right click on the field item. This is great to de-clutter a pivot table when you only need to show the full detail for one item. Add or Remove Expand or Collapse buttonsYou can add expand or collapse buttons to your pivot tables to make it more obvious to another user that they can expand or collapse the pivot table view as well as which items are already expanded or collapsed. To add these buttons, select your pivot table and go to the Analyze tab and press the +/- Buttons button in the Show section. Automatically Create a Pivot Table for each Item in a FilterLet’s say you have a pivot table with a field in the Filter area and you would like a pivot table for each item in the field. You might think this has to be done manually by copying the pivot table and then filtering on a new item in the field, but this can actually be done automatically using Show Report Filter Pages. In our example we have the Customer Name field in the filter area and pivot table is currently filtered on Arthur James, and we want a pivot table like this for each customer. Select you pivot table, it will need to have a field in the filter area. Go to the Analyze tab in the ribbon and press the Options button found in the PivotTable section then select Show Report Filter Pages from the menu. Select the desired field from the Show Report Filter Pages dialog box if you have multiple fields in the filter area of your pivot table then press the OK button. Excel will now create a new sheet for each item in the field you selected. Each sheet will be named after the item in your field and will contain a copy of your pivot table filtered on that item. It’s a big time saver when you have a lot of items in your field. Allow Multiple Filters Per FieldExcel has two types of filters available for a pivot table field, Label Filter and Value Filter. Let’s say you wanted to filter this pivot table on all Product Sold that start with “P” (using a Label Filter) and having a Total value larger than $20 (using a Value Filter), with the default settings this is not possible to have both filters at the same time. We can update the settings to allow this.
Select your pivot table and go to the Analyze tab in the ribbon and press the Options button in the PivotTable section. Enable multiple filters in the PivotTable Options dialog box.
Now you will be able to use both Label Filter and Value Filter at the same time on one field. Get a List of Unique Values from a FieldYou can use pivot tables to get a list of the unique values in any field of your data. Simply drag the field which you want unique values from into the Rows area of a blank pivot table and the resulting pivot table will contain a list of unique values from your data for that field. Count the Occurrence of an Item in a FieldPlacing any field with text data into the Values area of the pivot table will cause the calculation to default to Count instead of Sum. This means we will get the count of the number of occurrences of each item. In this example, we have placed Product Sold field which contains text data, into both the Rows and Values area of the pivot table, and we see Count of Product Sold in the Values area. Delete Your Source DataAfter creating your pivot table you can delete the source data if you want to reduce the workbook file size. You can delete your source data by deleting the sheet it’s contained on. Right click on the sheet tab and select Delete from the menu. Your pivot table contains a cache of the data so it will continue to work as normal. If you want to see your data again you can double left click on the grand total of your pivot table and the data will appear in a new sheet. Sort Items Alphabetically in Ascending or Descending OrderSort items alphabetically in either ascending or descending order. Left click on the filter icon and select Sort A to Z for ascending or Sort Z to A for descending order. Manually Sort ItemsSelect the item you want to move and hover your mouse cursor over the active cell border until it turns to a four-way arrow cross. Left click and drag the item to its new position. You will see a large green bar that indicates where the item will be placed. Release the item into its new position. Sort Items According to a Corresponding ValueYou can sort your pivot table by ascending or descending values. From the filter menu select the More Sort Options. Select either Ascending (A to Z) or Descending (Z to A) then choose one of the value fields in your pivot table and then press the OK button. Create a Custom Sort OrderIf sorting a field alphabetically in ascending or descending order doesn’t suit your needs, you can create a custom sort order by creating a custom list! To add a custom list, go to the File tab in the ribbon and select Options. From the Excel Options menu choose Advanced then scroll down to the General section and press the Edit Custom List button.
Refresh your pivot table and the order will change to that of the list you entered. This will also be the default sort order now for that field any time you create a pivot table with that field in it. Insert Blank Line After Each ItemFor a less cluttered look and feel you can insert a blank line after each item in your pivot table. Select your pivot table and go to the Design tab of the ribbon and click on the Blank Rows button in the Layout section then select Insert Blank Line after Each Item. Items in your pivot table will be visually separated with white space so the viewer knows that the data pertains to something different. You can get rid of these blank rows from the Design tab of the ribbon and clicking on the Blank Rows button in the Layout section then selecting Remove Blank Line after Each Item. Double Click to Open Value Field SettingsYou can double right click on any column heading to open the Value Field Settings for that field. Count Distinct ItemsTo count distinct items you will need to create your pivot table with data added to the Data Model. Check the Add this data to the Data Model box when creating your pivot table. In this example, we have our Product Sold field in the Rows area and Customer Name in the Values area which gives us a count of the orders by product. If we want a unique count of the customers who ordered each of the products then we need to change the default Count to Distinct Count for our values settings. Right click anywhere on the field which you want to obtain a distinct count for and then select Value Field Settings from the menu. From the Value Field Settings select Distinct Count to summarize value field by and press the OK button. Now the values will display the distinct count. Note the Grand Total now reflects that we have 7 distinct customer names in our data of 50 orders. Hide Selected ItemsYou can hide selected items quickly without going into the filter menu (small down arrow next to the column heading).
This allows you to quickly filter out items without going into the filter menu and checking or unchecking boxes in a long list of items. Keep Selected ItemsSimilarly to hiding selected items, you can choose to keep only the selected items with a filter.
Change the LayoutTo change the layout of your pivot table go to the Design tab and select Report Layout button under the Layout section. You can select from three different layout options.
To demonstrate the different layout options, we have created a pivot table with two fields (Product Sold and Customer Name) in the Rows section and a field (Total) in the Values section.
Repeat All Item LabelsYou can repeat all your pivot tables item labels by going to the Design tab and selecting the Report Layout button under the Layout section. Select Repeat All Item Labels to turn on repeated labels and select Do Not Repeat Item Labels to turn off repeated labels. By default, a pivot table will show the field label and then blank cells underneath for all other sub-fields included in the field heading. Creating a Tabular Form layout with Repeat All Item Labels is a great way to create another set of more aggregated “Source Data” that you can copy and paste as values and use elsewhere. Turn Grand Totals On or OffYou can add grand totals to your pivot table to help you see at a glance the total for any values field across any row or column. Go to the Design tab and select the Grand Totals command from the Layout section. Select from the four option for displaying grand totals.
Turn Subtotals On or OffWhen your pivot table has more that one dimension, you can add or remove subtotals to make results easier to understand.
Go to the Design tab and select the Subtotals command from the Layout section. Select from three option for displaying subtotals in your pivot table.
Turn Off GETPIVOTDATABy default when you try to reference a cell within a pivot table in a formula, Excel will create a GETPIVOTDATA formula for the reference. These can be annoying when you want a simple relative A1 style reference since the GETPIVOTDATA acts similarly to an absolute reference. You can turn this default option off by selecting your pivot table then going to the Analyze tab in the ribbon and clicking on the small down arrow next to the Options button under the PivotTable section. Uncheck the Generate GetPivotData option to turn this feature off. You can also turn it back on from there too! Add a Second Field to the Values AreaYou can add the same field to the Values area of your pivot table two or more times.
Each time you add the field to the Values area it will get a sequential number added to the end, but remember you can change these titles. You can then change the summarize type to show a Count, Average, Max, Min, Variance or Standard Deviation instead of the Sum. This will allow you to summarize the field in a variety of different ways at the same time. Add Data BarsAdding data bars can be a great way visually show the relative value of each item in your pivot table. In the above table we’ve added the Total field to the pivot table twice and used one instance to add data bars to the pivot table. Select the range in your pivot table where you’d like to add the data bars. Go to the Home tab in the ribbon and under the Styles section press the Conditional Formatting button then select the Data Bars option from the menu. You can choose either a Gradient Fill or Solid Fill and there are several different color options available. You can also create your own style data bars using the More Rules options in the menu. The cool thing is these data bars will be dynamic and applied to the entire field even if the range changes when you add dimensions or update data. Add Color ScalesYou can add color scales to your pivot table to create a heat map to easily identify high, medium and low values in your data. Select the range in your pivot table where you would like to add the color scales. Go to the Home tab and under the Styles section press the Conditional Formatting button then select the Color Scales option from the menu. There are several different color options to choose from or you can create your own rules and color options by selecting More Rules. Add Icon SetsYou can add various icon sets to your pivot tables to visually indicate items that increased, decreased or stayed the same. Select the range in your pivot table where you’re wanting to add the icons. From the Home tab and in the Styles section press the Conditional Formatting button and then select the Icon Sets option. You’ll find a large variety of icon options to choose from including arrows, shapes, flags, checks and X’s, stars and many others. You can adjust the rules for when each symbol appears by using the More Rules option. Add Highlighted Cells RulesYou can add conditional formatting to highlight cell values that fit certain rules to make them stand out. In this example I have created a rule to highlight cells between $100 and $300. You can create many different types of rules.
Go to the Home tab and in the Styles section select Conditional Formatting then select the Highlight Cells Rule option. You can then select from the options mentioned above and set the criteria values required. Add Highlighted Top or Bottom N FormattingYou can add conditional formatting to highlight cells that are in the top N or bottom N values of the pivot table. In this example I have added the formatting to show the top 3 values. Choose from several different options.
Although these options mention top and bottom 10, the number can be selected as desired. In the Home tab and under the Styles section select Conditional Formatting then select the Top/Bottom Rules option. You can then select from the options mentioned above. Format Numbers as Invisible TextIf you’ve added some sort of conditional formatting like data bars to your pivot table and want to get rid of the numbers to clean up the look of the table, then you can format the numbers as invisible text. Right click anywhere in the field which you want to format and select Number Format from the menu. In the Format Cells dialog box choose Custom from the Category and then type three semi-colons ;;; into the Type area and press OK. The data will still exist in your pivot table, but it just won’t be visible! Use Conditional Format Settings to Remove TextWhen you add data bars or icon sets with conditional formatting, there is actually a setting to show only the data bars or icons. This can be found in the More Rules menu when setting up your conditional formatting. This is a more simple option than messing around with custom formats, but is limited to data bars and icons. For the data bars check the Show Bar Only box. For the icon sets check the Show Icon Only box. Prevent Column Width Changing on UpdateBy default Excel will automatically adjust columns of a pivot table so that everything fits. This means those really long headings like Count of Customer Country will take up a lot of column space. If you adjust these wide columns to a smaller size, the next time you update the pivot table they will auto adjust back to fit the long heading title. You can change the settings so this doesn’t happen. Open the pivot table option. Select your pivot table and go to the Analyze tab in the ribbon then press the Options button in the PivotTable section. In the PivotTable Options window under the Layout & Format tab uncheck the Autofit column widths on update box. This will allow you to make changes to your pivot table without the column width automatically adjusting. Add A Calculated FieldAdding a calculated field to your pivot table is equivalent to adding a new column to your source data to perform a calculation based on the other data. For example, our data contains a Total Cost and Total amount for each order. If we want to calculate the Profit Margin on each order we could add another column with the calculation Profit Margin = 1 – (Total Cost / Total) or we can add calculated field. For a rate type calculations like a profit margin, it’s better to add the calculations as a Calculated Field rather than add an extra column with the calculation to the source data. Adding a rate calculation to the source data may result in incorrect calculations in your pivot table when viewing a pivot table at a more aggregated view than the data. Always add a calculated field instead! Select your pivot table and go to the Analyze tab in the ribbon and press the Fields, Items & Sets button found in the Calculations section. Then select Calculated Field from the menu. Add your calculation in the Insert Calculated Field dialog box.
Your calculated field will appear in the PivotTable Field list and can be used to create your pivot table just like any other field. Removing A Calculated FieldYou can delete a calculated field by selecting your pivot table by going to the Analyze tab in the ribbon and pressing the Fields, Items & Sets button then selecting Calculated Field from the menu. Delete a calculated field from the Insert Calculated Field dialog box.
The calculated field will no longer show up in your PivotTable Field list. Note, this can’t be undone! Insert a Calculated Field with a Keyboard ShortcutYou can quickly open the Insert Calculated Field dialog box to create a new calculated field or edit an existing calculated field by using the Ctrl + Shift + + keyboard shortcut. Add a Calculated ItemIf adding a calculated field is like adding a new column to your source data, then adding a calculated item is like adding a new row. Let’s say we have a simple table set up that shows the product sold along with the total sales. Our Total column in the data doesn’t include any tax, but there is a 15% chair tax we need to include in our analysis. No problem, we can add this with a Calculated Item! Select a field cell in your pivot table (the calculated item option will be grayed out if you select a value cell). Go to the Analyze tab then press the Fields, Items & Sets button in the Calculations section. Select Calculated Item from the menu. Give your new calculated row a name, then add in a formula. You can add an item into the calculation by selecting the appropriate field then double clicking on any of the items in the field or pressing the Insert Item button. I named the calculation Chair Tax and the formula will calculate 15% of the value being summarized. We now see a new row called Chair Tax appear in our Product Sold field and the value is 15% of the Chair value. Note that this new row does contribute to the grand total. Replace ErrorsIf you create a calculated field with a division operation like our profit margin calculation, then it’s possible you might see some #DIV/0! errors (divide by zero). You can replace these with a number like 0 or some text of your choosing to make the table more presentable. Seeing these errors won’t instill confidence in your audience, so it’s best to replace them with something more assuring. Select your pivot table and go to the Analyse tab and select Options in the PivotTable section. Enable error values option.
Now your pivot table will be much more presentable. Add Relationships to Between TablesYou can create relationships between different data tables using pivot tables and the Data Model. When creating a pivot table check the Add this to the Data Model box in the Create PivotTable window. For example if our sales data only contained a customer ID and the customers name was stored in another table, this would allow us to relate the customer ID to the name and build sales data pivot tables based on the customer name. Read this post for more detail on building relationships in pivot tables. Create a PivotChartPivot tables are amazing, but even with a pivot table it’s sometimes hard to see the trend or anomaly in the data. PivotCharts allow you to create a visualization of your pivot table summary. The cool thing is, they are dynamically linked together. If you change something in your pivot table the changes will happen in your pivot chart and vise-versa. You can turn your pivot tables into a variety of different chart types.
To insert a PivotChart select the pivot table you want to create PivotChart based on. Go to the Analyze tab in the ribbon and select PivotChart from the Tools section. Select the type of chart you want from the Insert Chart menu. This can also be accessed from the Insert tab in the Charts section with the PivotChart command. Now we have a visual representation of our pivot table! You can use the field buttons in the chart (lower left corner in the above example) to filter and sort your chart, notice this will also update your pivot table! Insert a PivotChart with a Keyboard ShortcutSelect a cell inside your pivot table and press Alt + F1 to quickly add a PivotChart to the same sheet as your pivot table. You can use the alternate ribbon command shortcut keys of Alt + N + SZ Add a SlicerSlicers are great for making dynamic and interactive dashboards. They work exactly like a filter but the list of filtered items will remain visible to the user. Go to the Analyze tab in the ribbon and select Insert Slicer under the Filter section. Select the fields for which you want to create the slicer. Selecting multiple fields will result in a separate slicer for each field selected. You can now filter on any combination of items from your slicer.
Add a TimelineTo add a Timeline to your pivot table or chart, your source data will need to contain a date field. Timelines are exactly like Slicers, but only for use with date fields. They allow you to filter on dates with a visual time line slider bar. Go to the Analyze tab in the ribbon and select Insert Timeline under the Filter section. Select the date fields for which you want to create the Timeline. Selecting multiple fields will result in a separate timeline for each field selected. You can now filter your data on any range of dates from your Timeline.
Hide All Field Buttons on a Pivot ChartGenerally speaking, having less junk on your charts is better! This is why I like to remove all the buttons on a PivotChart to free up valuable chart real estate. Any filtering needed can be done from the linked pivot table instead of from the chart. Right click on any of the buttons on the chart and select Hide All Field Buttons on Chart. Connect Slicers or Timelines to Multiple Pivot TablesYou can connect your slicers and timelines to any number of pivot tables. This means you can control many pivot tables or pivot charts from one single slicer or timeline. This is great for creating interactive dashboards. Right click on the slicer or timeline and then select Report Connections from the menu. You can also access this from the Slicer Tools Option ribbon tab when your slicer is selected. Select any pivot tables you want to connect to the slicer by checking the corresponding box and press the OK button. This is where properly naming your pivot tables can really pay off. Change the Number of Columns in a SlicerIf your field has a lot of items in it, you can conserve some space while still showing all items in the slicer by adjusting the number of columns. Right click on the slicer and then select Size and Properties from the menu. In the Format Slicer window under the Position and Layout section set the desired Number of columns. Now you can fit the same number of items in a smaller area within your slicer. Filter on Top N ItemsYou can add filters to show your top or bottom N from your pivot table. From the filter icon, go to the Value Filters section and select Top 10. You will be able to select from a variety of options.
Add a Value Filter for any FieldWe can filter any field in the row or column area of a pivot table based on the associated value in the values area. Click on the filter icon to the right of the field name. Select Value Filters from the menu. From here you can select any number of options.
Regardless of which value filter option you selected, you’ll be able to adjust it from the value filters criteria menu.
Increase the Row Label Indent in Compact Form LayoutYou can increase the indent for row labels in a compact form layout pivot table to add a bit more of a distinct separation between fields. Select your pivot table and go to the Analyze tab and select Options. Go to the Layout & Format tab then adjust the character count for your indent as desired. Add Multiple Subtotal CalculationsWhen you add subtotals to your pivot table, by default it will just show the sum subtotal. It is possible to change this to show a different calculation like Count, Average, Minimum, Maximum, Standard Deviation and others. It’s also possible to show multiple different subtotal calculations at the same time! For this, you’ll need to have a pivot table with at least two fields in the rows area of the pivot table. Right click on the field you’re going to add different subtotals to and then select Field Settings from the menu. From the Field Settings menu under the Subtotals & Filters tab select the Custom subtotals option then select any Subtotal Calculation type. This is an awesome way to show more summary information in your pivots. Include New Items in Manual FiltersLet’s say you’ve spent a decent amount of time manually filtering your pivot table to a select number of field items. You then add data to your source data set and the new data contains additional items in your field which weren’t in the previous data. When you refresh your pivot table, the new data items will not be included in the filtered items. You have to go through and manually select those new items if you want them to appear in the filtered pivot table. You can change this so that new data items in a field are automatically added to any manual filters. Right click on the field then select Field Settings. From the Field Settings menu go to the Subtotals & Filters tab and check the Include new items in manual filter box. Use an External Data Connection SourceYou can use an external data source for your pivot table. This means you can store your data in another Excel file or CSV and do your analysis in a separate workbook. Your data can be updated by other people or systems without affecting your current workbook and analysis. Select the cell where you want your new pivot table to appear then go to the Insert tab in the ribbon and select PivotTable from the Tables section. From the Create PivotTable menu select the Use an external data source radio button then click on the Choose Connection button. In the Existing Connection menu select Browse for More. In the resulting file picker menu, navigate to the desired file and select it then press the Open button. In the resulting select table menu select the location of the data from your file. My data was in a table on a sheet called Data so I have selected Data$ from the list. Make sure to check the First row of data contains column headers box if your data has column headers and then press the Ok button. You can now finish creating and building your pivot table as usual. Refresh External Connections on a ScheduleYou can set up your external connections to refresh with any new or updated data on a periodic schedule of your choosing. Go to the Data tab in the ribbon and select the Queries & Connections command. If you select the pivot table with your external connection first, you can directly open the Properties menu from the Data tab. Right click on the external connection from the Queries & Connections window and select Properties from the menu. Under the Usage tab in the Connection Properties menu, check the Refresh every N minutes box and then set the number of minutes. Note that all the Refresh control options are disabled (unchecked) by default. You can also enable a few other options from this menu.
Show Value AsThe next 10 tips are the among the most powerful features of pivot tables, yet most Excel users don’t know about them. At some stage you’ve probably gone off to the side of your pivot table and done some formula calculations to see how much of a percentage a value represents, calculated a running total or a percent difference. This stuff is already a baked in feature known as Show Values As. Unfortunately it’s sort of hidden in the right click menu or as the secondary tab in the Value Field Settings. It’s so useful and powerful it really deserves a featured spot in the Analyze tab of the ribbon. You can access this feature a couple of different ways. Right click on any value and then select Show Values As from the menu. In the sub-menu you’ll be able to select from many different calculation options. You’ll also be able to set a field back to No Calculation from here. Another option is to access this through the Value Field Settings menu. Go to the Analyze tab and press the Field Settings button found under the Active Field section. Or you can right click anywhere on the field to open the menu and then select Value Field Settings. Once you’re at the Value Field Settings menu go to the Show Values As tab. There are many options here as to how to display your values. We’ll explore these in the following tips. Show Value as % of Grand TotalSelect the % of Grand Total option to show all values as a percent of the grand total. When selected the Grand Total will show as 100% and all the values in the Value area will add up to 100%. Show Value as % of Column TotalSelect the % of Column Total option to show all values in each column as a percent of that columns total. When selected each column total will show as 100% and all the values in each column will add up to 100% including the Grand Total column. Show Value as % of Row TotalSelect the % of Row Total option to show all values in each row as a percent of that rows total. When selected each row total will show as 100% and all the values in each row will add up to 100% including the Grand Total row. Show Value as % of Parent ColumnSelect the % of Parent Column option to show all values in each row as a percent of its parent column. Each row of values within a parent column will add to 100%. The Grand Total column will contain all 100% values. A parent column will be the top most field in the Columns area of the pivot table. Show Value as % of Parent RowSelect the % of Parent Row option to show all values in each column as a percent of its parent row. Each column of values within a parent row will add to 100%. The Grand Total row will contain all 100% values. A parent row will be the top most field in the Rows area of the pivot table. Show Value as DifferenceSelect the Difference From option to show all values as the difference between the current item and previous item, next item or a fixed item’s value. Show Value as % of DifferenceSelect the % Difference From option to show all values as the percent difference between the current item and previous item, next item or a fixed item’s value. Show Value as Running TotalSelect the Running Total In option to show a running total for a given field. Show Value as % of Running TotalSelect the % Running Total In option to show the running total for a given field as a percent of the Grand Total. Show Value as RankSelect the Rank Smallest to Largest or Rank Largest to Smallest option to show a fields rank. Does a PivotTable have a values area?After you create a PivotTable or PivotChart, you are ready to add the fields that contain the data you want to display in the report. You typically select one field for each area in the layout section. However, to see different values for a specific field, you can also add multiple copies of a field to the Values area.
Which fields will you add to the PivotTable areas?At the bottom of the PivotTable Field List pane are the four areas of the pivot table: Report Filter, Column Labels, Row Labels, and Values. If you used a Recommended PivotTable layout, you will see the fields from that layout in those areas.
What is the data field on a pivot?PivotTable Fields is a Task Pane associated with a PivotTable. The PivotTable Fields Task Pane comprises of Fields and Areas. By default, the Task Pane appears at the right side of the window with Fields displayed above Areas. Fields represent the columns in your data – range or Excel table, and will have check boxes.
What happens by default when you select a numeric field to add to a PivotTable report?To choose the data you want to show in your pivot table, you can add fields into three sections: Columns, Rows and Values. By default, numeric fields are added to Values and other non-numeric fields are added to Columns or Rows.
|