Category: Excel Pivot Table

How to Change Multiple Pivot Table Fields in Excel

This post will guide you how to change the format of all pivot table fields at once in Excel. How do I change multiple pivot table fields from the default sum of function to the count of function at once with VBA in Excel.

Assuming that you have created a pivot table with your data. And as the default setting for numbers is to sum the data for all numeric fields and I want to change the count of function to sum of function. You can change each of columns using the value field setting option or you can also use an VBA macro to change all fields at once.

1. Change Multiple Pivot Table Fields by Manually

If you want to change multiple pivot table fields, you can change the function in the Value Fields Settings, just do the following steps:

Step1: select one filed in your pivot table, and right click on it, and then choose Value Fields Settings from the dropdown menu list. And the Value Fields Settings dialog will open.

change multiple pivot table fields1

Step2: select Count function in the Summarize value field by list box, and click Ok button.

change multiple pivot table fields2

Step3: you would see that the sum function has been changed to count function for your selected field.

change multiple pivot table fields3

Step4: you need to repeat the above steps to change other’s fields for your pivot table.

2. Change Multiple Pivot Table Fields by VBA

You can also convert all the fields in a selected pivot table to the count function or other functions with VBA Macro in Excel. Just do the following steps to run the macro:

Step1: select your pivot table that you want to convert fields

Step2: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1
Step3: then the “Visual Basic Editor” window will appear.

Step4: click “Insert” ->”Module” to create a new module.

export each sheet to csv2

Step5: paste the below VBA code  into the code window. Then clicking “Save” button.

change multiple pivot table fields4
Public Sub PivotFieldsToCount()
    Dim pf As PivotField
    With Selection.PivotTable
        .ManualUpdate = True
        For Each pf In .DataFields
            With pf
                .Function = xlCount
                .NumberFormat = "#,##0"
            End With
        Next pf
         .ManualUpdate = False
     End With
 End Sub

Step6: back to the current worksheet, then run the above excel macro. Click Run button.

change multiple pivot table fields5

Step7: Let’s see the result:

change multiple pivot table fields6

How to Hide Zero Values in Pivot Table in Excel

This post will guide you how to hide zero values in a pivot table in Excel. How do I remove rows with zero values in your pivot table. How to hide rows with zero values in pivot table in Excel 2013/2016.

If you are working with large data sets, you may have noticed that your pivot table displays zero values in empty cells. These zero values can clutter your data and make it harder to read and interpret.

 Fortunately, there are a few simple steps you can take to hide zero values in pivot tables. One way is to create a filter field that excludes zero values, or you can format cells to display blank instead of zero. Additionally, you can use the Filter function to remove zero values from your pivot table data.

1. Hide Zero Values in Pivot Table by Filter Field

If you have created a pivot table based on your data in your current worksheet, and you need to hide the zero values row in your pivot table. You just need to do the following steps to achieve the result.

Step1: select the pivot table in your worksheet, and the PivotTable Fields pane will appear.

hide zero values in pivottable1

Step2: drag fields which you want to filter or hide zero values from the Choose fields to add to report section to FILTERS section in PivotTable Fields pane.

hide zero values in pivottable2

Step3: click the drop down arrow of the field, and check Select Multiple Items, and uncheck 0 value. Click OK button.

hide zero values in pivottable3

Step4: all zero values in pivot table have been hidden.

hide zero values in pivottable4

2. Hide Zero Values in Pivot Table Using Format Cells Feature

If you want to hide pivot table zero values in Excel, you can use the Format Cells command. This method will not only hide the zero values but also keep the actual values available for calculation purposes. Here are the steps to follow:

Step1: Select the pivot table cells that contain zero values.

How to Hide Zero Values in Pivot Table in Excel 10.png

Step2: Right-click and select “Format Cells” from the context menu.

How to Hide Zero Values in Pivot Table in Excel 11.png

Step3: In the “Format Cells” dialog box, go to the “Number” tab. Under “Category” select “Custom“. In the “Type” field, enter three semicolons 0;-0;;@ .

How to Hide Zero Values in Pivot Table in Excel 12.png

Step4: Click “OK” to apply the custom format.

How to Hide Zero Values in Pivot Table in Excel 13.png

Now your pivot table will show blank cells instead of zero values, making it easier to read and interpret your data.

3. Hide Zero Values in Pivot Table Using Filter Function

If you want to hide zero value rows in pivot tables in Excel, you can use the Filter function. This method will not delete the rows containing zero values but instead temporarily hide them, making it easier to focus on the relevant data. Here are the steps to follow:

Step1: right click any row content inside the pivot table.

Step2: In the dropdown list, select “Filter” option. And select “Value Filters”.

How to Hide Zero Values in Pivot Table in Excel 14.png

Step3: In the “Value Filter” dialog box, select the type of filter you want to apply, such as “Equals” , “does not equal”, “is greater than,” “is less than”, etc.

How to Hide Zero Values in Pivot Table in Excel 15.png

For example, if you want to hide all rows that have a value of zero, you can select “does not equal”, and enter “0” as the filter criteria.

How to Hide Zero Values in Pivot Table in Excel 16.png

Step4: Click “OK” to apply the filter.

How to Hide Zero Values in Pivot Table in Excel 17.png

This method will hide all the rows that have zero values in the selected field, and you can quickly unhide them again by checking the box next to “0”.

4. Video: Hide Zero Values in Pivot Table

This video will demonstrate how to hide zero values in pivot tables in Excel using three methods: creating a filter field, formatting cells, and using the Filter function.

How to Repeat Row Labels in Pivot Table

This post will guide you how to repeat row labels in same row in your Pivot table in Excel. How do I repeat all row labels in your pivot table in Excel 2013/2016. How to repeat item labels in a PivotTable.

1. Video: Repeat Row Labels in Pivot Table

This video tutorial provides a step-by-step guide on how to repeat row labels in a pivot table, making it easier to analyze and interpret data.

2. Repeat Item Labels in Pivot Table

Assuming that you have a pivot table in your current worksheet, and you want to repeat all row labels in the same line to make data easier to read. And the below steps will show you how to do it:

Step1: select one cell in your current pivot table.

repeat row lables in pivot table1

Step2: go to Design tab in the Excel Ribbon, and click Report Layout command under Layout group, and select Show in Outline Form from the drop down menu list.

repeat row lables in pivot table2

Step3: you would see that all selected fields are displayed in the current Pivot table.

repeat row lables in pivot table3

Step4: then select Repeat All Item labels under Report Layout drop down list.

repeat row lables in pivot table4

Step5: you would see that all row labels have been repeated for all selected fields.

repeat row lables in pivot table5

3. Repeat Row labels in Pivot Table

If you want only repeat row labels for a specified field in your Pivot table, you can do the following steps:

Step1: select one cell in your current pivot table.

repeat row lables in pivot table1

Step2: go to Design tab in the Excel Ribbon, and click Report Layout command under Layout group, and select Show in Outline Form from the drop down menu list.

repeat row lables in pivot table2

Step3: you would see that all selected fields are displayed in the current Pivot table.

repeat row lables in pivot table3

Step4: right click on “Product” header and select Field Settings, and the Field Settings dialog box will appear.

repeat row lables in pivot table6

Step5: switch to “Layout & Print” tab in the Field Settings dialog box, and then check the Repeat item labels box, and also select Show item labels in tabular form option. Click Ok button.

repeat row lables in pivot table7

Step6: you would see that the row labels have been repeated for that specified field.

repeat row lables in pivot table8

4. Conclusion

Repeating row labels in a pivot table can greatly enhance its readability and ease of use. You can quickly and easily configure their pivot tables to display repeated row labels, making it easier to analyze and interpret their data.

How to Group Dates or A Period by Month/Quarter/Year in Pivot Table?

A pivot table is a table format that can dynamically arrange data and do category summarize strongly. It is commonly used in our daily life for creating summary report. It provides various combinations for row/column/values. Normally we can get the proper table format as we expect after setting group for these fields. As date or a period is an important parameter in summary report, in this article, we will introduce you how to group date (or a period of dates) by month/quarter/year in pivot table. This will help you to arrange your table group by different time settings flexible.

Initial Condition:

#1 Prepare a table with Product, Sales and Date.

Group Dates or A Period by MonthQuarterYear 1

#2 Then create pivot table for this table, then we can analysis data conveniently. Select range A1:C16, then click Insert->PivotTable, choose existing worksheet, locate pivot table in E1, then click OK.

Group Dates or A Period by MonthQuarterYear 2

#3 Verify that after clicking OK, PivotTable Fields settings pops up. Check on Product, Sales ($), Date in left control panel, and move Date to the top in Rows.

Group Dates or A Period by MonthQuarterYear 3

#4 Verify that pivot table is created properly. You can find that sales for all products are grouped by Year in this table.

Group Dates or A Period by MonthQuarterYear 4

Group Date or A Period by Month/Quarter/Year in Pivot Table

In above case, we know that date is grouped by Year by default. So, we just need to know how can we change Year to Month or others in pivot table.

Step 1: Select Year 2019 in pivot table, then right click to load menu, select Group.

Group Dates or A Period by MonthQuarterYear 5

Step 2: In Grouping window, uncheck Years (which is selected by default), then select Months, then click OK.

Group Dates or A Period by MonthQuarterYear 6

Step 3: Verify that date is grouped by Months properly. Due to length limit, only part of table is displayed in below screenshot.

Group Dates or A Period by MonthQuarterYear 7

Step 4: Repeat step#2, this time you can check on both Year and Month to see the difference.

Group Dates or A Period by MonthQuarterYear 8

Step 5: Click OK on above screenshot. Verify that this time, Year is added properly.

Group Dates or A Period by MonthQuarterYear 9

You can click ‘-‘ before 2019 to shrink months. You can also click ‘+’ to spread all months.

Group Dates or A Period by MonthQuarterYear 10

Step 6: Repeat step#2 to enter Grouping again. This time select Quarters.

Group Dates or A Period by MonthQuarterYear 11

Step 7: Click OK. Verify that date is grouped by quarters properly.

Group Dates or A Period by MonthQuarterYear 12

Comment:

If you just want to group a period of dates by months/quarters/year and others are not grouped, you can set start date and end date in Grouping. For example, enter 7/10/2019 as Starting at value, 10/10/2019 as Ending at value.

Group Dates or A Period by MonthQuarterYear 13

Click OK. Verify that only this period is grouped by months.

Group Dates or A Period by MonthQuarterYear 14

 

How to Count Data Total Numbers in Groups by Excel?

In daily work, we often need to count the total number for something in different groups, for example count products sales in different seasons. When these products are saved in different groups, do we have a convenient way to do statistic? This article will show you a simple and intuitive way to count data in multiple groups by PivotTable in Excel.

First as we usually do, prepare a list or table for Product and Sales. See below:

Count Data Total Numbers 1

This is a quite simple table for statistic sales. We can see there are only three products. The sales are different on different dates. So how can we count the total sales for the two days? Let’s get started.

Count Data for Multiple Groups by A Pivot Table

Step 1: First, we need to insert a Pivot Table.

Click on Insert tab, PivotTable is displayed in the first place of Insert functions.

Count Data Total Numbers 2

Step 2: Click on PivotTable, now Create PivotTable is loaded.

Count Data Total Numbers 3

Step 3: In ‘Choose the data that you want to analyze’, check on ‘Select a table or range’, refer to the screenshot in step#1, we know the data range is from A1 to C7, so in ‘Table/Range’ field, we just select the range by mouse. After releasing the mouse, the area is automatically displayed in the textbox.

Count Data Total Numbers 4

Step 4: In ‘Choose where to place the PivotTable’, check on ‘New worksheet’ or ‘Existing worksheet’ depends on your requirement. In this case, for comparing with original table, we check on ‘Existing worksheet’ to make original table and PivotTable in the same worksheet. We insert PivotTable into cell A15 for example.

Count Data Total Numbers 5

Step 5: Click on OK. Now PivotTable Builder is loaded.

Count Data Total Numbers 6

Step 6: As we want to count the sales for products, so we drag Product to Rows field, Sales to Values field.

Count Data Total Numbers 7

Step  7: Then a new table is displayed. We can see the total sales for each product via the new table.

Count Data Total Numbers 8

Step  8: Obviously, we can edit PivotTable Builder and update table with more conditions. For example, we can drag Sales to Column field.

Count Data Total Numbers 9

Guess what table can we get? See below. We can get more details from below table.

Count Data Total Numbers 10

Above all, you can adjust PivotTable per your requirement, add or remove conditions to make the table meet your needs. In our life, we can use PivotTable to count data in different groups refer to above steps.

 

How to Create a Chart with Two-level Axis labels in Excel

This post will guide you how to create a column chart with two-level Axis labels based on your original data in Excel. How do I create a Two-level X axis labels in your Chart with Pivot table in Excel 2013/2016.

Assuming that you have a list of data, and you want to create a column chart with two-level X Axis labels. This post will introduce two ways to achieve the result. You need to change the original data in the First method, including sorting and merging cells. And a pivot table need to be created based on your data, then create a Column Chart based on Pivot table.

create chart with two level axis1

Create a Chart with Two-Level Axis Label

You need to change the original data firstly, and then create column chart based on your data. Just do the following steps:

Step1: select the first column (product column) except for header row.

create chart with two level axis2

Step2: go to DATA tab in the Excel Ribbon, and click Sort A to Z command under Sort & Filter group. And the Sort Warning dialog will open.

create chart with two level axis3

Step3: keep the Expand the selection option be checked, and click Sort button in the Sort Warning dialog box.

create chart with two level axis4

Step4: the selected cells should be sorted.

create chart with two level axis5

Step5: select the first same category of product in the first column, such as: Excel.

Step6: go to Home tab, and click Merge & Center command under Alignment group. And the Microsoft Excel warning dialog box will open. And Click Ok button. And the first product should be merged into one cell.

create chart with two level axis7

create chart with two level axis8

Step7: you need to repeat the step 5-6 to merger other adjacent cells with the same category of product.

create chart with two level axis9

Step8: select your current source data, and go to Insert tab, click Insert Column Chart command and select Clustered Column from the dropdown list box.

create chart with two level axis10

Step9: you would see that the column chart with two-level Axis labels has been created successfully.

create chart with two level axis11

 

Create a Chart with Two-Level Axis Label Based on Pivot Table

You can also create a Column Chart with two-level axis labels based on a pivot table in your worksheet, just do the following steps:

Step1: select your source data, and go to Insert tab, click PivotTable command under Tables group.

create chart with two level axis12

Step2: check the Existing Worksheet option and select a blank cell to place your pivot table in your current worksheet, and click Ok button.

create chart with two level axis13

Step3: choose product, data, sales fields in the PivotTable Fields pane. And the pivot table should be created based on your source data.

Step4: select your pivot table, and go to Insert tab, click Insert Column Chart command and select Clustered Column from the dropdown list box.

create chart with two level axis14

Step5: you would see that the column chart with two-level Axis labels has been created successfully.

 

Sum Values in a Column based on Criteria in Another Column

This post will guide you how to sum values in a column based on criteria in another column in Excel. How do I sum values based on criteria in another column with Pivot table in Excel. How to sum all values in one column based on the text value in another column with formula in Excel.

Assuming that you have a list of data set in range A1:B6 and you want to sum the values in one column (Column B) based on the text value in another column (Column A), how to achieve it. You need to remove duplicates values in Column a, then sum values in column B based on the unique text values in column A. You can use the below two methods to achieve the result.

Sum Values based on Another Column with Formula

To use a formula to sum values in Column B based on Column A, you can create a formula based on the SUMIF function. Just do the following steps:

#1 select the text values in Column A (A1:A6), press Ctrl +C to copy these values, and paste into another blank column (Column D).

sum values based another column1

#2 keep the pasted values in Column D selected, go to DATA tab, click Remove Duplicates command under Data Tools group.

sum values based another column2

#3 select only pasted column in columns list under Remove Duplicates dialog box. And click Ok button.

sum values based another column5

Note: the duplicated values in selected Column D have been removed and only keep unique values.

sum values based another column6

#4 select one blank cell (E2) beside the pasted column and then type the following formula into the formula box and press Enter key in your keyboard, and drag the AutoFill Handle over other cells.

=SUMIF($A$2:$A$6, D2, $B$2:$B$6)

sum values based another column7

You will see that the summed values have been calculated in column E based on the text values in Column A.

Sum Values based on Another Column with Pivot Table

You can also create an Excel Pivot Table to sum values based on another column. Just do the following steps:

#1 select the data source that to be used for creating PivotTable.

sum values based another column8

#2 go to INSERT tab, click PivotTable command under Tables group. The Create PivotTable dialog box will open.

sum values based another column9

#3 select the Existing Worksheet radio button, and select one range of cells that used to place the pivot table.

sum values based another column10

#4 Choose fields to add to report in the PivotTable Fields pane. Drag the column contain text values into the ROWS section, and drag the column contain sale values into the VALUES section.

sum values based another column11

#5 the values in column B based on another column A have been summed.

sum values based another column12

Related Functions

  • Excel SUMIF Function
    The Excel SUMIF function sum the numbers in the range of cells that meet a single criteria that you specify. The syntax of the SUMIF function is as below:=SUMIF (range, criteria, [sum_range])…

Count Unique Values Using Pivot Table

This post will guide you how to count unique items in Excel Pivot Table. How do I use Pivot Table to count unique distinct values in Excel. How to get a distinct count in a pivot table in Excel.

Assuming that you have a list of 5 cells with 3 unique values, and you want to create an excel pivot table to count unique values, how to achieve it. You can insert a 3rd or helper column to check if the value is unique in the selected range of cells, and the create pivot table based on the 1st and 3rd column to count unique values. Or you can use the Distinct Count feature in pivot table to achieve the result, but this feature is only available in Excel 2013 and Excel 2016 version.

Count Distinct Values Using Pivot Table with help column

#1 Insert 3rd column named as helper column (Column C), then type this formula into the Cell C2.

=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1)

count unique values pivot table1

#2 press Enter key in your keyboard, and drag the AutoFill Handler over other cells in helper column to apply this formula. If the returned value is 1, then it indicated that the value is the first value, if it returns 0, it indicated that the value is a duplicated value.

count unique values pivot table2

#3 select the data to be included in a pivot table, the helper column should be also included.

count unique values pivot table5

#4 go to INSERT tab, click the PivotTable command under Tables group. The create PivotTable dialog will appear.

count unique values pivot table4

#5 select Existing Worksheet radio button, and then select one cell or range to place the PivotTable report. Click OK button.

count unique values pivot table6

#6 choose fields to add to report in PivotTable fields pane. Choose Product and Helper fields checkbox.

count unique values pivot table7

#7 let’ see the result.

count unique values pivot table8

Count Distinct Values Using Pivot Table with distinct Count Feature

If you are using the latest version of Excel 2013 or Excel 2016, there is a new feature to allow you to count distinct values automatically in the pivot table. Let’s see the below detailed steps:

#1 select the range of cells that you want to be added into pivot table.

count unique values pivot table9

#2 Click “INSERT” in the Ribbon tab, then clicking “Pivot Table” button in the “Tables” group. The Create PivotTable dialog will appear.

count unique values pivot table4

#3 select Existing Worksheet radio button, and then select one cell or range to place the pivottable report. and select the Add this data to the Data Model checkbox. Click OK button.

count unique values pivot table10

#4 arrange the Rows, Columns and Values areas by dragging fields in PivotTable Fields pane.

count unique values pivot table11

#5 click on Values area, and select Value Field Settings from the drop down list. The Value Field Settings dialog will open.

count unique values pivot table12

#6 click Summarize Values By tab, and scroll down to Distinct Count, then click OK button.

count unique values pivot table13

#7 Let’s see the result.

count unique values pivot table14

Related Functions

  • Excel SUMPRODUCT function
    The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….

 

Remove Grand Total in a PivotTable

This post will guide you how to remove grand total from pivot table in excel 2013/2016. How do I remove column grand totals and row grand total in a pivot table in excel. How to hide grand total in an excel pivot table.

Remove Grand Total

When you created an excel Pivot Table, the grand total will be added automatically by default. So if you do not need the grand total, just follow below steps to remove it:

#1 select one cell in your pivot table

remove grand total1

#2 go to DESIGN tab from the toolbar at the top of the screen, click Grand Totals command under Layout group and select Off for Rows and Columns from the popup menu list.

remove grand total2

Note:

  • if you only want to remove column grand total, just select On for Columns Only from the popup menu.

remove grand total5

  • if you only want to remove or hide row grand total, just select On for Row Only from the popup menu.

remove grand total4

#3 you will see that both row and column grand total are removed.

remove grand total3

How To Use Pivot Table In Microsoft Excel

Create a Pivot Table | Refresh a Pivot Table | Filter a Pivot Table | Change data source for pivot table | Remove column/row Grand Totals | Change pivot table name | Sort pivot table results | Change the type of calculation | Two-dimensional Pivot table

A Pivot Table allows you to extract certain data from a much larger data set to summarize complex data. This post will guide you how to use pivot table in Microsoft excel from the below subjects:

  • Create a Pivot Table
  • Refresh a Pivot Table
  • Filter a Pivot Table
  • Change data source for pivot table
  • Remove column/row Grand Totals
  • Change pivot table name
  • Sort pivot table results
  • Change the type of calculation
  • Two-dimensional Pivot table

Create a Pivot Table

The below data set will be used in the following pivot table examples.

excel pivot table data set1

To create a new Pivot table, just follow the below steps:

#1 Click any single cell in which you want to insert pivot table (select B16 in this example).

#2 Click “INSERT” in the Ribbon tab, then clicking “Pivot Table” button in the “Tables” group.

excel pivot table data set1

#3 A “Create PivotTable” window will appear.

excel pivot table data set1

#4 Select a range “A1:E13” for the pivot table and click on the OK.

excel pivot table data set1

#5 A Pivot Table will appear and “Pivot Table Fields” Layout also will appear in the right of window.

excel pivot table data set1

excel pivot table data set 6

#6 Drag “Product” field to the Filters area, “Team” field to the Row area and “Cost” field to the Values area.

excel pivot table data set 6

Now the PivotTable report is generated as follows:

excel pivot table data set 8

Refresh a Pivot Table

If the data source make some changes, then you need to refresh your pivot table to take effect. To refresh pivot table, just following the below steps:

1# right-click on the pivot table, then click “refresh”

excel pivot table data set 9

2# You will see that the pivot table refreshed. The sum of cost value have been changed from 410  to 470.

excel pivot table data set 9

Filter a Pivot Table

When creating pivot table, we need to drag fields to the Filters area, so we can filter this pivot table by this field that you dragged. In the above example, the “Product” field is dragged to the Filters area, so we can filter this pivot table by “Product” field. Just following the below steps:

#1 Click the filter drop-down button

excel pivot table data set 11

#2 select one item from the drop-down list. Such as: select “Ball pen”. Then click “OK” button.

excel pivot table data set 11

#3 let’s see the filter result.

excel pivot table data set 13

Change data source for pivot table

After created a PivotTable, you can change the range of its source data, such as, you can expand the source data to include more rows of data.

To change the data source for pivot table, just following the below steps:

#1 click any cell inside the pivot table, then the “PivotTable Tools” tab will show on the ribbon.

excel pivot table data set 14

#2 click “ANALYZE” Tab, then click “Change Data Source”.

excel pivot table data set 14

#3 the window of “Change PivotTable Data Source” will appear, then enter the range that you want to use.

excel pivot table data set 14

Remove column/row Grand Totals

If you want to remove grand totals for columns, just do the following:

#1 click “DESIGN” Tab under “PivotTable Tools” in Ribbon.

excel pivot table data set 17

#2 click “Grand Totals” button and then select “On for Rows Only”.

excel pivot table data set 17

#3 let’s see the result:

excel pivot table data set 17

Change pivot table name

By default, the first pivot table you create is named as “PivotTable1”, the second is “PivotTable2”… so on.  The below steps will guide you how to rename the existing pivot table, just do the following:

#1 Right click any cell inside the pivot table and then select “PivotTable Options”

Change pivot table name 2

#2 enter into the pivot table name that you want to use in the “PivotTable Name” textbox. Then click “OK” button.

Change pivot table name 2

Sort pivot table results

To sort the pivot table result, just following the below steps:

#1 right click any cell inside the “sum of Cost” field in the pivot table.

#2 click “Sort”, then click “sort Largest to Smallest” or “sort Smallest to Largest” from the popup menu

Sort pivot table results1

#3 the results of “sum of Cost” will be sort.

Sort pivot table results1

Change the type of calculation

By default, Excel will summarizes value field by summing the items. To change the type of calculation that you want to use to summarize data from the selected field, just following the below steps:

#1 click any cell inside the “Sum of Cost” column, then click “Value Field Settings…”

Change the type of calculation 1

#2 the window of “Value Field Settings” will appear.

Change the type of calculation 1

#3 choose one type of calculation you want to use under “Summarize Values By” Tab. Then click OK button.

Change the type of calculation 1

#4 let’s see the result:

Change the type of calculation 1

Two-dimensional Pivot table

Two-dimensional Pivot table can be created by dragging a field to the Rows area and Columns area.  The following steps will guide you how to create a two dimensional pivot table:

#1 insert a pivot table, then drag “Product” field to the Filters area, “Team” field to the Row area, “Quarter” field to the Columns area and “Cost” field to the Values area in the “PivotTable Fields” dashboard.

Two-dimensional Pivot table 1

#2 let’s see the result:

Two-dimensional Pivot table 1