Excel Table Tools Design Tab Missing
How to Create aPivot Table in Excel
[The Complete Guide]
Written by co-founder Kasper Langmann, Microsoft Office Specialist.
Who else wants to handle and visualize data without breaking a sweat?
I know I do!
That's why I've written this ultimate tutorial for you on "Pivot Tables" in Excel.
Pivot Tables make it easy to summarize data and create dynamic reports.
Kasper Langmann, Co-founder of Spreadsheeto This is our most ambitious tutorial.
It comes in at 7733 words, has a free exercise file, video and assignments.
So, grab a cup of coffee and get started!
*This tutorial is for Excel 2019/Microsoft 365 (for Windows). Got a different version? No problem, you can still follow the exact same steps.
Protip:
Get back to this guide in an instant. Hit CTRL + D to bookmark this page.
What is a Pivot Table and why use it?
Ah yes, the Pivot Table. No doubt, one of the most loved yet maligned Excel tools.
Many people have heard of Pivot Tables but fewer know exactly what they are.
If you are a business professional, you have likely seen Pivot Tables at work. If you are a job seeker, you might have noticed Pivot Table skills are highly sought after.
Kasper Langmann, Co-founder of Spreadsheeto So, what is a Pivot Table?
The best way to define it is: It's a built-in feature of Excel that allows you to take data and re-arrange it.
It creates a table that allows you to choose what and how you want to view your data.
Why use Pivot Tables?
For starters, they improve the ability to draw actionable conclusions from your data. This is especially true of large sets of data with many different attributes.
Later, I'm going to show how you manipulate the filters, columns, and rows of a Pivot Table. This allows you to quickly summarize and visualize the data from many different perspectives.
Kasper Langmann, Co-founder of Spreadsheeto Get your FREE exercise file
Before you start:
Throughout this guide, you need a data set to practice.
I've included one for you (for free).
Download it right below!
Download the FREE Exercise File
Video: Preparing the data
Before you create a Pivot Table, your data needs to be set up the right away.
In this video, I show you exactly how this is done.
How to create a Pivot Table
Alright, you're here to learn how to create a Pivot Table.
Follow the 6 steps to a Pivot Table outlined below. In a matter of minutes, you have created your very first Pivot Table 🙂
Then you'll learn how to create a Pivot Table from multiple sheets (which is surprisingly handy).
Let's get right into how to create a Pivot Table.
About the data set
We will use a data set that contains sales totals for some products of varying size and price. The data set also includes data of each transaction as well as the wholesale price for each.
How to create a Pivot Table in 6 (easy) steps
1: Ensure that all your data columns have headers.
2: Ensure that your data contains no blank rows
3: Click on any part of the data table
4: In the 'Tables' group on the 'Insert' tab, click on 'PivotTable'. The 'Table/Range' is selected by default as your contiguous range of data (ensured by steps 1 and 2). Leave the defaults selected in the 'Create PivotTable' dialog box.
5: Click 'OK' and your new worksheet will now be activated.
6: Choose fields to add to your Pivot Table as needed from the Pivot Table 'Field List'.
Note: The 'Field List' is only visible if you click the cursor into the Pivot Table itself. If you click into a cell outside the Pivot Table, the 'Field List' will become hidden.
The resulting Pivot Table is shown in the next figure. Note that 'Month' is set as the columns while 'Location' shows as the rows, just as we set it up.
The Pivot Table sums up all the sales figures by location and month for us. This is just the beginning of what Pivot Tables can do for you!
Kasper Langmann, Co-founder of Spreadsheeto 
How to create a Pivot Table from multiple sheets
Now you know how to create a Pivot Table from data on a single worksheet.
Let's look at how to create a Pivot Table from multiple worksheets.
In our example, we have three separate data worksheets for our locations. They are included as sheets in the example workbook file.
Kasper Langmann, Co-founder of Spreadsheeto Here, their names are 'Chicago', 'Nashville', and 'San Francisco'.
Let's select the first worksheet of the three, 'Chicago'.
Then go ahead and follow the next 11 steps…
1: Press Alt + D, then press P to make the 'PivotTable and PivotChart Wizard' appear.
2: Under "Where is the data you want to analyze?" select the radio button for "Multiple consolidation ranges" and then click 'Next'.
This takes you to 'Step 2' of the wizard.
3: Select the radio button "I will create the page fields" and click 'Next'.
4: The next thing you need to do is select your first data range.
In the example file, this will be the data table on the 'Chicago' tab and you need to select the range from A2:H7.
5: Repeat the same process for both the 'Nashville' and 'San Francisco' location sales data.
6: Now, highlight the first range in the 'All ranges' section. Then click the radio button next to '1' in the 'How many page fields do you want?' section.
7: Under 'Field one:' type in a name for the 'page field'. In this example, simply type 'Chicago'.
8: Now repeat this for the remaining two ranges. Then click 'Next.
9: In step 3 of the wizard, leave the default setting of 'New worksheet' selected and click 'Finish'.
The resulting Pivot Table should look like the following figure.
This Pivot Table has combined the sales figures from all three location worksheets.
One thing to note is the filter in cell B1. This filter allows you to select one or more of locations.
Make sure the box next to 'Select Multiple Items' is checked. Now you can select or unselect any combination of the filter items as you see fit.
Kasper Langmann, Co-founder of Spreadsheeto Update Pivot Table range
Has your source data for an existing Pivot Table changed?
Then, you need to update your Pivot Table range.
Sometimes existing data changes. In this case, refreshing the Pivot Table is necessary.
Kasper Langmann, Co-founder of Spreadsheeto In other cases, you may add more data.
To include this new data, you need to change your data source.
Here is a closer look at those 2 methods.
- Method 1: Change Data Source
- Method 2: Refresh a Pivot Table
Method 1: Change Data Source
Do you want to add new data to your Pivot Table source data?
Then, you need to change your data source to expand it to the new range.
Let's say you add October data to your original data. This original data has data from April through September. You will need to expand the data source range to include the new rows of October data.
Kasper Langmann, Co-founder of Spreadsheeto Please note that there is a worksheet in the example file with a tab name of 'Oct Data To Add'.
This can be copied and pasted to the original raw data on the 'Sales Data Raw' worksheet. This makes it a bit easier to follow along with the next few steps.
Kasper Langmann, Co-founder of Spreadsheeto Once you have added the new October data, go to your Pivot Table and click into any cell within the Pivot Table.
Now click on 'Change Data Source' in the 'Data' group of the 'Analyze' tab.
Make sure 'Table/Range' (in the 'Change PivotTable Data Source' window) matches the range that includes your new data.
Then click 'OK'.
If it doesn't match, you may need to manually select the entire range.
Just click a cell in the data, and use the shortcut Ctrl + A to expand to the new used range.
You should now be able to see the new October data as its own column in your Pivot Table now.
Kasper Langmann, Co-founder of Spreadsheeto 
Excel also allows for using 'dynamic named ranges' as data sources.
This can simplify the process of changing your data source.
For more information about how to create 'dynamic named ranges' in Excel, check out this resource.
You also need to know how to refresh your Pivot Table…
And that's exactly what you'll learn in method 2 below!
Method 2: Refresh a Pivot Table
Another method you need to know about is how to refresh a Pivot Table.
This is necessary when your source data changes.
Kasper Langmann, Co-founder of Spreadsheeto Let's say you need to make a price change:
- You want to increase the price from $275.79 to $299.99
- This applies to the product code BP110966-XL
You can find this in your original data for your Pivot Table.
The incorrect price is causing the sales numbers to be understated.
To solve this, filter on that product code and change the sales price manually.
The new price for all these rows of data will have an impact on the amounts in the 'Total' column.
The next step is to go back to the pivot table and click into it so that we can see the Analyze tab.
Kasper Langmann, Co-founder of Spreadsheeto Now find the 'Data' group and click 'Refresh'.
Notice in the following figure how the 'Grand Total' has increased due to the price change we made.
This only took effect once we refreshed the Pivot Table.
How to use the Pivot Table
Your Pivot Table is created and you're super excited to use it.
But how do you use the Pivot Table?
That's what this section is all about.
Topics:
- Fields
- Adding columns
- Report filters
- Value field settings
- Average
- Count
- Distinct count
- Grouping
- Calculated fields
- Drill down
- Use data from a Pivot Table in a formula
Using Pivot Table Fields
A Pivot Table 'field' is referred to by its header in the source data (e.g. 'Location') and contains the data found in that column (e.g. San Francisco).
By separating data into their respective 'fields' for use in a Pivot Table, Excel enables its user to:
- Display datasets in a more logical output e.g. 'Total Units Sold' in San Francisco. This is covered in greater detail later when we discuss 'Value Field Settings'.
- Extract and display relevant data by organizing it in a way that is more suitable – either figuratively or aesthetically.
- Filter the data accordingly based on values found in the selected 'field'.
Adding Columns to Your Pivot Table Fields
Let's take a step back and recreate the Pivot Table with our added October data:
Now we have our blank table with which to start arranging the data with our Pivot Table 'fields'.
All available fields found in the data set – as defined by its headers – are listed on the right-hand side in the 'PivotTable Field List' area.
Kasper Langmann, Co-founder of Spreadsheeto To add data columns into the table, drag and drop the desired field into 'ColumnLabels', 'RowLabels', or 'Values' (these 3 are also covered in more detail later).
This example setup would list the data in rows separated by 'Location' and 'Item'. Columns are separated by 'Month'.
This forms a grid to display the 'Total of UnitsSold' in each 'Location' by 'Month'.
The output of this arrangement looks like the following:
When we select 'Location' and 'Item' for our rows, something cool happens:
- The 'Location' totals for each month are now viewed as subtotals for each month.
- The breakdown by 'Item' is also visible within each 'Location'.
To hide 'Item' rows, click on the minus next to the 'Location' rows.
This leaves only the subtotals visible for that 'Location'.
Using Report Filters
With the Pivot Table set up, you can see the breakdown of total sales across the chosen ranges ('Month', 'Location', and 'Item').
What if you needed to provide a look at units sold by item for all the locations?
Kasper Langmann, Co-founder of Spreadsheeto This is where 'Report Filters' come into play.
You drag and drop 'Item' into the 'Report Filter'area.
Let's also set 'Rows' to 'Month' and 'Columns' to 'Location'.
This gives you a brand-new perspective on the data.
This adds another row above your Pivot Table with the selected 'Filter Field'.
There's also a drop-down box to make a selection…
This allows the user to select only the relevant data required for the query:
If you want to select multiple entries from the filter, check the box next to 'Select Multiple Items' at the bottom of the drop-down.
Then you will be able to select all available entries in the filter in whatever combination you need.
Here we will select "Car" Rack and note the change in values:
Value Field Settings
In the previous example, we used the 'Sum Value Field Setting'.
This displays 'Total Units Sold' for each 'Month' and 'Location' listed in the Pivot Table.
By using different 'Value Field Settings', the data in the field presented under 'Values' is presented in different ways.
To access a field's 'Value Field Settings', click on its entry. Then select it from the resulting drop-down:
There are several possibilities for 'Value Field Settings'. I'm going to show you the most common.
Kasper Langmann, Co-founder of Spreadsheeto These settings are only available for fields placed in the 'Values' area.
It's not possible to apply them elsewhere.
Average
Click on 'Value Field Settings' and select 'Average'.
This tells the Pivot Table to average the contentof all the entries in that field and display that as the value.
But, this doesn't tell you anything about how many entries (transactions) there are.
Neither does it tell how they're spread across all products.
It gives you the average number of products sold.
Count
Selecting 'Count' tells the Pivot Table to add up the numberofentries in that field.
Then display that as the value.
In this case, this translates to the number of transactions (or rows) in our original data.
Performing this action on the example data yields the following:
Notice how the upper left cell has changed to show it is now displaying values as 'Count of Units Sold'.
This data shows that there are 25 transactions occurring in April at Chicago.
Distinct Count (Count unique values)
Selecting 'DistinctCount' is like 'Count'.
But it will ignore (not count) any entries with a value it has already encountered.
Using the example data with 'UnitsSold', let's say two transactions in April at Chicago both sold 5units.
Only one is unique. So, it's only counted as one.
Notice how the upper left cell has changed to show it is now displaying values as 'Distinct Counts'.
Using 'Distinct Count' is a useful 'Value Field Setting'.
For instance, if you wanted to count how many types of an Item was sold in each month at each location.
Grouping
Still using the above example, the fields have been set up.
Any relevant filters are applied and the 'Value Field Settings' have been set to 'Sum'.
This shows us 'Total Units Sold'.
What if you no longer want to see it monthly, but want to see the data organized into financial quarters?
Kasper Langmann, Co-founder of Spreadsheeto That's where 'Grouping' comes into play!
First, select any entries you wish to group (this is only applicable for 'Row' and 'Column' fields).
In this case, we will group April, May, and June.
Right click and select 'Group':
This will display a new column preceding it with a defaulted group name.
Doing this for the remaining months splits them into quarters as follows:
Now, this operation has achieved the technical task of grouping the data.
But visually, it does not portray what we are trying to achieve.
To rename the separate group entries, click on their cell and replace the text.
In this case, 'Group 1, 2, and 3' are replaced with 'Quarter 1, 2, and 3'.
Perform the same to'Month2' to achieve the following:
Finally, you need to display the values in these groups.
Add 'Subtotals' to the groups by right clicking on 'Quarter' and select 'Subtotal"Quarter"'.
Now you will have subtotals for 'Units Sold' by quarter.
This displays subtotals in addition to the separate entries in the group.
If the sub-entries are no longer needed, collapse each group using the +/- box next to each 'group field'.
This displays the table with collapsed groupings:
Calculated Fields
'Calculated Fields' allow you to insert values into the Pivot Table based on formulas.
These formulas can use the values of fields that are present.
Let's say the manager at each location gets a bonus percentage added to his salary. This bonus is based on the number of units sold each month.
Kasper Langmann, Co-founder of Spreadsheeto If that bonus % was equal to Units Sold / 100 we'd have to add more data to the original table to be able to calculate this.
But, with 'Calculated Fields', this isn't necessary!
Select the Pivot Table.
Then, navigate to the 'Analyze' tab.
Find 'Fields', 'Items' and 'Sets' in the 'Calculations' group.
Clicking 'Calculated Field' here opens the 'Calculated Field' dialogue.
Experiment using different operations and using different fields to see what is possible.
Kasper Langmann, Co-founder of Spreadsheeto Using the example of 'Bonus' as a new 'Calculated Field', inserting it in the 'Values' area yields:
Now it's easy to see what percentage bonus each manager should get in each month.
All this without adding any extra data to the original source.
Pivot Table Drill Down
The primary purpose of the Pivot Table is to combine multiple 'fields' of the same type.
Then display them in the desired way.
It's also handy to be able to do the opposite:
Namely, view the results that comprise the given value entry on the table.
Consider the scenario where a 'Count of Units Sold' is provided.
This is broken down into 'Month' and 'Location' on a 2×2 grid layout (as in the earlier examples).
Let's say you are asked for a list of transactions that comprise the entry in May at San Francisco.
Further, it requires all data associated with those transactions.
Then the Pivot Table's collapsible functionality has hidden these details from view!
The operation of drilling counters this. Instead, every record associated with that field entry is displayed.
Kasper Langmann, Co-founder of Spreadsheeto Using the above example, right click the 'Value' cell associated with May and San Francisco.
Then select 'Show Details':
This will drill that data point to gather all its associated entries.
These will then be displayed in another sheet by default:
This is a way of backtracking to the original data.
You now have an auto-generated list of entries for this data point.
Use data from a Pivot Table in a formula with GETPIVOTDATA
So far, I've shown you how to use a Pivot Table with data that comes from somewhere else.
For example, you use a Pivot Table to create a report from a set of sales data.
Although a Pivot Table is powerful, you still have plenty of uses for other formulas in Excel. Sometimes, these formulas refer to data inside a Pivot Table.
Kasper Langmann, Co-founder of Spreadsheeto This poses an issue; what happens when the Pivot Table is rearranged?
Excel has made the 'GETPIVOTDATA' function to help you with references to Pivot Tables.
This function glues together the references and the cell they are referring to. It's just like using relative references.
Kasper Langmann, Co-founder of Spreadsheeto The 'GETPIVOTDATA' function is unique. As opposed to other functions, all you need to do is to click a cell.
Start a formula in cell G5.
Type the equal sign and click a cell with data within the Pivot Table. Then hit 'Enter'.
It will look like this:
The entire 'GETPIVOTDATA' function is constructed for you by Excel.
This is the syntax of the function:
GETPIVOTDATA(Data_field, Pivot_location, [field1, item1, field2, item2], … )
Which turns into:
GETPIVOTDATA("Units Sold",$A$3,"Location","Nashville","Month","April")
- 'Data_field' = Name of the field whose value is desired. In this case, "Units Sold".
- 'Pivot_location' = Cell reference to the top left cell of the Pivot Table. In this case, an absolute reference to A3.
- The fields and items are extra identifiers to tell where the cell is located.
You can edit a 'GETPIVOTDATA' function but don't try to create it from scratch yourself. Let Excel do all the work and just click the cell within the Pivot Table when you need it.
Kasper Langmann, Co-founder of Spreadsheeto Pro tip
Note that 'GETPIVOTDATA' only returns data that is visible.
Try to remove a field from the field list. If that field removes the cell entirely, the function returns an error.
Put the field back.
The function recognizes the reference again and returns the result.
Sorting and Filtering
Your Pivot Table is now ready to sort and filter.
Let's take a closer look at that!
Topics:
- Sorting by value
- Sorting by date
- Sort Top 10 using the value filter
- Filter and organize data using 'Slicers'
For all examples in this section, you need to set up your Pivot Table in a specific way.
Use the raw sales data with the following 'field' structure:
This lists all the 'Transactions' as 'Rows'. 'Total Units Sold' as 'Values' separated into 'Columns' depicting 'Months'.
Kasper Langmann, Co-founder of Spreadsheeto Sorting by Value
Ensure the 'Item' filter is set to include 'All Values'.
Notice, the 'Transaction field' contains data in the format of an 8-digit number (e.g. 20100000).
When the Pivot Table is formed in the above way, the rows are populated and sorted in ascending numerical order:
By default, Excel will sort any numerical, alphabetical or dated list. All it requires is a logical, natural order. Such as 1 2 3, a b c, Jan Feb Mar etc.
Kasper Langmann, Co-founder of Spreadsheeto Overriding this sorting mechanism is done in a few, easy, steps.
Access the drop-down arrow next to the field of your choice to see available sort options.
Just as in the picture below…
- Selecting 'Sort Smallest to Largest' will cause a sort in ascending order.
- 'Sort Largest to Smallest' will cause a sort in descending order.
In this example, 'Transaction' will be sorted in descending order:
Notice how the data has now been flipped upside down. This is confirmed by entries showing for October instead of April. This is indeed in the last segment of the data.
Kasper Langmann, Co-founder of Spreadsheeto Sorting by Date
Dates (including day names, month names, years) are logically understood by Excel as ordered lists.
The above method can be applied to the 'Month field' in the columns to sort them into their reverse order:
Using the Value Filter to Sort Top 10
Besides the mentioned 'Sort' methods, Excel has a built-in method to present the 'Top 10' records.
This is based on the selected 'Value field'.
As an example, say we want to see the 'Top 10' transactions for total units sold.
Kasper Langmann, Co-founder of Spreadsheeto To do this, select the drop-down next to the 'Transaction' entry.
Then navigate through 'Value Filters' and 'Top 10':
You will be prompted to select how many 'Top' entries to display (in this case, we want the top 10).
You also need to select which 'Value Field' to base the selection on. As we only have 'Units Sold' listed as values, currently that is the only option.
Proceed with the suggestion in the dialogue:
We will now be presented with the Top 10 transactions.
In this case, transactions with 6 units sold make it into the Top 10:
Slicers
'Slicers' offer a more intuitive way to filter and organize the data within a Pivot Table.
To insert a 'Slicer' linked to your Pivot Table, just do the following:
Ensure the Pivot Table is selected and navigate through 'Options' -> 'Insert Slicer' from the Excel Ribbon:
In the proceeding dialogue, you will be asked to select which field(s) to be toggled from this 'Slicer'.
For this example, select 'Month' and move the resulting 'Slicer' to the right of your Pivot Table:
The 'Slicer' is now populated with all available values for 'Month'.
These are based on all its findings within the source data for the Pivot Table.
Proceed to toggle each month's visibility in the Pivot Table by selecting/deselecting it from the 'Slicer'.
To do this, click on the month in the 'Slicer'.
This user interface avoids the need to enter the Pivot Table manually to apply your filters.
Instead, this is a much more intuitive approach.
'Slicers' can even contain fields that are not currently presented in the Pivot Table. Let's say the table was sliced on 'Item'. This could be used to only show values for 'Units Sold' for each item separately. It could also be grouped however you desire.
Kasper Langmann, Co-founder of Spreadsheeto Pivot Table formatting
Excel offers many built-in options for formatting the Pivot Table.
In this part of the tutorial, you'll learn about the most common and best options.
Topics:
- Pivot Table styles
- Banded rows
- Report layouts
- Compact form
- Outline form
- Tabular form
- Conditional formatting for a Pivot Table
- Number formats
- Removing blanks
- Change name of Row Labels
Pivot Table Styles
With the Pivot Table selected, navigate through the Excel Ribbon to 'Design':
The rightmost part of this tab's content is the 'Styles' section.
These are predefined graphical layouts for your Pivot Table.
These layouts are available in a range of color schemes. These color schemes are grouped into three categories; Light, Medium, and Dark.
Kasper Langmann, Co-founder of Spreadsheeto These categories might vary a bit in your end.
If you have another version of Excel, it might be categorized a bit different. Some third party add-ins also affect the categorization.
Hovering over a style will reveal its name in a tooltip.
The variety that suits your report the best will be dependent on any current color schemes you have.
It also depends on whether you have some other formatting to consider.
The 'Style' formatting takes a low precedence. This gives way to any 'hard' formats you may have already applied to cells in the Pivot Table manually. This would include formatting such as 'Background Fills', 'Font Color', etc.
Kasper Langmann, Co-founder of Spreadsheeto Banded Rows
Excel also enables you to 'band' the rows within the selected 'Style' and color scheme.
This makes it much easier to trace rows from the initial column through to its contained data.
To activate 'Banded rows', navigate back to the 'Design' tab.
Select the checkbox 'Banded Rows' within the 'Style Options' group for the Pivot Table.
The depth of color is dependent once again on the style already selected.
Report Layouts
Excel allows 3 main types of report layouts to be applied to you Pivot Tables.
These are:
- Compact
- Outline
- Tabular
Here's a quick run-down of each one…
Compact Form
This is the default layout for a Pivot Table.
It has the following characteristics:
1: All row labels are displayed in their own row
2: The 'row field' label is always above the labels for its inner fields
3: All nested 'field' labels are indented to highlight the hierarchy
4: All 'row fields' occupy a single column
5: Subtotals can be shown at the top or bottom of groups
6: Subtotals for 'column fields' are always shown at the bottom
Outline Form
This layout minimizes the width of the Pivot Table.
The benefit?
You reduce the need for scrolling (which is always a nice thing!).
This layout form has the following characteristics:
1: All row labels are displayed in their own row
2: The 'row field' label is always above the labels for its inner 'fields'
3: There is no indented hierarchical structure
4: Instead of indentation, each 'row field' occupies a different column
5: Subtotals can be shown at the top or bottom of groups
6: Subtotals for 'column fields' are always shown at the bottom
Tabular Form
This layout form has the following characteristics:
1: All row labels for outer fields are on the same row as their first inner field
2: There is no indented hierarchical structure
3: Instead of indentation, each 'row field' occupies a different column
4: Subtotals for 'row fields' are always shown at the bottom of each group
5: Subtotals for 'column fields' are always shown at the bottom
Conditional formatting for a Pivot Table
You can use Conditional Formatting on rows, columns or even individual cells in a Pivot Table.
Just like you know it from any type of data set.
Highlight the relevant data and navigate to Home -> conditional formatting.
In this example, we will look at highlighting all items that have sold more than 50 units in a month.
Kasper Langmann, Co-founder of Spreadsheeto 
Follow the prompt that opens and enter 50 as the boundary for the formatting causes the following:
Different effects can be achieved by using the other options available.
Find them under 'Highlight Cells Rules' such as 'Less Than', 'Between' and 'Equal To'.
Each is geared to highlighting data that can be segregated in some way against a benchmark.
The difference between conditional formatting in a Pivot Table and normal cells is this:
To apply a conditional formatting rule to the entire Pivot Table, use the 2nd or 3rd options in the button that appears after using conditional formatting.
Number formats
Some data displays in an inherent logical way (e.g. currency starting with $ or £).
In other cases, it might be a case of how precise the data should be (e.g. number of decimal places).
Modifying these characteristics in the display of a Pivot Table allow a more appropriate representation of the data.
Kasper Langmann, Co-founder of Spreadsheeto Consider the following data:
Total sales for the 5 items listed are displayed for the months of April, May and June:
While we might be able to guess that the numbers relate to money values, it is not immediately obvious.
Kasper Langmann, Co-founder of Spreadsheeto To solve this:
Select the values in the Pivot Table, right-click and select 'Format Cells'.
In the resulting window, select 'Currency' from the 'Category' box.
Then make your choice for which format you want in the 'Negative numbers' box.
Select 2 in the 'Decimal places' combo box and click OK.
Now you have your totals in currency format to 2 decimal places.
It is now easy for the user to see the type of data being displayed.
The decimal places have all been set to a uniform figure.
Removing blanks
If there are gaps in the source data, these will be detected by the Pivot Table and identified as (blanks).
Following the 'Filtering' sections of this guide, you have come across these already.
To remove the blanks from an active filter, uncheck the box next to it from the filter menu.
Blank values don't affect anything calculable within the Pivot Table. But, they damage the look and feel of it. Blank values make it more difficult to view the data. This completely defies the point of using a Pivot Table in the first place.
Kasper Langmann, Co-founder of Spreadsheeto It is worth noting that the same also applies to any 'Slicers' that are linked to the Pivot Table.
Simply deselect the entry for '(blank)' and they are removed from the Pivot Table.
Change name of Row Labels
Sometimes the headers in the source data don't describe the nature of what the data is in the correct context.
By default, this header is what is pulled through for each 'field' name and is displayed on the Pivot Table.
As a result, you might need to change this name to better reflect the nature of the data presented.
Consider the following:
What does 'Sum of Total' mean?
In the context of the original source data, this was more obvious based on the columns that surrounded it.
But, now it has been extracted for a more streamlined view in a Pivot Table.
So, it is no longer as obvious.
Something like 'Total Turnover' or 'Total Sales' is more appropriate in this situation.
To apply this change, we need to access the 'Field Settings' for this field.
Kasper Langmann, Co-founder of Spreadsheeto Select the cell containing 'Sum of Total' and navigate to the 'Options' tab in the Excel Ribbon.
Under the 'Active Field' group, you will find 'Field Settings'.
Now open the 'Field Settings' menu.
Set the value of 'Custom Name' within this dialogue.
Selecting 'OK' will update the field name on the Pivot Table:
The label is now more representative of the data being displayed and more obvious to whoever views it.
Pivot Charts
Pivot Charts visualize your Pivot Table in an instant.
Topics:
- What exactly is a Pivot Chart?
- Creating a Pivot Chart
- Using the Pivot Chart
What exactly is a Pivot Chart?
The relationship of Pivot Charts to Pivot Tables is like normal Excel charts to their source data.
"Pivot" simply indicates the extra functionality granted in Pivot Tables.
This becomes available for use in its graphical counterpart – the Pivot Chart.
In short, a Pivot Chart takes the data contained in the Pivot Table and outputs it in a graphical medium.
Kasper Langmann, Co-founder of Spreadsheeto This is done by using one of Excel's built-in chart types.
The most common types are:
- Bar Charts
- Line Graphs
- Pie Charts
In this example, we use a "Pie Chart".
Creating a Pivot Chart
Select the Pivot Table.
Navigate to the 'Insert' tab on the Excel Ribbon and select the desired chart from the 'Charts' group:
Excel offers a wide array of choices for each type of graph available.
For the sake of illustration, a simple '2D Pie Chart' will suffice.
Click it to insert it into the active sheet:
Using the Pivot Chart
The Pivot Chart is manipulated in much the same way as its corresponding Pivot Table.
In the 'field list' on the right, the 'Areas' section at the bottom changes.
This happens to better represent what can be modified on your Pivot Chart.
Notice that 'Row Labels' has changed to 'Axis Fields'.
'Column Labels' has changed to 'Legend Fields'.
These are the corresponding areas on graphs of all kinds, hence the use of 'Axis' even on a Pie Chart. The Pivot Table information will be pulled through these areas.
Kasper Langmann, Co-founder of Spreadsheeto With the set up on the left using the data from the 'Sales Data Raw' (Price change) worksheet, a pie chart' is displayed.
This pie chart shows the 'Total Sales' in its values, split into categories defined by Item type.
As pie charts are 2-dimensional, the equivalent 'Columns' entry (now a 'Legend Field') cannot be displayed.
Effectively it acts as a filter, allowing the pie charts to be cycled through each entry in 'Month'.
Any field entered into the areas 'Legend Field', 'Axis Field' or 'Report Filter' displays a button on the Pivot Chart…
Kasper Langmann, Co-founder of Spreadsheeto 
In the previous illustration, the month as a 'Legend Field' can be manipulated.
For instance, to present single months or multiple months (in the case of a pie chart, acting as a filter).
If this was a bar chart, these would be plotted as separate entries on the same graph as opposed to swapping with the data currently present.
The most important point to note is: The Pivot Chart has fantastic abilities to filter and represent the data. Just like the Pivot Table achieves the same.
Kasper Langmann, Co-founder of Spreadsheeto Troubleshooting your Pivot Table
Pivot Table won't sort
There can be many different causes of sorting issues.
Let's look at an example of a Pivot Table we have set up with the month as our rows.
We want the 'Month' values to sort naturally the way they occur sequentially through the year.
But, something is wrong with our source data so our values are sorting alphabetically.
Notice there are duplicate values for 'Month' in the filter list.
This is a huge tip-off on what is likely causing our issue.
Can you guess what it might be?
This is not what we want from our sorting.
We need to see the 'Month' values sorted in the order they fall in the year.
We need to check our source data!
One of the most common causes of this is trailing spaces. These are not visible to the naked eye. So, we need to ensure that our 'Month' values do not have extra spaces by using the trim function.
Kasper Langmann, Co-founder of Spreadsheeto 
Simply add a column next to 'Month' and type in the 'TRIM' function as shown in the previous figure.
Copy and paste all the way down.
Then Copy the entire column less the header and paste as values in the 'Month' column.
Also, delete the 'TRIM' column.
Everything looks just like it did before, but now we know that our 'Month' values contain no extra spaces. This has cleaned our values and changed how Excel will now detect them.
Kasper Langmann, Co-founder of Spreadsheeto Upon refresh of the Pivot Table, the 'Month' rows now sort naturally in sequential order.
Awesome, right?!
Pivot Table field name is not valid
Sometimes your source data may have a missing column header.
This could be due to a blank column mixed in with the source data.
It could also be a column with data that just has a blank header like the following:
Note columns F and H: This is one example of a completely blank column and the other with a missing header for a column of data.
If you attempt to create a Pivot Table with this data set, Excel will return the following error:
To solve this issue, you need to remove blank columns.
Then you (manually) need to type in any missing headers for data columns:
Be aware that this doesn't only effect Pivot Table creation!
Kasper Langmann, Co-founder of Spreadsheeto Let's say this source data is changed after a Pivot Table is created and a header is removed.
When the Pivot Table undergoes an update/refresh the same error will be encountered (the same solution applies).
Pivot Table field name already exists
When forming the source data in Excel there is no validation against two or more columns having the same designated header.
That is unless it is explicitly formatted as a 'List Object' (such as the built-in 'Table' functionality).
When it comes to pulling this data into a Pivot Table, any 'fields' formed must have unique names (as it too is a 'List Object' like a normal 'Table').
If Excel comes across several columns with the same header, it tries to handle it. This is done by suffixing an incremental number to the 'field' name. This happens correspondingly to the left-to-right order the columns are found in the data.
Kasper Langmann, Co-founder of Spreadsheeto You might run into this kind of issue where the 'Month' column header is actually a duplicate 'Date' header.
When you go to create your Pivot Table, you will notice that Excel has added a '2' on the end of the second 'Date' header.
Recall from the earlier section about renaming Pivot Table 'fields'?
Here, I showed how you can easily change this in your Pivot Table itself.
Just keep in mind that this does not change the header in the source data. For 'value fields' Excel will not allow the reuse of 'field' names that already exist in the source data.
Kasper Langmann, Co-founder of Spreadsheeto Where's the Pivot Table wizard?
In Excel 2016, the Pivot Table wizard is not shown in the Ribbon by default.
To add it to your view, navigate to 'File' -> 'Options' as in the next figure.
From here select the sub menu 'Customize Ribbon'.
Select 'Commands Not in the Ribbon' in the drop-down menu for 'Choose Commands' from:
Add this to whichever Ribbon entry you desire. It makes the most sense to place it on 'Insert' where the Pivot Table 'Insert' button is.
Kasper Langmann, Co-founder of Spreadsheeto Activate the Pivot Table / Chart wizard with this shortcut
Alternatively, the Pivot Table / Chart wizard is directly accessible using the following keyboard shortcut:
Alt + D, then P
Either option will offer the same outcome.
Is the 'Field list' missing?
The 'Field List' is the display on the right-hand side of the Excel window when a Pivot Table is selected.
It contains all the available 'fields' from the source data used at the top.
The display gives four areas where fields can be dragged.
These are:
- Filters
- Columns
- Rows
- Values
The 'Field List' is hidden by default until you select the Pivot Table. Can't find it? Then your first step is to make sure that the cell selected on the active worksheet belongs to the Pivot Table.
Kasper Langmann, Co-founder of Spreadsheeto If the list is still invisible after this, it is likely it has been manually hidden.
To unhide, navigate to the 'Show' group on the 'Analyze' tab and click on 'Field List'.
Alternatively, you can also go to the 'Show' group on the 'Analyze' tab and see that 'Field List' is unhighlighted.
Simply click to highlight.
Your 'Field List' should now appear in its usual place on the right-hand side of the worksheet.
So, you can hide the Field List with the same procedure. Just click it in the ribbon to return it to its unhighlighted and hidden state.
Kasper Langmann, Co-founder of Spreadsheeto 
Pivot Table not updating/refreshing?
You may find that when you add source data to your Pivot Table, that the Pivot Table doesn't update when you refresh.
An often-overlooked detail when adding new data to your source is changing your data source.
You must 'Change Data Source' to ensure that your Pivot Table data range includes any new data. You do this by clicking into your Pivot Table to make sure the 'Options' tab is visible in the Ribbon. Then simply click on 'Change Data Source' in the 'Data' group.
Kasper Langmann, Co-founder of Spreadsheeto The 'Change PivotTable Data Source' dialog appears showing the current selected table or range.
The best way to update all the way to the last row of your data (assuming there were no columns added) is to press:
Ctrl + Shift + the down arrow.
Does the table/range box indicate that entire columns are selected? Then press Ctrl + Shift + the up arrow to bring the bottom of the range to the last row of data.
Kasper Langmann, Co-founder of Spreadsheeto Now you simply need to click 'OK' and then click 'Refresh' once again.
This should now update your Pivot Table to include the newly added data.
Pivot Table cannot group that selection
When grouping 'fields' together on your Pivot Table, you might run into the"Cannot Group that Data" error.
This is likely due to one of two causes.
1: At least one of the 'fields' contains data formatted as different types (e.g. numerical and text within the same 'field').
Excel must apply the same grouping logic to all entries in the 'field'. This is obviously not possible if they are of different types.
2: At least one of the 'fields' contains blank cells (gaps in the data).
Excel cannot handle an empty value when trying to group with another 'field' that has no empty values.
The error appears as a dialogue when such an operation is attempted:
Does point 1 apply to your data set?
Then go back to the source data and ensure the column in question is all formatted as the same type (e.g. 'Currency' for price/money entries, 'Text' for textual entries etc.).
If point 2 applies, it is rarely an acceptable solution to add data into the blanks to make grouping work. This data has no true meaning and is false.
The question instead is whether this column makes sense to be grouped with another. Perhaps the answer is no and a different approach should be undertaken.
How do I display text as a value field?
A Pivot Table can only summarize data that are numerical.
If you want to summarize text data, you must use another tool or use a workaround.
This great guide by Chandoo tells you how to deal with summarizing text data as a 'Value Field'.
Try these free assignments
By practicing what you learn, you remember everything much better.
Additionally, you will be able to apply what you've learned to a broader range of real life scenarios.
I've made it easy for you to practice what you've learned in this tutorial.
Kasper Langmann, Co-founder of Spreadsheeto FREE Assignment workbook
Download this assignment workbook and start practicing using pivot tables.
Wrapping up
As you've just learned, Pivot Tables is one of the most powerful tools in Excel.
Why? Because no other tool in Excel has this much impact on your daily work.
Reports that normally would take hours to complete you can now finish in minutes.
Kasper Langmann, Co-founder of Spreadsheeto Does your boss want you to change the report right before a meeting?
No problem! Just a few clicks and it's ready.
Excel Table Tools Design Tab Missing
Source: https://spreadsheeto.com/pivot-tables/
Posted by: walkeriiii1981.blogspot.com
0 Response to "Excel Table Tools Design Tab Missing"
Post a Comment