SAM Training 12
In cell C13, enter a formula using the IFERROR function that uses the existing VLOOKUP function in cell C13 as the value function argument, and Invalid Job Title as the customized error message for the value_if_error function argument.
click cell C13 in the formula bar, click after the = and type IFERROR( type ,"Invalid Job Title") at the end press enter
In cell D6, create a data validation rule to allow only whole numbers from 1 to 5 inclusive to be input in the cell.
click cell D6 click the Data tab click Data Validation (next to Text to Columns) click tiny Allow: arrow click Whole Number type 1 in Minimum type 5 in Maximum click okay
In cell D6, create a customized error alert using the Stop style. Type the text Input Error as the error alert title and Value must be a whole number from 1 to 5 as the message text.
click cell D6 click the Data tab click Data Validation (next to Text to Columns) click the Error Alert tab click tiny arrow (Style) click Stop type Input Error in the Title type Value must be a whole number from 1 to 5 in the text box click okay
In cell D6, create an input message with Rating Parameters as the message title and Enter a whole number from 1 to 5 as the message text.
click cell D6 click the Data tab click Data Validation (next to Text to Columns) click the Input Message tab type Rating Parameters in the Title box type Enter a whole number from 1 to 5 in the Input message box click okay
Run the Bonus macro in cell F6. (The shortcut is CTRL+k).
click cell F6 click the Developer tab click the Macros button click Bonus click Run
Insert WordArt into the current worksheet using WordArt style Fill: Blue, Accent color 1; Shadow (first row, second option), then type Rockland Consulting in the WordArt text placeholder.
click on the Insert tab click the WordArt button (tiny blue A) click the specified option type Rockland Consulting
Circle invalid data in the current worksheet.
click the Data tab click tiny Data Validation arrow click Circle Invalid Data
Insert a Button (Form Control) in the worksheet in cell F2. Assign the Clear_Filter macro to the button.
click the Developer tab click the Insert button click the first option click cell F2 click Clear_Filter click OK
Insert a Command Button (ActiveX Control) into cell E1.
click the Developer tab click the Insert button click the first option under ActiveX Controls drag and select E1
Record a macro to be stored in This Workbook, with the title Bonus and the letter k as the shortcut key. The macro should perform in the following steps: type =OR(E6>25000,D6>=3) in the selected cell, press ENTER, and then stop recording the macro.
click the Developer tab click the Record Macro button type Bonus in the Macro name text box press TAB type lowercase k in the Shortcut key box click OK type =OR(E6>25000,D6>=3) in cell F6 and hit ENTER click the Stop Recording button
View the code for the Top_3 macro in the VBA Editor.
click the Developer tab click the Visual Basic button
Display the Developer tab on the ribbon.
click the File tab click Options (bottom left corner) click Customize Ribbon click the Developer check box click OK
Save the current workbook as a Macro-Enabled workbook. Do not change the file name.
click the File tab click Save As in Documents > Cengage, click Cengage click tiny Save as type arrow click Excel Macro-Enabled Workbook (second option) click Save
Protect the worksheet using cengage as the password.
click the Review tab click the Protect Sheet button type cengage in the Password to unprotect sheet click OK type cengage in Reenter password to proceed click OK
Protect the current worksheet, letting users select both locked and unlocked cells, but only make changes to unlocked cells. Use the password cengage to prevent unauthorized changes to the worksheet protection settings.
click the Review tab click the Protect Sheet button type cengage in the Password to unprotect sheet box check the box below click the Select locked cells box click OK confirm cengage password click okay
Protect the structure of the current workbook; do not create a password.
click the Review tab click the Protect Workbook button click OK
Unprotect the worksheet, using cengage as the password.
click the Review tab click the Unprotect Sheet button type cengage in the Password text box click OK
Add the Quick Print button to the Quick Access Toolbar.
click the tiny dropdown arrow in the very top green area click Quick Print
Assign the Top_3 macro to the Show Top 3 button, then run the macro using the macro button.
right-click the Show Top 3 button click Assign Macro click Top_3 click OK click cell G3 click the Show Top 3 button
Assign the Totals macro to the blue SUM button, then run the Totals macro by clicking the button.
right-click the blue SUM button click Assign Macro click Totals click OK click cell G1 click the blue SUM button
Insert a Funnel chart in the worksheet based on range B5:B9
select B5:B9 click the Insert tab in the charts group, click the strange staircase one choose the funnel icon
