Home Insert Draw Page Layout Formulas Data Review View Tell me Insert Calibri (Body) v 12 VAA ap Wrap Text v Accounting Delete Paste BI U V Ov Av = E = Merge & Center v $ ~ % " 00 00 Conditional Format Cell Formatting as Table Styles Forma H8 X V fx A B C D E F G H J K L M N P Q R S Year Month Day Job No. Type Category Quantity Unit Cost Item Cost W NH 2021 10 6500 Material X1 2.1 Use this data for creating a Table and then for creating a PivotTable. 2021 10 6501 Machine T2 7.9 When you create your PivotTables be sure to place the PivotTable somewhere 2021 10 6501 Machine T3 14.6 on the Pivot 2 tab. See figure below for how to do this. 2021 10 6502 Material X3 5.7 This is important to make sure you do not over-write your data and also 2021 10 6503 Labor Normal 13.75 to make it easier for me to look at your work. 2021 10 6503 Machine T3 3.6 2021 10 6503 Material X1 3.4 Create PivotTable 2021 10 6503 Material X2 3.4 Choose the data that you want to analyze. 10 2021 10 6504 Machine T1 11.05 Select a table or range 11 2021 10 6505 Machine T1 6. Table/Range: Data!$A$1:$F$501 Use an external data source 12 2021 10 6505 Machine T1 6.45 2021 6506 Labor Skilled Choose Connection... No data fields have been retrieved. 13 10 3.25 14 2021 10 6506 Machine T2 15.5 Choose where to place the PivotTable 15 2021 10 6506 Material X1 4.4 New worksheet . Existing worksheet 16 2021 10 6506 Material X1 15.8 Table/Range: Pivot!$A$2 17 2021 10 6508 Machine T2 14.95 Cancel OK 18 2021 10 6508 Machine T3 19.5 19 2021 10 6508 Material X1 5.5 20 2021 10 6509 Labor Normal 10.25 Use the blue area to create a look-up table that 21 2021 10 6513 Labor Normal 11.25 includes the unit costs for the various inputs 22 2021 10 6513 Labor Normal 5.75 Machine T1 23 2021 10 6516 Labor Normal 10 T2 Once you have this table completed add a column to 24 2021 10 6516 Labor Normal 7.75 T3 Data set called Unit Cost. In this column use the 25 2021 10 6516 Machine T1 10.8 Material X1 function XLOOKUP to find the unit cost for each 26 2021 10 6516 Material X1 4 X2 sub-category. 27 2021 10 6516 Material X1 14.6 X3 28 2021 10 6517 Machine T1 10.6 Labor Normal Next add another column to the Data set called 29 2021 10 6519 Labor Normal 8.5 Skilled Item cost. This is the Qnt . Unit Cost for each record 30 2021 10 6521 Machine T3 10.9 31 2021 10 6521 Material X3 8.6 You should complete this step before creating your 32 2021 10 6522 Material X3 5.5 PivotTable