Free MO-211 – Microsoft Office Specialist Practice Test Question

Reference / correct answer:

Step 1: Click cell I7, Shift-click cell J7, right-click in this area, and from the context menu select Format Cells.

Step 2: In the Format Cells dialog box select Category Accounting, change Decimal places to: 5, and click OK.

Step 3: Click cell I8, shift-click cell J8, right-click in this area, and from the context menu select Format Cells.

Step 4: In the Format Cells dialog box select Category Accounting, change Decimal places to: 5, set Symbol to: ¥ Japanese, and click OK.

Result will be like:

Reference / correct answer:

Step 1: Click cell I3.

Step 2: In the Home tab, click Conditional Formatting, and select New Rule.

Step 3: In the New Formatting Rule dialog box select Use a formula to determine which cells to format, in the Formal Values where this formula is true type: =TODAY()-I3>3, and then click the Format button.

Step 4: In the Format Cells dialog box click the Fill tab, click More Colors, in the Colors Dialog box click the Custom tab, change Green to 59, change Blue to 59, click OK, click OK, and finally click OK in the New Formatting Rule dialog box.

Reference / correct answer:

Step 1: Right-Click in cell I3, and select Insert comment from the context menu.

Step 2: Type: If red fill is present, update exchange rates

Step 3: Click cell I3.

You can test by hover above cell I3:

Reference / correct answer:

Step 1: Click cell E2, and click the Insert Function button.

Step 2: In the Insert Function dialog box select Category Lookup & Reference, select function LOOKUP, and click the OK button.

Step 3: In the Select Arguments dialog box make sure lookup, value, lookup vector, result vector is select and click OK.

Step 4: For Lookup_value click cell D2, for Lookup_vector select cells I12:K12, for Result vector cells I11:K11, and click the OK button.

Step 5: Click cell E2, click in cell reference I12 in the formula field, and press the F4 key (to make I12 an absolute reference.

.

Step 6: Make K12, I11 and K11 absolute references using F4 in the same way. Result will be:

Step 7: Click cell E2, and copy downwards to E44.

Result will be like:

Reference / correct answer:

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 worksheet click 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:

Reference / correct answer:

Step 1: In the name box type B5:I299 (or Click cell B5, shift-click cell I299).

Step 2: On the Home tab, select Conditional Formatting, and click New Rule

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.

Step 4: In the Format Cells dialog box select bold, Underline Single, and click OK.

Step 5: In The Edit Formatting Rule dialog box click Ok.

Reference / correct answer:

Step 1: On the Home tab select Conditional Formatting, and click Manage Rules.

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.

Step 3: Move the Red Text rule to the second place, Pink fill to the third. Will look like:

Step 4: Click OK.

Reference / correct answer:

Step 1: On the Home tab select Conditional Formatting, and click Manage Rules.

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.

Step 3: Change the formula to =$E5<"7:00:00" and click OK

Reference / correct 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.

Step 3: In the Create PivotChart dialog box make sure New Worksheet is select, and click OK.

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

Step 5: Select the fields Office and Name.

Step 6: Click-and-drag the Charge field to the Values area.

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.

Step 8: In the Value Field Settings box, select Average, click OK.

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.

Step 10: In the Value Field Settings box, select Count, click OK.

The result will look like:

Reference / correct answer:

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 6: Click on Date in the Pivot table, select 12-Apr-13, and click OK.