How To Make A Pivot Table With No Background

Pivot tables are awesome! They're ane of Excel'due south most powerful features, they let you lot to rapidly summarize large amounts of data in a thing of seconds. This drove of crawly tips and tricks volition help you chief pivot tables and become a data ninja!

You're gonna learn all the tips the pros employ, so get ready for a very very long post!

Download the example file with the information used in this post to follow forth.

Your Source Data Needs to be in Tabular Format

When using a pin table your source data volition need to be in a tabular format. This ways your data is in a table with rows and columns.

The kickoff row should contain your column headings which describes the information directly below in that cavalcade. There should be no blank column headings in your data.
Each row after the column headings should pertain to exactly i record in your data. For instance, if your table contains client data then each row might have the proper noun, street address, postal lawmaking and email address for exactly one customer.

Utilize a Table for Your Source Data

When creating a pivot table it'southward commonly a good idea to turn your data into an Excel Table. When calculation new rows or columns to your source data, you won't need to update the range reference in your pin tables if your information is in a Table.

Without a tabular array your range reference volition look something like above. In this case, if we were to add information past Row 51 or Cavalcade I our pivot table would not include it in the results.

To create and name your tabular array.

Select your data.
Go to the Insert tab and printing the Table button in the Tables section, or use the keyboard shortcut Ctrl + T.
Printing the OK button.
With the active cell inside the table, go to the Tabular array Tools Pattern tab.
Alter the Table Name under the Properties section and press Enter.

Now when you create a pin tabular array you tin reference information technology 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 information technology and the new information will appear in your results.

Change Source Information

Ok, if y'all decide not to utilize 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 push button then select Change Information Source from the menu. Update your range accordingly in the following Change PivotTable Data Source pop up dialog box.

Undock the PivotTable Fields Window

To undock the PivotTable Fields window pane hover your mouse cursor over the championship until it turns into a four way pointer, then correct 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 Window

To quickly dock the PivotTable Fields window pane hover your mouse cursor over the title until information technology turns into a 4 way arrow, and so double right click. It will dock to the last docked location (either to the right or left side).

Hibernate or Unhide the PivotTable Fields Window

You can get more screen real manor past hiding the PivotTable Fields window. Select a cell in your pivot table and and then get to theAnalyze tab in the ribbon. Printing the Field List push in the Testify section to toggle the PivotTable Fields window on or off.

Yous can also close the window using the X in the upper correct corner.

Yous tin besides show or hide the PivotTable Fields window with a right click anywhere inside your pivot table then select Testify Field List or Hibernate Field Listing (depending on the current state of your PivotTable Fields window).

Change the Default Arrangement of the PivotTable Fields Window

Click on the gear wheel with a downward pointer to change default appearance of the PivotTable Fields window.

In that location are five different available options you can select from.

Field Section and Areas Section Stacked
Field Section and Areas Section Side-By-Side
Field Section But
Areas Department Only (2 by two)
Areas Section Only (1 past iv)

Change the Sort Order of Your Field Listing

The list of data fields volition show in the same order as the source data past default. You tin change this to show in alphabetical order (A to Z) if yous prefer. Left click on the options card in the PivotTable Fields window to access the choice.

Select the Sort A to Z choice in the card. Your fields will now display in descending lodge!

Move, Resize and Shut the PivotTable Fields Window

Right click on the modest down arrow to the correct of the PivotTable Fields championship to move, resize or close the window.

Move – This will allow you lot to undock the window and movement it around the spreadsheet.
Size – This allows y'all to adjust the width and height (when undocked) of the window.
Shut – This allows you to close the window. You can open information technology once more from the Analyze tab > Field List command.

PivotTable and PivotChart Wizard Keyboard Shortcut

Utilize the keyboard shortcut Alt + D + P to open the PivotTable and PivotChart Wizard. This will take you through the steps to gear up either a pivot tabular array or pivot nautical chart, select your data and the location for your new pivot table or chart.

Create a PivotTable With a Keyboard Shortcut

Use the ribbon command keyboard shortcut Alt + N + Five to quickly create a pivot table.

Evidence Details Behind a Value

Double right click on a value inside a pivot tabular array to quickly see the information behind that aggregated value. A new canvass volition be created with just the information relating to that value.

You tin can also admission this feature by right clicking on any value then selecting Show Details.

Plough Off Show Details to Avoid Accidental Double Click

If the power to prove the detailed data behind a pin tabular array event doesn't interest yous, then you tin turn this characteristic off. This means you lot and can avoid creating new sheets with $.25 of information in them because of adventitious 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 carte du jour.

In the PivotTable Options carte du jour go to the Information tab and uncheck the Enable show details box to disable this feature.

Replace Blank Cells

This pivot table contains bare cells because our source data does non contain whatsoever records for those combinations of dimensions. For example, there is no data for Arthur James and French republic and so the intersection of the Arthur James row and France column is bare. We can change the settings to display something such equally a cipher or some text proverb "N/A" instead of a blank.

Left click anywhere in the pin table so select PivotTable Options.

In the PivotTable Options menu

Become to the Layout & Format tab.
Cheque the For empty cells show box and enter the value you would similar to bear witness for blanks. In our example nosotros will replace bare cells with 0.
Press the OK push.

Now the previously blank cells have been replaced past zeros.

Show Items with No Data

In this case we have create a pivot tabular array with Customer Name and Product Sold in the Rows surface 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 alter this so that nosotros 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 Production Sold field even when there is no data.

Remove Items from a Filter Using a Keyboard Shortcut

Highlight items in a row or column and press Ctrl + – to remove them from the filter. You can select non-adjacent cells past belongings Ctrl then clicking on the cell.

Add the Current Choice to the Filter

You tin can employ the Search from within a pivot table filter to add items to your previously selected items. This is substantially like using an OR condition in your filtered item searches.

Select your beginning fix of items to be filtered on either manually or using the search box (with the same method as step 2 & 3).
Use the Search box to search for and so select the 2nd set of items to be filtered on.
Check the box marked Add together current option to filter that will appear when using the search box.
Printing the OK button.
Now if y'all view the filter, you volition see both your selections from step 1 and selections from step 2 included in the filter.

Use the Select All Filter Toggle

Chop-chop select or deselect all items in the filter by using the Select All filter toggle. This tin be very handy when dealing with a long list of items. You tin 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 Update

You tin can defer updating the pivot tabular array while you lot make changes in the PivotTable Fields window. This is more often than not just useful if your tabular array is continued to a very large data source and you need to make many changes to the layout. This pick is more useful for connections to external data sources as pivot tables with any data you can fit into Excel should be pretty responsive.

Check the Defer Layout Update box in the PivotTable Fields window.
Make the changes to your layout in the Filters, Columns, Rows or Values department. Your pivot tabular array will remain static.
Press the Update push and your table will update to reflect all the changes made.

Add together or Remove Fields Using the Check Box

You can quickly add fields to your pivot table by using the check box side by side to the field name from the field list in the PivotTable Fields window. This can save time if y'all take a lot of fields to add instead of dragging and dropping each item. Fields containing text information will be added to the Rows section and fields containing numeric information will be added to the Values section when using the check box.

Filter Fields from the PivotTable Fields Window

You tin can filter items in a field from the field list in the PivotTable Fields window. The filter will but use when the field is added to the filters, columns or rows area. Hover over the desired field and click on the small-scale down pointer to the right of the field name to open the filter card.

Rename Whatever Label

You tin can rename any label in a pivot tabular array 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 yard total labels. The just weather are you lot 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 Infinite

One 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 pin 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 alert popular upward saying "PivotTable field proper name already exists". We tin get around this by adding a space character to the end of the proper noun. This will count as a dissimilar name but visually it will look the same every bit the old field name.

Group Together Items in a Field

You lot tin can group items in a field together to further summarize your data. Highlight the items and then correct click and select Group from the menu. You tin can select multiple non-adjacent field items by belongings the Ctrl key while making your selection. By default, the grouped name for a set of items will exist Group1, Group2, Group3 etc… But yous can change these to something more meaningful.

You lot can also ungroup a grouped field. Select it and right click so choose Ungroup from the card.

Yous volition detect a new field in appear which has the same name as the grouped field merely with a number appended to the cease. This is the newly created grouped field and you tin can utilise it just like any other field in your information. You tin can move it to the Filter, Row, Column expanse or remove it completely from the pivot table. Annotation that removing it from the pivot table will not ungroup the field.

Group Together Items in a Field Using a Keyboard Shortcut

Yous can quickly grouping together items in a field by highlighting the items you want to grouping then pressing Alt + Shift + Right Arrow key.

Ungroup Grouped Items Using a Keyboard Shortcut

You tin quickly ungroup grouped items by highlighting the grouped item and then pressing Alt + Shift + Left Arrow key.

Group Dates

Group dates works a piffling differently than grouping items in a field. When you add a date field into either the rows or columns expanse, Excel volition presume y'all probably want to view the information by Month, Quarter or Year and will automatically group the dates like this. If you really wanted the view by date, you lot volition need to right click on it and choose Ungroup from the menu.

I've added the Guild Date into the rows area and we can meet it's been grouped by year, quarter and calendar month.

But like when grouping items in a text field, Excel creates new fields which tin exist utilize like any other field. You can remove the original engagement field without affecting the year or quarter fields.

When y'all right click on the date field and select Group from the carte du jour, you will be presented with a diverseness of grouping options.

You tin can choose the Starting and Ending dates. All other dates outside the range will be bucketed into a grouping less than the start appointment and a group greater than the end date.
Cull the levels of granularity for your grouping.
If you select only Days as the group, you can choose to grouping based on the Number of days. Choosing 7 would be equivalent to grouping past weeks.

Group Numbers into Ranges

Excel can also group numerical fields. This can be handy if you desire 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 yous tin can select a Starting and Catastrophe point along with the interval length.

Now it's like shooting fish in a barrel to see what range most of the sales are in.

Search the PivotTable Fields List

If your source data has a lot of fields then using the search box can aid to narrow down the list to observe what you lot're looking for.

Give Your Pivot Tabular array a Different Style

Quickly change the style of whatever of your pivot tables using the preset PivotTable Styles.

Get to the Pattern tab in the ribbon and click on the pocket-size downwards arrow in the PivotTable Styles section to reveal a full selection of pivot table styles available. Notation, the Blueprint tab is only visible when the active jail cell cursor is in a pivot table.

Explore Different Style Options

Toggle different PivotTable Mode Options on or off. Go to the Design tab in the ribbon and look for the PivotTable Style Options section.

Each choice can be independently turned on or off to add together a particular style chemical element to your pivot table.

With all options unchecked the pivot table is empty of row headers, banded rows, column headers and banded columns.
Adding Row Headers.
Calculation Banded Rows.
Adding Column Headers.
Adding Banded Columns.

Refresh Your Information

You lot will need to refresh your pin tabular array when you add to or change your source data if you want to run into these changes reflected in your pivot table results. You can practice this from several locations.

Select a cell in your pivot table to activate the PivotTable Tools tabs.

Go to the Clarify tab.
Press the Refresh button.
Select either Refresh or Refresh All.
- Refresh will refresh any pivot table connected to the source information of the active pivot tabular array.
- Refresh All will refresh all data connections for all pivot tables in the workbook.

Yous can also refresh with a Right Click anywhere inside a pivot table and selecting Refresh from the menu.

Refresh with a Keyboard Shortcut

Refresh the connection to the active pivot tabular array's source data by using the Alt + F5 keyboard shortcut.

Refresh All with a Keyboard Shortcut

Refresh All data connections for all pivot tables in the workbook past using the Ctrl + Alt + F5 keyboard shortcut.

Automatically Refresh Data when Opening Your Workbook

If you want to brand sure you're ever looking at the latest data in your pivot tables, you can ready the workbook to refresh all pivot tables connected to particular information source. This is especially useful with external data sources.

Select one of the pin tables continued to your information source then go to the Analyze tab and press the Options button found in the PivotTables section.

From the PivotTable Options card, go to the Data tab and check the Refresh data when opening the file box. This volition refresh all pivot tables in the workbook which are connected to the same data source.

Select the Unabridged Pin Table

If you're similar most people, you'll probably finish 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 exist tricky to select all of it in social club 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 Deportment department then choose Entire PivotTable. This will select all of the pivot table including whatever filter elements above the table.

You can also cull to select only the Labels or the Values area from here.

Articulate All Filters

If you have multiple filters engaged on your pivot tabular array you can chop-chop clear them all without going into each individual filter bill of fare and selecting the Clear Filter From option.

Select a cell in the pivot table which you want to articulate filters from to activate the PivotTable Tools tabs in the ribbon.

Go to the Clarify tab.
Press the Clear push from the Deportment section.
Select Clear Filters from the menu.

Your pivot tabular array will revert back to a completely unfiltered state showing results based on all source information.

Clear Your Entire Pivot Table

Y'all can clear your pivot tables entirely back to the initial blank state if you want to first over completely with your pin tabular array analysis.

Go to the Analyze tab.
Press the Clear push button from the Actions section.
SelectClear All from the carte.

Your pivot tabular array will at present be in its initial blank state with all fields and filters removed.

Clear Quondam Field Items

You might have seen this happen before. You delete onetime data so add in the new data, but you withal see items from the onetime data after you refresh the pivot table. These items are still stored in the pivot cache and displayed in filter selections fifty-fifty if there is no data for information technology at all. It can be very confusing when it happens.

You tin change the settings so that your pin cache doesn't retain any of the sometime field items when y'all refresh your data. Become to the Clarify tab and press the Options push establish nether the PivotTable department to open up the PivotTable Option. And so go to the Data tab and select None under the Number of items to retain per field option.

Now when yous refresh, the old phantom items volition no longer announced.

Format Numbers

Unfortunately, number formatting from source data does not transfer into your pin tables. Y'all may want to format your numbers to brand them more readable.

To format a given field, Right Click on any number in that field and select Number Format from the menu. The familiarFormat Cell dialog box volition open with just the Numbers tab available and you volition 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 fashion will exist 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.

Aggrandize or Plummet Field Headings

If your pivot table has multiple dimension fields in a row or column you tin can expand or collapse the outer fields to show more or less detail.

Correct click on the field you want to expand or collapse and select Aggrandize/Collapse from the carte.

You lot can expand or plummet only the selected item and leave the remaining alone.
You lot tin aggrandize or plummet every particular in the field selected.
You can expand or collapse only the selected item to a given level.

Double Click to Expand or Collapse Field Headings

You can expand or collapse fields with a double right click on the field item. This is slap-up to de-clutter a pivot table when you only need to show the full particular for one particular.

Add or Remove Aggrandize or Collapse buttons

You tin add expand or collapse buttons to your pivot tables to make information technology more than obvious to some other user that they can expand or collapse the pivot table view besides as which items are already expanded or complanate.

To add these buttons, select your pivot tabular array 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 Filter

Allow's say you have a pivot table with a field in the Filter expanse and you would like a pivot table for each particular in the field. You might think this has to be done manually by copying the pivot table and so filtering on a new item in the field, merely this can actually be done automatically using Bear witness Written report Filter Pages. In our instance nosotros take the Customer Name field in the filter area and pivot table is currently filtered on Arthur James, and we desire a pivot table like this for each customer.

Select you pin table, it will demand to accept a field in the filter surface area. Go to the Analyze tab in the ribbon and printing the Options button found in the PivotTable department then select Show Report Filter Pages from the menu.

Select the desired field from the Evidence Study Filter Pages dialog box if yous take multiple fields in the filter area of your pivot table then press the OK button.

Excel will now create a new canvas for each item in the field you selected. Each sheet will be named subsequently the item in your field and will contain a copy of your pin tabular array filtered on that item. It's a big fourth dimension saver when you have a lot of items in your field.

Allow Multiple Filters Per Field

Excel has two types of filters available for a pivot table field, Characterization Filter and Value Filter. Permit'southward say you wanted to filter this pivot tabular array on all Product Sold that start with "P" (using a Label Filter) and having a Full value larger than $20 (using a Value Filter), with the default settings this is non possible to have both filters at the same time. We can update the settings to permit 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.

Go to the Totals & Filters tab.
Check the Let multiple filters per field box.
Press the OK button.

Now you lot volition be able to apply both Label Filter and Value Filter at the same time on i field.

Get a Listing of Unique Values from a Field

Y'all can employ pivot tables to get a list of the unique values in any field of your data. Simply drag the field which you lot want unique values from into the Rows surface 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 Field

Placing any field with text data into the Values area of the pin tabular array will crusade the calculation to default to Count instead of Sum. This means we will go 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 pin tabular array, and nosotros see Count of Product Sold in the Values expanse.

Delete Your Source Data

After creating your pivot table you lot tin can delete the source data if y'all desire to reduce the workbook file size. You can delete your source information past deleting the sheet it's independent on. Right click on the sheet tab and select Delete from the carte du jour. Your pivot table contains a cache of the data and then information technology will continue to piece of work equally normal. If y'all want to see your information again you lot tin can double left click on the grand total of your pin table and the data will appear in a new sheet.

Sort Items Alphabetically in Ascending or Descending Order

Sort 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 Items

Select the particular y'all want to move and hover your mouse cursor over the active prison cell border until it turns to a four-style arrow cross.

Left click and elevate the item to its new position. You will run into a large green bar that indicates where the particular volition be placed.

Release the detail into its new position.

Sort Items According to a Corresponding Value

You tin sort your pin table by ascending or descending values.

From the filter menu select the More than Sort Options.

Select either Ascending (A to Z) or Descending (Z to A) so choose 1 of the value fields in your pivot table and then press the OK push.

Create a Custom Sort Order

If 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, get to the File tab in the ribbon and select Options. From the Excel Options menu choose Advanced then scroll down to the Full general department and press the Edit Custom Listing button.

Select NEW List from the Custom lists box.
Enter your listing of field items actualization in the order you desire them to sort in your pin table.
Printing the Add together button to add your list.
Press the Ok button.

Refresh your pivot tabular array and the order will change to that of the list you entered. This volition also exist the default sort order now for that field whatsoever time you create a pin table with that field in it.

Insert Blank Line Later Each Particular

For a less cluttered look and experience you can insert a blank line after each item in your pivot table. Select your pivot table and go to the Pattern tab of the ribbon and click on the Blank Rows push button in the Layout section then select Insert Blank Line after Each Detail.

Items in your pivot table will be visually separated with white infinite and then the viewer knows that the data pertains to something different. You lot can get rid of these blank rows from theDesign tab of the ribbon and clicking on the Bare Rows button in the Layout department then selectingRemove Blank Line afterward Each Item.

Double Click to Open Value Field Settings

You can double right click on whatsoever column heading to open up the Value Field Settings for that field.

Count Distinct Items

To count distinct items yous volition need to create your pivot table with data added to the Data Model. Check the Add together this data to the Data Model box when creating your pivot table.

In this example, we take our Production Sold field in the Rows area and Customer Proper noun in the Values area which gives us a count of the orders by production. If nosotros want a unique count of the customers who ordered each of the products then we need to alter the default Count to Distinct Count for our values settings.Correct click anywhere on the field which you desire to obtain a distinct count for and so select Value Field Settings from the card.

From the Value Field Settings select Distinct Count to summarize value field past and press the OK button.

Now the values will display the distinct count. Note the Chiliad Total now reflects that nosotros accept seven distinct customer names in our data of 50 orders.

Hide Selected Items

You can hide selected items quickly without going into the filter menu (small down arrow adjacent to the column heading).

Select the items yous desire to hibernate with your filter. You tin can utilise Ctrl to select non-adjacent items. Then right click on the selected items.
Select Filter from the menu.
Select Hide Selected Items from the sub menu.

This allows you to chop-chop filter out items without going into the filter carte and checking or unchecking boxes in a long listing of items.

Continue Selected Items

Similarly to hiding selected items, yous can choose to keep only the selected items with a filter.

Select the items you want to keep with your filter. Yous can use Ctrl to select non-adjacent items. Then right click on the selected items.
Select Filter from the menu.
Select Proceed Only Selected Items from the menu.

Alter the Layout

To change the layout of your pin table go to the Pattern tab and select Study Layout push under the Layout section. Yous tin select from three dissimilar layout options.

Show in Meaty Form
Bear witness in Outline Form
Show in Tabular Class

To demonstrate the different layout options, we have created a pivot table with two fields (Product Sold and Customer Proper name) in the Rows department and a field (Total) in the Values section.

Compact form will contain all the Row fields in ane column in a hierarchical structure.
Outline form volition still take a hierarchical structure but each Row field will exist in a dissever column in the pivot tabular array.
Tabular form volition not be in a hierarchical structure and each Row field volition be in a separate column in the pivot tabular array.

Repeat All Item Labels

Yous tin can echo all your pin tables particular labels past going to the Design tab and selecting theStudy 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 groovy way to create another set up of more than aggregated "Source Data" that you can re-create and paste every bit values and utilise elsewhere.

Turn Grand Totals On or Off

Y'all can add grand totals to your pin table to help you lot see at a glance the total for whatsoever values field across any row or column.

Become to the Design tab and select the 1000 Totals control from the Layout section. Select from the four option for displaying grand totals.

Off for Rows and Columns (no yard totals volition display)
On for Rows and Columns
On for Row Simply
On for Columns Only

Plough Subtotals On or Off

When your pin table has more than that one dimension, you can add or remove subtotals to brand results easier to sympathize.

No subtotals results in a cleaner looking pivot table, just you lose vital information well-nigh totals across parent level field group.
Adding subtotals below the group results in actress rows in your pin tabular array.
Calculation subtotals above the group results in the extra data simply without the extra rows (with a compact layout).

Go to the Design tab and select the Subtotals control from the Layout section. Select from iii option for displaying subtotals in your pin table.

Do Not Testify Subtotals
Show all Subtotals at Lesser of Group
Show all Subtotals at Top of Group

Plow Off GETPIVOTDATA

By default when you lot endeavor to reference a cell within a pivot table in a formula, Excel will create a GETPIVOTDATA formula for the reference. These tin be annoying when yous desire a simple relative A1 mode reference since the GETPIVOTDATA acts similarly to an absolute reference.

Yous can turn this default option off past selecting your pivot tabular array and then going to the Clarify tab in the ribbon and clicking on the small-scale downward arrow next to the Options push under the PivotTable department. Uncheck the Generate GetPivotData option to turn this feature off. You lot tin can too turn it back on from there too!

Add a 2d Field to the Values Area

You tin can add the aforementioned field to the Values area of your pin tabular array ii or more times.

Correct click on the field yous desire to add together to the Values area again and select Add to Values.
Y'all tin besides left click and drag the field into the Values area again.

Each time you add the field to the Values area it will get a sequential number added to the end, but remember yous tin change these titles. You tin can then alter the summarize blazon 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 diverseness of dissimilar ways at the aforementioned time.

Add Data Bars

Adding data confined can exist a groovy way visually evidence the relative value of each detail in your pivot tabular array. In the above tabular array we've added the Full field to the pin table twice and used ane case to add data bars to the pivot table.

Select the range in your pin tabular array where yous'd similar to add the data confined.

Become to the Domicile tab in the ribbon and under the Styles section press the Conditional Formatting push then select the Data Bars selection from the carte. You lot tin can choose either a Gradient Fill or Solid Fill up and at that place are several different colour options available. You tin also create your own style data bars using the More than Rules options in the menu. The absurd thing is these data bars will be dynamic and applied to the entire field even if the range changes when you add together dimensions or update data.

Add Color Scales

You can add color scales to your pivot table to create a heat map to hands place 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 Habitation tab and under the Styles department press the Conditional Formatting push and then select the Color Scales option from the card. There are several different color options to choose from or you can create your own rules and color options by selecting More Rules.

Add together Icon Sets

You 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 pin table where you're wanting to add the icons.

From the Abode tab and in the Styles section press the Provisional Formatting button and so select the Icon Sets option. You lot'll find a large diversity 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 past using the More than Rules selection.

Add together Highlighted Cells Rules

Y'all tin can add conditional formatting to highlight jail cell values that fit certain rules to make them stand out. In this case I have created a rule to highlight cells betwixt $100 and $300. You tin can create many different types of rules.

Numbers greater than a given value.
Numbers less than a given value.
Numbers betwixt ii given values.
Numbers equal to a given value.
Text that contains a specific cord.
Dates that meet a given criteria.
Duplicate values.

Go to the Home tab and in the Styles section select Conditional Formatting and then select the Highlight Cells Dominion option. You lot can then select from the options mentioned above and set the criteria values required.

Add Highlighted Acme or Bottom North Formatting

You can add together conditional formatting to highlight cells that are in the elevation North or bottom N values of the pivot table. In this example I take added the formatting to show the top 3 values. Choose from several different options.

Superlative ten items.
Top ten percent.
Lesser x items.
Bottom ten percent.
Above average.
Beneath average.

Although these options mention top and bottom 10, the number can be selected as desired.

In the Dwelling tab and under the Styles section select Conditional Formatting then select the Top/Lesser Rules option. You tin can then select from the options mentioned to a higher place.

Format Numbers as Invisible Text

If you've added some sort of conditional formatting like data confined to your pivot tabular array and desire to get rid of the numbers to clean up the wait of the table, so you can format the numbers as invisible text.

Right click anywhere in the field which you lot want to format and select Number Format from the menu. In the Format Cells dialog box choose Custom from the Category then type three semi-colons ;;; into the Type expanse and printing OK. The data will yet exist in your pin table, just it just won't be visible!

Utilize Conditional Format Settings to Remove Text

When you add together data confined or icon sets with conditional formatting, in that location is really a setting to bear witness merely the data confined or icons. This tin can be constitute in the More than Rules menu when setting up your conditional formatting.

This is a more simple choice than messing effectually with custom formats, but is express to data confined and icons.

For the information bars check the Testify Bar But box.

For the icon sets check the Show Icon Simply box.

Forestall Column Width Changing on Update

By default Excel volition automatically adjust columns of a pivot table then that everything fits. This ways those really long headings like Count of Customer Land will take up a lot of column space. If you adjust these wide columns to a smaller size, the side by side time you lot update the pivot table they will auto conform dorsum to fit the long heading title. You can change the settings so this doesn't happen.

Open up the pivot table option. Select your pivot tabular array and go to the Analyze tab in the ribbon and then printing the Options button in the PivotTable section.

In the PivotTable Options window nether the Layout & Format tab uncheck the Autofit column widths on update box. This will allow yous to make changes to your pin tabular array without the cavalcade width automatically adjusting.

Add A Calculated Field

Adding 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 case, our data contains a Total Cost and Total amount for each order. If we desire to calculate the Turn a profit Margin on each guild we could add another column with the calculation Profit Margin = 1 – (Full Price / Total) or we can add calculated field.

For a rate type calculations like a profit margin, it's better to add the calculations equally a Calculated Field rather than add an extra column with the adding to the source information. Adding a rate adding to the source data may result in incorrect calculations in your pivot table when viewing a pin table at a more aggregated view than the data. Always add a calculated field instead!

Select your pivot table and become to the Analyze tab in the ribbon and press the Fields, Items & Sets push button found in the Calculations section. Then select Calculated Field from the carte.

Add together your adding in the Insert Calculated Field dialog box.

Give your new calculation a Name. This is the field proper name that volition appear in the pivot table.
Create your Formula. You can double right click whatever field in the field list to use it in your calculation.
Press the Add button.
Press the OK push.

Your calculated field will announced in the PivotTable Field list and can be used to create your pin table but like any other field.

Removing A Calculated Field

Y'all can delete a calculated field by selecting your pin table by going to the Analyze tab in the ribbon and pressing the Fields, Items & Sets push button then selectingCalculated Field from the menu.

Delete a calculated field from the Insert Calculated Field dialog box.

Employ the drop down menu to select the calculated field y'all want to delete.
Press the Delete button.
Press the OK button.

The calculated field will no longer show up in your PivotTable Field list. Note, this tin can't be undone!

Insert a Calculated Field with a Keyboard Shortcut

Y'all 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 together a Calculated Item

If adding a calculated field is like adding a new column to your source data, then calculation a calculated particular is like adding a new row.

Let's say nosotros have a unproblematic tabular array fix that shows the production sold forth with the total sales. Our Total column in the data doesn't include whatever tax, merely there is a 15% chair tax we need to include in our analysis. No problem, we can add this with a Calculated Particular!

Select a field cell in your pin tabular array (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 push button in the Calculations department. Select Calculated Item from the menu.

Give your new calculated row a name, then add in a formula. Yous can add an particular into the calculation past selecting the appropriate field and then double clicking on any of the items in the field or pressing the Insert Item push button.

I named the calculation Chair Revenue enhancement and the formula will calculate 15% of the value being summarized.

We at present run into a new row called Chair Tax appear in our Product Sold field and the value is fifteen% of the Chair value. Annotation that this new row does contribute to the one thousand total.

Supercede Errors

If you create a calculated field with a segmentation operation like our profit margin adding, then it's possible you might meet some #DIV/0! errors (separate by goose egg). You lot can supercede these with a number like 0 or some text of your choosing to make the table more presentable. Seeing these errors won't instill conviction in your audience, and then it's all-time to supplant them with something more assuring.

Select your pivot table and go to the Analyse tab and select Options in the PivotTable department.

Enable error values choice.

Go to the Layout & Format tab.
Bank check the For error values testify box and input a value or some text.
Press the OK button.

At present your pivot table volition be much more presentable.

Add Relationships to Between Tables

Yous tin can create relationships between different data tables using pivot tables and the Data Model. When creating a pivot tabular array check the Add this to the Data Model box in the Create PivotTable window.

For case if our sales data only contained a customer ID and the customers name was stored in some other table, this would permit usa to relate the customer ID to the proper name and build sales data pin tables based on the customer proper noun.

Read this post for more detail on building relationships in pin tables.

Create a PivotChart

Pivot tables are astonishing, but fifty-fifty with a pivot table it'south sometimes hard to see the tendency or anomaly in the data. PivotCharts allow you to create a visualization of your pivot table summary.

The cool matter is, they are dynamically linked together. If yous change something in your pivot table the changes will happen in your pivot chart and vise-versa.

You can turn your pin tables into a variety of dissimilar chart types.

Cavalcade charts
Line charts
Pie charts
Bar charts
Area charts
Radar charts

To insert a PivotChart select the pin table you want to create PivotChart based on. Go to the Analyze tab in the ribbon and select PivotChart from the Tools department. Select the type of chart y'all want from the Insert Nautical 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 nautical chart, notice this will also update your pivot table!

Insert a PivotChart with a Keyboard Shortcut

Select a cell inside your pivot table and press Alt + F1 to apace add a PivotChart to the aforementioned sheet as your pivot table.

Y'all can use the alternate ribbon command shortcut keys of Alt + Northward + SZ

Add together a Slicer

Slicers are not bad for making dynamic and interactive dashboards. They piece of work exactly like a filter but the list of filtered items volition remain visible to the user.

Go to the Analyze tab in the ribbon and select Insert Slicer under the Filter department.

Select the fields for which you desire to create the slicer. Selecting multiple fields will result in a separate slicer for each field selected.

Yous can at present filter on any combination of items from your slicer.

Select whatever item with a left click. You can select multiple adjacent items with a left click and drag.
Turn on multi-select manner to select multiple non-adjacent items.
Clear your selected filter and get-go again.

Add a Timeline

To add a Timeline to your pin tabular array or chart, your source data will need to contain a date field.

Timelines are exactly like Slicers, but only for use with appointment 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 desire to create the Timeline. Selecting multiple fields will result in a separate timeline for each field selected.

You tin now filter your data on any range of dates from your Timeline.

Select to filter by Days, Months, Quarters or Years.
Drag the finish of the timeline to adjust the filtered range. Unfortunately, there is no multi-select similar the slicers and you lot can only select one continuous range of dates.
Clear your filter to kickoff again.

Hibernate All Field Buttons on a Pivot Chart

Generally 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 tin can be done from the linked pivot table instead of from the nautical 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 Tables

Y'all can connect your slicers and timelines to any number of pivot tables. This means yous can control many pivot tables or pivot charts from ane single slicer or timeline. This is great for creating interactive dashboards.

Right click on the slicer or timeline and and so select Study Connections from the card. You can besides access this from the Slicer Tools Pick ribbon tab when your slicer is selected.

Select any pivot tables y'all want to connect to the slicer past 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 Slicer

If your field has a lot of items in it, you lot can conserve some space while still showing all items in the slicer by adjusting the number of columns.

Correct click on the slicer and then select Size and Properties from the menu.

In the Format Slicer window under the Position and Layout section gear up the desired Number of columns.

Now you tin can fit the same number of items in a smaller area within your slicer.

Filter on Tiptop N Items

You can add filters to show your top or bottom N from your pivot tabular array.

From the filter icon, get to the Value Filters department and select Top x. You will be able to select from a variety of options.

Select to either show the Top or Lesser results from your pivot table.
Select a number of items, percentage or total sum for the top or bottom criteria.
Select from either Items, Percentage or Sum.
- Items – This will testify the items in your field that accept the highest or everyman N values.
- Percent – This will show the items in your field where the value is in the top or lesser Nth percentile.
- Sum – This will bear witness the top or lesser items in your field where the sum is greater than the number entered in footstep two.
Select the metric in your pivot table values area to base the top or bottom results on.

Add together a Value Filter for any Field

We can filter any field in the row or column surface 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 bill of fare. From here you tin can select any number of options.

Filter on items where the value Equals a given value.
Filter on items where the value Does Not Equal a given value.
Filter on items Greater Than a given value.
Filter on items Greater Than Or Equal To a given value.
Filter on items Less Than a given value.
Filter on items Less Than Or Equal To a given value.
Filter on items Betwixt two given values.
Filter on items Not Between 2 given values.

Regardless of which value filter option yous selected, you'll exist able to adjust it from the value filters criteria menu.

Select which values field your criteria volition apply to.
Select the filtering option desired. This allows you to change the option yous previously selected.
Enter the criteria value to filter based on. If you selected a filtering option that requires two inputs, there will exist ii input fields here.

Increase the Row Label Indent in Compact Form Layout

Yous can increment the indent for row labels in a compact course layout pivot table to add a scrap more of a distinct separation between fields.

Select your pin tabular array and go to the Analyze tab and select Options.

Go to the Layout & Format tab and so adjust the character count for your indent equally desired.

Add Multiple Subtotal Calculations

When 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 unlike calculation like Count, Average, Minimum, Maximum, Standard Deviation and others. It's also possible to testify multiple different subtotal calculations at the aforementioned time!

For this, you'll demand to have a pivot tabular array 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 carte du jour.

From the Field Settings menu under the Subtotals & Filters tab select the Custom subtotals option then select any Subtotal Calculation type.

This is an crawly way to show more summary data in your pivots.

Include New Items in Transmission Filters

Permit's say you've spent a decent corporeality of time manually filtering your pin 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 information items will not be included in the filtered items. You have to get through and manually select those new items if you want them to appear in the filtered pivot table.

You can alter this so that new data items in a field are automatically added to whatsoever transmission filters. Right click on the field then select Field Settings.

From the Field Settings menu get to the Subtotals & Filters tab and check the Include new items in transmission filter box.

Employ an External Information Connexion Source

You can utilize an external data source for your pivot table. This means you can store your data in another Excel file or CSV and practice your assay in a split up workbook. Your data tin can exist updated by other people or systems without affecting your current workbook and assay.

Select the prison cell where you want your new pivot tabular array to appear so get to the Insert tab in the ribbon and select PivotTable from the Tables section.

From the Create PivotTable carte du jour select the Use an external data source radio button and 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 tabular array card select the location of the data from your file. My data was in a tabular array on a canvas called Data and so I have selected Data$ from the list. Make sure to bank check the First row of data contains column headers box if your data has column headers and and so press the Ok push.

You can now cease creating and building your pivot table equally usual.

Refresh External Connections on a Schedule

You tin can ready your external connections to refresh with any new or updated information on a periodic schedule of your choosing. Become to the Data tab in the ribbon and select the Queries & Connections control.

If you select the pivot table with your external connection first, you can directly open up the Backdrop menu from the Data tab.

Correct 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, bank check the Refresh every North minutes box and so ready the number of minutes.

Note that all the Refresh control options are disabled (unchecked) by default. You can besides enable a few other options from this menu.

Enable background refresh
Enable Refresh data when opening the file
Enable Refresh this connection on Refresh All

Show Value Equally

The 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 pin tabular array and done some formula calculations to see how much of a percent a value represents, calculated a running full or a percentage difference. This stuff is already a baked in feature known as Testify Values As.

Unfortunately information technology'southward 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 dissimilar ways.

Correct click on any value and and so select Show Values As from the carte. In the sub-menu yous'll be able to select from many different calculation options. Yous'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 bill of fare.

Go to the Analyze tab and printing the Field Settings button found under the Agile Field section.

Or you tin can right click anywhere on the field to open the bill of fare and then select Value Field Settings.

One time you lot're at the Value Field Settings carte become to the Show Values As tab.

There are many options here equally to how to brandish your values. We'll explore these in the following tips.

Testify Value every bit % of Grand Total

Select the % of One thousand Full option to bear witness all values as a percent of the thousand full. When selected the Grand Total volition show as 100% and all the values in the Value surface area volition add together up to 100%.

Show Value as % of Column Total

Select the % of Column Total selection to show all values in each cavalcade as a percent of that columns total. When selected each cavalcade total will show as 100% and all the values in each column volition add up to 100% including the Grand Total cavalcade.

Prove Value as % of Row Total

Select the % of Row Total option to prove all values in each row as a per centum of that rows total. When selected each row total volition prove equally 100% and all the values in each row volition add together up to 100% including the M Total row.

Show Value every bit % of Parent Cavalcade

Select the % of Parent Cavalcade option to show all values in each row equally a per centum of its parent column. Each row of values within a parent column will add to 100%. The Chiliad Total column will contain all 100% values.

A parent cavalcade volition be the height most field in the Columns area of the pivot table.

Evidence Value as % of Parent Row

Select the % of Parent Row option to show all values in each column as a pct of its parent row. Each column of values within a parent row will add to 100%. The Grand Full row will contain all 100% values.

A parent row volition be the acme most field in the Rows area of the pivot tabular array.

Show Value as Difference

Select the Difference From choice to testify all values every bit the difference between the electric current particular and previous detail, next detail or a fixed particular's value.

Show Value every bit % of Difference

Select the % Difference From pick to evidence all values equally the percent difference between the electric current item and previous item, next item or a fixed item'south value.