Question 6 of 12

Apply conditional formatting using a formula.

Cell range B5:I299 -

Apply if "Date" (column D) is a Saturday or Sunday

Function WEEKDAY (return MONDAY as 0)

Formatting Bold and Single Underline

Answer

Suggested Answer

Step 1: In the name box type B5:I299 (or Click cell B5, shift-click cell I299).
Exam 77-427: Question 6 - Image 1
Step 2: On the Home tab, select Conditional Formatting, and click New Rule
Exam 77-427: Question 6 - Image 2
Step 3: In The Edit Formatting Rule dialog box select Use a formula to determine which cells to format, type the formula: =WEEKDAY($D5,2)>5, and click the
Format button.
Exam 77-427: Question 6 - Image 3
Step 4: In the Format Cells dialog box select bold, Underline Single, and click OK.
Exam 77-427: Question 6 - Image 4
Step 5: In The Edit Formatting Rule dialog box click Ok. Use the following steps in explanation.
Question 7 of 12

Reorder all Conditional Formatting rules into a new order of execution.

"Monthy_Billing" WorksheetTop Rule (Bold and Underline)Second Rule (Red text)Third Rule (Pink Fill)Fourth Rule (Red Text with Strikethrough)

Answer

Suggested Answer

Step 1: On the Home tab select Conditional Formatting, and click Manage Rules.
Exam 77-427: Question 7 - Image 1
Step 2: In the Conditional Formatting Rules Manager, select Show formatting rules for: This worksheet, click on the Bold and Underline Rule, and move it to the top with the upward arrow.
Exam 77-427: Question 7 - Image 2
Step 3: Move the Red Text rule to the second place, Pink fill to the third.
Will look like:
Exam 77-427: Question 7 - Image 3
Step 3: Click OK. Use the following steps in explanation.
Question 8 of 12

Troubleshoot conditional formatting rule.

Fourth Rule (Red Text with Strikethrough)

Modify conditional formula to ensure formatting occurs if "Start" (Column E) is before 7:00 Am

Answer

Suggested Answer

Step 1: On the Home tab select Conditional Formatting, and click Manage Rules.
Exam 77-427: Question 8 - Image 1
Step 2: In the Conditional Formatting Rules Manager, select Show formatting rules for: This worksheet, click on the fourth rule Red Text with Strikethrough, and click Edit Rule.
Exam 77-427: Question 8 - Image 2
Step 3: Change the formula to =$E5<"7:00:00" and click OK
Exam 77-427: Question 8 - Image 3 Use the following steps in explanation.
Question 9 of 12

Create a PivotTable -

Cell range B4:I299 on Montly_Billing worksheetNew Worksheet Billing_Pivot ROWS: Office, Name VALUES: Sum of Charge, Average of Duration, Count of Duration

Answer

Suggested Answer

Step 1: In the name box type B4:I299 (or Click cell B4, shift-click cell I299).
Step 2: Click the INSERT tab, click the PivotChart button, and select PivotChart.
Exam 77-427: Question 9 - Image 1
Step 3: In the Create PivotChart dialog box make sure New Worksheet is select, and click OK.
Exam 77-427: Question 9 - Image 2
Step 4: Rename the worksheet by right-clicking on the sheet name (bottom left corner), select Rename from the context menu, and type: Billing_Pivot
Exam 77-427: Question 9 - Image 3
Step 5: Select the fields Office and Name.
Exam 77-427: Question 9 - Image 4
Step 6: Click-and-drag the Charge field to the Values area.
Exam 77-427: Question 9 - Image 5
Step 7: Click-and-drag the Duration field to the Values area, then click on the new Sum of Duration box, and select Value Field Settings.
Exam 77-427: Question 9 - Image 6
Step 8: In the Value Field Settings box, select Average, click OK.
Exam 77-427: Question 9 - Image 7
Step 9: Again click-and-drag the Duration field to the Values area, then click on the new Sum of Duration box, and select Value Field Settings.
Exam 77-427: Question 9 - Image 8
Step 10: In the Value Field Settings box, select Count, click OK.
Exam 77-427: Question 9 - Image 9
The result will look like:
Exam 77-427: Question 9 - Image 10
Exam 77-427: Question 9 - Image 11 Use the following steps in explanation.
Question 10 of 12

Filter the data in the PivotTable to show specific data.

PivotTable data -

"8-Apr-13"

"9-Apr-13"

"10-Apr-13"

"11-Apr-13"

"12-Apr-13"

Answer

Suggested Answer

Step 1: Drag the Date field to the Filters area.
Exam 77-427: Question 10 - Image 1
Step 2: Click on Date in the Pivot table, select 8-Apr-13, and click OK.
Exam 77-427: Question 10 - Image 2
Step 3: Click on Date in the Pivot table, select 9-Apr-13, and click OK.
Exam 77-427: Question 10 - Image 3
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. Use the following steps in explanation.