How to Easily Pass the Microsoft 77-427 Exam: Expert Advice

Step 1 (filter on the C column): Click somewhere in B4:I299 area such as in cell C4, click on the Data tab, and then the Filter button.

Step 2:Click on the Name header in column C, deselect Select all, select Stratsmaa, and click OK.

Step 3: Select cells D4 to D299 (D column – the date column), then hold CTRL key and select cells H4:I299 (H and I columns, the duration and charge columns).

Step 4: Click the Insert tab, click the Insert Combo chart button, and select Clustered Column – Line on Secondary axis.

Step 5 (to move the chart to a separate new worksheet): Right-Click the diagram, and select Move Chart from the Context menu.

Step 6: In the Move Chart dialog box select New Sheet, type the name: Straatsma_Chart, and click OK.

Step7 (Primary Axis Bounds):Click on the Diagram, select the Format tab, in the Charts Element box select Vertical (Value) Axis.

Step8:Click the Format Selection button, and change the AXIS options Bounds Minimum to 0.0 and maximum to 8.0.

Step 9 (Secondary Axis Bounds): Click on the Diagram, select the Format tab, in the Charts Element box select Vertical (Value) Axis, and click Format Selection.

Step 10: Change theMinimum and Maximumboundaries to2000 and 4000 respectively.

Step 1:Click cell I5, click the Insert Function button, select category Lookup & Reference, select VLOOKUP, and click OK.

Step 2:In the Functions arguments dialog box, for Lookup Value click cell C5, for Table_Array switch to Hourly_rates worksheetclick Cell B2, shift-click cell C29.

Step 3:For Col_index_num type 2, for Range_lookup select FALSE, and click OK.

Step 4:Edit the Formula by typing *5 in the formula box, and change the B2 and C29 references to absolute references by clicking them and using the F4 key.

Step 5:Copy the formula in cell I5 downward to cell I299.

Will look like:

Step 1:Drag the Date field to the Filters area.

Step 2:Click on Date in the Pivot table, select 8-Apr-13, and click OK.

Step 3:Click on Date in the Pivot table, select 9-Apr-13, and click OK.

Step 4:Click on Date in the Pivot table, select 10-Apr-13, and click OK.

Step 5:Click on Date in the Pivot table, select 11-Apr-13, and click OK.

Step 5: Click on Date in the Pivot table, select 12-Apr-13, and click OK.