Pivot Tables And Pivot Charts Pivot Tables and Charts 0913 1 The World of Big Data Companies have the ability to collect massive amounts of data about their customers Kroger s Capital One State Farm AT T are among many companies with databases that are terabytes in size That s too big to fit on most hard drives The data has to be translated into knowledge One of the quickest and best ways to do this is to visualize the data Pivot Tables and Charts 0913 2009 Pearson Education Inc Copyright 2 What is a Pivot Table Microsoft Excel started using pivot tables in the late 90 s Pivot tables were created to take large amounts of data and give the user the ability to easily sift through the data Usage With pivot tables we can isolate variables and control for other variables Pivot tables allow us to compare one variables over different levels of another variable Pivot Tables and Charts 0913 2009 Pearson Education Inc Copyright 3 Creating a Pivot Table Any data in Excel can be used to make a pivot table but it must have the following format Data labels at the top of each column Consistent labeling of values in the column Excel will view Y Yes y and yes all differently and register them as different values If there is mislabeled data in your pivot table you can easily remove it by unchecking the label Pivot Tables and Charts 0913 2009 Pearson Education Inc Copyright 4 Creating a Pivot Table PC Step 1 Select all data in your table This can be done by hitting Control A Step 2 Create the pivot table Click insert and then pivot table Pivot Tables and Charts 0913 2009 Pearson Education Inc Copyright 5 Creating a Pivot Table Mac Step 1 Select all data in your table This can be done by hitting Command A Step 2 Create the pivot table Under Data select PivotTable Pivot Tables and Charts 0913 2009 Pearson Education Inc Copyright 6 Pivot Table Options The pivot table has four general options Report filter Allows you to remove any results from the table by label Legend Fields Inputs a variable into the column Axis Fields Inputs a variable into the row Values Assigns the value that will be displayed in the table Pivot Tables and Charts 0913 2009 Pearson Education Inc Copyright 7 Changing the Value Displayed It is important that you select the proper value to be displayed on your chart By clicking on the value you can change it by selecting Value Field Settings Sum Count Average Max or Min Pivot Tables and Charts 0913 2009 Pearson Education Inc Copyright 8 Example Pivot Table The pivot table at the bottom of the screen was constructed using the following Column label Gender q01 Row label Fraternity Sorority q14 Values Average Height q03 What do these results tell us Pivot Tables and Charts 0913 2009 Pearson Education Inc Copyright 9 From Tables to Charts The first thing you should do when you get data is visualize it Pivot tables allow us to aggregate data but the real power is putting it on graphs and charts With a click of a button you can go from a pivot table to a pivot chart This can be found under Options PivotChart Pivot Tables and Charts 0913 2009 Pearson Education Inc Copyright 10 Creation of Pivot Charts Whatever is on your pivot table will be visualized on the pivot chart Changes made to the pivot table update automatically on the pivot chart There are many options to customize your pivot chart Always try to customize your pivot chart so that what is displayed in the pivot table is clear to the person looking at it Pivot Tables and Charts 0913 2009 Pearson Education Inc Copyright 11 Default Pivot Chart for Previous Example What is missing from this image Pivot Tables and Charts 0913 2009 Pearson Education Inc Copyright 12 Further Improvements to the Pivot Chart There are many options to improve your pivot chart Let s explore some of the basic options to improve your pivot table First a title for the chart is essential Under PivotChart Tools Layout Chart Title Above Chart Type a descriptive title for the chart Pivot Tables and Charts 0913 2009 Pearson Education Inc Copyright 13 Further Improvements to the Pivot Chart Cont What else is missing from this image Pivot Tables and Charts 0913 2009 Pearson Education Inc Copyright 14 Further Improvements to the Pivot Chart Cont PivotChart Tools Layout Axis Titles Pivot Tables and Charts 0913 2009 Pearson Education Inc Copyright 15 Further Improvements to the Pivot Chart Cont PivotChart Tools Layout Data Table Pivot Tables and Charts 0913 2009 Pearson Education Inc Copyright 16 Further Improvements to the Pivot Chart Cont Sometimes the order that the categories are displayed is not to your liking In the Pivot Table click the down arrows next to Column and or Row Labels Pivot Tables and Charts 0913 2009 Pearson Education Inc Copyright 17 Further Improvements to the Pivot Chart Cont What is different about this chart Pivot Tables and Charts 0913 2009 Pearson Education Inc Copyright 18 Excel and other Microsoft Products When you simply copy paste a pivot chart into Word or Powerpoint they are dynamically linked back to your pivot chart in Excel Certain changes made to the pivot chart in Excel are automatically reflected in your Word or Powerpoint presentation If you don t want changes you make in your pivot chart to be reflected in your Word or Powerpoint file you could Paste Special Bitmap After pasting the pivot chart right click Save as Picture then Insert the picture you just saved Pivot Tables and Charts 0913 2009 Pearson Education Inc Copyright 19 Data Exploration Example As far as Stat 201 students go is there a difference in how many children males vs females want to have Does how many children a person wants to have influenced by what sort of relationship they are currently in Let s look at Q01 Gender Q07 Relationship Status Q53 Kids plan on having Pivot Tables and Charts 0913 2009 Pearson Education Inc Copyright 20 Data Exploration Example Cont Pivot Tables and Charts 0913 2009 Pearson Education Inc Copyright 21 Data Exploration Example Cont Pivot Tables and Charts 0913 2009 Pearson Education Inc Copyright 22 Data Exploration Example Cont Why do males want so many children in the Engaged or Married category How much data is there in each of these categories Let s change the data displayed from Average of q53 Kids plan on having to count Pivot Tables and Charts 0913 2009 Pearson Education Inc Copyright 23 Data Exploration Example Cont Pivot Tables and Charts 0913 2009 Pearson Education Inc Copyright 24 Data Exploration Example Cont There were