1. This worksheet has validation rules applied.

Find and circle cells that violate those rules

-Click the “Data Validation button” arrow
-select Circle Invalid Data to identify the invalid data entries in your worksheet

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

- clicked the “What-if Analysis”
-clicked the “Scenario Manager”
- clicked the “Summary “
-clicked the OK

3. Set row 1 to print on every page.

-click the “Page Layout”.
-clicked the Expand Dialog
-Inside the Page Setup dialog, clicked the “Rows to repeat at top”
-Click OK

4. Add this comment to cell B4: Why are printing costs so high?
-click “Review”, click “New Comment”
-Type your comment text in the balloon
-Click the Post button

5. 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, clicked the “Macros button”
-Inside the Macros dialog from the Macro name: list, selected “FormatPieChart”
-clicked the Edit button
-edited the macro code, pressed Enter.

6. In cell C5, enter a formula to calculate the future value of this investment. Use cell
references wherever possible. The interest rate is stored in cell C4, the number of
payments in cell C2, and the monthly investment amount in cell C3. Remember to use a
negative value for the Pmt argument.
-On the Formulas tab, click the financial button
-Click FV
-typed C4/12 in the Rate input, pressed the Tab key
-typed C2 in the Nper input, pressed the Tab key
-typed -C3 in the Pmt input, and pressed the Enter key.

7. Ungroup the grouped sheets.

-right clicked the Dec 9 tab
-clicked the Ungroup Sheets menu item

8. Install the Solver add-in

On the Tools menu, select Excel Add-Ins. In the Add-Ins available box, select the Solver Add-In
check box, and then click OK. If Solver Add-in is not listed in the Add-Ins available box, click
Browse to locate the add-in. If you get a prompt that the Solver add-in is not currently installed on
your computer, click Yes in the dialog box to install it.

9. Group together the Dec 2 and Dec 9 worksheets

-click the first worksheet tab
-Hold down Shift and click the tab for the last worksheet you want included in the group

10. Enter a formula in cell B3 using the VLOOKUP function to find the meaning for the medical
abbreviation listed in cell A3. Use the name Abbreviation for the lookup table. The item
names are located in column 2 of the lookup table. Be sure to require an exact match
-on the Formulas tab, click “Lookup & Reference”
-Select “VLOOKUP”
-Enter the “Lookup value argument”
-Enter the “cell reference” for which you want to find a corresponding value. In other words, the
value you want to look up.
-Enter the “Table array” argument.
-Enter the range of cells (or the range name) that contains the lookup data
-Enter the “Col_index_num” argument. This argument is the position of the column in the
Table_array from which the function should return a matching value
-Enter the “column number” not the letter or the column heading.
-The “Range lookup” argument is optional. Type FALSE
-Click OK.

11. Change the Office theme to the Dark Gray theme

- File > ACCOUNT
-select a drop-down option from the Office Theme box
- press OK

12. Add GPA must be between 2.5 and 4. as an error alert to the validation rules for the selected
cells. Do not include a title.
-In Data Tab clicked the Data Validation button arrow
-In the Data Validation menu, you clicked the Data Validation... menu item
-clicked the Error Alert tab. Inside the Data Validation dialog, you typed GPA must be between 2.5
and 4
-in the Error message: input, clicked the OK button.

13. 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
-clicked the Protect Sheet button
-Inside the Protect Sheet dialog
-clicked the OK button.
14. Complete the two-variable data table in cells A7:E12. The formula has been entered for you
in cell A7. The substitute values in cells B7:E7 reference the original cost of goods percentage
in cell B3, and the substitute values in cells A8:A12 reference the original owner withdrawal
percentage in cell B4.
-In Data Ribbon Tab in the Forecast Ribbon Group, clicked the What-If Analysis button
-In the What-If Analysis menu, clicked the Data Table... menu item
-Inside the Data Table dialog, you typed B3 in the Row input cell input, typed B4 in the Column
input cell input
-clicked the OK button.

15. Use Solver to find the combination of procedures that will result in the maximum possible
profit per week in cell B7. Use the range name Optimal as the variable cells. Add these
constraints in order: values in the named range Optimal are less than or equal to the values in
the named range Max; values in the named range Optimal are whole numbers; values in the
named range Procedures are less than or equal to values in the named range MaxAllowed.
Run Solver and accept the solution.
-In Data Tab in the Analyze Ribbon Group, clicked the Solver button
-Inside the Solver Parameters dialog, you typed B7 in the Set Objective input
-typed Optimal in the By Changing Variable Cells input, clicked the Add button
-Inside the Add Constraint dialog, typed Optimal in the Cell Reference input
-typed Max in the Constraint input, clicked the Add button
-typed Optimal in the Cell Reference input
-Inside the Add Constraint dialog in the Operator drop-down, selected int
-Inside the Add Constraint dialog, you clicked the Add button, typed Procedures in the Cell
Reference input, typed MaxAllowed in the Constraint input, and clicked the OK button
-Inside the Solver Parameters dialog, clicked the Solve button
-Inside the Solver Constraints dialog, you clicked the OK button.

16. In cells D2:D20, use data validation to display an in-cell drop-down list of values from the
Days named range. Do not allow blanks.
-In Data Ribbon Tab in the Data Tools Ribbon Group, clicked Data Validation button arrow
-In Data Validation menu, clicked the Data Validation... menu item
-Inside the Data Validation dialog in the Allow drop-down, selected List
-Inside the Data Validation dialog, you typed =Days in the Source: input, unchecked the Ignore
blank check box
-clicked the OK button.

17. Use a custom date filter to show only rows where the expense date (Date column) is before
4/20/2020.
1. data > filter
2. click autofilter arrow at top of column wish to filter
3. Date Filter > before/after
4. enter custom date next to > OK
18. 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.

19. Import data from the StudentGPA text file into a table in a new worksheet.
-on the Data tab, in the “Get & Transform Data group” click the “From Text/CSV button”
-navigate to the location of the file. In the Import Data dialog, click the text file
-click Import button
-click Load

20. Create automatic subtotals to sum the cost for each change in the Category column. The
data have been sorted for you.
-In Data Ribbon Tab in the Outline Ribbon Group, clicked the Subtotal button
-Inside Subtotal dialog from the Add subtotal to list, checked the Category check box
-Inside Subtotal dialog in the At each change in drop-down, selected Category
-Inside Subtotal dialog from the Add subtotal to list, unchecked the Category check box
-clicked OK

21. Add Enter the transfer GPA. as an input message to the validation rules for the selected
cells. Do not include a title
-on Data tab, click the “Data Validation”
-Click “Input Message”
-The Show input message when cell is selected check box is checked by default
-Click OK

22. Save the file to a new folder inside the Documents folder on the computer. Name the new
folder Marketing. Name the file BusinessPlanUpdated
-click File > Save As
-select where you want to create your new folder
-click New Folder
-Type the name of your new folder, and press Enter
-Click Save

23. Add a calculated field to this PivotTable to calculate the total pay for each employee
(=Salary+Bonus). Name the new field Total Pay
-In Analyze =Tab in the Calculations Ribbon Group clicked the Fields, Items, & Sets button
-In the Fields, Items, & Sets menu, clicked the Calculated Field... menu item
-Inside the Insert Calculated Field dialog, typed Total Pay in the Name input
-typed =Salary+Bonus in the Formula input
-clicked the OK button.

24. 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
-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.
-Type the closing parenthesis and press Enter

25. Use a slicer to filter the data in this table to show only rows where the Category value is
Coffee or Meals.
-clicked “insert slicer”
-checked the category check box, clicked the ok button
-clicked coffee button

26. Create a new file based on the Inventory List template

-Click files
-Click the search bar. It's at the top of the Excel window
-Type inventory list into the search bar, then press Enter
-This will bring up a list of templates for inventory management.

27. Edit the code for the FormatPieChart macro in the Visual Basic Editor
-In the Mini Toolbar, clicked the Macros button
-Inside the Macro dialog from the Macro name: list, selected FormatPieChart
-Inside the Macro dialog, clicked the Edit button.

28. 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

29. Change the Office background for the application to Geometry.

-click File. Click Account or Office Account
-Click the drop-down menu under Office Background, select the background

30. Apply data validation rules to the selected cells to allow only decimal numbers between
2.5 and 4.0. Do not allow blanks.
-In Data Tab clicked the Data Validation button arrow
-In the Data Validation menu, you clicked the Data Validation... menu
-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.
31. Change the button text to: Create Q1 Pie Chart
-clicked the Button 1 text, and right-clicked the form button
-In the Form Button Right-Click menu, clicked the Edit Text menu item
-pressed the Enter key, clicked cell A3.

32. 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 Formulas Tab in the Function Library Ribbon Group, clicked the Date & Time button
-clicked Lookup & Reference button, clicked the Logical button, clicked Financial button
-In the Financial menu, clicked the NPV menu item. clicked cell B7, clicked cell B11.
-Inside the Function Arguments dialog, you pressed Backspace
-selected the cell range B11:B14, clicked cell B2
-clicked the OK button.

33. Enter a formula in cell F1 to find the rank of the value in cell C4 compared to the values in
cells C2:C13.
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

34. In cell F2, enter a formula using COUNTIFS to count the number of rows where values in the
range named Cost have a value less than 500 and cells in the range named Category have the
value "Computer Expense".
-In Formulas Tab in the Function Library Ribbon Group, clicked the More Functions button
-In More Functions menu in the Statistical menu, clicked the COUNTIFS menu item
-In Function Arguments dialog, typed Cost in the Criteria_range1 input
-typed <500 in the Criteria1 input
-typed Category in the Criteria_range2 input
-typed Computer Expense in the Criteria2 input

35. Insert the current date in cell A1. Do not include the current time.
-In Formulas Ribbon Tab in the Function Library Ribbon Group, clicked the Date & Time button.
clicked the TODAY menu item. Clicked OK

36. Enter a formula using a database function to calculate the total 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 Formulas Tab in the Function Library Ribbon Group, clicked the Insert Function button
-In Insert Function dialog in the Or select a category drop-down, selected Database
-In Insert Function dialog from the Select a function list, selected DSUM
-in Insert Function dialog, clicked the OK button
-In Function Arguments dialog, typed Expenses in the Database input, typed Cost in the Field
input, typed A2:E3 in the Criteria input
37. Enter a formula in cell G1 to calculate the standard deviation of the values in cells D2:D13.
Assume this array is a sample of a larger set of values.
-In Formulas Tab in the Function Library Ribbon Group, clicked the More Functions button
-In More Functions menu in the Statistical menu, clicked the STDEV.S menu item
-Inside Function Arguments dialog, clicked the DialogHeader view, clicked the dialog undefined
button, typed D2:D13 in the Number1 input
-clicked OK

38. Hide the column showing 2019 data and the bonus rate.
clicked cell B1, right clicked the column B header
In the Col Header Right-Click menu, clicked the Hide menu item

39. Edit the code for the FormatPieChart macro to change the chart style from 42 to 252.
-In the Mini Toolbar, you clicked the Macros button
-Inside the Macro dialog from the Macro name: list, selected FormatPieChart
-Inside the Macro dialog, you clicked the Edit button
-edited the macro code, clicked the Save button.

40. Using the Scenario Manager, show the Low COLA scenario. Close the Scenario Manager
when you are through.
-clicked the “What-if Analysis”
-clicked the Scenario Manager
-clicked the Scenarios: list
-selected Low COLA
-clicked the Show button
-clicked the Close button.

41. Insert the current date and time in cell A1

-on the Formulas tab, click the Date & Time button
-click NOW
-click OK.

42. Enter a formula in cell H1 to calculate the mean of the differences in cells E2:E13.
-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

43. 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 Data tab, in the Data Tools group, click Consolidate button
-Click Reference box >click Uptown worksheet tab>Click and drag to select B3:D8
-Click Add button>Click the Downtown worksheet tab>Click Add
-Click the City Center worksheet tab. Click Add. Click OK.
44. Manually create groups of rows that can be expanded or collapsed as needed. Start with
the group of selected cells.
-On Data tab, in the Outline group, click Group button
-In the Group dialog, Rows is selected by default
-Click OK

45. Use Goal Seek to find the value for cell C2 that will result in a value of $550,000 for cell
B15. Accept the solution
-On the Data tab, in the Forecast group, click the What-If Analysis button
-click Goal Seek...
-Enter 550000 in the To value box.
Enter C2 in the By changing cell box.
Click OK. Click OK

46. Add the Developer tab to the Ribbon

-Click File tab. Click Options
-Click Customize the Ribbon
-check the box next to Developer and click OK.

47. Import data from ClassSchedule table in the Registration Access database into 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.

48. Set the last data point (September Net Income) as a total.
Double Click the Chart > Set as total

49. 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.

50. Unlock the cells B3:D8 so the user can edit the cells when the worksheet is protected
-In Home Tab< Cells Ribbon Group<clicked the Format button
-In the Format menu licked the Lock Cell menu item.

51. Reset just the Quick Access Toolbar to its original state
-Click "Customize Quick Access Toolbar" drop-down menu button
-click "More Commands"
-click Customize
-Click the Reset button, and then click Reset only Quick Access Toolbar

52. 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 Formulas Tab in the Function Library Group, clicked the Lookup & Reference button
-In Lookup & Reference menu, clicked the INDEX menu item
-Inside the Select Arguments dialog, clicked the OK button
-Inside the Function Arguments dialog, typed Schedule in the Array input, pressed the Tab key,
pressed Backspace, pressed the Tab key, and typed 6 in the Column_num input
-clicked the Name Box. In the Recently Used menu, 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
-clicked the OK button.

53. 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 Home Ribbon Tab in the Styles Ribbon Group, clicked the Conditional Formatting button
-In Conditional Formatting menu, clicked the Manage Rules... menu item
-Inside the Conditional Formatting Rules Manager dialog from the Data Bar list, selected Data
Bar. Inside the Conditional Formatting Rules Manager dialog, clicked the Edit Rule... button.
-In the Color menu, 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.

54. 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.

55. Record a new macro with the name NewTheme. Do not assign a shortcut key or description.
The macro should apply the Facet theme and then use the AutoFit Column Width
command to resize the selected columns.
-On the Developer tab, click the Record Macro button
-Type NewTheme in the Macro name box
-Click OK
-On the Page Layout tab, in the themes group, click the Themes button and select Facet
-On the Home tab in the Cells group, click the Format button and select AutoFit Column Width
-On the Developer tab, in the Code group, click the Stop Recording button.

56. Enter a formula using PV in cell B6 to calculate the present value needed for this pension
fund. Cell B3 is the expected annual interest rate. Cell B4 is the total number of monthly
payments that will be made. Cell B2 is the amount of each monthly payment. Payments will
be made at the beginning of every period. Remember to express the Pmt argument as a
 negative.
-In Formulas Tab in Function Library Ribbon Group, clicked the Financial button
-In Financial menu, clicked the PV menu item
-Inside the Function Arguments dialog, typed B3/12 in the Rate input
-typed B4 in the Nper input, typed -B2 in the Pmt input
-typed 1 in the Type input, and clicked OK

57. Add a total row to this table and display the average for the Cost column instead of the sum
of all the values
-Click Table Tools Design tab and select the Total Row check box
-Click the totals row for the Item column, click the arrow and select

58. 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.
- clicked cell B1, typed =SUMPRO in cell B1
-double-clicked SUMPRODUCT in the Formula AutoComplete list
-typed =SUMPRODUCT(Rents,Leases in cell B1, and pressed Enter.

59. 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.
-On the formulas tab, in the function library group,
-click the financial button
-click PMT
-Enter B3/12 in the rate argument box.
-Enter b4 in the Nper argument box. Enter B2 in the argument box.
-Click ok.

60. 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.
-Click the Math & Trig button on the formula page, in the library category function.
-Click SUMIF
-In the range argument box, type category
-In the criteria box, type "office expense"
-In the sum range argument box, type cost
-Click ok

61. 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.

62. 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.
-In the Data Ribbon Tab in the Forecast Ribbon Group
-clicked the Data Table.... menu item
- typed B12 in the Column input cell input
-clicked the OK button.

63. 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
-clicked the Consolidate button
-typed SubCategoryCosts in the Reference input
-checked the Top row check box
-checked the Left Column check box
-clicked the OK

64. 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".
-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, clicked the Logical button,
clicked the Math & Trig button
-In the Math & Trig menu, clicked the SUMIFS menu item. Inside the Function Arguments dialog,
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.

65. 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 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 Array box enter Schedule. In the Row_num box, enter F8. In the Column_num box, enter 6
-Click OK

66. 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 grind
-On the Header &Footer Tools Design tab, in the header &footer elements group, click the Current
Date button.

67. 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".
-On Formulas tab, in the Function Library group, click More Functions button
-Under Statistical, click AVERAGEIFS
-In the Average_range argument box, type Cost
-In the Criteria_range1 argument box, type Catogory
-In the Criteria1 argument box, type “Computer Expense”
-In the Criteria_range2 argument box, type SubCategory
-In the Criteria2 argument box, type "Internet Access"
-Click OK.

68. Modify this workbook so users cannot change worksheet names

-In the review ribbon tab and the changes ribbon group click the protector workbook button inside
the protect structure and windows dialogue
-click the OK button

69. The Anne Cole data point has been selected for you. Rotate the pie chart exactly 200° so this
data point appears at the left side of the chart near the legend.
-On the Chart Tools Format tab,
-in the Current Selection group, click the Format Selection button
-In the Angle of first slice box, type the 200 and press Enter.

70. Insert a Radial Cycle SmartArt diagram. It is the second option in the third row of the
Cycle category. Enter Net Profit as the text for the circle in the middle. Add Returns as the
text for the top circle. Add Expenses as the text for the circle on the right. Add Salaries as the
text for the bottom circle. Add Draws as the text for the circle on the left. When you are
finished, Click outside the diagram.
-In Insert Tab, in Illustrations Ribbon Group, clicked the SmartArt button
-Inside the Choose a SmartArt Graphic dialog from the SmartArt Category list, clicked the Cycle
item
-Inside the Choose a SmartArt Graphic dialog, you clicked the Radial Cycle image
-clicked the OK button
-clicked the [Text] text, typed Net Profit, clicked the [Text] text, clicked the [Text] text, typed
Returns, clicked the [Text] text, clicked the [Text] text, typed Expenses, clicked the [Text] text,
clicked the [Text] text, typed Salaries, clicked the [Text] text, clicked the [Text] text, typed Draws
clicked cell J13.