Slide 1The World of Big DataWhat is a Pivot Table?Creating a Pivot TableCreating a Pivot Table (PC)Creating a Pivot Table (Mac)Pivot Table OptionsChanging the Value DisplayedExample Pivot TableFrom Tables to ChartsCreation of Pivot ChartsDefault Pivot Chart for Previous ExampleFurther Improvements to the Pivot ChartFurther Improvements to the Pivot Chart (Cont.)Further Improvements to the Pivot Chart (Cont.)Further Improvements to the Pivot Chart (Cont.)Further Improvements to the Pivot Chart (Cont.)Further Improvements to the Pivot Chart (Cont.)Further Improvements to the Pivot Chart (Cont.)Further Improvements to the Pivot Chart (Cont.)Excel and other Microsoft ProductsData Exploration ExampleData Exploration Example (Cont.)Data Exploration Example (Cont.)Data Exploration Example (Cont.)Data Exploration Example (Cont.)Data Exploration Example (Cont.)Data Exploration Example (Cont.)Data Exploration Example (Cont.)Data Exploration Example (Cont.)“Filtering” Out the OutliersData Exploration Example (Cont.)Data Exploration Example (Cont.)Pivot Chart OptionsAssignment (to count as quiz 2 grade)Assignment (Continued)Assignment (Continued)Assignment (Continued)Assignment (Continued)Pivot Tables and Charts 07151Pivot TablesAndPivot ChartsCopyright © 2009 Pearson Education, Inc. Pivot Tables and Charts 07152The World of Big DataCompanies 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 07153What 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 07154Creating a Pivot TableAny 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 07155Creating a Pivot Table (PC)Step 1 – Select all data in your tableThis can be done by hitting Control-AStep 2 – Create the pivot tableClick the INSERT tab and then Pivot TableCopyright © 2009 Pearson Education, Inc. Pivot Tables and Charts 07156Step 1 – Select all data in your tableThis can be done by hitting Command-AStep 2 – Create the pivot tableUnder “Data” select PivotTableCreating a Pivot Table (Mac)orNOTE: Other than this page, all of the images and instructions in this presentation are from Excel 2013 (which is a PC version of Excel)Copyright © 2009 Pearson Education, Inc. Pivot Tables and Charts 07157Pivot Table OptionsThe pivot table has four general options.FILTERS– Allows you to remove any results from the table that you choose.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.Copyright © 2009 Pearson Education, Inc. Pivot Tables and Charts 07158Changing the Value DisplayedIt 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 07159Example Pivot TableThe 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?Copyright © 2009 Pearson Education, Inc. Pivot Tables and Charts 071510From Tables to ChartsThe 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 ChartCopyright © 2009 Pearson Education, Inc. Pivot Tables and Charts 071511Creation of Pivot ChartsWhatever 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 071512Default Pivot Chart for Previous ExampleCan we improve the look of this image?Copyright © 2009 Pearson Education, Inc. Pivot Tables and Charts 071513Further Improvements to the Pivot ChartThere are many options to improve your pivot chart.Let’s explore some of the basic options to improve your pivot table.Copyright © 2009 Pearson Education, Inc. Pivot Tables and Charts 071514Click 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.Further Improvements to the Pivot Chart (Cont.)Copyright © 2009 Pearson Education, Inc. Pivot Tables and Charts 071515Further Improvements to the Pivot Chart (Cont.)What other improvements can we make?Copyright © 2009 Pearson Education, Inc. Pivot Tables and Charts 071516Further Improvements to the Pivot Chart (Cont.)What else could we add?Copyright © 2009 Pearson Education, Inc. Pivot Tables and Charts 071517Further Improvements to the Pivot Chart (Cont.)Copyright © 2009 Pearson Education, Inc. Pivot Tables and Charts 071518The 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
View Full Document