Create and apply a new conditional formatting rule for the selected cells D2:D15. Apply the default icon set Three traffic lights (unrimmed) icon set, but show only the icon, not the cell value. Change the values so the green circle icon (the first icon) will be applied if the cell value is >= 90 percent and the yellow circle icon (the second icon) will be applied if the cell value is <90 and >=10 percent.
answer
In the Home Ribbon Tab in the Styles Ribbon Group, you clicked the Conditional Formatting button. In the Conditional Formatting menu, you clicked the New Rule... menu item. Inside the New Formatting Rule dialog in the Format Style drop-down, you selected Icon Sets. Inside the New Formatting Rule dialog, you checked the Show Icon Only check box, typed 90 in the first icon value input, typed 10 in the second icon value input, and clicked the OK button.
question
Create and apply a new conditional formatting rule for the selected cells C2:C15 to apply bold font formatting to only cells that are equal to or below the average for the selected range.
answer
In the Home Ribbon Tab in the Styles Ribbon Group, you clicked the Conditional Formatting button. In the Conditional Formatting menu, you clicked the New Rule... menu item. Inside the New Formatting Rule dialog from the Select a Rule Type list, you clicked the Format only values that are above or below average list item. Inside the New Formatting Rule dialog in the Format values that are: drop-down, you selected equal or below. Inside the New Formatting Rule dialog, you clicked the Format... button. Inside the Format Cells dialog from the Font style list, you clicked the Italic list item, selected Bold. Inside the Format Cells dialog, you clicked the OK button. Inside the New Formatting Rule dialog, you clicked the OK button.
question
The selected cells C2:C15 have conditional formatting rules applied. Apply the Stop If True option to the Top 5 rule so cells with the top five values will not have the Data Bar rule applied to them.
answer
In the Home Ribbon Tab in the Styles Ribbon Group, you clicked the Conditional Formatting button. In the Conditional Formatting menu, you clicked the Manage Rules... menu item. Inside the Conditional Formatting Rules Manager dialog from the Top 5 list, you checked the Top 5 check box. Inside the Conditional Formatting Rules Manager dialog, you clicked the Apply button, clicked the OK button.
question
The selected cells C2:C15 have conditional formatting rules applied. Edit the data bar rule so the data bars use the Orange, Accent 2, Lighter 80% color.
answer
In the Home Ribbon Tab in the Styles Ribbon Group, you clicked the Conditional Formatting button. In the Conditional Formatting menu, you clicked the Manage Rules... menu item. Inside the Conditional Formatting Rules Manager dialog from the Data Bar list, you selected Data Bar. Inside the Conditional Formatting Rules Manager dialog, you clicked the Edit Rule... button. In the Color menu, you selected the Orange, Accent 2, Lighter 80% color option. In the Color menu, you selected the Orange, Accent 2, Lighter 80% color option. Inside the Edit Formatting Rule dialog, you clicked the OK button. Inside the Conditional Formatting Rules Manager dialog, you clicked the OK button.
question
The selected cells C2:C15 have conditional formatting rules applied. Delete the Equal to or below average conditional formatting rule that has been applied to the selected cells. Be careful. These cells have more than one conditional formatting rule applied to them.
answer
In the Home Ribbon Tab in the Styles Ribbon Group, you clicked the Conditional Formatting button. In the Conditional Formatting menu, you clicked the Manage Rules... menu item. Inside the Conditional Formatting Rules Manager dialog from the Equal to or Below Average list, you selected Equal to or Below Average. Inside the Conditional Formatting Rules Manager dialog, you clicked the Delete Rule... button, clicked the OK button.
question
Filter the data to show only cells with the solid red background in the Cost column.
answer
You clicked cell C1, clicked the Quick Sort dropdown. In the Table Filter menu in the Filter by Color menu, you clicked the #FF0000 color.
question
Sort the data so cells with the red down arrow icon in the Cost Level column appear on top.
answer
In the Data Ribbon Tab in the Sort & Filter Ribbon Group, you clicked the Sort button. Inside the Sort dialog in the Sort On drop-down, you selected Cell Icon. Inside the Sort dialog, you clicked the Order Icon button. In the exCustomSortByArrowIconMenu menu, you selected the red arrow option. Inside the Sort dialog in the Sort By drop-down, you selected Cost Level. Inside the Sort dialog, you clicked the OK button.
question
Sort the data first alphabetically by the values in the Category column and then by the dates in the Date column with the oldest dates first.
answer
In the Data Ribbon Tab in the Sort & Filter Ribbon Group, you clicked the Sort button. Inside the Sort dialog in the Sort By drop-down, you selected Category. Inside the Sort dialog, you clicked the Add level button. Inside the Sort dialog in the Sort By2 drop-down, you selected Date. Inside the Sort dialog, you clicked the OK button.
question
Use a custom text filter to show only rows where the expense category (Category column) contains the word server.
answer
In the Data Ribbon Tab in the Sort & Filter Ribbon Group, you clicked the Filter button. You clicked cell B1, clicked the Quick Sort dropdown. In the Table Filter menu in the Text Filters menu, you clicked the Custom Filter... menu item. Inside the Custom AutoFilter dialog in the contains drop-down, you selected contains. Inside the Custom AutoFilter dialog, you typed server in the second filter input, clicked the OK button.
question
Use a custom number filter to show only rows where the expense cost (Cost column) is less than 100.
answer
In the Data Ribbon Tab in the Sort & Filter Ribbon Group, you clicked the Filter button. You clicked cell C1, clicked the Quick Sort dropdown. In the Table Filter menu in the Number Filters menu, you clicked the Less Than... menu item. Inside the Custom AutoFilter dialog, you typed 100 in the second filter input, clicked the OK button.
question
Filter the data in place so that only rows where the Category value is Meals and the Cost value is greater than 20 are shown. The criteria range has been set up for you in cells A1:C2.
answer
In the Data Ribbon Tab in the Sort & Filter Ribbon Group, you clicked the Advanced button. You clicked cell A1. Inside the Advanced Filter dialog, you clicked the OK button.
question
Collapse the entire outline to show just the subtotals.
answer
You clicked the 2 outline symbol.
question
Create automatic subtotals to sum the cost for each change in the Category column. The data have been sorted for you.
answer
In the Data Ribbon Tab in the Outline Ribbon Group, you clicked the Subtotal button. Inside the Subtotal dialog from the Add subtotal to list, you checked the Category check box. Inside the Subtotal dialog in the At each change in drop-down, you selected Category. Inside the Subtotal dialog from the Add subtotal to list, you unchecked the Category check box. Inside the Subtotal dialog, you clicked the OK button.
question
Display the details for just the Office Expenses group.
answer
You clicked the row 23 + symbol.
question
In cell A2, use Consolidate to create subtotals using the Sum function for values in the named range SubCategoryCosts. Include both the top row and left column labels. The named range SubCategoryCosts has been defined for you. The consolidation should show the subtotals only and not the details.
answer
In the Data Ribbon Tab in the Data Tools Ribbon Group, you clicked the Consolidate button. Inside the Consolidate dialog, you typed SubCategoryCosts in the Reference input, checked the Top row check box, checked the Left Column check box, and clicked the OK button.
question
The total row for each expense category uses a SUM function to total the costs. Create an automatic outline from the rows in this data range.
answer
In the Data Ribbon Tab in the Outline Ribbon Group, you clicked the Group button arrow. In the Group menu, you clicked the Auto Outline menu item.
question
Manually create groups of rows that can be expanded or collapsed as needed. Start with the group of selected cells.
answer
In the Data Ribbon Tab in the Outline Ribbon Group, you clicked the Group button. Inside the Group dialog, you clicked the OK button.
question
Create a new watch in the Watch Window for the selected cells.
answer
In the Formulas Ribbon Tab in the Formula Auditing Ribbon Group, you clicked the Watch Window button. Inside the Watch Window dialog, you clicked the Add Watch... button. Inside the Add Watch dialog, you clicked the Add button.
question
Using the Scenario Manager, show the Low COLA scenario. Close the Scenario Manager when you are through.
answer
In the Data Ribbon Tab in the Forecast Ribbon Group, you clicked the What-If Analysis button. In the What-If Analysis menu, you clicked the Scenario Manager... menu item. Inside the Scenario Manager dialog from the Scenarios: list, you selected Low COLA. Inside the Scenario Manager dialog, you clicked the Show button, clicked the Show button, and clicked the Close button.
question
Create a new scenario to reflect a change in cell B9 to a value of 0.01 Name the scenario Low COLA.
answer
In the Data Ribbon Tab in the Forecast Ribbon Group, you clicked the What-If Analysis button. In the What-If Analysis menu, you clicked the Scenario Manager... menu item. Inside the Scenario Manager dialog, you clicked the Add... button. Inside the Add Scenario dialog, you typed Low COLA in the Scenario name: input, clicked the OK button. Inside the Scenario Values dialog, you typed 0.01 in the text box input, clicked the OK button.
question
Create a scenario summary report. Accept the recommended results cells.
answer
In the Data Ribbon Tab in the Forecast Ribbon Group, you clicked the What-If Analysis button. In the What-If Analysis menu, you clicked the Scenario Manager... menu item. Inside the Scenario Manager dialog, you clicked the Summary... button. Inside the Scenario Summary dialog, you clicked the OK button.
question
Create a Forecast Sheet based on the selected data. Use a line chart and forecast values through 2018.
answer
In the Data Ribbon Tab in the Forecast Ribbon Group, you clicked the Forecast Sheet button. Inside the Create Forecast Worksheet dialog, you clicked the Create button.
question
Install the Solver add-in.
answer
You opened the backstage view, clicked the Options navigation button, clicked the Add-Ins tab, and clicked Cell 9x0. Inside the Excel Options dialog, you clicked the Go... button. Inside the Add-ins dialog, you checked the Solver Add-in check box, clicked the OK button.
question
Refresh the PivotTable data.
answer
In the Analyze Ribbon Tab in the Data Ribbon Group, you clicked the Refresh button.
question
Change the data source for this PivotTable to use the table named PayrollTable.
answer
In the Analyze Ribbon Tab in the Data Ribbon Group, you clicked the Change Data Source button arrow. In the Change Data Source menu, you clicked the Change Data Source... menu item. Inside the Create PivotTable Data Source dialog, you typed PayrollTable in the Table/Range: input, clicked the OK button.
question
Add a calculated field to this PivotTable to calculate the total pay for each employee (=Salary+Bonus). Name the new field Total Pay.
answer
In the Analyze Ribbon Tab in the Calculations Ribbon Group, you clicked the Fields, Items, & Sets button. In the Fields, Items, & Sets menu, you clicked the Calculated Field... menu item. Inside the Insert Calculated Field dialog, you typed Total Pay in the Name input, typed = in the Formula input. Inside the Insert Calculated Field dialog from the Fields list, you clicked the Salary list item. Inside the Insert Calculated Field dialog, you clicked the Insert Field button. Inside the Insert Calculated Field dialog from the Fields list, you double-clicked the Bonus list item. Inside the Insert Calculated Field dialog, you typed = Salary+Bonus in the Formula input.
question
Add a slicer to the PivotTable for the Employee field and use the slicer to show only data where the value is Sue Chur.
answer
In the Analyze Ribbon Tab in the Filters Ribbon Group, you clicked the Insert Slicer button. Inside the Insert Slicer dialog, you checked the Employee check box, clicked the OK button. You clicked the Sue Chur button.
question
Add a report filter for the Employee field and use it to filter the PivotTable to show only data where the value of the Employee field is Dr. Patella.
answer
Inside the PivotTable Fields dialog from the PivotTable Fields list, you clicked the Employee list item. You dropped the item in the Filters box, clicked the xlPivotTableEmployeeCheckButtonDropItem view, clicked cell B1, and clicked the Quick Sort dropdown. In the Pivot Table Filter menu, you clicked the Dr. Patella checkbox, clicked the OK button.
question
Add a timeline to the PivotTable to filter the data by values in the Date field. Use the timeline to filter the PivotTable to show only dates in April.
answer
In the Analyze Ribbon Tab in the Filters Ribbon Group, you clicked the Insert Timeline button. Inside the Insert Timelines dialog, you checked the Date check box, clicked the OK button. You clicked the April segment.
question
Apply the light orange Pivot Style Medium 3 Quick Style (the third option in the first row under the Medium styles) to the PivotTable.
answer
In the Design Ribbon Tab in the PivotTable Styles Ribbon Group, you expanded the PivotTable Styles Gallery. In the Design Ribbon Tab in the PivotTable Styles Ribbon Group in the PivotTable Styles menu, you selected the Pivot Style Medium 3 option.
question
Add a slicer to this PivotChart for the Employee field and show only data where the Employee value is Ben Delbow.
answer
In the Analyze Ribbon Tab in the Filters Ribbon Group, you clicked the Insert Slicer button. Inside the Insert Slicer dialog, you checked the Employee check box, clicked the OK button. You clicked the Ben Delbow button.
question
Apply the Style 2 Quick Style to the PivotChart.
answer
In the Design Ribbon Tab in the Chart Styles Ribbon Group in the Chart Styles menu, you clicked the Style 2 gallery option.
question
Change the PivotChart type to the first pie chart chart option.
answer
In the Design Ribbon Tab in the Type Ribbon Group, you clicked the Change Chart Type button. Inside the Change Chart Type dialog from the Chart Type list, you selected Pie. Inside the Change Chart Type dialog, you clicked the OK button.
Haven't found what you need?
Search for quizzes and test answers now
Quizzes.studymoose.com uses cookies. By continuing you agree to our cookie policy
