Simplifying Excel Pivot
Table and Pivot Chart
Setup
Overview of Excel Pivot Tables
The Excel pivot table is an incredibly convenient tool to summarize and better understand large amounts of data by creating instant cross tabulations that can be quickly rearranged to highlight different aspects of the data. Excel pivot tables give the user the ability to instantly summarize data in almost any possible configuration. Pivot tables allow you to quickly pull useful information out of large amounts of raw data. Important insights about the data can be readily obtained from Excel pivot tables that might be difficult to discover otherwise.
The most common use of pivot tables is to summarize data by all available categories and subcategories within the raw data. The incredible versatility of pivot tables allows you to expand and collapse all levels of data and instantly reshuffle all categories and subcategories in order to obtain just the right information and insights.
Overview of Excel Pivot Charts
The Excel pivot chart is an excellent supplement to any Excel pivot chart. The pivot chart provides a graphical representation of the pivot table's information that enables quicker comprehensive of trends and comparisons in the data. The pivot chart can instantly created in Excel in a single step after the pivot table has been constructed. The pivot chart a number of different chart types that facilitate even faster comprehension of data's messages.
All of that power and versatility can leave a person new to pivot tables wondering where to start. Fortunately, pivot tables are quite a bit more intuitive than they may initially appear. An easy, stepbystep process will be provided in this article to quickly create a pivot table with multiple configurations on the following data set:
Step 1 – Determine the Overall Objective
The most important aspect of creating an Excel pivot table is to establish the main goal and any lesser goals of the analysis. These goals will drive every aspect of the pivot table analysis going forward.
Two following goals will be established for this pivot table analysis:

Compare sales results of the two salespeople

Compare sales results of the two products
Step 2 – Determine the Query Question That Meets Each Goal
As often happens, getting the right answer depends on asking the right question. The query question determines how the data will be arranged and summarized within the pivot table. The following framework can be used to translate the goal determined in step 1 to a correct query question.
Here is a good way to structure the query question for each goal:
Compare _______________
For each _______________
For each _______________
Goal 1 – Compare Sales Results of the Two Salespeople
Compare: Sales Results of Each Salesperson
For each: Product
For each: Quarter
Goal 2 – Compare Sales Results of Each Product
Compare: Sales Results of Each Product
For each: Quarter
Step 3 – Create the Pivot Table That Implements Each Query Question
When the query question has been correctly created, it is usually a simple and straightforward to configure a pivot table that answers the query question. An empty pivot table dialogue box can be brought up in Excel as follows:
Insert / PivotTable will bring up the following empty pivot table dialogue box.
The data table should be arranged in contiguous (touching) rows. The Table/Range consists of the entire table with headers.
The location where the pivot table should be placed will be the cell address of the upper left corner of the output.
Clicking OK brings up the following PivotTable Field dialogue box. All data column headers from the data table that was selected are shown in the empty PivotTable Field dialogue box.
The column headers can now be arranged in the dialogue box as follows to answer the query question corresponding to each goal.
Goal 1 – Compare Sales Results of the Two Salespeople
Compare: Sales Results of Each Salesperson
For each: Product
For each: Quarter
All columns headers have been checked. Headers of categorical (nonnumeric) data are placed as Rows within the dialogue box. Categorical data include Quarter, Product, and Salesperson. These categorical data labels are placed in the dialogue box in the order that they appeared in the query question.
Numeric data to be evaluated is summed up under Values in the dialogue box.
The following pivot tables is now produced.
Step 4 – Create the Pivot Chart From the Pivot Table
As soon as the pivot table is created, the corresponding pivot chart for that pivot table can be created in a single step. Simply click anywhere on the pivot table, click on the PivotTable Tools Design tab and then select Pivot Chart as follows:
This will bring up the the chart type dialogue box. In this case the 3D Stacked Column chart was selected.
The resulting pivot chart is the following:
The chart provides a more intuitive comparison between sales of the two salespeople than does the table.
If the query question were changed to the following:
Compare: Sales Results of Each Salesperson
For each: Quarter
For each: Product
then the PivotTable Fields dialogue box would be reconfigured to implement the new query question by simply dragging the Product column label to the top of Rows to reflect the order in the new query question.
This would produce the following pivot table.
These changes to the table are instantly reflected in the automatically updated pivot chart as follows:
Showing the data in separate columns in the pivot table for each quarter can be implemented by dragging the Quarter column header to Columns in the PivotTable Fields dialogue box as follows:
This instantly produces the following pivot table data for each quarter in its own column.
When the data's arrangement is significantly changed within the pivot table, the pivot chart type sometimes should be changed to provide the most intuitive presentation of the data. In this case the pivot chart type was changed from a 3D Stacked Column chart to a 3D Clustered Column chart to create greater clarity. In the PivotChart Design tools tab, select Change Chart Type as follows:
The 3D Clustered Chart is selected from the following Chart Type dialogue box:
This instantly produces the following pivot chart:
Goal 2 – Compare Sales Results of Each Product
Compare: Sales Results of Each Product
For each: Quarter
The Salesperson column label can be unchecked because the query question does not request any information about individual salespeople.
The following pivot table is produced as a result of the Quarter and Product under Rows:
The following pivot chart more intuitively represents the data of this pivot table in the 3D Stacked Column chart type:
Excel Master Series Blog Directory
Statistical Topics and Articles In Each Topic
 Histograms in Excel
 Bar Chart in Excel
 Combinations & Permutations in Excel
 Normal Distribution in Excel
 Overview of the Normal Distribution
 Normal Distribution’s PDF (Probability Density Function) in Excel 2010 and Excel 2013
 Normal Distribution’s CDF (Cumulative Distribution Function) in Excel 2010 and Excel 2013
 Solving Normal Distribution Problems in Excel 2010 and Excel 2013
 Overview of the Standard Normal Distribution in Excel 2010 and Excel 2013
 An Important Difference Between the t and Normal Distribution Graphs
 The Empirical Rule and Chebyshev’s Theorem in Excel – Calculating How Much Data Is a Certain Distance From the Mean
 Demonstrating the Central Limit Theorem In Excel 2010 and Excel 2013 In An EasyToUnderstand Way
 tDistribution in Excel
 Binomial Distribution in Excel
 zTests in Excel
 tTests in Excel
 Overview of tTests: Hypothesis Tests that Use the tDistribution
 1Sample tTests in Excel
 Overview of the 1Sample tTest in Excel 2010 and Excel 2013
 Excel Normality Testing For the 1Sample tTest in Excel 2010 and Excel 2013
 1Sample tTest – Effect Size in Excel 2010 and Excel 2013
 1Sample tTest Power With G*Power Utility
 Wilcoxon SignedRank Test As a 1Sample tTest Alternative in Excel 2010 and Excel 2013
 Sign Test As a 1Sample tTest Alternative in Excel 2010 and Excel 2013
 2IndependentSample Pooled tTests in Excel
 Overview of 2IndependentSample Pooled tTest in Excel 2010 and Excel 2013
 Excel Variance Tests: Levene’s, BrownForsythe, and F Test For 2Sample Pooled tTest in Excel 2010 and Excel 2013
 Excel Normality Tests KolmogorovSmirnov, AndersonDarling, and Shapiro Wilk Tests For TwoSample Pooled tTest
 TwoIndependentSample Pooled tTest  All Excel Calculations
 2Sample Pooled tTest – Effect Size in Excel 2010 and Excel 2013
 2Sample Pooled tTest Power With G*Power Utility
 MannWhitney U Test in Excel as 2Sample Pooled tTest Nonparametric Alternative in Excel 2010 and Excel 2013
 2Sample Pooled tTest = SingleFactor ANOVA With 2 Sample Groups
 2IndependentSample Unpooled tTests in Excel
 2IndependentSample Unpooled tTest in Excel 2010 and Excel 2013
 Variance Tests: Levene’s Test, BrownForsythe Test, and FTest in Excel For 2Sample Unpooled tTest
 Excel Normality Tests KolmogorovSmirnov, AndersonDarling, and ShapiroWilk For 2Sample Unpooled tTest
 2Sample Unpooled tTest Excel Calculations, Formulas, and Tools
 Effect Size for a 2IndependentSample Unpooled tTest in Excel 2010 and Excel 2013
 Test Power of a 2Independent Sample Unpooled tTest With GPower Utility
 Paired (2Sample Dependent) tTests in Excel
 Paired tTest in Excel 2010 and Excel 2013
 Excel Normality Testing of Paired tTest Data
 Paired tTest Excel Calculations, Formulas, and Tools
 Paired tTest – Effect Size in Excel 2010, and Excel 2013
 Paired tTest – Test Power With GPower Utility
 Wilcoxon SignedRank Test As a Paired tTest Alternative
 Sign Test in Excel As A Paired tTest Alternative
 Hypothesis Tests of Proportion in Excel
 Hypothesis Tests of Proportion Overview (Hypothesis Testing On Binomial Data)
 1Sample Hypothesis Test of Proportion in Excel 2010 and Excel 2013
 2Sample Pooled Hypothesis Test of Proportion in Excel 2010 and Excel 2013
 How To Build a Much More Useful SplitTester in Excel Than Google's Website Optimizer
 ChiSquare Independence Tests in Excel
 ChiSquare GoodnessOfFit Tests in Excel
 F Tests in Excel
 Correlation in Excel
 Pearson Correlation in Excel
 Spearman Correlation in Excel
 Confidence Intervals in Excel
 Overview of zBased Confidence Intervals of a Population Mean in Excel 2010 and Excel 2013
 tBased Confidence Intervals of a Population Mean in Excel 2010 and Excel 2013
 Minimum Sample Size to Limit the Size of a Confidence interval of a Population Mean
 Confidence Interval of Population Proportion in Excel 2010 and Excel 2013
 Min Sample Size of Confidence Interval of Proportion in Excel 2010 and Excel 2013
 Simple Linear Regression in Excel
 Overview of Simple Linear Regression in Excel 2010 and Excel 2013
 Simple Linear Regression Example in Excel 2010 and Excel 2013
 Residual Evaluation For Simple Regression in Excel 2010 and Excel 2013
 Residual Normality Tests in Excel – KolmogorovSmirnov Test, AndersonDarling Test, and ShapiroWilk Test For Simple Linear Regression
 Evaluation of Simple Regression Output For Excel 2010 and Excel 2013
 All Calculations Performed By the Simple Regression Data Analysis Tool in Excel 2010 and Excel 2013
 Prediction Interval of Simple Regression in Excel 2010 and Excel 2013
 Multiple Linear Regression in Excel
 Basics of Multiple Regression in Excel 2010 and Excel 2013
 Multiple Linear Regression Example in Excel 2010 and Excel 2013
 Multiple Linear Regression’s Required Residual Assumptions
 Normality Testing of Residuals in Excel 2010 and Excel 2013
 Evaluating the Excel Output of Multiple Regression
 Estimating the Prediction Interval of Multiple Regression in Excel
 Regression  How To Do Conjoint Analysis Using Dummy Variable Regression in Excel
 Logistic Regression in Excel
 Logistic Regression Overview
 Logistic Regression Performed in Excel 2010 and Excel 2013
 R Square For Logistic Regression Overview
 Excel R Square Tests: Nagelkerke, Cox and Snell, and LogLinear Ratio in Excel 2010 and Excel 2013
 Likelihood Ratio Is Better Than Wald Statistic To Determine if the Variable Coefficients Are Significant For Excel 2010 and Excel 2013
 Excel Classification Table: Logistic Regression’s Percentage Correct of Predicted Results in Excel 2010 and Excel 2013
 HosmerLemeshow Test in Excel – Logistic Regression GoodnessofFit Test in Excel 2010 and Excel 2013
 SingleFactor ANOVA in Excel
 Overview of SingleFactor ANOVA
 SingleFactor ANOVA Example in Excel 2010 and Excel 2013
 ShapiroWilk Normality Test in Excel For Each SingleFactor ANOVA Sample Group
 KruskalWallis Test Alternative For Single Factor ANOVA in Excel 2010 and Excel 2013
 Levene’s and BrownForsythe Tests in Excel For SingleFactor ANOVA Sample Group Variance Comparison
 SingleFactor ANOVA  All Excel Calculations
 Overview of PostHoc Testing For SingleFactor ANOVA
 TukeyKramer PostHoc Test in Excel For SingleFactor ANOVA
 GamesHowell PostHoc Test in Excel For SingleFactor ANOVA
 Overview of Effect Size For SingleFactor ANOVA
 ANOVA Effect Size Calculation Eta Squared (?2) in Excel 2010 and Excel 2013
 ANOVA Effect Size Calculation Psi (?) – RMSSE – in Excel 2010 and Excel 2013
 ANOVA Effect Size Calculation Omega Squared (?2) in Excel 2010 and Excel 2013
 Power of SingleFactor ANOVA Test Using Free Utility G*Power
 Welch’s ANOVA Test in Excel Substitute For SingleFactor ANOVA When Sample Variances Are Not Similar
 BrownForsythe FTest in Excel Substitute For SingleFactor ANOVA When Sample Variances Are Not Similar
 TwoFactor ANOVA With Replication in Excel
 TwoFactor ANOVA With Replication in Excel 2010 and Excel 2013
 Variance Tests: Levene’s and BrownForsythe For 2Factor ANOVA in Excel 2010 and Excel 2013
 ShapiroWilk Normality Test in Excel For 2Factor ANOVA With Replication
 2Factor ANOVA With Replication Effect Size in Excel 2010 and Excel 2013
 Excel Post Hoc Tukey’s HSD Test For 2Factor ANOVA With Replication
 2Factor ANOVA With Replication – Test Power With GPower Utility
 ScheirerRayHare Test Alternative For 2Factor ANOVA With Replication
 TwoFactor ANOVA Without Replication in Excel
 Normality Testing in Excel
 Creating a Box Plot in 8 Steps in Excel
 Creating a Normal Probability Plot With Adjustable Confidence Interval Bands in 9 Steps in Excel With Formulas and a Bar Chart
 ChiSquare GoodnessofFit Test For Normality in 9 Steps in Excel
 KolmogorovSmirnov, AndersonDarling, and ShapiroWilk Normality Tests in Excel
 Nonparametric Testing in Excel
 MannWhitney U Test in 12 Steps in Excel
 Wilcoxon SignedRank Test in 8 Steps in Excel
 Sign Test in Excel
 Friedman Test in 3 Steps in Excel
 ScheirerRayHope Test in Excel
 Welch's ANOVA Test in 8 Steps Test in Excel
 BrownForsythe F Test in 4 Steps Test in Excel
 Levene's Test and BrownForsythe Variance Tests in Excel
 ChiSquare Independence Test in 7 Steps in Excel
 ChiSquare GoodnessofFit Tests in Excel
 ChiSquare Population Variance Test in Excel
 Post Hoc Testing in Excel
 Creating Interactive Graphs of Statistical Distributions in Excel
 Interactive Statistical Distribution Graph in Excel 2010 and Excel 2013
 Interactive Graph of the Normal Distribution in Excel 2010 and Excel 2013
 Interactive Graph of the ChiSquare Distribution in Excel 2010 and Excel 2013
 Interactive Graph of the tDistribution in Excel 2010 and Excel 2013
 Interactive Graph of the Binomial Distribution in Excel 2010 and Excel 2013
 Interactive Graph of the Exponential Distribution in Excel 2010 and Excel 2013
 Interactive Graph of the Beta Distribution in Excel 2010 and Excel 2013
 Interactive Graph of the Gamma Distribution in Excel 2010 and Excel 2013
 Interactive Graph of the Poisson Distribution in Excel 2010 and Excel 2013
 Solving Problems With Other Distributions in Excel
 Solving Uniform Distribution Problems in Excel 2010 and Excel 2013
 Solving Multinomial Distribution Problems in Excel 2010 and Excel 2013
 Solving Exponential Distribution Problems in Excel 2010 and Excel 2013
 Solving Beta Distribution Problems in Excel 2010 and Excel 2013
 Solving Gamma Distribution Problems in Excel 2010 and Excel 2013
 Solving Poisson Distribution Problems in Excel 2010 and Excel 2013
 Optimization With Excel Solver
 Maximizing Lead Generation With Excel Solver
 Minimizing Cutting Stock Waste With Excel Solver
 Optimal Investment Selection With Excel Solver
 Minimizing the Total Cost of Shipping From Multiple Points To Multiple Points With Excel Solver
 Knapsack Loading Problem in Excel Solver – Optimizing the Loading of a Limited Compartment
 Optimizing a Bond Portfolio With Excel Solver
 Travelling Salesman Problem in Excel Solver – Finding the Shortest Path To Reach All Customers
 ChiSquare Population Variance Test in Excel
 Analyzing Data With Pivot Tables
 SEO Functions in Excel
 Time Series Analysis in Excel
No comments:
Post a Comment