CIS 101 Excel Expert Practice Exam 2

Project 1 Task 5 On the Attendance Trends worksheet, add a Linear trendline to the chart that shows the moving average for Zion.

1. On the Attendance Trends worksheet, click on the chart to select it and display the CHART TOOLS contextual tabs. 2. On the CHART TOOLS DESIGN tab, in the Chart Layouts group, click Add Chart Element. Select Trendline and click Linear. 3. In the Add Trendline pop-up window, select the series Zion. 4. Click OK.

Project 3 Task 5 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.

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

Project 3 Task 4 In cell E3 on the Daily Schedule worksheet, insert the GETPIVOTDATA function to calculate the number of Marketing sessions offered on Thursday.

1. On the Daily Schedule worksheet, click cell E3 to select it. 2. In the Formula Bar just below the ribbon type, = 3. Click on cell B19(Hint: This is the cell containing the Count of Session Titles for Marketing on Thursday)(Note: This value will be in cell B5 if you have already completed Task 5) 4. Excel should automatically write the GETPIVOTDATA function for you.(Hint: the result displayed in cell E3 should be 5 and the final formula should read, =GETPIVOTDATA("Session Title",$A$3,"Day","Thursday","Track","Marketing")) 5. Press Enter on your keyboard

Project 3 task 2 On the Dive Show Exhibitors worksheet, add a conditional formatting rule to the Exhibitor Name column that applies a Yellow fill with a 6.25% Graypattern if the Industry is Clothing and the Booth Size is more than $1,000.

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

Project 4 Task 3 On the Exporting Countries worksheet, use a Query beginning at cell A1 to import the Country Code and Country columns from the Codesworksheet in the CodeList.xlsx workbook located in the GMetrixTemplates folder.

1. On the Exporting Countries worksheet, click cell A1 to select it. 2. On the DATA tab, in the Get & Transform group, click New Query. (HINT: Based on your Office installation you may need to select Get Data instead of New Query) 3. Select From File and click From Workbook. 4. Browse to the GMetrixTemplates folder and select CodeList.xlsx. 5. Click Import. 6. In the Navigator pop-up window, in the left pane expand CodeList.xlsx and click the Codes worksheet to select it. 7. At the bottom of the window click the Edit button to open the worksheet in the Query Editor. 8. In the Query Editor, on the Home tab, in the Manage Columns group, click Choose Columns. 9. In the Choose Columns pop-up window, deselect all columns except for Country Code and Country. 10. Click OK .11. On the HOME tab click the Close & Load dropdown and select Close & Load To... 12. In the Load To pop-up window, in the Select where data should be loaded section, select Existing Worksheet. 13. Click Load.

Project 5 task 2 On the Farmers Market worksheet, change the format in cell E1 to the English (Canada) date format DD-MMM-YY.

1. On the Farmers Market worksheet, click on cell E1 to select it.2. On the HOME tab, in the Number group, click the dialog box launcher located in the lower-right corner of the group.3. In the Format Cells pop-up window, in the category pane on the left, click Date.4. Click the down-arrow to open the Locale (location) drop-down menu and select English (Canada).5. In the Type: pane, select 14-Mar-12.6. Click OK.

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

1. On the Grain Exports worksheet, click anywhere on the table to select it.2. On the INSERT tab, in the Tables group, click PivotTable.3. On the Create PivotTable pop-up window, accept all other defaults and click OK.4. In the PivotTable Fields pane, drag the Country field into the Rows area.5. Drag the Product field into the Rows area and drop it below the Country field.6. 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.)7. Right-click the new Sheet tab and rename the tab PivotTable.8. Drag the PivotTable worksheet tab right so that it comes after the other two.

On the Grain Exports worksheet, name the cell range A2:A85 CountryCode.

1. On the Grain Exports worksheet, select cell range A2:A85.2. In the Name Box located left of the Formula Bar, type CountryCode.3. Press Enter on your keyboard to commit the name assignment.-OR-2. On the FORMULAS tab, in the Defined Names group, click Define Name and select Define Name...3. In the New Name pop-up window, type the name CountryCode. Accept all other defaults.4. Click OK.(Hint: The field Refers to: should contain the structured reference =Table2[Country Code])

Project 5 Task 1 Change the theme fonts to Arial and save the theme as FarmersMarket into the GMetrixTemplates folder.

1. On the PAGE LAYOUT tab, in the Themes group, click Fonts.2. Click Arial.3. In the Themes group, click Themes and select Save Current Theme...4. Browse to the GMetrixTemplates folder.5. Type the File name: FarmersMarket.6. Click Save.

Project 1 Task 6 On the Programs by Park pivot table, display the data in outline form.

1. On the Programs by Park worksheet, click on the pivot table to select it and display the PIVOTTABLE TOOLS contextual tabs. 2. On the PIVOTTABLE TOOLS DESIGN tab, in the Layout group, select Report Layout and click Show in Outline Form.

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

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

Project 3 Task 1 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.

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

Project 1 Task 3 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, else otherwise displays No. Populate the remaining cells in the column with the same formula.

1. On the Ranger-led Activities worksheet, click cell F4. 2. In the Formula Bar just below the ribbon type, =NOT then press Tab on your keyboard to begin entering the function arguments. 3. To the left of the Formula Bar click fx to open the Function Arguments pop-up wizard. 4. In the Function Arguments wizard, enter the following: Logical: C4="None" (Hint: the text string "None" is case-sensitive) Click OK. 5. Select the formula in the Formula Bar and cut it. (Hint: You can cut it by using either the Cut command on the HOME tab, or by right-clicking and selecting Cut, or by using the keyboard short-cut CTRL-X.) 6. In the Formula Bar just below the ribbon type, =AND then press Tab on your keyboard to begin entering the function arguments. 7. To the left of the Formula Bar click fx to open the Function Arguments pop-up wizard. 8. In the Function Arguments wizard, enter the following: Logical1: NOT(C4="None") Logical2: E4="Yes" Click OK. 9. Select the formula in the Formula Bar and cut it. 10. In the Formula Bar enter =IF then press Tab on your keyboard to begin entering the function arguments. 11. To the left of the Formula Bar click fx to open the Function Arguments pop-up wizard. 12. In the Function Arguments wizard, enter the following: Logical_test: AND(NOT(C4="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(C4="None"),E4="Yes"),"Yes","No") 13. 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")

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

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

Project 5 task 4 In cell E2 on the Sales Summary worksheet, add a formula using cube functions and the Data Model to retrieve the top selling product for all three seasons.

1. On the Sales Summary worksheet, click cell E2.2. In the Formula Bar just below the ribbon type, =CUBESET then press Tab on your keyboard to begin entering the function arguments.(Hint: Note the syntax for this function is =CUBESET(connection, set_expression,[caption],[sort_order],[sort_by])3. Next, type a " and Excel Intellisense will suggest the connection ThisWorkbookDataModel. Click it and press the Tab key on your keyboard to accept this connection.(Hint: Notice it is now written into the function in the Formula Bar).4. Let Excel Intellisense help you complete the remaining function arguments as you type the following:=CUBESET("ThisWorkbookDataModel","[MarketTable].[Product].children","",2,"[Measures].[Sum of Sales]") Press Enter on your keyboard to complete the function.5. Select the formula in the Formula Bar and cut it.(Hint: You can cut it by using either the Cut command on the HOME tab, or by right-clicking and selecting Cut, or by using the keyboard short-cut CTRL-X.)6. In the Formula Bar just below the ribbon type, =CUBERANKEDMEMBER then press Tab on your keyboard to begin entering the function arguments.7. To the left of the Formula Bar click fx to open the Function Arguments pop-up wizard.8. In the Function Arguments wizard, enter the following:Connection: ThisWorkbookDataModelSet_expression: CUBESET("ThisWorkbookDataModel","[MarketTable].[Product].children","",2,"[Measures].[Sum of Sales]") (Hint: Paste this formula from the cut you made in step 5 above and remove the = symbol.)Rank: 1Caption: *This is optional, so leave this field emptyClick OK.(Hint: the result displayed in cell E2 should be Vegetables and the final formula should read,=CUBERANKEDMEMBER("ThisWorkbookDataModel",CUBESET("ThisWorkbookDataModel","[MarketTable].[Product].children","",2,"[Measures].[Sum of Sales]"),1) )

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

1. On the Session Schedule worksheet, click cell G1 to select it. 2. In the Formula Bar just below the ribbon type, =Now then press Tab on your keyboard 3. 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() )

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

1. On the Ski School Enrollment worksheet, select cell range C13:C15. 2. On the DATA tab, in the Data Tools group, click Data Validation and select Data Validation... 3. In the Data Validation pop-up window, configure the following: a. On the Settings tab - Allow: Whole number (Hint: ""Ignore blank"" should be enabled.) Data: between Minimum: 3 Maximum: 10 b. 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 c. 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 4. Click OK.

Project 2 Task 3 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.

1. On the Utah Ski Prices worksheet, click cell L6 to select it. 2. In the Formula Bar just below the ribbon type, =AVERAGEIFS then press Tab on your keyboard to begin entering the function arguments. 3. To the left of the Formula Bar click fx to open the Function Arguments pop-up wizard. 4. In the Function Arguments wizard, enter the following: Average_range: E6:E19 Criteria_range1: H6:H19 Criteria1: >2000 Criteria_range2: I6:I19 Criteria2: >15 5. 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")

Project 2 Task 5 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.

1. On the Utah Ski Prices worksheet, select cells B5:B19. 2. 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.) 3. On the INSERT tab, in the Charts group, click Insert Combo Chart and select Create Custom Combo Chart... 4. 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 5. Click OK. 6. Drag the chart to position it below the table so the upper-left corner of the chart is in cell A22.

Project 2 Task 4 On the Utah Ski Prices worksheet, name cell range J6:J19 SkiRuns.

1. On the Utah Ski Prices worksheet, select cells J6:J19. 2. In the Name Box located left of the Formula Bar, type SkiRuns and press Enter on the keyboard to commit the name. -OR- 2. On the FORMULAS tab, in the Defined Names group, click Define Name and select Define Name... 3. In the Define Name pop-up window, configure the following: Name: SkiRuns Accept all other defaults. 4. Click OK.

Project 1 Task 1 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.

1. On the Visitor Attendance worksheet, click cell C12 to select it. 2. In the Formula Bar just below the ribbon type, =AVERAGE then press Tab on your keyboard to begin entering the function arguments. 3. 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. 4. 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]]) Previous Task Mark Completed Show Me Mark for Review Next Task

Project 1 Task 2 Using the Fill Series option in Excel, extend the Attendance table to include the remaining months of the year July through December.

1. On the Visitor Attendance worksheet, click cell G3 to select it. 2. 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.

Project 1 Task 5 Enable the error checking rule that flags numbers formatted as text or preceded with an apostrophe.

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

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

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

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

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