Black Belt
On the Summary worksheet, in cell B2, use GETPIVOTDATA to extract the value of the Sum of Total Pay field in the Date row for the date January 1, 2022. The value is currently stored in cell D4 in the PivotTable worksheet.
1. Type the equal sign (=) in cell B2. Click the PivotTable worksheet tab.3. Click cell e154. Press Enter
From the Word Options dialog, add the Add Table command to the Quick Access Toolbar.
Click Customize the Quick Access Toolbar, and then click More Commands. In the Choose commands from list, click File Tab. Choose the command, and then click Add. Remember to click OK.
In cell A10, enter a formula using OR to display TRUE if net profit before tax in 2019 (cell B5) are greater than 750000 (seven hundred, fifty thousand) or net profit before tax in 2020 (cell C5) are greater than 750000 (seven hundred, fifty thousand). Use cell references where appropriate and enter the arguments exactly as described in this question.
Click cell A10, type =or, double-click OR in the Formula AutoComplete list, type =OR(B5>750000,C5>750000 , press Enter
Use a slicer to filter the data in this table to show only rows where the Category value is Coffee or Meals.
Design > Tools > Insert Slicer > Category checkbox > Click multiselect > select everything EXCEPT Coffee/Meals
Using cell references, enter a formula in cell B6 to calculate monthly payments for the loan described in this worksheet. Omit the optional arguments. Use a negative value for the Pv argument.
Formulas tab > Function Library group, click the Financial button, click PMT. Enter B3/12 in rate, enter B4 in Nper, enter -B2 in the Pv. Click ok.
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 =Salary+Bonus in the Formula input, and clicked the OK 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.
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. In the Data Validation menu, you clicked the Clear Validation Circles menu item.
Add a Step Up Process SmartArt diagram to the worksheet. It is the second option in the first row of the Process category.
On the Insert tab, in the Illustrations group, click the SmartArt button. Inside the Choose a SmartArt Graphic dialog, from the SmartArt Category list, click the Process list item. Inside the Choose a SmartArt Graphic dialog, click the Step Up Process image, click OK
Modify the Bonus field to use the MAX function instead of the SUM function.
On the PivotTable Tools Analyze tab, in the Active Field group, click the Field Settings button. Select Max. Click OK.
Reply to the comment in cell B4 with the text We switched vendors.
Reply to the comment in cell B4 with the text We switched vendors.
Use Flash Fill to autofill the cells in this column. Start by entering the pattern ECN Department in cell B2.
Type ECN Department in cell B2 and press Enter. Type ENG in cell B3 and press Enter to accept Flash Fill.
Save the file to a new folder inside the Documents folder on the computer. Name the new folder Marketing. Name the file BusinessPlanUpdated.
With your document open, click File > Save As.Under Save As, select where you want to create your new folder. ...In the Save As dialog box that opens, click New Folder.Type the name of your new folder, and press Enter. ...Click Save.
Hide the column showing 2019 data and the bonus rate.
You clicked cell B1, clicked the column B header, and clicked the column B header. In the Home Ribbon Tab in the Cells Ribbon Group, you clicked the Format button. In the Format menu in the Hide & Unhide menu, you clicked the Hide Columns menu item.
Enter a formula in cell B10 to return a value of 35000 if the Net Profit After Tax (cell B9) is greater than or equal to 470000 or 100 if it is not.
You clicked cell B10, typed =if in cell B10, pressed Tab, typed =IF(b9>=470000,35000,100) in cell B10, and pressed Enter.
Enter a formula in cell E2 to calculate the absolute value of C2-D2.
You clicked cell E2. In the Formulas Ribbon Tab in the Function Library Ribbon Group, In the Math & Trig menu, you clicked the ABS menu item. You clicked cell C2. Inside the Function Arguments dialog, you typed C2- in the Number input. You clicked cell D2. Inside the Function Arguments dialog, you clicked the OK button.
Enter a formula in cell H1 to calculate the mean of the differences in cells E2:E13.
You clicked cell H1, typed =av in cell H1, clicked the =av view, double-clicked AVERAGE in the Formula AutoComplete list, selected the cell range E2:E13, and pressed Enter.
Change the button text to: Create Q1 Pie Chart
You clicked the Button 1 text, right-clicked the form button. In the Form Button Right-Click menu, you clicked the Edit Text menu item. You clicked the Button 1 text, pressed the Enter key, and clicked cell C1.
Move the image so it is centered in cell A1.
You dragged the Profit sign image to the left (by 7 columns) and up (by 1 rows).
Change the Office theme to the Dark Gray theme.
You opened the backstage view, clicked the Account navigation button, clicked the Office Theme drop-down, and clicked the Dark Gray item.
Check if this workbook contains elements that are not compatible with earlier versions of Excel. Close the Compatibility Checker without making any changes.
You opened the backstage view, clicked the Info navigation button, and clicked the Check for issues button. In the Check for Issues menu, you clicked the Check Compatibility menu item. Inside the Microsoft Excel - Compatibility Checker dialog, you clicked the OK button.
0Mark the workbook as final.
You opened the backstage view, clicked the Info navigation button, and 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.
Remove workbook protection so users can add new worksheets.
You opened the backstage view, clicked the Info navigation button, clicked the Enable Content button, clicked the Enable Content button, and clicked the back arrow button. In the Review Ribbon Tab in the Protect Ribbon Group, you clicked the Protect Workbook button.
Create a new file based on the Inventory List template.
You opened the backstage view, clicked the New navigation button, searched for inventory in the Template Search Input, clicked the Inventory list template, and clicked the Create button.
Add the macro CreateQ2Chart to the Quick Access Toolbar.
You opened the backstage view, clicked the Options navigation button, and clicked the Quick Access Toolbar tab. Inside the Excel Options dialog in the Macros drop-down, you selected Macros. Inside the Excel Options dialog from the Macros Command list, you selected CreateQ2Chart. Inside the Excel Options dialog, you clicked the Add >> button, clicked the OK button.
Save this file as a template that allows macros.
You opened the backstage view, clicked the Save As navigation button, clicked the Save as type drop-down, clicked the Excel Macro-Enabled Template (*.xltm) item, and clicked the Save button.
This workbook includes macros. Save it using the file type that allows macros.
You opened the backstage view, clicked the Save As navigation button, clicked the Save as type drop-down, clicked the Excel Macro-Enabled Workbook (*.xlsm) item, and clicked the Save button.
Group together the Dec 2 and Dec 9 worksheets.
You pressed and held the Shift key while clicking the Dec 9 tab.
Remove the hyperlink from cell B3.
You right clicked cell B3. In the Right-Click menu, you clicked the Remove Hyperlinks menu item.
Add this comment to cell B4: Why are printing costs so high?
You right clicked cell B4. In the Right-Click menu, you clicked the Insert... menu item. Inside the Insert Cells dialog, you clicked the Close button. You right clicked cell B4. In the Right-Click menu, you clicked the Insert Comment menu item. You clicked the comment balloon, changed the comment in cell B4 to KenDishner: Why are printing costs so high?.
Ungroup the grouped sheets.
You right clicked the Dec 2 tab. In the Tab Right Click menu, you clicked the Ungroup Sheets menu item.
Enter a formula in cell F1 to find the rank of the value in cell C4 compared to the values in cells C2:C13.
You typed =RANK in cell F1, double-clicked RANK.EQ in the Formula AutoComplete list, typed =RANK.EQ(C4,C2:C13 in cell F1, and pressed Enter.
Insert the current date in cell A1. Do not include the current time.
You typed =today() in cell A1, pressed Enter.
In cell E2, enter a formula using TEXTJOIN to combine the text from cells B2:D2 into a single text string. Use the # character as the delimiter. Ignore blank cells. Use a cell range as the Text1 argument
click on formulas tab--text-TEXTJOIN--enter # in delimiter--enter B2:D2 in text1--click ok
Add a hyperlink from the selected cell to link to cell B4 in the Social Media worksheet. Don't forget to use B4 as the cell reference
select insert tab. click on links group. click place in this document then select social media. type B4 as cell reference
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.
the formula bar, clicked the formula bar, typed "=IFERROR(MODE.SNGL(D2:D14),"none")" in the formula bar, and pressed Enter.
Add the Developer tab to the Ribbon.
Click the File tab. Click Options to open the Excel Options dialog. Click Customize Ribbon. At the right side of the Customize the Ribbon window, the Customize the Ribbon list shows all the tab and groups available. Click the Developer check box to add the Developer tab to the Ribbon. Click OK.
Add an element to the center section of the header to display just the current date (not Dec 2, which is the worksheet name).
Click the Page Layout button on the status bar. Click in the center section of the header area above the worksheet grid. On the Header & Footer Tools Design tab, in the Header & Footer Elementsgroup, click the Current Date button.
Apply data validation rules to the selected cells to allow only decimal numbers between 2.5 and 4.0. Do not allow blanks.
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 Data Validation... menu item. Inside the Data Validation dialog in the Allow drop-down, you selected Decimal. Inside the Data Validation dialog, you typed 2.5 in the Minimum: input, clicked the DialogHeader view, clicked the dialog undefined button, clicked the DialogHeader view, clicked the dialog undefined button, typed 4.0 in the Maximum: input, unchecked the Ignore blank check box, and clicked the OK button.
Add Enter the transfer GPA. as an input message to the validation rules for the selected cells. Do not include a title.
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 Data Validation... menu item. You clicked the Input Message tab. Inside the Data Validation dialog, you typed Enter the transfer GPA. in the Input message input, clicked the OK button.
This worksheet has validation rules applied. Find and circle cells that violate those rules.
In the Data Ribbon Tab in the Data Tools Ribbon Group, you clicked the Data Validation button. Inside the Data Validation dialog, you clicked the dialog Close button. 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 Circle Invalid Data menu item.
Use the Flash Fill command to autofill the remaining cells in this column with the pattern from cell F2.
In the Data Ribbon Tab in the Data Tools Ribbon Group, you clicked the Flash Fill button.
Create a Forecast Sheet based on the selected data. Use a line chart and forecast values through 2021.
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.
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,m 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.
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, you clicked the Scenarios: list. 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 Close button.
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.
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.
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.
Edit the code for the FormatPieChart macro in the Visual Basic Editor.
In the Developer Ribbon Tab in the Code Ribbon Group, you clicked the Macros button. Inside the Macro dialog from the Macro name: list, you selected FormatPieChart. Inside the Macro dialog, you clicked the Edit button.
Run the FormatPieChart macro.
In the Developer Ribbon Tab in the Code Ribbon Group, you clicked the Macros button. Inside the Macro dialog, you clicked the Run button.
Begin recording a new macro.
In the Developer Ribbon Tab in the Code Ribbon Group, you clicked the Record Macro button.
The Anne Cole data point has been selected for you. Explode the Anne Cole data point by exactly 50% to move it away from the rest of the pie chart.
In the Format Ribbon Tab in the Current Selection Ribbon Group, you clicked the Format Selection button. Inside the Format Data Point dialog from the Trendline options list, you clicked the Series Options list item, typed 50 in the Point Explosion input, and pressed the Enter key.
Apply the Drop Shadow, Rectangle Quick Style.
In the Format ribbon tab in the Picture Styles ribbon group in the Picture Styles Gallery menu, you clicked the Drop Shadow Rectangle gallery option.
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 Date & Time button, clicked the Financial button. In the Financial menu, you clicked the NPV menu item. Inside the Function Arguments dialog, you typed b2 in the Rate input, typed b11:b14 in the Value1 input, and clicked the OK button.
Insert the current date and time in cell A1.
In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Date & Time button. In the Date & Time menu, you clicked the TODAY menu item. Inside the Function Arguments dialog, you clicked the OK button.
Enter a nested function in cell F9 using INDEX and MATCH to find the ending balance for the date listed in cell C8. Use the named range Schedule to reference the cell range for the schedule of loan payments. The Ending Balance column is column 6. In the INDEX Row_num function argument, use MATCH to look up the row number for the date listed in C8. Use the named range PayDates to reference the cell range for the Payment Date column. Require an exact match.
In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Lookup & Reference button. In the Lookup & Reference menu, you clicked the INDEX menu item. Inside the Select Arguments dialog, you clicked the OK button. Inside the Function Arguments dialog, you typed Schedule in the Array input, pressed the Tab key, pressed Backspace, pressed the Tab key, and typed 6 in the Column_num input. You clicked the Name Box. In the Recently Used menu, you clicked the Match menu item. Inside the Function Arguments dialog, you typed C8 in the Lookup_value input, pressed the Tab key, typed PayDates in the Lookup_array input, pressed the Tab key, typed 0 in the Match_type input, and clicked the OK button.
In cell D2, use CONCAT to combine the text from cell B2 with the text from cell C2, with a colon : in between. The result of the formula should look like this: Rent:Parking
In the Formulas Ribbon Tab in the FunctionLibrary Ribbon Group, you clicked the Textbutton. In the Text menu, you clicked theCONCAT menu item. Inside the FunctionArguments dialog, you typed B2:C2 in theConcat Text1 Formula Input, clicked the OKbutton.
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.
Insert a PivotChart using the first pie chart type.
In the Insert Ribbon Tab in the Charts Ribbon Group, you clicked the PivotChart button. In the PivotChart menu, you clicked the PivotChart menu item. Inside the Insert Chart dialog from the Chart Type list, you clicked the Pie item. Inside the Insert Chart dialog, you clicked the OK button.
Edit the code for the FormatPieChart macro in the Visual Basic Editor.
In the Mini Toolbar, you clicked the Macros button. Inside the Macro dialog from the Macro name: list, you selected FormatPieChart. Inside the Macro dialog, you clicked the Edit button.
Edit the code for the FormatPieChart macro to add a new comment in the blank line above the chart style code that reads Change chart style Press Enter when you are done.
In the Mini Toolbar, you clicked the Macros button. Inside the Macros dialog from the Macro name: list, you selected FormatPieChart. Inside the Macro dialog, you clicked the Edit button. You edited the macro code, pressed Enter.
Display the Accessibility Checker task pane.
In the Review Ribbon Tab in the Accessibility Ribbon Group, you clicked the Check Accessibility button.
Modify this worksheet so users can select but not edit locked cells. Users should be able to select and edit any cells that are unlocked.
In the Review Ribbon Tab in the Changes Ribbon Group, you clicked the Protect Sheet button. Inside the Protect Sheet dialog, you clicked the OK button.
Modify this workbook so users cannot change worksheet names.
In the Review Ribbon Tab in the Changes Ribbon Group, you clicked the Protect Workbook button. Inside the Protect Structure and Windows dialog, you clicked the OK button.
Modify the chart so the Owner Draw data series is plotted along the secondary axis.
On the Charts Tools Design tab, in the Type group, click the Change Chart Type button. IN the Change Chart Type dialog, click the Secondary Axis check box next to the Owner Draw series.
Use Consolidate to enter values in the selected cells by summing data from cells B3:D8 in the worksheets Social Media, Direct Mail, and Other. Do not include links to the source data.
On the Data tab, in the Data Tools group, click the Consolidate button. Click in the Reference box and then click the Bob worksheet tab. Click and drag to select B3:D8. Click the Add button. Click the Cathy worksheet tab. Click Add. Click the Mario worksheet tab. Click Add. Click the Create links to source data check box. Click OK.
Import data from the StudentGPA text file into a table in a new worksheet.
On the Data tab, in the Get & Transform group, click the Get Data button. Point to From Database, and click From Microsoft Access Database. Click the Appointments database file, and then click the Import button. Click StudentGPA, and then click the Load button
In cell F9, enter a formula using the INDEX function to return the ending loan balance for the row listed in cell F8. Use the named range Schedule to reference the cell range for the schedule of loan payments. The Ending Balance column is column 6.
On the Formulas tab, in the Function Library group, click the Lookup & Reference button. Select INDEX. Select the array,row_num,column_num option. Click OK. In the Function Arguments dialog, in the Array box enter Schedule. In the Row_num box, enter F8. In the Column_num box, enter 6. Click OK.
Enter a formula in cell B1 using the SUMPRODUCT function to calculate the total value of the current leases by multiplying the current monthly rents by the remaining months on each lease. Use the range names Rents and Leases.
On the Formulas tab, in the Function Library group, click the Math & Trig button. Click SUMPRODUCT. In the Array1 argument box, type SpecialPrice. In the Array2 argument box, type MembershipsSold. Click OK.
Complete the one-variable data table in cells E3:F8 to calculate the breakeven sales point for varying owner withdrawal amounts. The formula has been entered for you in cell E3. It references the original owner withdrawal value in cell B12. The substitute values have been entered for you in cells E4:E8.
Ribbon Group, you clicked the What-If Analysis button. In the What-If Analysis menu, you clicked the Data Table... menu item. Inside the Data Table dialog, you clicked the DialogHeader view, clicked the dialog undefined button, typed B12 in the Column input cell input, and clicked the OK button.
The 2022 data point in the Gross Profit data series has been selected. Change the outline width to 2 1/4 pt.
The 2022 data point in the Gross Profit data series has been selected. Change the outline width to 2 1/4 pt.