MGSC 290 SimNet Practical

Add a slicer based on the Location column. Set the slicer to Slicer Style Light 5.

Click the Insert Slicer button [Table Tools Design tab, Tools group]. Select Location and press OK. Click the More button [Slicer Tools Options tab, Slicer Styles group]. Select a style for the Slicer. Select the Slicer Style Light 5 slicer style.

Insert the current date and time in cell A1.

You clicked cell A1, double-clicked in cell A1, double-clicked in cell A1, clicked cell A1, clicked the A1 Cell Input, typed in cell A1, clicked cell A1, double-clicked in cell A1, double-clicked in cell A1, typed in cell A1, clicked the formula bar, typed "" in the formula bar, clicked the formula bar, typed "=now()" in the formula bar, and pressed Enter.

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.

You clicked cell F2. In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the AutoSum button arrow, clicked the Lookup & Reference button. In the Lookup & Reference menu, you clicked the Insert Function… menu item. Inside the Insert Function dialog, you typed sumif in the Search for a function input, pressed the Enter key, and clicked the OK button. Inside the Function Arguments dialog, you 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.

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.

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 Insert Function… menu item. Inside the Insert Function dialog, you typed vlookup in the Search for a function input, pressed the Enter key, and clicked the OK button. Inside the Function Arguments dialog, you typed a3 in the Lookup_value input, typed abbreviation in the Table_array input, typed 2 in the Col_index_num input, typed false in the Range_lookup input, and clicked the OK button.

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.

In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Financial button. In the Financial menu, you clicked the PMT menu item. Inside the Function Arguments dialog, you clicked the DialogHeader view, clicked the dialog undefined button, and clicked the dialog Close button. In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Financial button. In the Financial menu, you clicked the PMT menu item. You clicked cell B3. Inside the Function Arguments dialog, you typed B3/12 in the Rate input. You clicked cell B4. Inside the Function Arguments dialog, you typed – in the Pv input. You clicked cell B2. Inside the Function Arguments dialog, you clicked the OK button.

Without adjusting the column widths, guarantee that all columns will print on one page.

In the Home Ribbon Tab in the Number Ribbon Group, you clicked the Number group dialog launcher. Inside the Format Cells dialog, you clicked the dialog Close button. In the Page Layout Ribbon Tab in the Scale To Fit Ribbon Group, you clicked the Scale To Fit Group Dialog Launcher dialog launcher. Inside the Page Setup dialog, you selected the Fit to: Radio Button, clicked the OK button.

From Page Break Preview view, insert a page break immediately above the selected cell.

In the Page Layout Ribbon Tab in the Page Setup Ribbon Group, you clicked the Breaks button. In the Breaks menu, you clicked the Insert Page Break menu item.

Apply the preset margin option that will add the least amount of white space around the edges of the printed page.

In the Page Layout Ribbon Tab in the Page Setup Ribbon Group, you clicked the Margins button. In the Margins menu, you selected the Narrow option.

Add a footer that displays the page number in the center section.

In the Design Ribbon Tab in the Header & Footer Elements Ribbon Group, you clicked the Page Number button.

Use Format Painter to copy the formatting from cell D1 and apply it to cell E1.

In the Home Ribbon Tab in the Clipboard Ribbon Group, you clicked the Format Painter button. You clicked cell E1.

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.

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 SUMPRODUCT menu item. Inside the Function Arguments dialog, you typed Rents in the Array1 input, pressed the Tab key, typed Leases in the Array2 input, clicked the DialogHeader view, clicked the dialog undefined button, and clicked the OK button.

Rename Sheet1: Dec 26

You right clicked the Sheet1 tab. In the Tab Right Click menu, you clicked the Rename menu item. You typed "Dec 26", pressed Enter.

Insert the current date in cell A1. Do not include the current time.

You typed =today() in cell A1, pressed Enter.

Add a new worksheet to the left of the Dec 18 sheet.

You right clicked the Dec 18 tab. In the Tab Right Click menu, you clicked the Insert… menu item. Inside the Insert dialog, you clicked the Worksheet image, clicked the OK button.

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 350000 or 1000 if it is not.

In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Logical button. In the Logical menu, you clicked the IF menu item. Inside the Function Arguments dialog, you typed 35000 in the Logical_test input. You clicked cell B7, clicked cell B9. Inside the Function Arguments dialog, you typed B9>=350000 in the Logical_test input, typed 35000 in the Value_if_true input, typed 1000 in the Value_if_false input, and clicked the OK button.

Enter a formula in cell D5 to calculate B5/B4 rounded up to 4 decimal places.

You clicked the formula bar, typed "=roundup(b5/b4,4)" in the formula bar, and pressed Enter.

Enter a formula in the selected cell to calculate the profit projection for 2017: total sales (cell F4) minus the cost of goods sold (cell F5)

You clicked cell F4, clicked cell F6, double-clicked in cell F6, double-clicked in cell F6, clicked cell F6, clicked the F6 Cell Input, typed = in cell F6, clicked cell F4, typed =F4- in cell F6, clicked cell F5, and pressed Enter.

Hide the formulas in this worksheet and display the values instead.

You clicked cell C2, clicked cell C2, clicked cell B1, and pressed the Ctrl + C keyboard shortcut. In the Formulas Ribbon Tab in the Formula Auditing Ribbon Group, you clicked the Show Formulas button.

Hide the column showing 2016 data and the bonus rate.

You right clicked the column B header. In the Col Header Right-Click menu, you clicked the Hide menu item.

Show the tracer arrows from the precedent cells to cell C7.

In the Formulas Ribbon Tab in the Formula Auditing Ribbon Group, you clicked the Trace Precedents button.

Use AutoFill to copy the formula and formatting in cell E2 to cells E3:E6.

You clicked and dragged the cell E2 fill handle, released the mouse button on cell E6.

Add a Blue, Accent 5 (in the top row of theme colors, the second color from the right) bottom border to the selected cells. Use the thickest single line style available.

In the Home Ribbon Tab in the Font Ribbon Group, you clicked the Borders button arrow. In the Borders menu, you clicked the More Borders… menu item. Inside the Format Cells dialog from the Style: list, you clicked the Double Thick Underline item. Inside the Format Cells dialog, you clicked the Border Bottom button. In the Line Color menu, you selected the Blue, Accent 5 color option. Inside the Format Cells dialog, you clicked the OK button.

Center the content in the selected cells horizontally.

In the Home Ribbon Tab in the Alignment Ribbon Group, you clicked the Align Center button.

Print the entire workbook.

You opened the backstage view, clicked the Print navigation button, clicked the Print Selection button, clicked the Print Selection button, and clicked the Print Selection button. In the Print Selection menu, you clicked the Print Entire Workbook menu item. You clicked the Print button.

Modify the Dec 18 worksheet so gridlines will print.

In the Page Layout Ribbon Tab in the Page Setup Ribbon Group, you clicked the Page Setup dialog launcher. You clicked the Margins tab, clicked the Header/Footer tab, and clicked the Sheet tab. Inside the Page Setup dialog, you checked the Gridlines check box, clicked the OK button.

Enter a formula in cell D5 to calculate B5/B4 rounded down to 4 decimal places.

You clicked the formula bar, typed "=rounddown(b5/b4,4)" in the formula bar, and pressed Enter.

Clear the print area.

In the Page Layout Ribbon Tab in the Page Setup Ribbon Group, you clicked the Print Area button. In the Print Area menu, you clicked the Clear Print Area menu item.

Enter a formula in cell D5 to calculate B5/B4 rounded to 4 decimal places.

You clicked the formula bar, typed "=round(b5/b4,4)" in the formula bar, and pressed Enter.

Display the formulas in this worksheet.

In the Formulas Ribbon Tab in the Formula Auditing Ribbon Group, you clicked the Show Formulas button.

Add an element to the center section of the header to display the current date (not Dec 18, which is the worksheet name).

MISSED (E1-52)

Set row 1 to print on every page.

MISSED (E1-56)

Insert a Clustered Column chart based on the first recommended chart type.

Click the Quick Analysis Tool button, and then click the Charts tab. Click Clustered Column.

1. Drag the chart object so that the top left corner is at cell A20.
2. Resize the selected chart so the lower right corner of the chart is at cell H35.

1. Click and drag the chart so it appears to the below the last row of data. 2. Click and drag a resize handle down and to the right until the cursor is at cell H35.

Apply the Style 7 chart style to the chart.

Click the More button on the Chart Tools Design tab, Chart Styles group. Click Style 7 in the gallery.

Apply the Monochromatic Palette 2 color scheme from the Monochromatic section.

On the Chart Tools Design tab, in the Chart Styles group, click the Change Colors button. Click Monochromatic Palette 2, it is the second option in the Monochromatic section.

Add a title to the chart object above the chart that reads Second Quarter Activity.

Click the Chart Elements button to the right of the chart. Click to place a check mark in the Chart Title check box. Click to edit the new Chart Title element and type Second Quarter Activity and press Enter.

Add data labels to the selected pie chart.

Click the Chart Elements button in the top right corner of the chart. Select the Data Labels box.

Add a trendline to this chart.

Click the Chart Elements button. Select the Trendline box in the Chart Elements pane.

Switch the data series with the categories on this chart.

Click the Switch Row/Column button [Chart Tools Design tab, Data group].

Change the selected chart to a Clustered Column chart.

Click the Change Chart Type button [Chart Tools Design tab, Type group]. Click the Recommended Charts tab. Select Clustered Column. Click OK.

Filter out Excursion data from the chart.

Click the Chart Filters button at the top right corner of the chart. Deselect the Excursions box in the Series area. Click Apply.

Change the color of the selected gridlines to Black, Text 1. It is the second option in the first row under Theme Colors.

Click the Shape Outline button arrow[Chart Tools Format tab, Shape Styles group] and select the second option in the first row under Theme Colors.

Apply the the fourth option in the first row in the WordArt gallery to the title of the chart.

Click the More button [Chart Tools Format tab, WordArt Styles group]. Choose the fourth option in the first row in the WordArt gallery.

Insert a 3‐D Pie chart.

Click the Insert Pie or Doughnut Chart button [Insert tab, Charts group]. Select the 3-D Pie chart type.

Double-click the pie to open the Format Data Series task pane.

Double-click the pie chart.

Add a Clustered Column – Line on Secondary Axis combo chart to the worksheet.

Click the Insert Combo Chart button [Insert tab, Charts group]. Select the Clustered Column – Line on Secondary Axis chart type .

Change the combo chart so the series is plotted on the secondary axis

Click the Format Selection button [Chart Tools Format tab, Current Selection group]. Select the Secondary Axis box in the task pane.

Insert a Sunburst chart.

Click the Insert Hierarchy Chart [Insert tab, Charts group]. Choose Sunburst.

Insert a Column sparkline in cells H5:H8 for the selected cells.

Click the Column Sparkline button [Insert tab, Sparklines group]. With the Location Range box active, select cells H5:H8. Click OK.

Change the selected sparklines to Green, Accent 6, Darker 25%.

Click the Sparkline Line Tools Design tab. Click the Sparkline Color button [Sparkline Tools Design tab, Style group]. Choose Green, Accent 6, Darker 25%.

Clear the sparklines in the group with the selected sparkline.

Click the Sparkline Tools Design tab. Click the arrow with Clear Selected Sparklines button [Sparkline Tools Design tab, Group group]. Choose Clear Selected Sparklines Groups.

Format the selected data as a table with Table Style Medium 20 with headers.

Click the Format as Table button. Select Table Style Medium 20 from the gallery. Verify that the My table has headers box is selected in the Format As Table dialog box. Click OK.

Add a Total Row to the table and add a Count calculation to the Category column

Click the Table Tools Design tab and select the Total Row check box [Table Tools Design tab, Table Style Options group]. Click cell D31. Click the cell drop-down arrow and select Count.

Use the Table Tools Design tab to change the style of the table to Table Style Light 15.

Click the Table Tools Design tab. Click the More button [Table Tools Design tab, Table Styles group]. Select Table Style Light 15.

Remove the duplicates in the selected table based on the Names and Amount columns.

Click the Table Tools Design tab. Click the Remove Duplicates button [Table Tools Design tab, Tools group]. Make sure only the Name and Amount check boxes are selected. Click OK. Click OK.

Convert the selected table back to a range.

Click the Table Tools Design tab. Click the Convert to Range button [Table Tools Design tab, Tools group]. Click Yes in the message box.

Create a Conditional Formatting rule to format cells that are Less Than 4000 with Light Red Fill with Dark Red Text.

Click the Conditional Formatting button. Hover over Highlight Cells Rules and select Less Than. In the Format cells that are LESS THAN box, type 4000. Click OK.

Add a Top 10% highlight rule to the selected cells.

Click the Quick Analysis button. Choose Top 10%.

Add Data Bars to the current selection.

Click the Quick Analysis button. Choose Data Bars.

Sort the data for the selected cell from A to Z..

Click the Data tab. Click the A to Z button.

Sort the selected data first by Location on Values from A to Z and then by Amount on Values from Largest to Smallest.

Navigate to the Data tab. Click the Sort button. Click the Sort by arrow and select Location. Choose Values for Sort On and A to Z for Order. Click Add Level. Click the Then by arrow and select Amount. Choose Values for Sort On and Largest to Smallest for Order . Click OK.

Filter the data on Rentals in the Category column.

Navigate to the Data tab and click the Filter button. Click the down arrow in the Category cell (D4). Deselect all of the data types by clicking (Select All). Select Rentals. Click OK.

Add subtotals at each change in Location to the Transactions and Amount columns using the SUM function.

Click the Subtotal button [Data tab, Outline group]. Click the At each change in arrow and choose Location. In the Add subtotal to list box, select Transactions and Amount. Click OK.

Import the ImportText-04.txt to the workbook at the current location.

Click the From Text/CVS button [Data tab, Get & Transform Data group]. Select ImportText-04.txt in the Import Data dialog box. Click Import. Click the Load arrow and select Load To. Select Existing worksheet. Confirm cell A4 as the location. Click OK in the Import Data dialog box.

Add the email address [email protected] in the current cell. Start adding the email address for Corey Daniels: [email protected] and use Flash Fill to complete the column.

Type [email protected] and press Enter. Start typing [email protected]. Press Enter when Flash Fill suggests email addresses.

Export the file as a tab delimited text file with the current name in the Documents folder.

Click the File tab and select Export. Select Change File Type. Select Text (Tab delimited) as the file type. Click Save As. Accept the file name for the text file in the File name box. Click Save. Click Yes to acknowledge that Excel features may be lost.

Add a new Recommended PivotTable showing the sum of Amount by Location.

Click the Recommended PivotTables button [Insert tab, Tables group]. Locate and click the Sum of Amount by Location preview tile. Click OK.

Add the Classification field to the bottom of the Rows area.

Click and drag the Classification field from the Choose fields to add to report area to the bottom of the Rows area.

Set the PivotTable to Pivot Style Light 7. Add Banded Rows and Banded Columns to the PivotTable. Remove Grand Totals from both Rows and Columns on the PivotTable.

Click the More button for the PivotTable Styles group. Select the Pivot Style Light 7 style. Select the box for Banded Rows in the PivotTable Styles Options group. Select the box for Banded Columns in the PivotTable Styles Options group. Click the Grand Totals button [Layout group] . Choose Off for Columns and Rows.

Insert a Clustered Column PivotChart based on the selected PivotTable.

Click the PivotChart button [PivotTable Tools Analyze tab, Tools group]. Select the Clustered Column chart. Click OK.

Create a Static Consolidation
1. Open the Consolidate dialog box.
2.Using the SUM function, add the references for the Cass Lake tab, cells B5:D8 and the Baudette tab, cells B5:D8. Complete the data consolidation

1.Click the Data tab. Click the Consolidate button [Data Tools group]. 2.Verify the SUM function is selected. Click the Reference box. Click the Cass Lake sheet tab. Select cells B5:D8. Click Add in the Consolidate dialog box. Click the Baudette sheet tab. Click Add in the Consolidate dialog box. Click OK.

Create a Dynamic Consolidation
1. Open the Consolidate dialog box
2. Using the SUM function, add the references for the Cass Lake tab, cells B5:D8 and the Baudette tab, cells B5:D8. Make sure that updates to the Cass Lake tab and Baudette tab end up in the consolidation automatically. Complete the data consolidation.

1. Click the Data tab. Click the Consolidate button [Data Tools group]. 2. Verify the SUM function is selected. Click the Reference box. Click the Cass Lake sheet tab. Select cells B5:D8. Click Add in the Consolidate dialog box. Click the Baudette sheet tab. Click Add in the Consolidate dialog box. Select the Create links to

Create a Dynamic Data Consolidation by Category
1. Open the Consolidate dialog box
2.Set the calculation for Average. Then add the Breezy Point tab, cells B4:D8 and Walker tab, cells B4:D8 to the Consolidation.
3. Ensure that this consolidation correctly identifies the labels in the top row of your source data and ensure that any updates to the source data are automatically updated in the consolidation. Complete the consolidation.

1. Click the Data tab. Click the Consolidate button [Data Tools group] 2.Click the Function arrow. Select Average. Click the Reference box. Select the Breezy Point sheet tab. Select cells B4:D8. Click the Add button in the Consolidate dialog box. Click the Walker sheet tab. Click Add. 3. Ensure that this consolidation correctly identifies the labels in the top row of your source data and ensure that any updates to the source data are automatically updated in the consolidation. Complete the consolidation.

Group all of the tabs in this workbook.

Click the Cass Lake sheet tab. Press Shift and click the Dynamic sheet tab to group all sheets.

Link Workbooks with a Dynamic Data Consolidation
1. Open the Consolidate dialog box.
2. Using the SUM function, add the data from cells B5:D8 from the Cass Lake tab on the PP E5-1 workbook. Then add the data from cells B5:D8 from the Baudette tab on the PP E5-1 workbook.
3. Link the data in the dependent notebook to the source notebook. Complete the Consolidation and return to the dependent notebook.

1. Click the Data tab. Click the Consolidate button [Data Tools group]. 2. Make sure the SUM function is selected. Click the Reference box. On the View tab, in the Window group, click Switch Window and select PP E5-1.xlsx. Select cells B5:D8 on the Cass Lake tab. Click Add on the Consolidate window. Click the Baudette tab. Click Add. 3. Select the Create links to source data box. Click OK. On the View tab, in the Window group, click Switch Window and select PP E5-2.xlsx to switch to the dependent workbook.

1. Insert a SmartArt graphic of the Radial Venn type.
2. Enter the text Room and Cabin Rentals in the top circle of the SmartArt. Click outside the SmartArt when you have finished adding text.

1. Click the Insert tab. Click the Insert a SmartArt Graphic button [Illustrations group]. Select Cycle on the left hand menu. Select the Radial Venn diagram (first option, fourth row). Click OK. 2. Click the top circle of the SmartArt diagram. Type Room and Cabin Rentals then click outside the SmartArt.

1. Set the SmartArt style to the 3-D Inset
2. Set the SmartArt style to Colorful – Accent Colors.

1. Click the SmartArt Tools Design tab. Click the More button in the SmartArt Styles group. Select Inset in the 3-D section. 2. Click the SmartArt Tools Design tab.. Click the Change Colors button [SmartArt Styles group]. Select Colorful – Accent Colors.

Insert the PLR image at the current location.

Click the Insert tab. Click the Pictures From File button [Illustrations group]. Select the PLR file. Click Insert.

Add a hyperlink at the selected cell (G2) that will link to the SmartArt tab. The link should say View Illustration with a ScreenTip that says Click this link to view the graphic.

Select the Insert tab. Click the Link button. Select Place in This Document. Select the sheet name SmartArt in the Or select a place in this document list. Enter the text View Illustration in the Text to display box. Click the ScreenTip button. Type the text Click this link to view the graphic. Click OK in the Set Hyperlink ScreenTip dialog box. Click OK in the Insert Hyperlink dialog box.

Mark this workbook as final.

Click the File tab. Click the Protect Workbook button. Select Mark as Final. Click OK in the dialog box. Click OK.

Use the password PLR#18 to encrypt this workbook.

Click the File tab. Click the Protect Workbook button. Select Encrypt with Password. Type the password PLR#18 and click OK. Reype the password PLR#18 and click OK.

1. In this cell enter 3 letters and a wildcard character so that when you use the DSUM function you will return Cass Lake values.
2.In cell D23 enter a DSUM function based on the List database, Goal field, and the Location criteria you entered in step 1.

1. Type cas* in cell B3. 2. Click the Insert Function button. Select Database from the Or select a category drop-down menu. Select DSUM from the Select a function section. Click OK. Click F3 to open the Paste Names dialog. Select List. Click OK. Type goal in the Field text box. Select the Criteria text box. Select cells B2:B3. Click OK.

1. Open the Function Arguments dialog box for the AND function.
2. Add D7>=C7 to the Logical1 box and D7>=$L$7 to the Logical2 box.

1. Click the Formulas tab. Click the Logical button. Select AND. 2. Select the Logical1 box. Type D7>=C7. Select the Logical2 box. Type D7>=$L$7. Click OK to add the AND function to the worksheet.

View the results of a function that will return TRUE if D7 is greater than or equal to C7 or D7 is greater than or equal to L7.

Type =or(d7>=c7,d7>=l7) and press Enter.

Nest an AND function within an IF function and return its value. The AND function should require d7 to be greater than or equal to c7 and d7 or greater than or equal to L7. If the AND function is true, the IF function should return Met both but if not it should return No.

Type =IF(AND(D7>=C7,D7>=L7),"Met both","No") and press Enter.

Add an IFS function that will return Excellent if the value in I7 is greater than or equal to 96%; Good if I7 is greater than or equal to 85%; and Average if the value in I7 is less than 85%.

Click the Logical button [Formulas tab, Function Library group] and choose IFS. Enter I7>=96% in the Logical_test1 box. Type Excellent in the Value_if_true1 box. Type I7>=85% in the Logical_test2 box. Type Good in the Value_if_true2 box. Type I7<85% in the Logical_test3 box. Type Average in the Value_if_true3 box. Click OK.

Insert a function in the selected cell such that it will display the value of D7/C7 when the function works or Check values in columns C and D. if the function has an error.

Click the Logical button [Formulas tab, Function Library group]. Choose IFERROR. Enter D7/C7 in the Value argument. Enter Check values in columns C and D. the Value_if_error argument. Click OK.

Create an index based on an array of the description data for this worksheet that will display the description of the 10th item.

Click the Lookup & Reference button. Select INDEX. The array,row_num,column_num option is selected. Click OK. Select B5:B29. Type 10 for the Row_num argument. Click OK.

Build a MATCH function to find exact matches of the value of the result in H9 with the values in the Description column of the inventory.

To Build a MATCH Function, click the Lookup & Reference button [Formulas tab, Function Library group]. Choose MATCH. Select cell H9 for the Lookup_value. Select cells B5:B29 for the Lookup_array. Enter 0 for the Match_type text box. Click OK.

Nest MATCH in INDEX
1. Using the Function Arguments dialog, start an INDEX function.
2. Use the named range Inventory for the array. For the Row_num argument, nest a MATCH function using the Function Arguments dialog. The MATCH function should lookup the value for cell B15 in the range B5:B29 with a perfect match.
3. Return to the INDEX Function Arguments dialog and move to the Column_num argument.
4.For the Column_num argument, nest a MATCH function using the Function Arguments dialog. The MATCH function should look up the value for the header cell E4 in the range A4:E4 with a perfect match.
5. Return to the INDEX Function Arguments dialog box and insert the function.

1. Click the Lookup & Reference button [Formulas tab, Function Library group]. Choose INDEX. Array, row_num, column_num is selected by default. Click OK. 2. Type Inventory in the Array box. Click the Row_num box. Click the Name box drop-down arrow. Select MATCH from the Name drop-down list. Type B15 in the Lookup_value box. Click the Look_array text box. Select cells B5:B29. Click the Match_type text box. Type 0. 3. Click the word INDEX in the formula bar. Click the Column_num text box to select it. 4. Ensure the Column_num box is selected. Click the Name box arrow. Select MATCH from the Name box drop-down menu. Type E4 in the Lookup_value box. Click the Lookup_array box. Select cells A4:E4. Click the Match_type box. Type 0. 5. Click the word INDEX in the formula bar. Click OK.

Calculate the years that have passed since BJ Francine was hired in the current cell using the TODAY function in cell F3.

Type =( then select cell F3. Type – then select cell C3. Type )/365.25 and press Enter.

Calculate the number of minutes passed from the start to first catch for Greenfield Timothy in cell E3.

Type =( and select cell D3. Type – then select C3. Type )2460 and press Enter.

Build a MAD Calculation
1. Calculate the absolute value of the difference between the forecast value and actual value for each item.
2. In cell H7 calculate the average of the absolute values in E5:E29.

1. In cell E5, type =abs( and select C5. Type – and select cell D5. Press Enter to complete the formula. Copy the formula to cells E6:E29. 2. Type =average(E5:E29 and press Enter to complete the function.

In cell H8, calculate a sample standard deviation for the sales data in this worksheet.

Click the More Functions button. In Statistical and choose STDEV.S. Select cells D5:D29. Click OK.

Use a function to rank the number of Magnets, PLR sold compared to the number of each item type sold.

Click the More Functions button [Formulas tab, Function Library group]. Highlight Statistical and choose RANK.EQ. Select cell D19. Click the Ref text box. Select cells D5:D29, all items sold. Click OK.

Calculate the present value of the monthly annuity payout in cell C9. The pmt is monthly so the rate and nper will have to be adjusted to match. There is no cash balance amount and payments are due at the beginning of the pay period.

Click the Financial button and choose PV. Select cell C7 for the Rate argument and type /12. Click the Nper argument box. Select cell C6 and type *12. Click the Pmt box and select cell C5. Type 1 in the Type box.

Calculate the future value of the IRA growth in cell F9. The rate, number and payment are all annual. There is no present value and the payment is at the end of the period.

Click the Financial button and choose FV. Click cell F7 for the Rate argument. Select cell F6 for the Nper argument. Select cell F5 for the Pmt argument. Click OK to calculate the function.

Use the NPV Function
1. In C18 calculate the net present value using the annual discount rate and the value of the three years of return above the destination cell.
2. Add the initial cost of investment value to your calculation to have a more realistic understanding of your net present value.

1. Click the Financial button and choose NPV. Select cell C13 for the Rate argument. Click the Value1 box and select cell C15. Add C16 to the Value2 argument and C17 to the Value3 argument. Click OK to calculate the function. 2. Click the Formula bar at the end of the formula. Type +C14 to add the cost of the investment. Press Enter to calculate the formula.

Use the TEXTJOIN function to join the text in cell A3 and B3 in cell C3. Use the spacebar as a delimiter and ignore blank spaces.

Click the Text button [Formulas tab, Function Library group] and choose TEXTJOIN. Press Spacebar for the Delimiter argument. Click the Text1 box and select cell A3. Click the Text2 box and select cell B3. Click OK.

Use the CONCAT function to join the text from cells A3 and B3 to look like "BJ Francine" in cell C3.

Click the Text button [Formulas tab, Function Library group] and choose CONCAT. Click A3 for the first text argument. Click the Text2 box and type a space for a delimiter. Click the Text3 box and select cell B3. Click OK.

Use the SUMIFS function to add up all the cells in the Calls column that come from a location that starts with the letters cas and were made by someone with a title that starts with tr.

Click the Math & Trig button [Formulas tab, Function Library group] and choose SUMIFS. Select cells D3:D21 for the Sum_range argument. Click the Criteria_range1 box and select cells C3:C21, the location column. Click the Criteria1 box and type cas to include cells with data for Cass Lake. Click the Criteria_range2 box and select cells B3:B21, the job title column. Click the Criteria2 box and type tr to include trainee data. Click OK.

In cell I6, use the AVERAGEIFS function to determine the average number of calls that people from the location starting with cas made with a title beginning with the letters tr.

Click the More Functions button [Formulas tab, Function Library group], navigate to Statistical and selecting AVERAGEIFS. Select cells D3:D21 for the Average_range argument. Click the Criteria_range1 box and select cells C3:C21 for the location. Click the Criteria1 box and type cas for Cass Lake as the location. Click the Criteria_range2 box and select cells B3:B21 for the job title. Click the Criteria2 box and type tr for Trainee as the title. Click OK.

Add a new watch to the selected cells.

Click the Watch Window button [Formulas tab, Formula Auditing group]. Click Add Watch. Verify cells A3:A21 on the Marketing worksheet are selected and click Add.

Create a new workbook based on the Monthly college budget template.

Create a new workbook based on the Monthly college budget template.

Save a Workbook as a Template
Save this file as a template named PP E7-1Template. Make sure it is available as a preview in the backstage view.

Click the File tab. Choose Save As. Choose This PC. Click the More Options link. Type PP E7-1Template in the File Name box. Select the Save Thumbnail box. Click the Save as type arrow. Choose Excel Template. Click Save.

Set data validation for the selected cells to require whole numbers larger than 0

Click the Data tab. Click the Data Validation button [Data Tools group]. Click the Allow arrow and choose Whole Number. Click the Data arrow and choose greater than. Click the Minimum box and type 0. Click OK.

Create an input message for the selected cells titled Current Stock which says Number must be positive. (include the period)

Select the Data tab. Click the Data Validation button. Click the Input Message tab. Verify that the Show input message when cell is selected box is selected. Click the Title box and type Current Stock for the title. Click the Input message box and type Number must be positive. Click OK.

Create an error alert for the selected cells that displays a warning titled Wait! and features the text Please choose from the list.

Click the Data Validation button [Data tab, Data Tools group]. Click the Error Alert tab. Verify that the Show error alert after invalid data is entered box is selected. Click the Style arrow and choose the Warning style. Click the Title box and type a label for the message box. Click the Error message box and type the message to appear in the warning box. Click OK.

Limit entries in the selected cells to a drop-down list of the options Intern, Manager, and Representative.

Click the Data Validation button [Data tab, Data Tools group]. Click the Settings tab. Click the Allow arrow and choose List. Click the Source entry box and enter Intern,Manager,Representative with no space after each comma. Click OK

Circle Invalid Data
1.Circle the invalid data in the selected cells.
2.Delete invalid data then turn off the validation circles.

1.Click the Data Validation arrow [Data tab, Data Tools group]. Select Circle Invalid Data. 2.Select cell D7 and press Delete. Select cell D20 and press Delete. Click the Data Validation arrow [Data Tools group]. Select Clear Validation Circles.

Show the form button and the developer tab
1. Add the Form… command to the Quick Access Toolbar.
2. Add the Developer tab to the Ribbon and close the Excel Options dialog box.

1. Click File and click the Options button. Select Quick Access Toolbar in the left pane. Click the Choose commands from drop-down menu and select All Commands. Choose Form… in the list of commands. Click Add. 2.Click Customize Ribbon in the left pane. Select the Developer box in the Main Tabs group. Click OK.

Unlock the selected cells.

Click the Format button [Home tab, Cells group]. Choose Lock Cell.

Protect the structure of this worksheet without a password.

Click the Protect Sheet button [Review tab, Changes group]. Ensure the Select locked cells and Select unlocked cells options are selected. Click OK.

unprotect this worksheet

Click the Unprotect Sheet button [Review tab, Changes group].

Protect this workbook without a password.

Click the Protect Workbook button [Review tab, Changes group]. Verify that the Structure box is selected. Click OK.

Share this worksheet allowing changes by multiple users at once. Keep changes for 25 days and accept other defaults.

Click the Review tab. Click the Share Workbook button [Changes group]. Select the Allow changes by more than one user at the same time box on the Editing tab. Click the Advanced tab. Click the Keep change history for text box. Type 25. Click OK to close the Share Workbook dialog box. Click OK to resave the shared workbook.

Insert a new comment from User2 which says Jennifer spent two days in Baudette. then make sure the comment will display after you click on a different cell.

Click the New Comment button. Delete the default user and type User 2: with the punctuation. On the blank line type Jennifer spent two days in Baudette. with the punctuation. Deselect the comment. Click the Show All Comments button.

Highlight the changes in this workbook that have not been reviewed.

Click the Track Changes button [Review tab, Changes group]. Choose Highlight Changes. Click the When arrow. Select Not yet reviewed. Click OK.

1. Open the Accept or Reject Changes dialog with all changes that haven’t been reviewed yet.

2.Return the first change to the original value. Then accept all other changes.

1. Click the Track Changes button [Review tab, Changes group]. Click Accept/Reject Changes. Click the When menu arrow. Choose Not yet reviewed. Click OK. 2. Click 3 (Original Value). Click Accept. Click Accept All to accept all edits.

Inspect this document. Remove all document properties and personal information. Leave the rest of the document unchanged.

Click the Check for Issues button [File tab]. Choose Inspect Document. Click Yes to save the workbook. Click Inspect. Click Remove All from the Document Properties and Personal Information section. Note the Hidden Worksheets information but do not remove them. Click Close.

Open a new worksheet tab compatibility report.

Click the Check for Issues button [File tab]. Choose Check Compatibility. Click Copy to New Sheet to create a sheet that details the issues.

Add the PLRData folder to your trusted locations. Include all subfolders and add the description PLR Tracking Data to the new trusted location.

Choose the Options command [File tab]. Choose Trust Center in the left pane. Click Trust Center Settings. Select Trusted Locations in the left pane. Click Add new location. Click Browse. Select the PLRData file. Click OK. Select the Subfolders of this location are also trusted box. Click the Description box. Type PLR tracking data. Click OK to add the folder to the list of trusted locations. Click OK to close the Trust Center. Click OK to close the Excel Options dialog box.

Add a new scenario named Best for the selected cells. Show the new scenario with the following values: 62000 for Rentals; 36000 for Excursions; 28000 for Spa_Services; and 20000 for Shop_purchases. Close the Scenario Manager.

Click the What-if Analysis button [Data tab, Forecast group]. Select Scenario Manager. Click Add. Type Best as the name in the Add Scenario dialog box. Click OK. Type 62000 for Rentals; 36000 for Excursions; 28000 for Spa_Services; and 20000 for Shop_purchases. Click OK to complete the scenario. Click Show to show the Best scenario Click Close to close the Scenario Manager dialog box.

Create a Scenario Summary for this worksheet. Use the totals in cell E8 for the Results cells.

Click the What-if Analysis button [Data tab, Forecast group]. Select Scenario Manager. Click Summary. Choose Scenario summary as the Report type. Click the Result cells box and type E8. Click OK.

Use Goal Seek to update the selected cell to 50 by updated the value in B4. Accept the solution.

Click the What-if Analysis button [Data tab, Forecast group]. Choose Goal Seek. Click the To value box and type 50. Click the By changing cell box and B4. Click OK. Click OK to accept the solution.

Install the Solver Add-in.

Select Options [File tab]. Click Add-Ins in the left pane. Click Solver Add-in in the list of Inactive Application Add-Ins. Click Go near the bottom of the dialog box to open the Add-ins dialog box. Select the Solver Add-in box. Click OK.

Open the Solve Parameters dialog box. Set the objective location to D8 and the calculation to Max. Have the solver change variables in cells B4:B7. Shift to the Add Constraint dialog box.
Add the constraint B4 is &lt;= 150.
Make sure that all variables in the solver are positive. Use the GRG Nonlinear method for solving. Run the solver and keep the results.

Click the Solver button [Data tab, Analyze group]. Type D8 in the Set Objective box. Verify the MAX radio button is selected. Click the By Changing Variable Cells box. Select cell B4:B7. Click Add to the right of the Subject to the Constraints box. Select B4 for the Cell Reference. Verify that the operator is <= and select the Constraint box. Type 150. Click Add. Verify the Make Unconstrained Variables Non-Negative box is selected and the GRG Nonlinear option is selected. Click Solve. Click OK to save the solution.

Create the One-Variable Data Table based on the column input cell B8.

Click the What-If Analysis button. Choose Data Table. Select the Column input cell text box. Type B8 and click OK.

Create a two-variable data table from the selected cells. Use the data in B15 for the row input. Use the data in cell B8 for the column input.

Click the What-If Analysis button. Choose Data Table. Type B15 in the Row input cell. Enter B8 in the Column input cell. Click OK.

Create a forecast sheet with a line chart.

Click the Forecast Sheet button [Forecast group]. Verify the Create a line chart option is selected and click Create.

Create a Slicer based on the selected PivotTable. Organize the slicer based on the Department option and display the Apparel field.

Click the Insert Slicer button [PivotTable Tools Analyze tab, Filter group]. Select Department from the Insert Slicers dialog box. Click OK. Click Apparel in the Slicer window.

Insert a new timeline based on the TblSpaSales.Date data.
Change the timeline style to Timeline Style Dark 5.

Select the PivotTable Tools Analyze tab. Click the Insert Timeline button [Filter group]. Click the TblSpaSales.Date check box. Click OK. Click the Timeline Tools Options tab. Click the More button [Timeline Styles group]. Select Timeline Style Dark 5 in Timeline Styles gallery.

Insert a calculated field named Increased Sales based on the Sold field increased by 20%.

Click the Fields, Items, and Sets button [PivotTable Tools Analyze tab, Calculations group]. Select Calculated Field. Type Increased Sales in the Name box. Delete the 0 and space next to the =. Click the Sold field. Click Insert Field. Type *120%. Click OK.

Add grand totals for the end of the PivotTable columns but not rows.

Select the PivotTable Tools Design tab. Click the Grand Totals button [Layout group]. Select On for Columns Only.

Add the Developer tab to the ribbon. Ensure that macros are disabled with notification then open the ParadiseLakes-09 workbook and enable its content.

Click the File tab. Select Options. Click Customize Ribbon in the left pane. Select the Developer box in the Main Tabs list. Click OK. Click the Developer tab. Click the Macro Security button [Code group]. Verify the Disable all macros with notification button is selected. Click OK. Click the File tab. Click Open on the left. Double click ParadiseLakes-09 on the recent list. Click Enable Content in the message bar.

Run the InsertDate macro

Click the Developer tab. Click the Macros button [Code group]. The InsertDate macro is selected. Click Run.

Begin recording a macro stored in this workbook named FirstQuarter with the shortcut Ctrl+Q and the description Display First Quarter in cell A32. (include the period). Record the macro with the steps select cell A32 and type First Quarter then move the cursor to cell A33. Complete the macro.

Click the Record Macro button [Developer tab, Code group]. Type FirstQuarter. Click the Shortcut key box and type Q. Verify that the Store macro in box is set to This Workbook. Click the Description box. Type Display First Quarter in cell A32. (include the period). Click OK. Click cell A32. Type First Quarter and press Enter to move the cursor to cell A33. Click the Stop Recording button.

Save the file as a macro-enabled workbook with the name PP E9-1.

Click the File tab and select Save As. Type PP E9-1. Click the Save as type arrow and choose Excel Macro-Enabled Workbook. Click Save.

Open the Visible Basic Editor for the InsertDate macro.

Click the Insert Controls button [Developer tab, Controls group]. Click the Button (Form Control) command. Click and drag the button to cover cells E2:F2. Select InsertDate and click OK. Click inside the button control to display an I-beam pointer. Type Insert Date as the caption for the button. Deselect the button.

Insert a Button form control in cells E2:F2. Assign the InsertDate macro to the button and rename it Insert Date. Finish assigning the macro to the button. Update all of the references to cell E4 to cell E5 and all references from E5 to E6 in the macro. Close the VBE window.

Click the Macros button [Developer tab Code group]. Select InsertDate in the list of macro names. Click Edit. Click after E4 in the comment Insert current date in cell E4. to place the insertion point. Change E4 to E5. Click after the text E4 after the first code line Range("E4").Select. Change E4 to E5. Click after the text E5 after the last code line Range("E5").Select. Change E5 to E6. Click the Close button.

Begin recording a macro that uses relative references. Name the macro AddFill. Use the shortcut Ctrl+F. Insert the description Add fill to row. (include punctuation). Record a macro that does the following: selects the 3 cells that make up the row of the currently selected cell; fills them with a Green, Accent 6, Lighter 80% fill; and then moves to the cell at the beginning of the row, two rows down.

Click the Use Relative References button [Developer tab, Code group]. Click Record Macro. Type AddFill. Click the Shortcut key text box and type F. Verify the macro will be saved in this workbook and click the Description box. Type Add fill to row. Click OK. Select A5:C5. Click the Fill Color arrow [Home tab Font group]. Choose Green, Accent 6, Lighter 80%. Click cell A7. Click the Developer tab. Click the Stop Recording button.

Save this file as a macro-enabled template with the same name and in the same folder as the current workbook.

Choose Save As from the File tab. Choose Excel Macro-Enabled Template from the Save as type list. Click Save.

Set the Navigate To Category to Tables and Related Views. Change the Filter By setting to All Tables.

Click the Navigation Pane arrow to the right of All Access Objects. Select the Tables and Related Views category. Click the Navigation Pane arrow. Select All Tables in the Filter By Group area.

Create a new blank desktop database named PP A1-2.

Click the File tab and select New. Select the Blank database button. Type PP A1-2 and click Create.

Save a backup of the database. Accept the file name suggested by Access.

Click the File tab. Click Save As. Click Back Up Database in the Save Database As section at the right side of the screen. Click the Save As button. Click Save.

Create a new table in Datasheet view.

Click the Create tab. Click the Table button.

Change the Primary Key ID to EmpID and the data type to Short Text.

Double-click the ID field. Type EmpID press the down arrow. Navigate to the Table Tools Fields tab. Click the Data Type drop down arrow. Select Short Text.

Add a new field with the Short Text data type and is named FirstName.

Click the Click to Add column header. Select Short Text. Type FirstName and press Enter.

Using Datasheet view, set the Caption of the selected field to First Name. Set the Description to Employee’s First Name. Change the Field Size to 25.

Click the Name & Caption button [Table Tools Fields tab, Properties group]. Type First Name in the Caption property. Type Employee’s First Name in the Description property. Click OK. Type 25 in the Field Size property [Table Tools Fields tab, Properties group] and press Enter.

Save the table with the name Employees.

Click the File tab. Select Save. Type Employees in Save As dialog. Click OK.

Open the Employees table

Double-click the Employees table.

Add 101 to the Employee ID field and move to the next field.

Type 101 and press Tab.

Append the CentralSierraDataFile-01 Excel file to the Employees table.

Click the New Data Source button [ External Data tab, Import & Link group], select From File and then select Excel. Click the Browse button. Select the CentralSierraDataFile-01 Excel file. Click the Open button. Select the Append a copy of the records to the table radio button. Click OK. Click the Next button. Click Finish. Click the Close button.

Move the Branch field to the right of the Title field.

Click the Branch field. Click and drag the Branch field to the right of the Title field.

Use the Search box to find the first record with the word Health.

Click in the Search box at the bottom of the window and type Health.

Using the Drop-down List, sort the Start Date from Newest to Oldest.

Click the drop-down arrow to the right of Start Date. Select Sort Newest to Oldest on the drop-down list.

Sort the records so the results are sorted by Branch and then by Department with the last entry in alphabetical order first

Click the Branch field and drag to the Department field to select both. Click the Descending button [Home tab, Sort & Filter group].

Filter the data by the Title field for all records that have the text Insurance Agent.

Click Insurance Agent cell in the Title field for Employee 103. Click the Selection button. Select Equals "Insurance Agent" from the drop-down list.

Filter the data so that any record that contains the text Assistant in the Title field are displayed.

Click the drop-down arrow to the right of the Title field name. Hover the mouse over Text Filters and select Contains. Type Assistant in the Title contains box and press Enter to submit the filter.

Create a new table in Design view.

Create a new table in Design view.

Add a new field named ProductName that is the Short Text data type without leaving Design view.

Click the first blank field name cell to select it. Type ProductName and press Enter to move to the Data Type cell. Click the Data Type drop down arrow. Select Short Text.

In Design view, add a new field before the DailyRentalRate field.

Click the row selector next to the DailyRentalRate field. Click the Table Tools Design tab. Click the Insert Rows button.

In Design view change the primary key to the ProductName field.

Click the row selector for the ProductName field. Click the Table Tools Design tab. Click the Primary Key button.

In Design view, delete the Comments field.

Click the Comments row selector. Select the Table Tools Design tab. Click the Delete Rows button. Click Yes.

Switch to Datasheet view.

Click the View button.

Change the EquipmentID Field Size to 5.

Click the Field Size property box for the EquipmentID field. Type 5 in the Field Size property box and press Enter.

Change the Format property for the DatePurchased field to Medium Date.

Click the DatePurchased row selector. Click the Format property. Click the Format property drop-down arrow. Select Medium Date.

Set the default value for the State field to CA.

Click the State row selector. Click the Default Value Field Property. ype CA and press Enter.

Add the Validation Rule Like "#####" to the EquipmentID field.

Click the Validation Rule property box. Type Like "#####" in the Validation Rule property box.

Use the Expression Builder to create the validation rule Like "#####".

Click the Validation Rule property box. Click the Build button. Type Like "#####". Click OK.

Create this record validation rule: [WeeklyRentalRate]&gt;[DailyRentalRate] And [MonthlyRentalRate]&gt;[WeeklyRentalRate]

Click the Table Tools Design tab. Click the Property Sheet button. Click the Validation Rule property on the Property Sheet pane. Type [WeeklyRentalRate]>[DailyRentalRate] And [MonthlyRentalRate]>[WeeklyRentalRate]. Press Enter.

Add Validation Text, The Equipment ID must contain 5 numbers for the EquipmentID field.

Click the Validation Text property box. Type The Equipment ID must contain 5 numbers. Press Enter.

Add a Lookup Wizard to the Category field with the options Generators; Hand Tools; Plumbing; Sawing, Cutting; and Tractors.

Click the Data Type property field. Click the drop-down arrow. Select Lookup Wizard. Select the I will type in the values that I want radio button. Click the Next button. Click the first cell in the Col1 column. Type Generators in cell 1, Hand Tools in cell 2, Plumbing in cell 3 , Sawing, Cutting in row 4, and Tractors in row 5. Click the Next button. Click the Limit To List check box. Click the Finish button.

Add the Medium Date Input Mask to the DatePurchased field. Do not edit the input mask or change the placeholder character.

Click the DatePurchased row selector. Click the Input Mask property box. Click the Build button. Select the Medium Date option. Click Next. Click Next. Click Finish.

Open the Relationship Tools Design tab and the Show Table window. Add the Equipment and ServiceHistory tables to the Relationships tab. Create a relationship between the EquipmentID primary key and the FKEquipmentID foreign key. Enforce referential integrity in the relationship.

Click the Database Tools tab. Click the Relationships button. Select the Equipment. Click Add. Select the ServiceHistory table and click Add. Click Close. Click the EquipmentID field and drag it over the FKEquipmentID field and release the mouse. Click the Enforce Referential Integrity check box. Click Create.

Delete the relationship in this database.

Select the Database Tools tab. Click the Relationships button. Select the relationship line. Right-click the relationship line between the Equipment table and the OrderLineDetails field. Select Delete. Click Yes to confirm the deletion. Click Close.

Edit the relationship in this database to Cascade Update Related Fields. Close the relationship view when you are finished.

Click the Database Tools tab. Click the Relationships button. Select the relationship line. Click Edit Relationships. Select the Cascade Update Related Fields check box. Click OK. Click Close.

Open the Print Preview tab for the Equipment table.Change the orientation to Landscape.Set the Margins to Normal.Close Print Preview without closing the table.

Click the Equipment table on the Navigation Pane. Click the File tab. Click the Print button. Click Print Preview On the Print Preview tab, in the Page Layout group, click the Landscape button. Click the Margins button [Print Preview tab, Page Size group]. Select Normal. On the Print Preview tab, in the Close Preview group, click the Close Print Preview button.

Compact &amp; Repair the open database.

Click the File tab. Click the Compact & Repair Database button.

Use the Simple Query Wizard to create a query showing the details for every record in the results. Include these fields in this order: AlbumName, Artist, and Genre and ReleaseDate from the Albums table and SongTitle from the Songs table. Select the option to open the query to view information. Use the query title: SongsOnAlbums

On the Create tab, in the Queries group, click the Query Wizard button. In the New Query dialog, Simple Query Wizard is selected by default. Click OK. Click the AlbumName and click Add Single Field button. Click the Artist and click Add Single Field button. Click the Genre and click Add Single Field button. Click the ReleaseDate and click Add Single Field button. Expand the Tables/Queries list and select Table: Songs. Click the SongTitle and click Add Single Field button. Click Next. Click Next. Type SongsOnAlbums in the What title do you want for your query? box. Click Finish.

Use Query Design to create a new query with the Instructors table.

Click the Create tab on the Ribbon. Click the Query Design button in the Queries group. Select the Instructors table. Click Add to add the table. Click the Close button.

Add the Instructor field to the query.

Double-click the Instructor field.

Run the query.

Click the Run button [Query Tools Design tab, Results group]

Save with the name InstructorList and close the query.

Click Save button on the File tab. Type InstructorList in the Query Name box. Click OK. Click the X in the upper right corner of the query.

Use Query Design view to add the Classes and Departments tables.

Click the Create tab. Click the Query Design button. The Classes table is selected. Click Add to add it to the query. Click the Departments table. Click the Add button to add the Departments table. Click Close.

Add a criterion to the query so that the only results will be from the City of Roseville. Run the query and view the results.

Click the Criteria cell for the City field. Type Roseville. On the Query Tools Design tab, in the Results group, click the Run button.

Update the criteria to the Garage field so that it displays any data that ends in the word Attached and has any length of characters before it. Run the query.

Select the Criteria row of the Garage field. Type Like "*Attached". Click the Run button [Query Tools Design tab, Results group].

Sort the City field in Ascending order. Run the query and review the results.

Click the City Sort cell to select it. Click the drop-down arrow in the City field Sort cell. Select Ascending. On the Query Tools Design tab, in the Results group, click the Run button.

Add the AND criteria Rick to the FirstName and Chung to the LastName field. Run the query.

Click the Criteria cell in the FirstName field. Type Rick. Click the Criteria cell of the LastName field. Type Chung. On the Query Tools Design tab, in the Results group, click the Run button.

Use AND criteria to show records with SqFt values between 2000 and 3000 (including the numbers 2000 and 3000). Run the query.

Click the Criteria cell for the SqFt field. Type >=2000 AND <=3000. Click the Run button [Query Tools Design tab, Results group].

Add conditions to the query so that it will now show records from Rocklin AND with a List Price less than or equal to 600000 OR from Roseville. Run the query and review the results.

Select the Criteria cell in the City field. Type Rocklin. Click the Criteria cell of the ListPrice field to add the AND condition. Type <=600000. Click the or cell in the City field. Type Roseville. On the Query Tools Design tab, in the Results group, click the Run button.

Create a parameter query for the Artist field. Use the prompt Enter an Artist Name. Don’t forget to enclose the prompt in brackets. Run the query to test it. Test the parameter query using the artist Adele.

Click in the Criteria row under the Artist field and type [Enter an Artist Name]. On the Query Tools Design tab, in the Results group, click the Run button. Type Adele in the Enter Parameter Value dialog. Click OK.

Add a new calculated field named Potential Commission in the first empty column to the right of the ListPrice field. The new field should calculate a value that is .06 times the value in the ListPrice field. Run the query.

Right-click the first empty cell in the Field row of the query design grid. Select Zoom from the menu. Type Potential Commission:[ListPrice]*.06. Click OK. Click the Run button [Query Tools Design tab, Results group].

Create a new query using the Query Design button. Add the Agents and Listings tables to the query. Add the FirstName and LastName fields from the Agents table and the ListPrice field from the Listings table. Add Totals to the fields in the query. Change the calculation in the ListPrice field to Count. Run the query.

Click the Create tab. Click the Query Design button [Queries group]. Click the Agents table and click Add. Click the Listings table and click Add. Close the dialog. Click the first blank field menu and select Agents.FirstName. Click the next blank field menu and select Agents.LastName. Click the next blank field menu and select Listing.ListPrice. Click the Totals button [Query Tools Design tab, Show/Hide group]. Click the Total row cell of the ListPrice field. Select Count. Click the Run button [Query Tools Design tabs, Results group].

Create a form using the Form Wizard using every field from the Listings table that displays in a Columnar layout named Listings – Form Wizard.

Click the Create tab on the Ribbon. Click the Form Wizard button. Click the Tables/Queries drop-down arrow and select Table: Listings. Click the Move All Fields button. Click the Next button. Click the Next button. Type Listings – Form Wizard in the Title box. Click Finish.

Create a form based on the Listings table using the Form button.

In the Navigation Pane, select the Listings table. Click the Create tab. Click the Form button.

Create a Split form based on the Listings table.

In the Navigation Pane, click to select the Listings table. Click the Create tab. Click the More Forms button and select Split Form.

Create a Blank form based on the Employees table with the EmpID field.

Click the Create tab and the Blank Form button. Click the Show all tables link. Click the plus sign to expand the Employees table. Double-click the EmpID field to add it to the form.

Use the Add Existing Fields button in Layout view and add the FirstName field from the Employees table to the form.

Click the View button. Click the Add Existing Fields button [Form Layout Tools Design tab, Tools group]. Click the Show All Tables link. Click the plus sign to expand the Employees table. Double-click the FirstName field to add it to the form.

In Layout view, resize the EmployeeID label to display the Employee ID text.

Click the View button to switch to Layout view. Click the Employee ID text box control to select it. Click and drag the right border to resize the text box.

Add a row below the First Name label and FirstName text box from Layout view.

Click the View button. Select the Form Layout Tools Arrange tab. Select the First Name control. Click the Select Row button. Click the Insert Below button

In Layout view, move the License# label control to the blank control space below it.

Ensure License# label control is selected. Click and drag the License# label control to the blank control below it and release the mouse button.

Add a title that is the selected form name in Layout view. Add a Date and Time to the header. The date format should be like Saturday, August 10, 2017 and the time format should be like 4:02:45 PM.

Click the View button. Click the Title button [Form Layout Tools Design tab, Header/Footer group]. Click the Date and Time button [Form Layout Tools Design tab, Header/Footer group]. Click OK to accept the default selections.

In Layout view, change the Data restrictions in this Form to allow Data Entry and Allow Additions.

Click the View button to switch to Layout view. Click the Property Sheet button. Click the drop-down arrow in the Selection box of the Property Sheet. Select Form. Click the Data tab of the Property Sheet. Click the Data Entry property box. Click the Data Entry drop-down arrow and select Yes. Click the Allow Additions property box. Click the Allow Additions drop-down arrow and select Yes.

Edit the Property Sheet for the Form so that users cannot make edits or make deletions with the form.

Click the View button and select the Property Sheet button [Form Layout Tools Design tab, Tools group]. Click the drop-down arrow in the Selection box of the Property Sheet and select Form. Select the Allow Deletions property and click the Allow Deletions drop-down arrow. Select No. Click the Allow Edits property box and click the Allow Edits drop-down arrow. Select No.

Use Layout view to add a Button to the blank space 2 rows below the text Base Monthly Salary. The button should use the Save Record Record Operation. The button should display the text Save Employee Record and be named AddEmpForm-SaveButton.

Click the View button. Click Button [Form Layout Tools Design Tab, Controls group, 4th option on the gallery]. Click in the large blank location in the second row below the text Base Monthly Salary. In the Command Button Wizard, in the Categories section, select Record Operations. In the Actions section, select Save Record. Click Next. Click the Text radio button. Type Save Employee Record. Click Next. Type AddEmpForm-SaveButton. Click Finish.

Add a column to the right of the column of labels including the First Name label from Layout view.

Click the View button. Select the Form Layout Tools Arrange tab. Select the First Name control. Click the Select Column button. Click the Insert Right button

Open the Report Wizard. Create a report to include the ListingID, DateListed, ListPrice, Address, City, and SqFt fields from the Listings table. Do not group the report. Sort the report by ListPrice in Ascending order. Accept the report layout defaults. Name the report Listings – Report Wizard and finish the wizard.

Click the Create tab. Select the Report Wizard button. Click the Tables/Queries drop-down arrow. Select the Table:Listings. Double click the ListingID, DateListed, ListPrice, Address, City and SqFt fields. Click the Next button. Click the Next button. Click the first drop-down arrow. Select ListPrice. Click the Next button. The default Layout, Tabular and default Orientation, Portrait are selected. Click the Next button. Type Listings – Report Wizard in the Title box. Click the Finish button.

Create a blank report in this database.

Click the Blank Report button [Create tab, Reports group].

Using the Field List, add the ListingID field from the Listings table.

Click the View button. On the Report Design Tools Design tab, click the Add Existing Fields button. Click Show all tables. Click the plus sign next to the Listings table. Double-click the ListingID field.

Change the width of the ListingID column to 0.62.

Click the Property Sheet button. Click the Format tab. Type .62. Press Enter.

Open the Listings – Blank Report report in Layout view. Add the default title to the report. Add page numbers in the bottom left corner of the page that displays page totals.

Right-click the Listings – Blank Report Button report. Select Layout View. Click the Title button. Click the Page Numbers button. Select the Page N of M radio button. Select the Bottom of Page [Footer] radio button. Click the Alignment drop-down arrow. Select Left. Click OK.

Add a sort to the PetsWithoutMatchingOwner report that sorts on the Type field from Z to A. Close the Group, Sort, and Total pane when you have created the sort.

Right-click the PetsWithoutMatchingOwner report. Select Layout View. Click the Group & Sort button [Design tab, Grouping & Totals group]. Click Add a sort in the Group, Sort, and Total pane. Click Type in the select field menu. Click the with A on top drop-down arrow. Select with Z on top. Click the X in the upper right corner of the Group, Sort, and Total pane to close it.

Group the EmployeesBranchParameterReport based on the Branch field.

Right-click the EmployeesBranchParameterReport report and select Layout View. Click the Group & Sort button [Report Layout Tools Design tab, Grouping & Totals group]. Click Add a group. Select the Branch field.

Add a Count Records total for the Song Title data on the SongsOnAlbums report.

Right click the SongsOnAlbums report. Select Layout View. Select a Song Title from the report (e.g. Tired). Click the Totals button. Select Count Records.

Create a new formatting rule that changes all cells in the Base Monthly Salary column that are greater than or equal to 10000 bold and red. Apply the formatting rule and close any dialog boxes.

Right-click the EmployeesBranchParameterReport report. Select Layout View. Click any cell in the Base Monthly Salary column. Click the Report Layout Tools Format tab. Click the Conditional Formatting button. Click the New Rule button. Click the comparison drop-down arrow and select greater than or equal to as the comparison. Type 10000 into the value box. Click the Bold button. Click the Font button to change the font to the default red color. Click OK. Click Apply. Click OK.

Open the Print Preview for the EmployeeBranchParameterReport report. Change the margins to Normal and then close the Print Preview.

Open the Print Preview for the EmployeeBranchParameterReport report. Change the margins to Normal and then close the Print Preview.

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.

Remove worksheet protection from the Direct Mail worksheet.

You opened the backstage view, clicked the Unprotect Sheet 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.

Add the Developer tab to the Ribbon.

You opened the backstage view, clicked the Options navigation button, and clicked the Customize Ribbon tab. Inside the Excel Options dialog, you checked the Developer check box, clicked the OK 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 clicked the dialog undefined button, checked the Employee check box, and clicked the OK button. You clicked the Sue Chur button.

Run the Document Inspector and remove any issues found.

You opened the backstage view, clicked the Check for issues button. In the Protect Workbook menu, you clicked the Inspect Document menu item. Inside the Microsoft Excel dialog, you clicked the Yes button. Inside the Document Inspector dialog, you clicked the Re-Inspect button, clicked the Remove All (Comments and Annotations) button, clicked the Remove All (Document Properties and Personal Information) button, clicked the Re-Inspect button, and clicked the Close button.

Change the button text to: Create Q1 Pie Chart

You clicked the Button 1 text, and 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, typed Create Q1 Pie Chart.

Data that violate the validation rules in this worksheet have been circled. Remove the validation circles.

You selected the cell range D2:D33. 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 Clear Validation Circles menu item.

Add the folder named My Spreadsheets as a new trusted location.

You opened the backstage view, clicked the Options navigation button, and clicked the Trust Center tab. Inside the Excel Options dialog, you clicked the Trust Center Settings… button. Inside the Trust Center dialog, you clicked the Trusted Locations item, clicked the Trusted Locations item, clicked the Trusted Locations item, clicked the Trusted Locations item, clicked the Trusted Locations item, clicked the Trusted Locations item, clicked the Trusted Locations item, and clicked the Add New Location… button. Inside the Microsoft Office Trusted Location dialog, you clicked the Browse… button. You clicked mySpreadsheetsViewContent. Inside the Browse dialog, you clicked the Open button. Inside the Microsoft Office Trusted Location dialog, you checked the Subfolders of this location are also trusted check box, clicked the OK button. Inside the Trust Center dialog, you clicked the OK button. Inside the Excel Options dialog, you clicked the OK 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.

Install the Solver add-in.

You opened the backstage view, clicked the Options navigation button, clicked the Add-Ins tab, and clicked Cell 9×0. Inside the Excel Options dialog, you clicked the Go… button. Inside the Add-ins dialog from the addInsAvailibleSelectList list, you selected Solver Add-in. Inside the Add-ins dialog, you checked the Solver Add-in check box, clicked the OK button.

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, 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 typed b12 in the Column input cell input, clicked the OK button.

Enable sharing so more than one person at a time can work on this workbook.

[…] You clicked the Advanced tab, clicked the Editing tab. Inside the Share Workbook dialog, you clicked the Who has this workbook open now: list. Inside the Share Workbook dialog from the Who has this workbook open now: list, you selected Ken Dishner (Exclusive) – 10/7/2017 9:48 AM. Inside the Share Workbook dialog, you clicked the OK button. In the Review Ribbon Tab in the Changes Ribbon Group, you clicked the Share Workbook button. Inside the Share Workbook dialog from the Who has this workbook open now: list, you selected Ken Dishner (Exclusive) – 10/7/2017 9:48 AM. You clicked the Advanced tab. Inside the Share Workbook dialog, you selected the Just see other users’ changes Radio Button. You clicked the Editing tab. Inside the Share Workbook dialog from the Who has this workbook open now: list, you selected Ken Dishner (Exclusive) – 10/7/2017 9:48 AM. Inside the Share Workbook dialog, you checked the Use the old shared workbooks feature instead of the new co-authoring experience. check box. Inside the Share Workbook dialog from the Who has this workbook open now: list, you selected Ken Dishner (Exclusive) – 10/7/2017 9:48 AM. Inside the Share Workbook dialog, you clicked the Who has this workbook open now: list, clicked the OK button. Inside the Microsoft Excel dialog, you 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 Forecast Ribbon Group, you clicked the What-If Analysis button. In the Data Ribbon Tab in the Data Tools Ribbon Group, you clicked the Data Validation button. 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.

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.

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 Mini Toolbar, 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.

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.

In the Developer Ribbon Tab in the Code Ribbon Group, you clicked the Record Macro button. Inside the Record Macro dialog, you typed NewTheme in the Macro Name: input, clicked the OK button. In the Page Layout Ribbon Tab in the Themes Ribbon Group, you clicked the Themes button. In the Themes menu, you selected the Facet option. In the Home Ribbon Tab in the Cells Ribbon Group, you clicked the Format button. In the Format menu, you clicked the AutoFit Column Width menu item. In the Developer Ribbon Tab in the Code Ribbon Group, you clicked the Stop Recording 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.

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 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 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, and clicked the OK button.

Edit the code for the FormatPieChart macro to change the chart style from 42 to 252. Save your changes.

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. You edited the macro code. In the File menu, you clicked the Save menu item.

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.

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. You edited the macro code. In the File menu, you clicked the Save menu item.

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.

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 Goal Seek… menu item. Inside the Goal Seek dialog, you typed 550000 in the To value: input, typed c2 in the By changing cell: input, and clicked the OK button. Inside the Goal Seek Status dialog, you 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 arrow. In the Data Validation menu, you clicked the Circle Invalid Data menu item.

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. Inside the Data Validation dialog in the Allow drop-down, you selected Decimal. Inside the Data Validation dialog, you unchecked the Ignore blank check box, typed 2.5 in the Minimum: input, typed 4 in the Maximum: input, and clicked the OK button.

Add a form control button to run the CreateQ1Chart macro. The button should be placed at approximately cell A1.

In the Developer Ribbon Tab in the Controls Ribbon Group, you clicked the Insert button. In the Insert menu, you clicked the Button (Form Control) button. You clicked the undefined view. Inside the Assign Macro dialog, you 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.

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.

Begin recording a new macro.

In the Developer Ribbon Tab in the Code Ribbon Group, you clicked the Record Macro button.

Change the security setting to disable all macros and display a warning in the Message Bar so you can choose to enable them on a case-by-case basis.

In the Developer Ribbon Tab in the Code Ribbon Group, you clicked the Macro Security button. Inside the Trust Center dialog, you selected the Disable all macros with notification Radio Button, 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.