CIS 101 Excel Expert practice exam 1

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

1. Click on the Top 15 Cities worksheet tab. 2. Click the pivotchart to activate it. 3. On the INSERT tab, in the Filters group, click Slicer. 4. In the Insert Slicer pop-up window, click the Country box to select it. 5. Click OK. 6. Drag the slicer to position to the right of the pivotchart.

Project 5 Task 2 In column H of the 2016 Shipping worksheet, create a formula that uses functions to show how many days occurred from the time an order was placed until it shipped.

1. On the 2016 Shipping worksheet, click cell H2. 2. In the Formula Bar just below the ribbon type, = DAYS 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:End_date: G2Start_date: E2 5. Click OK. 6. 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 9 and the final formula should read,=DAYS(G2,E2)).

Project 5 Task 5 On the "2016 Summary Chart" worksheet, apply Style 16 to the PivotChart.

1. On the 2016 Summary Chart worksheet, click on the pivotchart to select it and reveal the PIVOT CHART TOOLS contextual tabs. 2. On the PIVOT CHART TOOLS DESIGN tab, in the Chart Styles group, click More down-arrow located on the right side of the gallery to open it. 3. Click on Style 16.

Project 3 Task 4 On the "Schedule" worksheet, link a Combo Box form control to cell B7 that displays all of the names from the "Instructor List" worksheet. Apply 3-D shading to the control.

1. On the DEVELOPER tab, in the Controls group, click Insert. 2. In the drop-down menu, in the Form Controls section, click Combo Box (Form Control). 3. On the Schedule worksheet, click on cell B7 to place the Combo Box near the cell. Drag the Combo Box and the resize handles to fill cell B7 with the box. 4. With the Combo Box selected, in the Controls group, click Properties. 5. In the Format Control pop-up window, on the Control tab, configure the following:Input_range: 'Instructor List'!$A$7:A$12Click the 3-D shading box to enable it.Accept all other defaults and click OK. 6. Click anywhere outside the table to deselect the control.(Hint: Test your Combo Box control by clicking on its down-arrow. The list of instructor names should appear and allow you to select one.)

Project 3 Task 5 Remove the Instructors named range.

1. On the FORMULAS tab, in the Defined Names group, click Name Manager.2. In the Name Manager pop-up window, select the Instructors named range.3. Click Delete.4. If prompted, "Are you sure you want to delete the name Instructors?" Click Yes.5. Click Close to exit the Name Manager.

Project 3 Task 3

1. On the Find an Instructor worksheet, click on cell B8. 2. In the Formula Bar just below the ribbon type, = VLOOKUP 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:Lookup_value: B6Table_array: Courses!A6:B31Col_index_num: 2Range_lookup: FALSEClick 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))

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

1. On the Flavor Prices worksheet, click the C and D column indices to select the entire column.(Hint: This will apply the format to new rows that may be added later.)2. 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.)

Project 1 Task 5 On the Flavor Prices worksheet, add a watch to cell F4.

1. On the Flavor Prices worksheet, select cell F4. 2. Click the FORMULAS tab to select it. 3. In the Formula Auditing group, click Watch Window. 4. In the Watch Window pop-up window, click Add Watch... 5. In the Add Watch pop-up window, ensure that the reference reads, ='Flavor Prices'!$F$4 then click Add.

Project 2 Task 1 On the Food Stock 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.

1. On the Food Stock Inventory worksheet, select cell range A2:A57. 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, inside the pane Select a Rule Type, click Format only unique or duplicate values. 4. Beneath the Edit the Rule Description section, configure the following: a. Click the drop-down arrow and select Duplicate. b. Click the Format... button. On the Font tab, click the Color down-arrow and beneath Standard Color select Red. c. Click OK. 5. Click OK.

Project 1 Task 4 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 for the March or less than the average orders for the quarter (January - March). Otherwise, display No Change.

1. On the Monthly Orders worksheet, click cell F4. 2. In the Formula Bar just below the ribbon type, = OR 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:Logical1: E4<D4Logical2: E4<AVERAGE(B4:D4)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 enter = IF 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:Logical_test: OR(E4<D4,E4<AVERAGE(B4:D4))Value_if_true: DecreasingValue_if_false: No ChangeClick 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")

Project 3 Task 2 Create a new set of customized colors with Accent 1 as Blue, Accent 1, Darker 25%. Name the customized colors Ocean Colors.

1. On the PAGE LAYOUT tab, in the Themes group, click Colors and at the bottom of the drop-down menu choose Customize Colors... 2. In the Create New Theme Colors pop-up window, click the drop-down arrow to change Accent 1 to be Blue, Accent 1, Darker 25% .3. Type the Name: Ocean Colors 4. Click Save.

Project 2 Task 5 On the PivotTable worksheet, add Grand Totals for the columns only.

1. On the PivotTable worksheet, click anywhere on the table to select it and reveal the PIVOTTABLE TOOLS contextual tabs .2. 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).

Project 1 Task 3 On the Q1 Sales worksheet, apply formatting to the Quarts Sold column that displays the 5 least selling ice cream flavors with a Light Red Fill.

1. On the Q1 Sales worksheet, click the column E index to select the entire column of Quarts Sold.(Hint: This will apply the format to new rows that may be added later.) 2. On the HOME tab, in the Styles group, click the Conditional Formatting and select Top/Bottom Rules, then select Bottom 10 Items ... 3. 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, then select Light Red Fill. 4. Click OK.(Hint: The five flavors Blueberry Choco, Jawbreaker Mint, Peacon and Peanut Truffle, Animal Crackers, and Campbell's Soup Souffle should be highlighted.)

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

1. On the Q1 Sales worksheet, select cell range A3:B26, and then hold down the Ctrl key on your keyboard and select cell range D3:D26. 2. On the Insert tab, in the Charts group, click Insert Combo Chart. 3. In the Combo pop-up window, click on Clustered Column - Line on Secondary Axis. 4. Position the chart to the right of the table.

project 2 task 4 On the Q1 Target Sales sheet, save the Pie chart as a template named TargetSales in the GMetrixTemplates folder.

1. On the Q1 Target Sales worksheet, right-click the chart and select Save as Template ... 2. Browse to the GMetrixTemplates folder. 3. Type the File name: TargetSales.crtx4. Click Save.

Project 2 Task 3 On the Qtr 1 Actual Sales worksheet, name the table Q1_Sales

1. On the Qtr 1 Actual Sales worksheet, click anywhere on the table to select it and reveal the TABLE TOOLS DESIGN contextual tab. 2. On the far left in the Properties group, type the Table Name: Q1_Sales 3. Press Enter on your keyboard to commit the name.

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

1. On the Qtr 1 Actual Sales worksheet, click cell H12. 2. In the Formula Bar just below the ribbon type, = COUNTIFS 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:Criteria_range1: B11:B43Criteria1: Hamburger MealCriteria_range2: E11:E43Criteria2: >25005. 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"))

Project 3 Task 1 Protect the workbook so users cannot add, change, or delete worksheets. Use the password "12345".

1. On the REVIEW tab, in the Protect group, click Protect Workbook. 2. In the Protect Structure and Windows pop-up window, select only Structure. 3. In the Password (optional) field type, 123454. When prompted, re-enter the password. 5. Click OK.

Project 5 Task 1 On the "Specialty Item Prices" worksheet, format the data in column F as British Pound Sterling (GBP), but with no specific region. Do not create a custom format.

1. On the Specialty Item Prices worksheet, click the column F index to select the entire column.(Hint: This will apply the format to new rows that may be added later.) 2. On the HOME tab, in the Number group, click the Number Format down-arrow and select More Number Formats... 3. In the Format Cells pop-up window, on the Number tab, in the Category pane select Currency. 4. Click the down-arrow to the right of the Symbol field. Scroll down the list and select GBP.(Hint: You can press the G key on your keyboard to help you scroll down the list to the letter G's) 5. Accept all other defaults and click OK.(Hint: GBP should now appear before each price in the F column, similar to the formats in columns D and E)

Project 4 Task 5 On the "Top 15 Cities" worksheet, modify the chart to display the cities by rank order instead of alphabetical order.

1. On the Top 15 Cities worksheet, click on the pivotchart to select it and reveal the PIVOT CHART TOOLS contextual tabs. 2. On the PIVOT CHART TOOLS ANALYZE tab, in the Show/Hide group, click Field List to display it. 3. 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)

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

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

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

1. On the World Capital Cities worksheet, click cell G2. 2. In the Formula Bar just below the ribbon type, = MATCH 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: Lookup_value: F2 Lookup_array: $B$2:$B$198 Match_type: 0 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 enter =INDEX 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 Select Arguments pop-up window, select array, row_num, col_num then click OK 9. 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))

Project 4 Task 1 Display the Developer tab on the Ribbon.

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 Customize Ribbon option. 4. In the Main Tabs pane of the Customize Ribbon window, click the box by the Developer tab to enable it. 5. Click OK.

Project 1 Task 1 Enable only digitally signed macros in this workbook.

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 Trust Center option. 4. Beneath the Microsoft Excel Trust Center section, click the button, Trust Center Settings ... 5. In the left pane of the Trust Center pop-up window, click Macro Settings .6. Under Macro Settings section, click the radial button Disable all macros except digitally signed macros. 7. Click OK twice.

Project 4 Task 2 Configure the workbook calculation options so formulas do not show result changes until manually recalculated or when the 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 5 Task 4 On the "2016 Summary Chart" worksheet, modify the table so that the Product Names are grouped within each Country.

1. Select the Pivot Chart on the 2016 Summar Chart sheet. 2. 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, and in the Show/Hide group enable Field List by clicking on it.)