Pivot Tables And Pivot Charts Pivot Tables and Charts 0517 1 The World of Big Data Companies have the ability to collect massive amounts of data about their customers Kroger Capital One State Farm AT T are among many companies with databases that are several petabytes in size 1000 Terabytes 1 Petabyte That s too big to fit on your hard drive 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 0517 2009 Pearson Education Inc Copyright 2 What is a Pivot Table Microsoft Excel developed 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 0517 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 0517 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 the INSERT tab and then Pivot Table Pivot Tables and Charts 0517 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 or NOTE Other than this page all of the images and instructions in this presentation are from Excel 2013 which is a PC version of Pivot Tables and Charts 0517 2009 Pearson Education Copyright Inc Excel 6 Pivot Table Options The pivot table has four general options COLUMNS Inputs a variable into the column ROWS Inputs a variable into the row VALUES Assigns the value that will be displayed in the table FILTERS Allows you to remove any results from the table that you choose Pivot Tables and Charts 0517 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 0517 2009 Pearson Education Inc Copyright 8 Example Pivot Table The pivot table at the bottom of the screen was constructed using the following COLUMNS Gender q01 ROWS Fraternity Sorority q14 VALUES Average Height q03 What do these results tell us Pivot Tables and Charts 0517 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 PIVOTTABLE TOOLS ANALYZE Pivot Chart Pivot Tables and Charts 0517 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 0517 2009 Pearson Education Inc Copyright 11 Default Pivot Chart for Previous Example Can we improve the look of this image Pivot Tables and Charts 0517 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 Pivot Tables and Charts 0517 2009 Pearson Education Inc Copyright 13 Further Improvements to the Pivot Chart Cont Click the symbol to the right of the Pivot Chart to reveal the Chart Elements options A descriptive title for your Pivot Chart is a must Pivot Tables and Charts 0517 2009 Pearson Education Inc Copyright 14 Further Improvements to the Pivot Chart Cont What other improvements can we make Pivot Tables and Charts 0517 2009 Pearson Education Inc Copyright 15 Further Improvements to the Pivot Chart Cont What else could we add Pivot Tables and Charts 0517 2009 Pearson Education Inc Copyright 16 Further Improvements to the Pivot Chart Cont Pivot Tables and Charts 0517 2009 Pearson Education Inc Copyright 17 Further Improvements to the Pivot Chart Cont The images shown previously are screenshots of Pivot Charts while they are still in the Excel worksheet Here is what the Pivot Chart from the previous slide looks like once it is copy pasted into Powerpoint Notice how important it is for us to have a title and label the axes Pivot Tables and Charts 0517 2009 Pearson Education Inc Copyright 18 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 0517 2009 Pearson Education Inc Copyright 19 Further Improvements to the Pivot Chart Cont What is different about this chart Pivot Tables and Charts 0517 2009 Pearson Education Inc Copyright 20 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 0517 2009 Pearson Education Inc Copyright 21 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 0517 2009 Pearson Education Inc Copyright 22 Data
View Full Document
Unlocking...