Pivot Tables and Charts 09131Pivot TablesAndPivot ChartsCopyright © 2009 Pearson Education, Inc. Pivot Tables and Charts 09132The 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.Copyright © 2009 Pearson Education, Inc. Pivot Tables and Charts 09133What 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.Copyright © 2009 Pearson Education, Inc. Pivot Tables and Charts 09134Creating 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.Copyright © 2009 Pearson Education, Inc. Pivot Tables and Charts 09135Creating 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 tableCopyright © 2009 Pearson Education, Inc. Pivot Tables and Charts 09136 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 PivotTableCreating a Pivot Table (Mac)Copyright © 2009 Pearson Education, Inc. Pivot Tables and Charts 09137Pivot 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.Copyright © 2009 Pearson Education, Inc. Pivot Tables and Charts 09138Changing 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 MinCopyright © 2009 Pearson Education, Inc. Pivot Tables and Charts 09139Example 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?Copyright © 2009 Pearson Education, Inc. Pivot Tables and Charts 091310From 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 -> PivotChartCopyright © 2009 Pearson Education, Inc. Pivot Tables and Charts 091311Creation 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.Copyright © 2009 Pearson Education, Inc. Pivot Tables and Charts 091312Default Pivot Chart for Previous Example What is missing from this image?Copyright © 2009 Pearson Education, Inc. Pivot Tables and Charts 091313Further 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:Copyright © 2009 Pearson Education, Inc. Pivot Tables and Charts 091314Further Improvements to the Pivot Chart (Cont.) What else is missing from this image?Copyright © 2009 Pearson Education, Inc. Pivot Tables and Charts 091315 PivotChart Tools, Layout, Axis Titles:Further Improvements to the Pivot Chart (Cont.)Copyright © 2009 Pearson Education, Inc. Pivot Tables and Charts 091316Further Improvements to the Pivot Chart (Cont.) PivotChart Tools, Layout, Data Table:Copyright © 2009 Pearson Education, Inc. Pivot Tables and Charts 091317Further 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:Copyright © 2009 Pearson Education, Inc. Pivot Tables and Charts 091318Further Improvements to the Pivot Chart (Cont.) What is different about this chart?Copyright © 2009 Pearson Education, Inc. Pivot Tables and Charts 091319Excel 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 Powerpointpresentation. 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.Copyright © 2009 Pearson Education, Inc. Pivot Tables and Charts 091320 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 havingData Exploration ExampleCopyright © 2009 Pearson Education, Inc. Pivot Tables and Charts 091321Data Exploration Example (Cont.)Copyright © 2009 Pearson Education, Inc. Pivot Tables and Charts 091322Data Exploration Example