HII

Remove all duplicate Exhibitor Names from the Exhibitors worksheet. Do not remove any other records.

At the bottom of the workbook, click the Exhibitors tab. Click any cell within the table to select it. On the Data tab, in the Data Tools group, click Remove Duplicates. (Hint: Notice the entire table is automatically selected.) In the Remove Duplicates pop-up window, click the Unselect All button, check the box next to Exhibitor Name, and click OK.

On the Rental Revenue worksheet, create a group that includes only columns B:G.

At the bottom of the workbook, click the Rental Revenue worksheet tab. Select columns B:G. On the Data tab, in the Outline group, click Group.

On the Daily Schedule worksheet, create a PivotChart that shows only the Sales and Marketing sessions for Thursday. Position the PivotChart so the upper-left corner of the chart is in cell C5.

On the Daily Schedule worksheet, click anywhere on the PivotTable to select it and reveal the PivotTable Tools contextual tabs. On the PivotTable Tools Analyze tab, in the Tools group, click PivotChart. Accept the default Clustered Column chart and click OK. At the bottom of the PivotChart, click the Day filter to open it.Click Select All to clear all of the boxes for each day.Click the box for Thursday to display that day only.Click OK. At the bottom of the PivotChart, click the Track filter to open it.Click Select All to clear all of the boxes for each day.Click the boxes for Sales and Marketing to display only those two tracks.Click OK. Drag the PivotChart to position it so the upper-left corner is in cell C5.

On the Exhibitors worksheet, add a conditional formatting rule to the Exhibitor Name column that applies a Yellow fill with a 6.25% Gray pattern if the Industry is Clothing and the Booth Size is more than $1,000.

On the Exhibitors worksheet, select cell range A3:A38. On the Home tab, in the Styles group, click Conditional Formatting and select New Rule... In the New Formatting Rule pop-up window, select Use a formula to determine which cells to format. In the field beneath Format values where this formula is true: type the following: =AND(B3="Clothing",D3>1000). Click the Format... button. In the Format Cells pop-up window, on the Fill tab, configure the following:Beneath Background Color select Yellow.Beneath Pattern Style select 6.25% Gray. Click OK. Click OK again. (Hint: The yellow fill color with gray pattern should be applied to the cells containing BottomsUp, Marine Swimwear, SCUBA-wear, and Women's Divewear.)

On the Farmers Market worksheet, insert a Subtotal that calculates the Sum in the Sales column for each Location.

On the Farmers Market worksheet, select cell range A2:E86. On the Data tab, in the Outline group, click Subtotal. In the Subtotal pop-up window, configure the following:At each change in: LocationUse function: SumAdd subtotal to: Sales Accept all other defaults. Click OK.

Add French (Canada) as an editing language, but do not configure the keyboard.

On the File tab, click Options. On the Excel Options pop-up window, click Language. Beneath Choose Editing Languages, click the down arrow next to [Add additional editing language] and select French (Canada). Click the Add button. Do not enable the keyboard. Accept all other default settings. Click OK.

Create a PivotTable on a new worksheet named PivotTable that shows the amount of each type of grain exported by each country. Use the data on the Grain Exports worksheet. Position the new worksheet as the rightmost tab.

On the Grain Exports worksheet, click anywhere on the table to select it. On the Insert tab, in the Tables group, click PivotTable. On the Create PivotTable pop-up window, accept all other defaults and click OK. In the PivotTable Fields pane, drag the Country field into the Rows area. Drag the Product field into the Rows area and drop it below the Country field. Drag the Metric Tons field into the Values area. (Hint: The table should show each country listed below the Row Labels column with a group of grains listed below each country. The amount of each grain should be listed below the Sum of Metric Tons column.) Right-click the new Sheet tab and rename the tab PivotTable. Drag the PivotTable worksheet tab right so that it comes after the other four.

In cell B8 on the Payment Calculator worksheet, insert a function that uses the information provided in the B column to forecast how many payments will be required to pay off the loan.

On the Payment Calculator worksheet, click on cell B8. (Note: Always insert your formula in the cell where you want the result displayed.) In the Formula Bar, type =NPER, and then press Tab on your keyboard. To the left of the Formula Bar, click fx to open the Function Arguments pop-up wizard. In the Function Arguments wizard, enter the following:Rate: B4/12 (Note: Payments are made monthly, so you must convert the annual interest rate of 5% into the monthly rate of 5%/12.)PMT: -B5 (Note: Input the payment for the loan as a negative value because it represents an outgoing payment.)PV: B3 (Note: The PV is the payment value of the loan, which is the current amount owed.)FV: 0 (Note: The FV is the future value of the loan after all payments are completed, which should be zero. You can also leave this field blank.)Type: (Note: Type is the payment amount after the loan is paid off, which should be zero. You can also leave this field blank.) Click OK. (Hint: The result displayed in cell B8 should be 130 months, and the final formula should read =NPER(B4/12,-B5,B3,0).)

On the Programs by Park PivotTable, display the data in outline form.

On the Programs by Park worksheet, click on the PivotTable to select it and display the PivotTable Tools contextual tabs. On the PivotTable Tools Design tab, in the Layout group, select Report Layout and click Show in Outline Form.

On the Projected Sales (Next Year) worksheet, add a new column named 3% Increase that projects Sales if increased by 3%.

On the Projected Sales (Next Year) worksheet, click anywhere on the PivotTable to display the PivotTable Tools contextual tabs. On the PivotTable Tools Analyze tab, in the Calculations group, click Fields, Items, & Sets and select Calculated Field... In the Insert Calculated Field pop-up window, configure the following:Name: 3% Increase Formula: =(Sales*3%)+Sales Fields: Sales Click OK.

In cell F4 on the Ranger-led Activities worksheet, add a formula that dispays Yes if the National Park offers both Youth Programs and Astronomy Programs and otherwise displays No. Populate the remaining cells in the column with the same formula.

On the Ranger-led Activities worksheet, click cell F4. In the Formula Bar just below the ribbon, type =NOT, and then press Tab on your keyboard to begin entering the function arguments. To the left of the Formula Bar, click fx to open the Function Arguments pop-up wizard. In the Function Arguments wizard, enter the following:Logical: B4="None" (Hint: The text string "None" is case sensitive.)Click OK. Select the formula in the Formula Bar and cut it. (Hint: You can cut it by using the Cut command on the Home tab, by right-clicking and selecting Cut, or by using the keyboard shortcut Ctrl+X.) In the Formula Bar just below the ribbon, type =AND, and then press Tab on your keyboard to begin entering the function arguments. To the left of the Formula Bar, click fx to open the Function Arguments pop-up wizard. In the Function Arguments wizard, enter the following:Logical1: NOT(B4="None")Logical2: E4="Yes"Click OK. Select the formula in the Formula Bar and cut it. In the Formula Bar, enter =IF, and then press Tab on your keyboard to begin entering the function arguments. To the left of the Formula Bar, click fx to open the Function Arguments pop-up wizard. In the Function Arguments wizard, enter the following:Logical_test: AND(NOT(B4="None"),E4="Yes")Value_if_true: YesValue_if_false: NoClick OK. (Hint: The result displayed in cell F4 should be No, and the final formula should read =IF(AND(NOT(B4="None"),E4="Yes"),"Yes","No").) Drag the Fill Handle located in the lower-right corner of the cell selection box to populate the remaining cells (F5:F8). (Hint: Only Bryce Canyon and Capitol Reef should display "Yes.")

On the Rental Revenue worksheet, trace the precedents for cell C7 to determine the problem. Fix the formula.

On the Rental Revenue worksheet, click on cell C7. On the Forumulas tab, in the Formula Auditing group, click Trace Precedents. You will see that it is skipping cell C5. Change the formula to =C4*C5*C6.

On the Rental Revenue worksheet, use an Excel forecasting feature in cell H7 to calculate a new Rental Fee that will result in a total Projected Revenue (Next Year) of $160,000.

On the Rental Revenue worksheet, select cell H7. On the Data tab, in the Forecast group, click What-If-Analysis and select Goal Seek... In the Goal Seek pop-up window, configure the following:Set cell: H7To value: 160000By changing cell: H6 Click OK twice. (Hint: The value in cell H7 should now be $160,000, and the value in H6 should be $22.99.)

On the Top Sponsors worksheet, create a password-protected range from A3:B13. Name the range Sponsors. Use 12345 as the password. Protect the sheet with the same password.

On the Review tab, in the Protect group, click Allow Users to Edit Ranges. In the Allow Users to Edit Ranges pop-up, click New... In the New Range pop-up window, configure the following:Title: SponsorsRefers to cells: =A3:B13Range password: 12345 Click OK. When prompted to confirm the password, type 12345. Click OK. Click Protect Sheet... Accept all default settings and enter the password 12345. When prompted to confirm the password, type 12345.

In cell C3 on the Sales Department worksheet, create an email address by appending the First Name and Last Name to FusionTomo.com (for example, [email protected]). Use Flash Fill to create the email addresses for the remainder of the column.

On the Sales Department worksheet, click cell C3. Type [email protected] in cell C3. Click in cell C4. On the Home tab, in the Editing group, click Fill and select Flash Fill. (Note: If the Flash Fill does not work, you may need to complete one or two more email addresses for Excel to learn the pattern. Then try clicking Flash Fill for the remaining empty cells.)

On the Session Schedule worksheet, use a function to add the current date and time to cell G1.

On the Session Schedule worksheet, click cell G1 to select it. In the Formula Bar just below the ribbon, type =Now, and then press Tab on your keyboard. Press Enter on your keyboard to accept the function. (Hint: The result displayed in cell G1 should be the current date and time. The final formula should read =NOW().)

In cell B6 on the Shipping Calculator worksheet, insert a function that uses only the required number of business days to calculate the Estimated Date of Arrival.

On the Shipping Calculator worksheet, click on cell B6. (Note: Always insert your formula in the cell where you want the result displayed.) In the Formula Bar, type =WORKDAY, and then press Tab on your keyboard. To the left of the Formula Bar, click fx to open the Function Arguments pop-up wizard. In the Function Arguments wizard, enter the following:Start_date: B3Days: B4Holidays: B5Click OK. (Hint: The result displayed in cell B6 should be a date six days into the future, and the final formula should read =WORKDAY(B3,B4,B5).)

On the Ski School Enrollment worksheet, add data validation to cell range C13:C15 that displays the Input Message of Enter a value between 3 and 10. Give the Input Message the titleClass Size. Display a Stop error alert with the title Invalid Class Size. When the user enters a value less than 3 or greater than 10 or a number that includes a fraction, display the text Must be a minimum of 3 and maximum of 10 students.

On the Ski School Enrollment worksheet, select cell range C13:C15. On the Data tab, in the Data Tools group, click Data Validation and select Data Validation... In the Data Validation pop-up window, configure the following:On the Settings tab:Allow: Whole number (Hint: Ignore blank should be enabled.)Data: betweenMinimum: 3Maximum: 10On the Input Message tab (Hint: Show input message when cell is selected should be enabled.):Title: Class SizeInput message: Enter a value between 3 and 10On the Error Alert tab (Hint: Show error alert after invalid data is entered should be enabled.):Style: StopTitle: Invalid Class SizeError message: Must be a minimum of 3 and maximum of 10 students Click OK.

In cell L6 on the Utah Ski Prices worksheet, calculate the average price of an Adult Annual Pass (US $) for ski resorts operating more than 15 lifts and over 2,000 acres.

On the Utah Ski Prices worksheet, click cell L6 to select it. In the Formula Bar just below the ribbon, type =AVERAGEIFS, and then press Tab on your keyboard to begin entering the function arguments. To the left of the Formula Bar, click fx to open the Function Arguments pop-up wizard. In the Function Arguments wizard, enter the following:Average_range: E6:E19Criteria_range1: H6:H19Criteria1: >2000Criteria_range2: I6:I19Criteria2: >15 Click OK. (Hint: The result displayed in cell L6 should be $1,129.50, and the final formula should read =AVERAGEIFS(E6:E19,H6:H19,">2000",I6:I19,">15").)

Using the data on the Utah Ski Prices worksheet, add a chart that shows the price at each resort for an Adult Annual Pass (US $) as an area chart and the price for a Child Annual Pass (US $)as a secondary axis line chart. Position the chart below the table with the upper-left corner of the chart in cell A22.

On the Utah Ski Prices worksheet, select cells B5:B19. Hold down the Ctrl key on the keyboard and also select cells E5:F19. (Hint: All three cell ranges B5:B19, E5:E19, and F5:F19 should now be selected.) On the Insert tab, in the Charts group, click Insert Combo Chart and select Create Custom Combo Chart... In the Insert Charts pop-up window, on the All Charts tab, verify that the type of chart selected is Custom Combination. Configure the chart as follows:Series Name: Adult Annual Pass (US $) Chart Type: Area Secondary Axis: (blank)Series Name: Child Annual Pass (US $) Chart Type: Line Secondary Axis: enabled Click OK. Drag the chart to position it below the table so the upper-left corner of the chart is in cell A22.

In cell C12 on the Visitor Attendance worksheet, use structured references to elements of the table to calculate the total average attendance at all the National Parks.

On the Visitor Attendance worksheet, click cell C12 to select it. In the Formula Bar just below the ribbon, type =AVERAGE, and then press Tab on your keyboard to begin entering the function arguments. To include structured references in a formula or function, click the table cells you want to reference instead of typing their cell reference. Do this by selecting cell range B4:G8. Press Enter on your keyboard to complete the formula. (Hint: The result displayed in cell C12 should be 97614.43, and the final formula should read =AVERAGE(Attendance[[Jan]:[June]]).)

Using the Fill Series option in Excel, extend the Attendance table to include the remaining months of the year (July through December).

On the Visitor Attendance worksheet, click cell G3 to select it. Drag the Fill Handle located in the lower-right corner of the cell selection box to the to the right and drop it in cell M3.

Enable the error-checking rule that flags numbers formatted as text or preceded with an apostrophe.

Select the File tab. In the list of categories on the left, click Options. In the Excel Options pop-up window, click the Formulas option. Beneath the Error Checking Rules section, click the box Numbers Formatted as text or preceded with an apostrophe to enable it. Click OK.

Modify Excel options so function results are not automatically calculated until a workbook is saved.

Select the File tab. In the list of categories on the left, click Options. In the Excel Options pop-up window, click the Formulas option. In the Calculation options window, change the Workbook Calculation to Manual. Verify that Recalculate workbook before saving is enabled. Click OK.

Modify the workbook options so only the Bluejay and Chipmunk class sizes can be edited when opening the workbook in a browser.

Select the File tab. In the list of categories on the left, verify the default Info is selected. At the bottom of the Info pane, in the Browser View Options section, click the Browser View Options button. In the Browser View Options pop-up window, configure the following:On the Parameters tab, click the Add button.Click the boxes for Bluejay and Chipmunk to enable them. Click OK twice.