Excel Case 2
Complete the pivot table on the Pivot Tables cont. tab in the Advanced Excel Workshop spreadsheet. Complete the Basic Exercise: Recreate the Pivot Table/Chart with all features included (including a State filter). This time, summarize on Vehicle Class. When summarized in the pivot, make sure these numbers are sorted from lowest to highest relativity. What is the data telling you?
M20 A xv fx A B D E F G H 1 J L 1 PIVOT TABLES 2 State (All) Automobile insurance customer information 3 Data from: http://www.scan-support.com/help/sample-data-sets 4 Coverage Type - Total Claim Amount # of Policies Claim Amt per Policy Claim Amt Per Policy Relativity 5 Basic 2,110,474 5,568 379 87.32% 6 Extended 1,317,747 2,742 481 110.71% Report Features: 7 Premium 536,746 824 651 150.06% White background 8 8 Grand Total 3,964,967 9,134 434 100.00% Pivot table columns do not auto fit when filter is updated 9 Column Headers are clean names/make sense 10 Numbers are formatted with column and no decimals, except the relativity which has 2 decimals 11 Claim Amount/Policy Based on Coverage Type Total Claims/Policy is a calculated field 12 Total Claims/Policy Relativity is a calculated field and shown as a percentage of the total 13 160.00% Line Chart showing the relativity in relation to the variable of interest 14 140.00% 15 120.00% 16 17 100.00% 18 80.00% 19 20 60.00% 21 40.00% 22 20.00% 23 24 0.00% Basic Extended Premium 25 26 27 28 29 30 Exercise - Basic 31 Recreate the above Pivot Table/Chart with all features included. 32 This time, summarize on Vehicle Class. When summarized in the pivot, make sure these numbers are sorted from lowest to highest. 33 What is the data telling you? 34 35 Exercise - Advanced 36 Recreate the above Pivot Table/Chart with all features included. 37 This time, create a new column in the Raw Data tab that groups Monthly Premium Auto into different rounded buckets based on 50s (0,50,100,...,300). When summarized in the pivot, make sure these numbers are sorted from lowest to highest 38 What is the data telling you? 39 anner Divot Table 39 40 Insert Pivot Table 41 42 FILE HOME INSERT 43 44 45 PivotTable Recommended Table 46 PivotTables 47 Tables 48 ? X 49 Pick Data Source 50 Create PivotTable 51 52 Choose the data that you want to analyze 53 Select a table or range 54 Table/Range: 'Raw Data'!$A$1:$X$9135 55 Use an external data source 56 Choose Connection... 57 Connection name: 59 Choose where you want the PivotTable report to be placed 60 New Worksheet 61 Existing Worksheet 62 Location: 63 Choose whether you want to analyze multiple tables 64 Add this data to the Data Model 65 66 OK 67 58 Cancel PivotTable Fields 68 69 70 71 72 73 74 75 Choose fields to add to report Filters These are on top of pivot table and allow you to filter the data based on different values of a variable Columns Lets you look at different levels of a variable by column Rows Le you look at different levels of a variable by row Values the data you are analyzing. You can sum, count, average, etc. Value Field Settings Source Name: Claim Amount per Policy Custom Name: Total Claims Per Polic Summarize Values By Show Values AS Summarize value field by Choose the type of calculation that you want to use to summarize data from the selected field Sum Count Average Max Min Product Employment Status Gender Income Location Code Marital Status Monthly Premium Auto Months Since Last Claim Months Since Policy Inception Number of Open Complaints Number of Policies Policy Type Policy Renew Offer Type Sales Channel Total Clair Amount Vehicle Class Vehicle Size CLTV per Policy CLTV per Policy Claim Amount per Policy MORE TABLES. Number Format OK 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 Cancel Drag fields between areas below: Y FILTERS State Ill COLUMNS Values ROWS Coverage VALUES Total Claims # of Policies Total Claims.. Total Claims... 1on D 111 112 Filtering 113 If you have a variable in the row or column you can still filter on certain levels of the variable by clicking on the little box on the lower right hand corner 114 115 Search 116 All 117 Arizona 118 California 119 Nevada 120 Oregon 121 Washington 122 123 124 125 126 Select Multiple Items 127 OK Cancel 128 129 130 131 Calculated Fields within pivot table 132 ANALYZE DESIGN Insert Calculated field 133 134 Name: Claim Amount per Policy 135 Refresh Change Data Clear Select Move Fields, Items, OLAP Re 136 Formula: - Total Claim Amount'/ Number of Policies Source Pivot Table & Sets Tools 137 Data Actions 138 Calculated field.. Fields: 139 Renew Offer Type Calculated Item... Sales Channel 140 Total Claim Amount Solve Order... 141 Vehicle Class 142 Fr List Formulas Vehicle Size CLTV per Policy 143 CLTV per Policy Create Set Based on Bow Items... Claim Amount per Policy 144 Create Set Based on Column Items... 145 Insert Field 146 Show Values As Manage Sets OK 147 You can change the value variable to display as a percentage of a row or column. 148 ? X Modify Delete ons Close 146 Show Values As Manage Sets 147 You can change the value variable to display as a percentage of a row or column. 148 149 Value Field Settings 150 151 Source Name: Claim Amount per Policy 152 Custom Name: Total Claims Per Policy 153 Summarize Values By Show Values As 154 155 Show values as 156 No Calculation 157 Base field: Base items 158 Customer 159 State Customer Lifetime Value 160 Response 161 Education 162 163 164 Number Format OK Cancel 165 166 167 168 Field settings - Layout and Print 169 Show items with no data 170 171 Field Settings ? x 172 Source Name: Coverage 173 Custom Name: Coverage 174 175 Subtotals & Filters Layout & Print 176 Layout 177 178 Shaw item labels in outline form 179 Display labels from the next field in the same column (compact form) 180 Display subtotals at the top of each group 181 Showitem labels in tabular form 182 Repeat item labels 183 Insert blank line after each item label 184 Showitems with no data 185 186 187 Print 188 Insert page break after each item 189 190 OK Cancel 191 102 193 194 Fix the column width when filters change 195 Go to PivotTable Options and uncheck Autofit column widths on update 196 197 PivotTable Options ? 198 199 PivotTable Name: PivotTablet 200 Layout & Format Totals & Filters Display Printing Data Alt Text 201 202 Layout 203 Merge and center cells with labels 204 When in compact form indent row labels: 1 characteris) 205 206 Display fields in report filter area: Down, Then Over 207 Report filter fields per column: 0 208 Format 209 210 For error values show: 211 For empty cells show: 212 213 Autofit column widths on update 214 Preserve cell formatting on update 215 216 217 218 219 220 OK Cancel 221 222 223 M20 A xv fx A B D E F G H 1 J L 1 PIVOT TABLES 2 State (All) Automobile insurance customer information 3 Data from: http://www.scan-support.com/help/sample-data-sets 4 Coverage Type - Total Claim Amount # of Policies Claim Amt per Policy Claim Amt Per Policy Relativity 5 Basic 2,110,474 5,568 379 87.32% 6 Extended 1,317,747 2,742 481 110.71% Report Features: 7 Premium 536,746 824 651 150.06% White background 8 8 Grand Total 3,964,967 9,134 434 100.00% Pivot table columns do not auto fit when filter is updated 9 Column Headers are clean names/make sense 10 Numbers are formatted with column and no decimals, except the relativity which has 2 decimals 11 Claim Amount/Policy Based on Coverage Type Total Claims/Policy is a calculated field 12 Total Claims/Policy Relativity is a calculated field and shown as a percentage of the total 13 160.00% Line Chart showing the relativity in relation to the variable of interest 14 140.00% 15 120.00% 16 17 100.00% 18 80.00% 19 20 60.00% 21 40.00% 22 20.00% 23 24 0.00% Basic Extended Premium 25 26 27 28 29 30 Exercise - Basic 31 Recreate the above Pivot Table/Chart with all features included. 32 This time, summarize on Vehicle Class. When summarized in the pivot, make sure these numbers are sorted from lowest to highest. 33 What is the data telling you? 34 35 Exercise - Advanced 36 Recreate the above Pivot Table/Chart with all features included. 37 This time, create a new column in the Raw Data tab that groups Monthly Premium Auto into different rounded buckets based on 50s (0,50,100,...,300). When summarized in the pivot, make sure these numbers are sorted from lowest to highest 38 What is the data telling you? 39 anner Divot Table 39 40 Insert Pivot Table 41 42 FILE HOME INSERT 43 44 45 PivotTable Recommended Table 46 PivotTables 47 Tables 48 ? X 49 Pick Data Source 50 Create PivotTable 51 52 Choose the data that you want to analyze 53 Select a table or range 54 Table/Range: 'Raw Data'!$A$1:$X$9135 55 Use an external data source 56 Choose Connection... 57 Connection name: 59 Choose where you want the PivotTable report to be placed 60 New Worksheet 61 Existing Worksheet 62 Location: 63 Choose whether you want to analyze multiple tables 64 Add this data to the Data Model 65 66 OK 67 58 Cancel PivotTable Fields 68 69 70 71 72 73 74 75 Choose fields to add to report Filters These are on top of pivot table and allow you to filter the data based on different values of a variable Columns Lets you look at different levels of a variable by column Rows Le you look at different levels of a variable by row Values the data you are analyzing. You can sum, count, average, etc. Value Field Settings Source Name: Claim Amount per Policy Custom Name: Total Claims Per Polic Summarize Values By Show Values AS Summarize value field by Choose the type of calculation that you want to use to summarize data from the selected field Sum Count Average Max Min Product Employment Status Gender Income Location Code Marital Status Monthly Premium Auto Months Since Last Claim Months Since Policy Inception Number of Open Complaints Number of Policies Policy Type Policy Renew Offer Type Sales Channel Total Clair Amount Vehicle Class Vehicle Size CLTV per Policy CLTV per Policy Claim Amount per Policy MORE TABLES. Number Format OK 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 Cancel Drag fields between areas below: Y FILTERS State Ill COLUMNS Values ROWS Coverage VALUES Total Claims # of Policies Total Claims.. Total Claims... 1on D 111 112 Filtering 113 If you have a variable in the row or column you can still filter on certain levels of the variable by clicking on the little box on the lower right hand corner 114 115 Search 116 All 117 Arizona 118 California 119 Nevada 120 Oregon 121 Washington 122 123 124 125 126 Select Multiple Items 127 OK Cancel 128 129 130 131 Calculated Fields within pivot table 132 ANALYZE DESIGN Insert Calculated field 133 134 Name: Claim Amount per Policy 135 Refresh Change Data Clear Select Move Fields, Items, OLAP Re 136 Formula: - Total Claim Amount'/ Number of Policies Source Pivot Table & Sets Tools 137 Data Actions 138 Calculated field.. Fields: 139 Renew Offer Type Calculated Item... Sales Channel 140 Total Claim Amount Solve Order... 141 Vehicle Class 142 Fr List Formulas Vehicle Size CLTV per Policy 143 CLTV per Policy Create Set Based on Bow Items... Claim Amount per Policy 144 Create Set Based on Column Items... 145 Insert Field 146 Show Values As Manage Sets OK 147 You can change the value variable to display as a percentage of a row or column. 148 ? X Modify Delete ons Close 146 Show Values As Manage Sets 147 You can change the value variable to display as a percentage of a row or column. 148 149 Value Field Settings 150 151 Source Name: Claim Amount per Policy 152 Custom Name: Total Claims Per Policy 153 Summarize Values By Show Values As 154 155 Show values as 156 No Calculation 157 Base field: Base items 158 Customer 159 State Customer Lifetime Value 160 Response 161 Education 162 163 164 Number Format OK Cancel 165 166 167 168 Field settings - Layout and Print 169 Show items with no data 170 171 Field Settings ? x 172 Source Name: Coverage 173 Custom Name: Coverage 174 175 Subtotals & Filters Layout & Print 176 Layout 177 178 Shaw item labels in outline form 179 Display labels from the next field in the same column (compact form) 180 Display subtotals at the top of each group 181 Showitem labels in tabular form 182 Repeat item labels 183 Insert blank line after each item label 184 Showitems with no data 185 186 187 Print 188 Insert page break after each item 189 190 OK Cancel 191 102 193 194 Fix the column width when filters change 195 Go to PivotTable Options and uncheck Autofit column widths on update 196 197 PivotTable Options ? 198 199 PivotTable Name: PivotTablet 200 Layout & Format Totals & Filters Display Printing Data Alt Text 201 202 Layout 203 Merge and center cells with labels 204 When in compact form indent row labels: 1 characteris) 205 206 Display fields in report filter area: Down, Then Over 207 Report filter fields per column: 0 208 Format 209 210 For error values show: 211 For empty cells show: 212 213 Autofit column widths on update 214 Preserve cell formatting on update 215 216 217 218 219 220 OK Cancel 221 222 223
