Excel exam 2
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.
Type Justin Fine in cell D2 and press Enter. Begin typing Julie Smith, and when Flash Fill suggests a pattern for the remaining cells, accept it.
You clicked cell D2, typed Justin Fine in cell D2, pressed Enter, and typed Julie Smith in cell D3. In the Data Ribbon Tab in the Data Tools Ribbon Group, you clicked the Flash Fill button.
Collapse the entire outline to show just the subtotals.
You clicked the 2 outline symbol.
Delete the comment from cell D7.
You clicked the undefined view, right clicked cell D7. In the Right-Click menu, you clicked the Delete... menu item. Inside the Delete dialog, you clicked the Close button. In the Review Ribbon Tab in the Comments Ribbon Group, you clicked the Delete button.
Export this worksheet as a tab-delimited text file.
You opened the backstage view, clicked the Export navigation button, clicked the Change File Type button, clicked the Text (Tab delimited) button, and clicked the Save As button. Inside the Save As dialog, you clicked the Save button.
Add the password zyx987 to this workbook.
You opened the backstage view, clicked the Protect Workbook button. In the Protect Workbook menu, you clicked the Encrypt with Password menu item. Inside the Encrypt Document dialog, you typed zyx987 in the Password input, clicked the OK button. Inside the Confirm Password dialog, you typed zyx987 in the Reenter password input, clicked the OK button.
Mark the workbook as final.
You opened the backstage view, clicked the Protect Workbook button. In the Protect Workbook menu, you clicked the Mark as Final menu item. Inside the Microsoft Excel dialog, you clicked the OK button. Inside the Microsoft Excel dialog, you 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 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.
Data that violate the validation rules in this worksheet have been circled. Remove the validation circles.
In the Data Ribbon Tab in the Data Tools Ribbon Group, you clicked the Data Validation button arrow, clicked the Data Validation button arrow. You right clicked cell D2. In the Data Ribbon Tab in the Data Tools Ribbon Group, you clicked the Data Validation button arrow. In the Data Validation menu, you clicked the Clear Validation Circles menu item.
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 cell C1, and 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 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 Filter button. You clicked cell D1, clicked the Quick Sort dropdown. In the Table Filter menu in the Sort by Color menu, you selected the red arrow option.
Apply the Colored Fill - Green, Accent 6 Quick Style to the shape. It is the last option in the second row of the gallery.
In the Format Ribbon Tab in the Shape Styles Ribbon Group, you expanded the Shape Styles Gallery. In the Format Ribbon Tab in the Shape Styles Ribbon Group in the Shape Styles Gallery menu, you selected the Colored Fill - Green, Accent 6 option.
Enter a formula in cell D5 to calculate B5/B4 rounded up to 4 decimal places.
In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Math & Trig button. In the Math & Trig menu, you clicked the ROUNDUP menu item. Inside the Function Arguments dialog, you typed B5/B4 in the Number input, typed 4 in the Num_digits input, and clicked the OK button.
Enter a formula in the selected cell using SUMIF to calculate the total expenses for the category Office Expense. Use the range name Category for the Range argument, the text string "Office Expense" for the Criteria argument, and Cost for the Sum_range argument.
In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Math & Trig button. In the Math & Trig menu, you clicked the SUMIF menu item. Inside the Function Arguments dialog, you right-clicked the Range input, typed Category in the Range input, typed Office Expense in the Criteria input, typed Cost in the Sum_range input, and clicked the OK button.
Unlock the cells B3:D8 so the user can edit the cells when the worksheet is protected.
In the Home Ribbon Tab in the Cells Ribbon Group, you clicked the Format button. In the Format menu, you clicked the Lock Cell menu item.
Remove worksheet protection from the Direct Mail worksheet.
In the Review Ribbon Tab in the Changes Ribbon Group, you clicked the Unprotect Sheet button.
Display all the comments in this worksheet at once.
In the Review Ribbon Tab in the Comments Ribbon Group, you clicked the Show All Comments 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.
Add a Step Up Process SmartArt diagram to the worksheet. It is the second option in the first row of the Process category.
In the Data Ribbon Tab in the Forecast Ribbon Group, you clicked the What-If Analysis button. In the Insert Ribbon Tab in the Illustrations Ribbon Group, you clicked the SmartArt button. Inside the Choose a SmartArt Graphic dialog from the SmartArt Category list, you clicked the Process item. Inside the Choose a SmartArt Graphic dialog, you clicked the Step Up Process image, clicked the OK button.
Export this worksheet as a CSV file.
In the Data Ribbon Tab in the Get & Transform Data Ribbon Group, you clicked the From Text/CSV button. You clicked ImportText-04. Inside the Import Data dialog, you clicked the dialog Close button. You opened the backstage view, clicked the Export navigation button, clicked the Change File Type button, clicked the CSV (Comma delimited) button, clicked the CSV (Comma delimited) button, clicked the CSV (Comma delimited) button, and clicked the Save As button. Inside the Save As dialog, you clicked the Save 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.
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.
Apply the Style 2 Quick Style to the PivotChart.
In the Design Ribbon Tab in the Chart Styles Ribbon Group, you expanded the Chart Styles Gallery. In the Design Ribbon Tab in the Chart Styles Ribbon Group in the Chart Styles menu, you clicked the Style 2 gallery option.
Add another data series to the chart to represent gross profit. Use the value of cell A2 as the series name and cells B2:D2 as the series values. Do not rebuild the chart from scratch.
In the Design Ribbon Tab in the Data Ribbon Group, you clicked the Select Data button. Inside the Select Data Source dialog, you clicked the Add button. You clicked cell A2. Inside the Edit Series dialog, you pressed Backspace. You clicked cell B2. Inside the Edit Series dialog, you clicked the OK button. Inside the Select Data Source dialog, you clicked the OK button.
Apply the Sparkline Dark Blue, Sparkline Style Dark #4 Quick Style to the selected Sparkline group. It is the fourth option in the fifth row of the gallery.
In the Design Ribbon Tab in the Group Ribbon Group, you clicked the Axis button. In the Design Ribbon Tab in the Style Ribbon Group, you expanded the Sparkline Styles Gallery, expanded the Sparkline Styles Gallery. In the Design Ribbon Tab in the Style Ribbon Group in the Sparkline Styles menu, you selected the Sparkline Style Dark #4 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.
Apply the Colored Outline- Accent 1 color set to the diagram (it is the first option in the Accent 1 section of the gallery).
In the Design Ribbon Tab in the SmartArt Styles Ribbon Group, you clicked the Change Colors button. In the Change Colors menu, you selected the Colored Outline - Accent 1 option.
Apply the Intense Effect SmartArt style to the diagram. It is the last option in the Best Match for Document section of the gallery.
In the Design Ribbon Tab in the SmartArt Styles Ribbon Group, you expanded the SmartArt Styles Gallery. In the Design Ribbon Tab in the SmartArt Styles Ribbon Group in the SmartArt Styles menu, you selected the Intense effect 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 clicked the Pie item. Inside the Change Chart Type dialog, you clicked the insertChartPie1Image0823a item, clicked the OK button.
Change the Sparklines from columns to lines.
In the Design Ribbon Tab in the Type Ribbon Group, you clicked the Line button.
Apply the Preset 4 picture effect to the picture (the last effect in the first row of preset effects).
In the Format Ribbon Tab in the Picture Styles Ribbon Group, you expanded the Quick Styles Gallery, expanded the Quick Styles Gallery, clicked the Quick Styles menu, and clicked the Picture Layout button. In the Format Ribbon Tab in the Adjust Ribbon Group, you clicked the Reset Picture button arrow, clicked the Reset Picture button arrow, clicked the Reset Picture button arrow, clicked the Artistic Effects button, and clicked the Artistic Effects button. In the Format Ribbon Tab in the Picture Styles Ribbon Group, you clicked the Picture Effects button. In the Picture Effects menu in the None menu, you selected the Preset 4 option.
In cell B7, enter a formula using the function for the Declining balance depreciation method. Use the cell names Cost and Salvage for the Cost and Salvage function arguments. Use the appropriate cell reference for the Life argument. Use the appropriate cell reference for the Period argument. Be sure to use the appropriate cell reference types to allow you to copy the formula to the rest of the column. The asset will be in use in year 1 for 5 months.
In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Financial button. In the Financial menu, you clicked the DB menu item. You clicked cell B2, clicked cell B4, and clicked cell B3. Inside the Function Arguments dialog, you typed $B$3 in the Life input, typed 5 in the Month input, typed A7 in the Period input, and clicked the OK button.
In cell B7, enter a formula using the function for the Double-declining balance depreciation method. Use the cell names Cost and Salvage for the Cost and Salvage function arguments. Use the appropriate cell reference for the Life argument. Use the appropriate cell reference for the Period argument. Be sure to use the appropriate cell reference types to allow you to copy the formula to the rest of the column. Use a multiplier of 3.
In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Financial button. In the Financial menu, you clicked the DDB menu item. You clicked cell B2, clicked cell B4, and clicked cell B3. Inside the Function Arguments dialog, you typed $B$3 in the Life input, typed 3 in the Period input, typed 3 in the Factor input, typed A7 in the Period input, and clicked the OK button.
In cell B7, enter a formula using the function for the Straight-line depreciation method. Use the cell names Cost and Salvage for the Cost and Salvage function arguments. Use the appropriate cell reference for the Life argument. Be sure to use the appropriate cell reference type to allow you to copy the formula to the rest of the column.
In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Financial button. In the Financial menu, you clicked the SLN menu item. You clicked cell B2, clicked cell B4, and clicked cell B3. Inside the Function Arguments dialog, you typed $B$3 in the Life input, clicked the OK button.
In cell B9, enter a formula using NPV to calculate the present value of a payment plan with variable annual payments as shown in cells B11:B14. The interest rate is stored in cell B2. Use a cell range as a single Value argument.
In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Financial button. You clicked the formula bar, typed "=B9" in the formula bar. In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Financial button. In the Financial menu, you clicked the NPV menu item. You clicked cell B2, selected the cell range B11:B14. Inside the Function Arguments dialog, you pressed the Enter key.
Enter a formula in cell F2 using AVERAGEIF to calculate the average cost for the category Computer Expense. Use the range name Category for the Range argument, the text string "Computer Expense" for the Criteria argument, and Cost for the Average_range argument.
In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Math & Trig button, clicked the Lookup & Reference button, and clicked the More Functions button. In the More Functions menu in the Statistical menu, you clicked the AVERAGEIF menu item. Inside the Function Arguments dialog, you typed Category in the Range input, typed Computer Expense in the Criteria input, typed Cost in the Average_range input, and clicked the OK button.
Enter a formula using a database function to calculate the average value in the Cost column for expenses that meet the criteria in the criteria range A2:E3. The database is defined by the named range Expenses.
In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the More Functions button, clicked the Insert Function button. Inside the Insert Function dialog in the Or select a category drop-down, you selected Database. Inside the Insert Function dialog from the Select a function list, you selected DAVERAGE. Inside the Insert Function dialog, you clicked the OK button. Inside the Function Arguments dialog, you typed Expenses in the Database input, typed "Cost" in the Field input, typed A2:E3 in the Criteria input, and clicked the OK button.
Enter a formula in the selected cell using AVERAGEIFS to calculate the average expense (use the named range Cost) where the value in the Category named range is equal to the text string "Computer Expense" and the value in the SubCategory named range is equal to the test string "Internet Access".
In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the More Functions button. In the More Functions menu in the Statistical menu, you clicked the AVERAGEIFS menu item. Inside the Function Arguments dialog, you right-clicked the Average_range input, typed Cost in the Average_range input, pressed the Tab key, typed Category in the Criteria_range1 input, pressed the Tab key, typed Computer Expense in the Criteria1 input, pressed the Tab key, pressed Backspace, pressed the Tab key, typed Internet Access in the Criteria2 input, typed "Computer Expense" in the Criteria1 input, pressed the Tab key, pressed Tab, pressed the Tab key, typed "Internet Access" in the Criteria2 input, and clicked the OK button.
Select the Gross Profit data series and apply the Intense Effect - Green, Accent 6 shape style (the last style in the last row).
You clicked the 2019 Gross Profit data point. In the Format Ribbon Tab in the Shape Styles Ribbon Group, you expanded the Shape Styles Gallery. In the Format Ribbon Tab in the Shape Styles Ribbon Group in the Shape Styles Gallery menu, you selected the Intense Effect - Green, Accent 6 option.
Edit the MODE.SNGL formula in cell G3 using IFERROR so if the formula results in an error, the message none will display instead of the #N/A error. Edit the formula directly in the cell or the formula bar.
You clicked the formula bar, typed "=IFERROR(MODE.SNGL(D2:D14)" in the formula bar, clicked the formula bar, typed "=IFERROR(MODE.SNGL(D2:D14),"none")" in the formula bar, and pressed Enter.
Enter a formula in cell D5 to calculate B5/B4 rounded to 4 decimal places.
You clicked the formula bar, typed "=RO" in the formula bar, double-clicked ROUND in the Formula AutoComplete list, typed "=ROUND(B5/B4,4)" in the formula bar, and pressed Enter.
Enter a formula in cell F2 using SUMIFS to calculate the total expense (use the named range Cost) where the value in the Category named range is equal to the text string "Office Expense" and the value in the SubCategory named range is equal to the text string "Parking".
You clicked the formula bar, typed "=SUM" in the formula bar, and double-clicked SUMIF in the Formula AutoComplete list. In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Logical button, clicked the Math & Trig button. In the Math & Trig menu, you clicked the SUMIFS menu item. Inside the Function Arguments dialog, you typed Cost in the Sum_range input, typed Category in the Criteria_range1 input, typed Office Expense in the Criteria1 input, typed SubCategory in the Criteria_range2 input, typed Parking in the Criteria2 input, and clicked the OK button.
Save this workbook as an Excel template.
You opened the backstage view, clicked the Save As navigation button, clicked the Save as type drop-down, clicked the Excel Template (*.xltx) item, and clicked the Save button.
Remove all the markers from this Sparkline group.
You right clicked cell B6. In the Design Ribbon Tab in the Show Ribbon Group, you unchecked the Markers check box.
Remove the hyperlink from cell B3.
You right clicked cell C5, right clicked cell B3. In the Right-Click menu, you clicked the Remove Hyperlinks menu item.
The Expenses data series has been selected on this chart. Reset the data series line to match the chart style.
You right clicked the undefined chart, dragged the chart. In the Format Ribbon Tab in the Current Selection Ribbon Group, you clicked the Reset to Match Style button.
The Gross Profit data series is already selected. Select just the 2019 data point in the series and change the outline color to Black, Text 1 (the second color from the left in the top row of theme colors).
idk
Add this comment to cell B4: Why are printing costs so high?
type in then click outside of bubble