Introducing Pivot Tables and Pivot Charts 0) Introduction: 1) Another Database, Another Query: 2) Pivot Tables: 3) Pivot Charts: 4) Summary: 0) Introduction: In this lecture we take one final look at a database query, on a new database, and start looking at Pivot Tables (and Pivot Charts: the charts constructed from these table) on this database and another. Pivot tables also allow us to explore databases by catergorizing and condensing their information. 1) Another Database, Another Query: The NBA Excel worksheet for this lecture represents a database of all games played in the NBA in the 2001-2002 season. Each record in the database is stored in columns D-J. Each record is unique, because each Game # is guaranteed to be different from the others. The meaning of each field/column is shown on the right. The original data I retrieved did not have the HW or AW columns, which I added; I defined the name TeamName to refer to the cell range B9:B37, and the name TeamNumber to refer to refer to the cell range A9:A37 (which are used in various MATCH and LOOKUP functions). The user can enter two different teams names in cells A2 and B2 (which appear in green) so that we can find the records of these teams playing against each other (regardless of which team is home and which is away); each name must be in the TeamNameRange cell range, and be a different team from the other. I have used conditional formatting here to flip the cells to red if either constraint is not met (and if the team names are the same, cell C2 displays "Same Teams!" otherwise it is blank). The total number of wins for each team against the other (based on the queries in D1:E1 and G1:H1, which we will discuss in a minute) appear in cells A4 and B4. Here is the BOOLEAN expression that yields red conditional formating. =OR(ISERROR(MATCH($B$2,TeamName,0)),$A$2=$B$2) If we want to display all the records showing the results of these two teams met, we use the query in the range D1:E3, which specifies the Home and Away team numbers, which are looked up (e.g., =LOOKUP($A$2,TeamName,TeamNumber) in cell D2 and =LOOKUP($B$2,TeamName,TeamNumber) in cell E2, and their reverse in cells D3 and E3). So, Excel constructs the actual query based on information that we supply in different cells. -->Enter the name of two different teams in green cells A2 and B2 and perfrom --> and Advanced filter to see the records of the games they played -->Enter a word in green cell A2 that is not a team name; notice that that cell --> turns red, then undo the new entry -->Enter into green cell A2 the team name in cell B2; notice that both cells --> turn red, then undo the new entry We will need two queries to compute their statistics: one for the number of times the first team played AT HOME against the second team (and won) and another for the number of times the first team played AWAY against the second team (and won). We can reuse the query in cells D1:E2 to represent the first team playing AT HOME against the second team; and we can use the new query in cells G1:H2 to represent the first team playing AWAY against the second team. For the first team, we would like to sum of the HW column for the query in cells D1:E2 and add it to the sum the AW for the query in G1:H2. We can write this combined query adding together two calls to the DSUM function as =DSUM($D$6:$J$1266, "HW", $D$1:$E$2) + DSUM($D$6:$J$1266, "AW", $G$1:$H$2) Likewise, for the second team, we would like to sum of the AW column for the query in cell D1:E2 and add it to the sum the HW for the query in cell G1:H2. Notice we switch the HW and AW sums using the same queries. We can write this query adding together two calls to the DSUM function as =DSUM($D$6:$J$1266, "AW", $D$1:$E$2) + DSUM($D$6:$J$1266, "HW", $G$1:$H$2) Together these sum produces the required information. If we wanted, we could compute the total number of wins and loses for the first team against all other teams. The form would be the same, but the queries would use just D1:D2 (all home games) and H1:H2 (all away games) and use the appropriate "HW" or "AW" fields for summing. -->For the NBA worksheet, include queries and calls to DSUM to compute --> the total number of games won and lost by the first team 2) Pivot Tables: Last week we have learned various simple and complicated ways to analyze the information in databases: mostly by filtering the records to display, and by computing statistics on these records (or all the records in the database). In the rest of this lecture we will begin to learn about how to display databases in a much different format (as Pivot Tables) to gain more insight into the information that they contain. To start, let's look at the database on the Travel worksheet in the Excel file for this lecture. This data records the amount of money spent by travelers on vacations, according to their age and their gender. Note that there are many records that have the same age and gender values (e.g., autofilter 40 year-old males and you will see 10 records). This means that when collecting data, they found 10, 40 year-old males, and recorded how much money each spent on travel. Generally, pivot tables are useful when many of the records have the same values (these are called the dependent values, which we know before we collect the data), and we want to take every record with those values and summarize their independent values (the data that we collect). For example, below we will compute the average amount of money spent by travelers, catgorized by their age and gender. Imagine that we want to use this information to identify which travelers spend the most money, so that we can target them with some kind of mailing that advertises a special travel package. Here are the steps needed to construct a simple pivot table. (1) We first select the entire database (including the field labels). We do this, as we did last week, by selecting one cell in the database and pressing ctrl/a, which should select a rectangular range consisting of the entire database (which can be hundreds or thousands of records long). (2) Click thePivotTable icon in the Tables group on the Insert tab. For the pop-up window, named Create PivotTable... (a)... leave the "Select a table or range" radion button pressed. The Table/Range box should already be filled in with the database records we selected (we can reselecte the database here if necessary). (b) ...click the the "Existing worksheet" radio button, click inside the "Location" box, and then click a on the Travel Chart worksheet (say cell E2) that will become the upper-left corner of the pivot table. Then click OK. We can also click the "New Worksheet" radio button, in which case Excel will put the pivot table on a new worksheet that it creates, different from the one containing the database. (3) Right-click the Pivot Table and select Pivot Table Options... In the PivotTable Options, on the Display Tab, click (at the bottom of the third section) the checkbox for "Classic Pivot Table layout (enables dragging of fields in the grid)" and click OK. Excel will bring a Pivot Table to the selected location. It has three main areas to fill in: Rows (on the left), Columns (on the top), and Data (in the middle). There is also a "PivotTable Field List" on the far right, which has a top box containing a check list labeled by each of the column/field headings in the database, and four smaller boxes: Row Labels represents the "Drop Row Fields Here" rows in the Pivot table; Column Labels represents the "Drop Column Fields Here" columns in the Pivot table; Sigma Values represents the "Drop Data Items Here" body of the PivotChart. Also notice that that two new tabs appear on the ribbon (as happened when we added a chart): Options and Design -->Select the field labels and all the records in the data base and create --> a Pivot table starting in cell E16. -->We will eventual refine this Pivot table until it resembles the one already --> on this page. We can add each icon/column heading into the pivot chart in two ways (1) Select the Name in the icon/column heading in the "Choose fields to add to report"; drag/drop it into the appropriate box below. or (2) Select the Name in the icon/column heading in the "Choose fields to add to report"; drag/drop it into the pivot table, where it says Drop Row/Column Fields Here or Drop Data Item Here. If we make a mistake, we can select the icon/column heading from inside the PivotTable Field List box and either move it so the appropriate spot, or click its disclosure triangle and select the place to move it (or to remove it). So, it is very easy to "undo" a mistake (or said another way, it is very easy to experiment with pivot tables). If the "Pivot Table Field List" on the far right disappears, we can resummon it by right-clicking inside the pivot table and clicking Show Field List. Typically we put dependent information in the rows/columns (the information we use to classify the independent information) and put independent information (the measurements we have collected and categorized according to the dependent data) in the data items, summarizing these values: e.g., using sum, max, min, average, etc. to reduce all the measurements in these categories to a single value. -->For the Travel worksheet, drop the "Age" information into the rows, the --> "Gender" information into the columns, and the "Amount Spent on Travel" --> as the data item. Note that the after the names "Age" and "Gender" appear disclosure triangles, which when clicked show very similar information to autofilters, which apply to that row or clumn Note too that the title "Sum of Amount Spent on Travel" appears as a gray label on top of "Age" and to to the left of "Gender". When numeric data is used as a data item, Excel assumes we want to summarize this data by "summing" its values (for non-numeric values it assumes we want to count them). We will change this soon, because we don't want to know the "sum" of the amount spent by age and gender, but instead want to know the "average" amount. Pivot tables also summarize the data in rows and columns. The "Grand Total" at the bottom of the Gender F column is the sum of amounts spent by females of all ages (similarly for M). The "Grand Total" to the far right of the Age 25 row is the sum of amounts spent by all 25-year olds (females and males). We can change what summary is applied to this data in many ways (a) doubling clicking "Sum of Amount Spent on Travel" in the pivot table itself (b) right clicking "Sum of Amount Spent on Travel" in the pivot table itself aqnd clicking "Value Field Settings..." (c) clicking the disclosure triange for "Average of A..." in the Sigma Values part of the PIvotTable Field list. In the pop-up window named Value Field Setting pop-up, we can... (a) ...rename the label (by filling in the "Custom Name:" box) (b) ... choose the "Summarize by" tab and change how the data is summarized: choosing Sum, Count, Average, Max, Min, Product, Count Numbers, or four other options having to do with the statistical quantities standard deviation and variance. (c) ... choose the "Show values as" tab and change how the values are computed (mostly having to do with normal and percent); we can also click Number format here to format these values (or format them the more standard way by right-clicking their cell and clicking Format cells. -->For the Travel worksheet, change the Name to "Average Spent"; change it to --> be summarized by Average (not by Sum) and choose numbers whose formats --> show no decimal digits. --> Notice that the Grand Totals also change from sums to averages now, since we changed how the data is summarized. Notice that the ages are automatically grouped, from 25 to 65 in groups of 5. We can see all the individual data by right-clicking one of the age groupings (say, cell E5 and clicking Ungroup). Doing so shows the data collected for every age (just as it is shown for every gender). By right clicking on one of the individual ages and clicking Group, we can regroup the information specifing the extremal values and the number of values in every group. -->For the Travel worksheet, ungroup the data and then regroup it using the --> automatically selected start and end, grouping 5 ages together. Notice that the grand totals on the right are still also very close to each other. The variation for ages were mostly smoothed away by this grouping. Finally, if we right-click any cell inside the PivotTable and select "Show Details" Excel will create a new worksheet with all the data from the original database filtered (for the particular cell we clicked). We can further filter the data there, or remove the page if we don't want it any more; doing an undo remove the page, which requires the user to verify the removal. 3) Pivot Charts: Even with grouping, there is still a large amount of data here. So, let's put this data into chart form, by creating a Column chart to display its data. We can do so by clicking anywhere in the pivot table (selecting it) and then clicking on the Column icon in the Chart group, on the Inser tab. Excel automatically produces a chart of that chart type (we still have to use our intelligence to choose good type), and puts this chart on the same worksheet. Notice that the pop-up window named PivotChart Filter Pane allows us to select which ages and genders to display on the chart (and generally filter and sort them any way we want by using the disclosure triangles); if we change these values, they are similarly changed as well on the pivot table from which this pivot chart was constructed. Likewise, if we make a change on the pivot pivot table, its associated pivot chart will be similarly changed as well. If we deselect the PivotChart, the PivotChart Filter disappears; if we reslect it the PivotChart Filter returns. BUT, if we close the PivotChart Filter, it is a bit difficult to get back: to get it back we must select the PivotChart, click the Analyze tab under PivotChart Tools (look all the way on the right), and final click the PivotChart Filter in the Show/Hide group. So my bottom-line recommendations are don't delete the PivotChart Filter (which disappears anyway when the PivotChart is delselected) or put the icon to show it on the QAT for easy access. This is unlike the PivotTable FieldList, which we can delete and then recall by right-clicking the Pivot Table and selecting Show Field List. -->For the Travel worksheet, create a Column chart from the pivot table; change --> the Chart Type from Clustered Column to a Stacked Column. Notice that some interesting order emerges from the data on the chart. It shows that generally younger men and older women spend the most money on vacations. If we had limited resources for sending out mailings, these would be the two groups to target: younger men/older women (not the ages in between). If we want to put such a chart on its own page, we can select the chart, and then click the Move Chart icon in the Location group on the Design tab. We can select the New Sheet radio button, and name the worksheet as well, before clicking OK. Finally, it takes some cleverness to decide how to analyze information in databases to find patterns. In large databases with many fields it might not be obvious what information to use in a pivot table, but because pivot tables are so easy to construct, we can easily explore, creating many pivot tables/charts looking for interesting patterns. 4) Summary: Here is a quick summary of skills to acquire from working on this lecture. You should be able to discuss each of these topics a bit, but more importantly know HOW TO DO something in Excel, Know how to create a pivot table, and begin to understand of how to select the information and format that displays the information that we want to see in an easy to understand form. Know how to create a pivot chart from a pivot table.