How to Do a Pivot Table in Excel

At first it is better to introduce Pivot table and answer to this question;

What is Pivot table?

Pivot table is a special excel tool that allows you to summarize and explore data interactively.

By using Pivot table you can quickly transform huge number of rows and columns into meaningful formatted report.

What You Should Do Before Insert a Pivot Table;

Before start to create your Pivot table you should make sure that your data is organized properly. For that you should check these four steps;

1- Make sure each column has a header in the first row. because these labels will appear in the field list after you create your pivot table.

2- You should not have any empty columns in the middle of the table. If you have any empty columns in your data set remove them.

3- You should not have any empty rows in the middle of the data set. If you have any empty rows in your data set remove them.

4- You should not have all total values in the middle of your data set.

It is also better to create an official excel table before creating pivot table, the advantage of this work is whenever you add new data into your data set your pivot table will automatically reflect that.

How to Create an Official Excel Table;

1- Click on Insert tab.

2- In the Table part select Table.

3- Select the range of your table.

4- Now you have your data set in the official table.

How to Create a Pivot Table in Excel;

1- Click on the Insert tab.

2- In the table part click on the Pivot table.

3- In the “Create Pivot Table” dialoge Click on the “Select a table or range” and then click on the Arrow.

you can see a moving dashed line in the background around the full set of data, this is the area which excel guest for your pivot table range, you can alter it if you need by clicking on the arrow in the select table range pivot.

4- After you select your desired rang click on the arrow again to come back “Create Pivot Table” dialoge,

5- Now you should define for the program where do you want to place your pivot table, For that you should follow these steps;

• If you choose “New Worksheet” the program will place your pivot table in the sheet which will be created by itself.

• If you choose “Existing Worksheet”, you can specify the sheet which you want to place your pivot table on it.

• select the arrow.

• choose your desired sheet.

• select the cell which you want to start your table from it.

• Select the arrow.

• Click on Ok.

Now you have new worksheet into the workbook.

An empty place holder for the pivot table in left side of the window.

A Field list pane in the right side of the window.

you can change the arrangment of the field list if you need.

by that you should click on the setting key in the upright side of the field list.

select your desired arrangement.

How to Use Recommended Pivot Table Command in Excel;

This command will help you to acquire what you are looking for more easily and faster.

For using this command you should follow these steps;

1- Click on the “Analyze” tab.

2- In the Tools rebon, click on the “Recommended Pivot Tables”.

3- Choose your desired report.

4- Click on Ok.

How to Add a Field in Pivot Table Manually;

In the Pivot table Field we have two parts;

Field section.

In the “Feild” section you can see all the column labels which you have in your original table and that is the reason that each column in original table must have header.

Area sections.

In the area section we have four different areaes.

• Filter Area;

If you put fields in Filter area you can have comprehensive reports from that field in your original table.

Here I am going to add Branch field to the filter part, For that;

1- You should select it and drag it to the filter area.

2- Click the filter arrow in the pivot table.

3- Tick “Select multiple Items” box.

4- Choose your desired city and click Ok.

• Row Area;

By default excel adds text fields to the Row labels area. If you put each field in the row area you can see only unique names in the rows, for instance if your table have 1000 rows and you have only five unique values in your table you will see only 5 rows in your pivot table and if you have 100 unique items in your table you will 100 rows in your pivot table.

• Columns Area;

Here I want to show Sum of Sales for each city in different columns, For that

1- Select Branch.

2- Drag it to the Column area.

Now you can see the values of each city in one column.

• Values Area;

In this part only numeric fields make sense. By default excel adds numeric field to the Value areaes.

How to Add Feilds in Pivot Table;

For adding a field to the pivot table you can just tick the check boxes or drag that item from the field list to that area.

How to Remove Feilds in Pivot Table;

In the Field section uncheck the field which you want to remove from the pivot table.

How Clear Pivot Table;

1- Click on the Analyze tab.

2- Click on the Action Key.

3- Click on the Clear.

4- Click on the Clear all.

5- Select Clear pivot table option.

How to Change Aggregation in Pivot Table to Average or Count;

If you want to change the aggregation in pivot table you should follow these steps;

For changing that you can follow two ways, at first lets change sum value to average value with first method;

1- In the Values area click on the arrow.

2- Click on the Value Field Settings.

3- In the Summarize Value By Field part choose Average.

4- Click on the Ok.

For introducing second method we will explain How to change Sum value to Count value.

1- Right Click on your desird column in Pivot table.

2- Click on the Value Field Settings.

3- In the Summarize Value By Field part choose Count.

4- Click Ok.

Now you can see Sum of Quantity change to Count of Quantity.

How to Change Number Format in Pivot Table;

Here I am going to explain this part by two examples;

How to add curency symbol to the column’s values;

1- Right click on the column.

2- Choose “Number Format”.

3- Choose Currency.

4- You can change “Decimals place” here we modified it to zero and also you can choose your desired currency.

5- Click Ok.

How to add 1000 Seperator to the Numbers;

1- Right click on the column.

2- Choose Number Format.

3- Choose Number in the Categories.

4- You can change “Decimals place” here we modified it to zero.

5- Tick Use 1000 seperator.

6- Click on Ok.

How to Change Pivot Table Layout;

In the Layout ribon we have four items, here we are going to explain them;

How to Change Report Layout;

we have three different options for report layout;

Compact Form

This option is the default setting for Report layout, but this option does not show field name .

you can also have subtotal on the top and bottom of the each sections.

1- Click on the table.

2- Click on the Design tab.

3- Click on the Report Layout.

4- Click on the “Show in Compact Form”.

In the compact Form, you can have the subtotals on the top or bottom of the groups.

• Click on the Subtotals.

• If you want to have subtotals on the bottom of the group, choose “Show all Subtotals at the Bottom of Group”.

• If you want to have subtotals on the top of the group, choose “Show all Subtotals at the Top of Group”.

Outline Form;

In this option you can have field name in your pivot table and also you can have subtotal on the top or bottom of the each groups.

1- Click on the table.

2- Click on the Design tab.

3- Click on the Report Layout.

4- Click on the “Show in Outline Form”.

In the Outline Form, you can have the subtotals on the top or bottom of the groups.

• Click on the Subtotals;

• Select the “Show all Subtotals at the Bottom of Group” if you want to have subtotals in the bottom of the groups.

• Click on the Subtotals;

• Select the “Show all Subtotals at the Top of Group”. if you want to have subtotals in the top of the groups.

Tabular Form;

Tabular form and outline form are similare together but they have only two difference

• you can see grid line in Tabular form.

• you can only have subtotals on the buttom of the groups, not on the top of the groups.

for having Tabular form we should follow these steps;

1- Click on the table.

2- Click on the Design tab.

3- Click on the Report Layout.

4- Click on the “Show in Tabular Form”.

5- Click on the Subtotals.

6- In this Form you can have only subtotals on the buttom of the groups, so for having subtotals you can only select “Show all Subtotals at the Bottom of Group”.

How to Remove Subtotals in Pivot Table;

For removing subtotals in pivot table you should follow these steps;

1- Click on the table.

2- Click on the Design tab.

3- Click on the Subtotals.

4- Select “Do Not Show Subtotals”.

Here you can see how we remove subtotals in three different report layouts.

How to Repeat all Items Labels in the Report Layout;

For showing all items labels in the report layout you should follow these steps;

1- Select the Pivot table.

2- Select Design tab.

3- Click on the Report Layout.

4- Select “Repeat All Item Labels”.

For removing all items labels in the report layout you should follow these steps;

1- Select the Pivot table.

2- Select Design tab.

3- Click on the Report Layout.

4- Select “Do Not Repeat Item Labels”.

How to Add or Remove Grand Totals in Pivot Table;

1- Select the table.

2- Click on the Design tab.

3- Click on the Grand Totals.

If you want to have Grand totals in the rows and columns you should select;

“On for Rows and Columns”.

If you want to add grand totals on rows you should follow these steps;

1- Select the table.

2- Click on the Design tab.

3- Click on the Grand Totals.

4- “On for Rows Only”.

If you want to add grand totals on columns, you should follow these steps;

1- Select the table.

2- Click on the Design tab.

3- Click on the Grand Totals.

4- “On for Columns Only”.

For removeing Grand totals you should follow these steps;

1- Select the table.

2- Click on the Design tab.

3- Click on the Grand Totals.

4- “Off for Rows and Columns”.

How to Add or Remove Blank Rows in Pivot Table;

If you want to add blank row after each items in the pivot table you should follow these steps;

1- Select the table.

2- Click on the Design tab.

3- Click on the Blank Rows.

4- Choose “Insert Blank Line after Each Item”.

For removing blank row after each item you should follow these steps;

1- Select the table.

2- Click on the Design tab.

3- Click on the Blank Rows.

4- Choose “Remove Blank Line after Each Item”.

How to Add or Remove +/- Buttons in Pivot Table;

In the left side of the each items you can see +/- buttons which give you this opportunity to collapse or expand them.

For removing these buttons you can follow these steps;

1- Select the table.

2- Click on the Analyze tab.

3- In the Show part select on the “+/- buttons” button to turn off these buttons.

For adding these buttons you can follow these steps;

1- Select the table.

2- Click on the Analyze tab.

3- In the Show part select on the “+/- buttons” button to turn on these buttons.

How to Add or Remove Field List in Pivot Table;

If you Click on the pivot table field list will be appear and if you click on the out side of pivot table it will be disappear.

But sometimes you close pivot table by clicking on the close button on the up right corner of the field list after that if you click on the pivot table it will not reappear again, for solving this problem you can follow these steps;

1- Select the table.

2- Click on the Analyze tab.

3- In the Show part click on the Field List.

You can also add field list by following these steps too;

1- Right Click on the pivot table.

2- Select the “Show Field list”.

How to Add or Remove Field Header in Pivot Table;

For removing field header in pivot table you can follow these steps;

1- Select the table.

2- Click on Analyze tab.

3- In the Show part click on the “Field Headers” to turn off field header on the ribon.

For adding field header in pivot table you can follow these steps;

1- Select the table.

2- Click on Analyze tab.

3- In the Show part click on the “Field Headers” to turn on field header on the ribon.

How to Change Pivot Table Style

If you need, you can change the style of pivot table, for that you can follow these steps;

1- Click on the design tab.

In the PivotTable Styles part you can choose your desired style for your Pivottable and you can vreate your desired style if you want.

In the PivotTable Styles Options part you can change other styles like Row or column headers.

• Select Row Headers box if you want to highlight the header of the Rows.

• Select Column Headers box if you want to highlight the header of the columns.

• You can specify the boundary of each row by selecting “Banded Rows” Box.

• You can specify the boundary of each column by selecting “Banded Columns” Box.

How to Sort Values in Pivot Table;

For sorting values you should follow these steps;

1- Right-click on the table.

2- In the Sort section you can choose your desired option.

How to Add Percentage in Pivot Table;

Here we are going to add percentage of grand sum in the table for that you should follow these steps;

1- In the Field section select Sum of sell and drag it to the value part.

2- Change the name.

3- Right-click on the column and click on the show value As;

Here you can choose different commands here because we wanted to calculate the Grand total percentage we chose “% of Grand Total”.

If you want to know more information aboutAdding a Calculated Field in Pivot Table; you can follow this page.

How to Drill Down to See the Data Behind Any Value Inside Pivot Table;

If you want to see the detailed data behind any value inside the pivot table you can easily double-click on your desired value or

1- Right-click on that cell.

2- choose “Show Details”.

In the new sheet you can see the detailed data about it.

How to Delete Your Source Data;

If you want you can delete your source data after creating your pivot table.

For that you can easily Right-click on the sheet tab and select delete option.

How to Recover Source Data;

If you want to have your source data again you can

1- right-click on the Grand total.

2- choose “Show Details”.

How to Replace Blank Cells in Pivot Table

If you have empty cells in your pivot table and you want to replace them with the specific value you can follow these steps;

1- Right-click on the cell.

2- Choose “Pivot Table Options”.

3- Click on the “Layout & Format” tab.

4- In the Format part click on the “For empty cells show” box and write your desired text.

5- Click Ok.

How to Show Items With No Data in Pivot Table;

1- Right-click on the cell

2- Choose “Field Settings”.

3- In the “Layout & Print” tab, check “Show items with no data” box.

4- Click Ok.

How to Group Text Fields in Pivot Table;

you can group text field by following these steps;

1- select your desired items which you want to place them in the same group.

2- Right-click on them and select Group.

3- Do the same things for other items.

4- Click on the Group header on the pivot table and modify the name of them if you want.

5- On the right side click on the new added item and modify the name of it too if you want.

for that

• Click on the new item.

• Choose “Field Settings”.

• In the Field Setting Dialoge in the custom Name write your desired name.

• Click on Ok.

For more information about how to group in pivot table you can follow this page.

How to Automatically Refresh Data in Pivot Table;

1- Select the table.

2- Right-click on the table.

3- Select “PivotTable Option”.

4- Click on the “Data” tab.

5- Select the “Refresh data when opening the file” box.

6- Click Ok.

How to Automatically Create a Pivot Table for each Item in a Filter;

1- Select the table.

2- Select the field which you want to create a pivot table for their items in the filter area.

3- Select Analyze tab.

4- Click on the Pivot Table.

5- Click on the ARROW near the Options.

6- Select “Show Report Filter Pages…”.

7- Choose your desired Group.

8- Click Ok.

Now you can see near the old sheets some new sheets were created and in the each sheet there is a pivot table report about the items which you have in your group.

How to Turn Off GETPIVOTDATA in Pivot Table;

If you click on the cells which they have a numeric value you can see each cell have formula on it, for turning off this formula you can follow these steps;

1- select the pivot table.

2- select Analyze tab.

3- Click on the PivotTable.

4- Click on the ARROW near Option.

5- Unselect “Generate GetPivotData”.

How to Disable Autofit in Pivot Table;

Sometimes you have a pivot table and you manually change the size of the column width or other properties of that but after each refresh, everything is changed. for solving this problem you should follow these steps;

1- Right-click on the table.

2- Choose “PivotTable Options…”.

3- In the “Layout&Format” tab, select “Autofit column widths on update” box.

4- Click Ok.

5- Now you can see after refreshing your PivotTable, the shape of the PivotTable will not be changed.

How to Add Slicer in Pivot Table;

If you want to see the value of each region or each part of your data set, you can use the filter in the PivotTable, or adding slicer in the PivotTable.

Here we are going to explain how you can add slicer in the PivotTable.

1- Click on the PivotTable.

2- In the right side, we want to create a slicer in the area part, for that we deselect Area part.

3- Right-click on the Area part and choose Add a slicer.

4- you can see a new table was created near you pivot table and in this table, you have the name of each region on it.

If you click on each of them you can see the information about that region.

5- If you want to see the data about different regions at the same time, you can hold Ctrl and select your desired regions or select the key near the Area part on the slicer and then select your desired part.

For changing the appearance of the slicer

1- you can select the slicer tools.

2- In the Buttons part, you can define the number of columns and the size of the buttons.

3- In the Size part, you can modify the size of the slicer table.

4- In the Slicer Styles, you can change the colour and other properties of the slicer table.