Menu
Excel 2011, while working with pivot tables, quits unexpectedly after receive a warning that asks if you want to replace the contents of the destination cells. For Excel Versions:Excel Office 365, Excel Office 365 for Mac, Excel 2016, Excel 2016 for Mac, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2008 for Mac, Excel 2007, Excel Online. This tutorial demonstrates how to use the pivot table feature in Excel, the cornerstone of any Excel analytics project. This is an introductory tutorial suitable.
You can summarize a PivotTable by placing a field in ∑ VALUES area in the PivotTable Fields Task pane. By default, Excel takes the summarization as sum of the values of the field in ∑ VALUES area. However, you have other calculation types, such as, Count, Average, Max, Min, etc.
In this chapter, you will learn how to set a calculation type based on how you want to summarize the data in the PivotTable.
Sum
Consider the following PivotTable wherein you have the summarized sales data regionwise, salesperson-wise and month-wise.
As you can observe, when you drag the field Order Amount to ∑ VALUES area, it is displayed as Sum of Order Amount, indicating the calculation is taken as Sum. In the PivotTable, in the top-left corner, Sum of Order Amount is displayed. Further, Grand Total column and Grand Total row are displayed for subtotals field-wise in rows and columns respectively.
Value Field Settings
With Values Field Settings, you can set the calculation type in your PivotTable. You can also decide on how you want to display your values.
The Value Field Settings dialog box appears.
The Source Name is the field and Custom Name is Sum of field. Calculation Type is Sum. Click the Show Values As tab.
In the box Show Values As, No Calculation is displayed. Click the Show Values As box. You can find several ways of showing your total values.
% of Grand Total
You can show the values in the PivotTable as % of Grand Total.
The PivotTable summarizes the values as % of the Grand Total.
As you can observe, Sum of Order Amount in the top-left corner of the PivotTable and in the ∑ VALUES area in the PivotTable Fields pane is changed to the new Custom Name - % of Grand Total.
% of Column Total
Suppose you want to summarize the values as % of each month total.
The PivotTable summarizes the values as % of the Column Total. In the Month columns, you will find the values as % of the specific month total.
% of Row Total
You can summarize the values as % of region totals and % of salesperson totals, by selecting % of Row Total in Show Values As box in the Value Field Settings dialog box.
Count
Suppose you want to summarize the values by the number of Accounts region wise, salesperson wise and month wise.
The Count of Account will be displayed as shown below −
Average
Suppose you want to summarize the PivotTable by average values of Order Amount region wise, salesperson wise and month wise.
The average will be displayed as shown below −
You have to set the number format of the values in the PivotTable to make it more presentable.
The Format Cells dialog box appears.
The PivotTable values will be formatted to numbers with two decimal places.
Excel Pivot Table Tutorial Youtube
Max
Suppose you want to summarize the PivotTable by the maximum values of Order Amount region-wise, salesperson-wise and month-wise.
The PivotTable will display the maximum values region wise, salesperson wise and month wise.
Min
Suppose you want to summarize the PivotTable by the minimum values of Order Amount region wise, salesperson wise and month wise.
Pivot Table Excel 2011 Mac Tutorial Software
The PivotTable will display the minimum values region wise, salesperson wise and month wise.
Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |