Microsoft MO-201 - Questions & Answers

1. Click the File tab.
2. Click Options.
3. Click Trust Center, and then click Trust Center Settings.
4. In the Trust Center, click Macro Settings.
5. Select the Disable all macros without notification option.
6. Click OK.

In the PivotTable, right-click a value and select Group.

In the Grouping box, select Starting at and Ending at checkboxes, and edit the values if needed. (0 and
200 respectively)

Under By, enter a number that specifies the interval for each group. (100)

Select OK.

1. Click anywhere in the table or PivotTable.

2. On the Home tab, go to Insert > Slicer.
3. In the Insert Slicers dialog box, select the check boxes for the fields you want to display, then select OK.
4. A slicer will be created for every field that you selected. Clicking any of the slicer buttons will automatically apply that filter to the linked table or PivotTable.

1. Select your data.
2. Click Insert > Insert Statistic Chart > Histogram.
3. Right-click the horizontal axis of the chart, click Format Axis, and then click Axis Options.

4. Use the information in the following table to decide which options you want to set in the Format Axis task pane.

The Developer tab isn't displayed by default, but you can add it to the ribbon.

On the File tab, go to Options > Customize Ribbon. Under Customize the Ribbon and under Main Tabs, select the Developer check box.

Record a macro 1. In the Code group on the Developer tab, click Record Macro.
2. Optionally, enter a name for the macro in the Macro name box and a description in the Description box, and then click OK to start recording.

3. Select Page layout from the Ribbon and expand Page Setup.
4. Select the Header/Footer tab and click Custom Header.
5. In the Left section, click the Insert Sheet Name button.
6. In the Right section, click the Insert Page Number button.
7. Click OK. Then click OK again.
8. On the Developer tab, click Stop Recording.

9. On the Developer tab, click Macros to view the available Macros.
10. Select the Macro and click Run.

1. Select the range of cells that has duplicate values you want to remove.

2. Click Data > Remove Duplicates.
3. Under Columns, check only the Products column.
4. Click OK.

In the PivotTable Field list, click on the field that you want to use as a Report Filter.
Drag the field into the Filters box.
On the worksheet, Excel adds the selected field to the top of the pivot table, with the item (All) showing.
The values in the pivot table do not change. Click the Report Filter's drop-down arrow, to see a list of items in the field.
Click on an item to select it, and click OK.

Select all value cells for First-Year and Second-Year Sales.
Drag the fill handle to the Fifth-Year Projections column.

1. Select the cell(s) you want to create a rule for.

2. Select Data >Data Validation.
3. On the Settings tab, under Allow, select an option:
Whole Number - to restrict the cell to accept only whole numbers. Decimal - to restrict the cell to accept only decimal numbers.
List - to pick data from the drop-down list.
Date - to restrict the cell to accept only date. Time - to restrict the cell to accept only time. Text Length - to restrict the length of the text.
Custom ?for custom formula.
4. Under Data, select a condition:
between
not between
equal to
not equal to
greater than
less than greater than or equal to less than or equal to
5. On the Settings tab, under Allow, select an option:
6. Set the other required values, based on what you chose for Allow and Data. For example, if you select between, then select the Minimum: and Maximum: values for the cell(s).
7. Select the Ignore blank checkbox if you want to ignore blank spaces.
8. If you want to add a Title and message for your rule, select the Input Message tab, and then type a title and input message.
9. Select the Show input message when cell is selected checkbox to display the message when the user selects or hovers over the selected cell(s).
10. Select OK.Now, if the user tries to enter a value that is not valid, a pop-up appears with the message, "This value doesn't match the data validation restrictions for this cell."

Click in Cell L5, and select Formulas from the ribbon. Click on the arrow of the AutoSum button in the Function Library section, and select Average.
Click on Cell H4, H5, H6, then press Enter.The formula should look like this:
=AVERAGE(H4,H5,H6)