UA CS 285 TEST 8

Filter the data to show only cells with the solid red background in the Cost column.

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.

Collapse the entire outline to show just the subtotals.

You clicked the 2 outline symbol.

Display the details for just the Office Expenses group.

You clicked the row 23 + symbol.

Install the Solver add-in.

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.

Add a calculated field to this PivotTable to calculate the total pay for each employee (=Salary+Bonus). Name the new field Total Pay.

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.

Change the data source for this PivotTable to use the table named PayrollTable.

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.

Refresh the PivotTable data.

In the Analyze Ribbon Tab in the Data Ribbon Group, you clicked the Refresh button.

Add a slicer to this PivotChart for the Employee field and show only data where the Employee value is Ben Delbow.

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.

Add a slicer to the PivotTable for the Employee field and use the slicer to show only data where the value is Sue Chur.

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.

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.

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.

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.

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.

Create a Forecast Sheet based on the selected data. Use a line chart and forecast values through 2018.

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.

Using the Scenario Manager, show the Low COLA scenario. Close the Scenario Manager when you are through.

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.

Create a new scenario to reflect a change in cell B9 to a value of 0.01 Name the scenario Low COLA.

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.

Create a scenario summary report. Accept the recommended results cells.

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.

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.

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.

Manually create groups of rows that can be expanded or collapsed as needed. Start with the group of selected cells.

In the Data Ribbon Tab in the Outline Ribbon Group, you clicked the Group button. Inside the Group dialog, you clicked the OK button.

Create automatic subtotals to sum the cost for each change in the Category column. The data have been sorted for you.

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.

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.

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.

Use a custom text filter to show only rows where the expense category (Category column) contains the word server.

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.

Use a custom number filter to show only rows where the expense cost (Cost column) is less than 100.

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.

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.

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.

Sort the data so cells with the red down arrow icon in the Cost Level column appear on top.

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.

Apply the Style 2 Quick Style to the PivotChart.

In the Design Ribbon Tab in the Chart Styles Ribbon Group in the Chart Styles menu, you clicked the Style 2 gallery option.

Apply the light orange Pivot Style Medium 3 Quick Style (the third option in the first row under the Medium styles) to the PivotTable.

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.

Change the PivotChart type to the first pie chart chart option.

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.

Create a new watch in the Watch Window for the selected cells.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.