Excel Expert Bleh

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.

On the Q1 Sales worksheet, create a chart that shows the Cost on the left vertical axis, the Profit on the right vertical axis, and the Flavors on the horizontal axis.

On the Q1 Sales worksheet, simultaneously select the Flavors column (cell range A3:A26), the Cost column (cell range B3:B26), and the Profitcolumn (cell range D3:D26). On the Insert tab, in the Charts group, click Insert Combo Chart. In the Combo pop-up window, click on Clustered Column - Line on Secondary Axis. Position the chart to the right of the table.

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.

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

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.

Add Spanish (Mexico) 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 Spanish (Mexico). Click the Add button. Do not enable the keyboard. Accept all other default settings. Click OK to exit the window.

On the Find an Instructor worksheet, insert a function in cell B8 that displays the name of the SCUBA instructor from the Courses worksheet who is qualified to teach the course displayed in cell B6.

On the Find an Instructor worksheet, click on cell B8. In the Formula Bar just below the ribbon, type =VLOOKUP, 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:Lookup_value: B6Table_array: Courses!A6:B31Col_index_num: 2Range_lookup: FALSE Click OK. (Hint: The result displayed in cell B8 should be Dan Webster, and the final formula should read =VLOOKUP(B6,Courses!A6:B31,2,FALSE).)

On the Flavor Prices worksheet, format the Markup column and the Price column to display numeric values to two decimal places. The format should be applied to existing and new rows.

On the Flavor Prices worksheet, click the C and D column indices to select the entire columns. (Hint: This will apply the format to new rows that may be added later.) On the Home tab, in the Number group, click the Increase Decimal icon once to increase the number of decimals dispayed by one. (Hint: The data in both columns should now display two decimal places.)

Configure the workbook calculation options so that formulas do not show result changes until manually recalculated or until the 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.

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.

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

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 Top 15 Cities worksheet, modify the chart to display the cities by rank order instead of alphabetical order.

On the Top 15 Cities worksheet, click on the PivotChart to select it and reveal the PivotChart Tools contextual tabs. On the PivotChart Tools Analyze tab, in the Show/Hide group, click Field List to display it. Drag the Rank field into the AXIS (Category) area and drop it before the Capital City field. (Hint: The table and chart should now be reorganized by Rank.)

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:E19 Criteria_range1: H6:H19 Criteria1: >2000 Criteria_range2: I6:I19 Criteria2: >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").)

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.

On the Courses worksheet, below the Foundation Courses heading, group rows 7 through 12.

At the bottom of the workbook, click the Courses worksheet tab. Click and drag to select rows 7 through 12. On the Data tab, in the Outline group, click Group and select Group... In the Group pop-up window, verify that Rows are selected. Click OK.

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.

Sort the Sales worksheet by Product Name from A to Z and insert a Subtotal that calculates the Sum in the Extended Price column at each change inProduct Name.

Click the Sales worksheet tab to select it. On the H column, click the autofilter down arrow located to the right of the column heading, Product Name, and select Sort A to Z. Click anywhere in the table to select it. On the Data tab, in the Outline group, click Subtotal. In the Subtotal pop-up window, configure the following: At each change in: Product Name Use function: Sum Add subtotal to: Extended Price Accept all other defaults. Click OK.

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

Remove all duplicate records from the World Capital Cities worksheet. Do not remove any other records.

At the bottom of the workbook, click the World Capital Cities 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.) Accept all defaults in the Remove Duplicates pop-up window and click OK. (Hint: The pop-up window should confirm that 2 duplicate records were removed.) Click OK.

On the Top 15 Cities worksheet, add a slicer for the Country column. Place the slicer to the right of the PivotChart.

Click on the Top 15 Cities worksheet tab. Click the PivotChart to activate it. On the Insert tab, in the Filters group, click Slicer. In the Insert Slicer pop-up window, click the Country box to select it. Click OK. Drag the slicer to position to the right of the PivotChart.

Modify proofing options to allow Spanish voseo verb forms only.

Click the File tab. At the bottom of the left pane, select Options. In the Excel Options pop-up window, select Proofing from the list on the left. Beneath the Custom Dictionaries... button, click the Spanish modes: down arrow and select Voseo verb forms only. Click OK.

On the Food Inventory worksheet, write a conditional formatting rule for the range A2:A57 that sets the font color to red for all duplicate values in the column.

On the Food Inventory worksheet, select cell range A2:A57. On the Home tab, in the Styles group, click Conditional Formatting and select New Rule... In the New Formatting Rule pop-up window, inside the pane Select a Rule Type, click Format only unique or duplicate values. Beneath the Edit the Rule Description section, configure the following:Click the dropdown arrow and select Duplicate.Click the Format... button. On the Font tab, click the Color down arrow; beneath Standard Color, select Red.Click OK. 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 theGrain 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.

On the Monthly Orders worksheet, add a formula in cell F4 of the Demand column that displays Decreasing if the orders in April were less than the orders in March or less than the average orders for the quarter (January - March). Otherwise, display No Change.

On the Monthly Orders worksheet, click cell F4. In the Formula Bar just below the ribbon, type =OR, 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: E4<D4Logical2: E4<AVERAGE(B4:D4) 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, 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: OR(E4<D4,E4<AVERAGE(B4:D4)) Value_if_true: Decreasing Value_if_false: No Change Click OK. (Hint: The result displayed in cell F4 should be Decreasing and the final formula should read=IF(OR(E4<D4,E4<AVERAGE(B4:D4)),"Decreasing","No Change").)

On the PivotTable worksheet, add Grand Totals for the columns only.

On the PivotTable worksheet, click anywhere on the table to select it and reveal the Pivot Table Tools contextual tabs. On the PivotTable Tools Design tab, in the Layout group, click Grand Totals and select On for Columns Only. (Hint: A Grand Total row should now appear at the bottom of the table.)

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.

On the Q1 Actual Sales worksheet, add a watch to cell H12.

On the Q1 Actual Sales worksheet, select cell H12. Click the Formulas tab to select it. In the Formula Auditing group, click Watch Window. In the Watch Window pop-up window, click Add Watch... In the Add Watch pop-up window, ensure that the reference reads ='Qtr 1 Actual Sales'!$H$12, and then click Add.

On the Q1 Sales worksheet, apply formatting to the Pounds Sold column that displays the 5 least-selling ice cream flavors with a Light Red Fill.

On the Q1 Sales worksheet, click the column E index to select the entire column of Pounds Sold. (Hint: This will apply the format to new rows that may be added later.) On the Home tab, in the Styles group, click the Conditional Formatting dropdown, select Top/Bottom Rules, and then select Bottom 10 Items... In the Bottom 10 Items pop-up window, click the down arrow to change the amount to 5. To the right of with, click the down arrow to open the format window, and then select Light Red Fill. Click OK. (Hint: The five flavors Blueberry Choco, Jawbreaker Mint, Peacon and Peanut Truffle, Animal Crackers, and Campbell's Soup Soufflé should be highlighted.)

On the Q1 Target Sales worksheet, create a Sunburst chart using only the data in the Location and Totals columns. Position the chart to the right of the table.

On the Q1 Target Sales worksheet, select cell range A11:A15. Hold Ctrl on your keyboard to simultaneously select cell range E11:E15. Click the Insert tab to select it. In the Charts group, click Recommended Charts. In the Recommended Charts pop-up window, click the All Charts tab. In the All Charts pop-up window, on the left side, select Sunburst. Click OK. Drag the chart to position it to the right of the table.

In cell H12 on the Qtr 1 Actual Sales worksheet, use a formula to calculate the number of trucks that had Hamburger Meal sales of more than $2,500 in March.

On the Qtr 1 Actual Sales worksheet, click cell H12. In the Formula Bar just below the ribbon, type =COUNTIFS, 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: Criteria_range1: B11:B43 Criteria1: Hamburger Meal Criteria_range2: E11:E43 Criteria2: >2500 Click OK. (Hint: The result displayed in cell H12 should be 2, and the final formula should read =COUNTIFS(B11:B43,"Hamburger Meal", E11:E43,">2500").)

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: Yes Value_if_false: No Click 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, 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.

Protect the workbook so users cannot add, change, or delete worksheets. Use the password 12345.

On the Review tab, in the Protect group, click Protect Workbook. In the Protect Structure and Windows pop-up window, select only Structure. In the Password (optional) field, type 12345. When prompted, reenter the password. Click OK.

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 H2 of the Shipping worksheet, create a formula that identifies the numerical day of the week on which the order was shipped using Monday = 0 and Sunday = 6. Apply that formula to all of column H.

On the Shipping worksheet, click cell H2. In the Formula Bar just below the ribbon, type =WEEKDAY. 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: Serial_number: G2 Return_type: 3 Click OK. Double-click the fill handle of the selection box for cell H2 to autopopulate the remaining cells in the column. (Hint: The result displayed in cell H2 should be 6, and the final formula should read, =WEEKDAY(G2,3).)

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 title Class 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: between Minimum: 3 Maximum: 10 On the Input Message tab (Hint: Show input message when cell is selected should be enabled.): Title: Class Size Input message: Enter a value between 3 and 10 On the Error Alert tab (Hint: Show error alert after invalid data is entered should be enabled.): Style: Stop Title: Invalid Class Size Error message: Must be a minimum of 3 and maximum of 10 students Click OK.

On the Summary Chart worksheet, apply Style 4 to the PivotChart.

On the Summary Chart worksheet, click on the PivotChart to select it and reveal the PivotChart Tools contextual tabs. On the PivotChart Tools Design tab, in the Chart Styles group, click the More down arrow located on the right side of the gallery to open it. Click on Style 4.

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

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.

To store new specialty items, your export company built an extension onto your warehouse. In cell B7 of the Warehouse Extension worksheet, add a formula that calculates the monthly payment amount, assuming that the payment is due at the beginning of the month.

On the Warehouse Extension worksheet, click cell B7. In the Formula Bar just below the ribbon, type =PMT. 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: Rate: B5/12 NPR: B6*12 PV: B4 FV: 0 Type: 1 Click OK.

In cell G2 on the World Capital Cities worksheet, use the INDEX function combined with the MATCH function to find the population of the capital city entered into cell F2.

On the World Capital Cities worksheet, click cell G2.In the Formula Bar just below the ribbon, type =MATCH, 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: Lookup_value: F2 Lookup_array: $B$2:$B$198 Match_type: 0 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 selectingCut, or by using the keyboard shortcut Ctrl+X.)In the Formula Bar, enter =INDEX, 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 Select Arguments pop-up window, select array, row_num, and col_num,and then click OK. In the Function Arguments wizard, enter the following: Array: $C$2:$C$198 Row_num: MATCH(F2,$B$2:$B$198,0) Col_num: leave this field blank Click OK. (Hint: The result displayed in cell G2 should be 2,206,300, and the final formula should read=INDEX($C$2:$C$198,MATCH(F2,$B$2:$B$198,0)).)

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.

Enable only digitally signed macros in this workbook.

Select the File tab. In the list of categories on the left, click Options. In the Excel Options pop-up window, click the Trust Center option. Beneath the Microsoft Excel Trust Center section, click the Trust Center Settings... button. In the left pane of the Trust Center pop-up window, click Macro Settings. Under the Macro Settings section, click the radial button Disable all macros except digitally signed macros. Click OK twice.

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.

On the Summary Chart worksheet, modify the table so that the Product Names are grouped within each Country.

Select the Pivot Chart on the Summary Chart sheet. Drag Product Name from the PivotChart Fields list into the Axis (Categories) area and drop it below the Country field. (Hint: To open the PivotChart Fields window, simply select the chart or click the Analyze tab. In the Show/Hide group, enable Field List by clicking on it.)