Spreadsheet: 882363d1730732481-looking-up-lots-of-data-at-once-tfa-pivottable
Sheet: Sheet1

LocationReference1 | (All)
ProductTypeName1 | ProductCode1 | [NAME] | Sum of Quantity1 | Sum of Average Price | Sum of NettTotal1
Bacon | 20038 | Semi Skimmed Milk 2L | 1 | 1.95 | 1.95 |  | 1) Convert source data into an Excel Table, so PivotTable may auto-expand when new data is added
 | 20038 Total |  | 1 | 1.95 | 1.95 |  | 2) Remove "£" (use Find and Replace) from Quantity1 and NetTotal1 to convert the text strings to numbers
Bacon Total |  |  | 1 | 1.95 | 1.95 |  | 3) Average Price is a Calculated Field = NetTotal1/Quantity1
Bread | 12056 | [NAME] | 38 | 0.65 | 24.7
 | 12056 Total |  | 38 | 0.65 | 24.7
 | 11190 | Semi Skimmed Milk 2L | 8 | 1.95 | 15.6
 |  | Glass Full 1 Pint | 11 | 0.65 | 7.15
 | 11190 Total |  | 19 | 1.197368421052632 | 22.75
 | 12063 | [NAME] | 23 | 0.65 | 14.95
 | 12063 Total |  | 23 | 0.65 | 14.95
Bread Total |  |  | 80 | 0.78 | 62.40000000000001
Cakes, Biscuits & Confectionery | TYCBB | Semi Skimmed Milk 2L | 13 | 1.95 | 25.35
 | TYCBB      Total |  | 13 | 1.95 | 25.35
 | TYCWC | Semi Skimmed Milk 2L | 8 | 1.95 | 15.6
 | [NAME] |  | 8 | 1.95 | 15.6
Cakes, Biscuits & Confectionery Total |  |  | 21 | 1.95 | 40.95
Cold Drinks | 577 | Semi Skimmed Milk 2L | 7 | 1.95 | 13.65
 | 577 Total |  | 7 | 1.95 | 13.65
Cold Drinks Total |  |  | 7 | 1.95 | 13.65
Dairy Alternatives | 2034 | Semi Skimmed Milk 2L | 13 | 1.95 | 25.35
 |  | [NAME] | 22 | 0.65 | 14.3
 | 2034 Total |  | 35 | 1.132857142857143 | 39.65000000000001
 | 2030 | [NAME] | 40 | 0.65 | 26
 | 2030 Total |  | 40 | 0.65 | 26
 | 2032 | Full Cream Milk 2L | 3 | 1.95 | 5.85
 | 2032 Total |  | 3 | 1.95 | 5.85
Dairy Alternatives Total |  |  | 78 | 0.9166666666666666 | 71.5
Dairy Products | 20044 | [NAME] | 40 | 0.65 | 26
 | 20044 Total |  | 40 | 0.65 | 26
 | 20040 | Semi Skimmed Milk 2L | 5 | 1.95 | 9.75
 |  | Semi Skimmed Milk 1L | 7 | 1.03 | 7.21
 |  | Full Cream Milk 1 Pint | 1 | 0.65 | 0.65
 | 20040 Total |  | 13 | 1.354615384615385 | 17.61
 | 20043 | Semi Skimmed Milk 2L | 4 | 1.95 | 7.8
 | 20043 Total |  | 4 | 1.95 | 7.8
 | 20042 | Full Cream Milk 2L | 2 | 1.95 | 3.9
 | 20042 Total |  | 2 | 1.95 | 3.9
 | W2L | Semi Skimmed Milk 1L | 1 | 1.03 | 1.03
 | W2L        Total |  | 1 | 1.03 | 1.03
Dairy Products Total |  |  | 60 | 0.9390000000000001 | 56.34
Eggs | 27224 | Semi Skimmed Milk 2L | 17 | 1.95 | 33.15
 |  | Full Cream Milk 2L | 2 | 1.95 | 3.9
 | 27224 Total |  | 19 | 1.95 | 37.05
Eggs Total |  |  | 19 | 1.95 | 37.05
Fruit & Veg | 27180 | Semi Skimmed Milk 2L | 13 | 1.95 | 25.35
 | 27180 Total |  | 13 | 1.95 | 25.35
 | 27184 | Semi Skimmed Milk 2L | 13 | 1.95 | 25.35
 | 27184 Total |  | 13 | 1.95 | 25.35
 | 27196 | Semi Skimmed Milk 2L | 12 | 1.95 | 23.4
 | 27196 Total |  | 12 | 1.95 | 23.4
 | 27191 | Semi Skimmed Milk 2L | 12 | 1.95 | 23.4
 | 27191 Total |  | 12 | 1.95 | 23.4
 | 27182 | Semi Skimmed Milk 2L | 8 | 1.95 | 15.6
 | 27182 Total |  | 8 | 1.95 | 15.6
 | 27189 | Semi Skimmed Milk 2L | 8 | 1.95 | 15.6
 | 27189 Total |  | 8 | 1.95 | 15.6
 | 27193 | [NAME] | 21 | 0.65 | 13.65
 | 27193 Total |  | 21 | 0.65 | 13.65
 | 27195 | Semi Skimmed Milk 2L | 7 | 1.95 | 13.65
 | 27195 Total |  | 7 | 1.95 | 13.65
Fruit & Veg Total |  |  | 94 | 1.659574468085107 | 156
Fruit Juices | 3541 | Semi Skimmed Milk 2L | 5 | 1.95 | 9.75
 |  | Full Cream Milk 2L | 4 | 1.95 | 7.8
 | 3541 Total |  | 9 | 1.95 | 17.55
 | 3539 | Full Cream Milk 2L | 4 | 1.95 | 7.8
 |  | Semi Skimmed Milk 2L | 3 | 1.95 | 5.85
 |  | Full Cream Milk 1 Pint | 1 | 0.65 | 0.65
 | 3539 Total |  | 8 | 1.7875 | 14.3
 | 3540 | Full Cream Milk 2L | 3 | 1.95 | 5.85
 | 3540 Total |  | 3 | 1.95 | 5.85
Fruit Juices Total |  |  | 20 | 1.885 | 37.7
Milk | 214 | Semi Skimmed Milk 2L | 124 | 1.95 | 241.8
 |  | Semi Skimmed Milk 1L | 70 | 1.03 | 72.1
 |  | Full Cream Milk 2L | 29 | 1.95 | 56.55
 |  | [NAME] | 21 | 0.65 | 13.65
 |  | Semi Skimmed Milk 189ml | 48 | 0.28 | 13.44
 |  | Glass Full 1 Pint | 12 | 0.65 | 7.8
 |  | Semi Skimmed Milk 1Pint | 6 | 0.65 | 3.9
 |  | Full Cream Milk 1 Pint | 3 | 0.65 | 1.95
 | 214 Total |  | 313 | 1.31370607028754 | 411.19
 | M6 | Semi Skimmed Milk 2L | 106 | 1.978301886792452 | 209.7
 |  | Full Cream Milk 2L | 17 | 1.95 | 33.15
 |  | Semi Skimmed Milk 189ml | 72 | 0.28 | 20.16
 |  | Full Cream Milk 1L | 16 | 1.03 | 16.48
 |  | Glass Full 1 Pint | 25 | 0.65 | 16.25
 |  | Semi Skimmed Milk 1L | 6 | 1.03 | 6.180000000000001
 |  | Full Cream Milk 1 Pint | 2 | 0.65 | 1.3
 | M6         Total |  | 244 | 1.242704918032786 | 303.22
 | M3 | Semi Skimmed Milk 2L | 78 | 1.95 | 152.1
 |  | Semi Skimmed Milk 1Pint | 72 | 0.6499999999999999 | 46.8
 |  | Semi Skimmed Milk 189ml | 72 | 0.28 | 20.16
 |  | Full Cream Milk 2L | 2 | 1.95 | 3.9
 |  | Full Cream Milk 1L | 2 | 1.03 | 2.06
 |  | Semi Skimmed Milk 1L | 2 | 1.03 | 2.06
 | M3         Total |  | 228 | 0.9959649122807014 | 227.08
 | M5 | Semi Skimmed Milk 2L | 66 | 1.959090909090909 | 129.3
 |  | Semi Skimmed Milk 189ml | 96 | 0.28 | 26.88
 |  | [NAME] | 41 | 0.6499999999999999 | 26.65
 |  | Glass Full 1 Pint | 36 | 0.6499999999999999 | 23.4
 |  | Full Cream Milk 1L | 6 | 1.03 | 6.18
 |  | Full Cream Milk 2L | 3 | 1.95 | 5.85
 |  | Semi Skimmed Milk 1L | 4 | 1.03 | 4.12
 |  | Semi Skimmed Milk 1Pint | 1 | 0.65 | 0.65
 | M5         Total |  | 253 | 0.8815415019762843 | 223.03
 | M4 | Semi Skimmed Milk 2L | 57 | 1.960526315789474 | 111.75
 |  | Semi Skimmed Milk 189ml | 72 | 0.28 | 20.16
 |  | Full Cream Milk 1L | 6 | 1.03 | 6.18
 |  | Semi Skimmed Milk 1L | 1 | 1.03 | 1.03
 | M4         Total |  | 136 | 1.022941176470588 | 139.12
 | M2 | Semi Skimmed Milk 2L | 17 | 1.985294117647059 | 33.75
 |  | Semi Skimmed Milk 189ml | 48 | 0.28 | 13.44
 |  | Glass Full 1 Pint | 11 | 0.65 | 7.15
 | M2         Total |  | 76 | 0.7150000000000001 | 54.34
 | 213 | Semi Skimmed Milk 2L | 10 | 1.95 | 19.5
 |  | Full Cream Milk 2L | 4 | 1.95 | 7.8
 |  | Semi Skimmed Milk 1Pint | 2 | 0.65 | 1.3
 |  | Full Cream Milk 1 Pint | 1 | 0.65 | 0.65
 | 213 Total |  | 17 | 1.720588235294118 | 29.25
 | M1 | Semi Skimmed Milk 2L | 5 | 2.07 | 10.35
 |  | Full Cream Milk 2L | 4 | 1.95 | 7.8
 |  | Full Cream Milk 1L | 2 | 1.03 | 2.06
 | M1         Total |  | 11 | 1.837272727272727 | 20.21
 | 215 | Semi Skimmed Milk 2L | 5 | 1.95 | 9.75
 | 215 Total |  | 5 | 1.95 | 9.75
 | M4A | Semi Skimmed Milk 2L | 3 | 1.95 | 5.85
 | M4A        Total |  | 3 | 1.95 | 5.85
 | M9 | Semi Skimmed Milk 2L | 1 | 1.95 | 1.95
 | M9         Total |  | 1 | 1.95 | 1.95
Milk Total |  |  | 1287 | 1.107218337218337 | 1424.99
Organic Milk | 220 | Semi Skimmed Milk 2L | 9 | 1.95 | 17.55
 |  | Full Cream Milk 2L | 4 | 1.95 | 7.8
 | 220 Total |  | 13 | 1.95 | 25.35
 | OM2 | Semi Skimmed Milk 2L | 8 | 1.95 | 15.6
 | OM2        Total |  | 8 | 1.95 | 15.6
 | OM6 | Semi Skimmed Milk 2L | 3 | 1.95 | 5.85
 | OM6        Total |  | 3 | 1.95 | 5.85
 | 219 | Full Cream Milk 2L | 3 | 1.95 | 5.85
 | 219 Total |  | 3 | 1.95 | 5.85
 | OM5 | Semi Skimmed Milk 2L | 2 | 1.95 | 3.9
 | OM5        Total |  | 2 | 1.95 | 3.9
 | OM3 | Semi Skimmed Milk 2L | 1 | 1.95 | 1.95
 | OM3        Total |  | 1 | 1.95 | 1.95
Organic Milk Total |  |  | 30 | 1.95 | 58.5
Welsh Traditional | 19018 | Semi Skimmed Milk 2L | 1 | 1.95 | 1.95
 | 19018 Total |  | 1 | 1.95 | 1.95
Welsh Traditional Total |  |  | 1 | 1.95 | 1.95
Grand Total |  |  | 1698 | 1.15605418138987 | 1962.98