Advanced Microsoft Excel: Final Exam

What is one way we can create “custom” keyboard shortcuts?

After selecting an entire row, what is the keyboard shortcut to quickly add a new row?

Using the Alt key, what is the keyboard shortcut to change the text to left align?

Match the actions to the correct keyboard shortcut:

How do you indicate that a cell value can be changed or is a manual input?

I have the formula =A$1*$B1 in cell C1. When I copy this formula down one row to C2, what will the formula look like in cell C2?

Refer to the Final Exam Workbook. What is the name of the range B4:Z4?

Refer to the image below. What value would the formula =INDEX(A1:E8,4,5) return?

Refer to the Final Exam Workbook. Create a dynamic INDEX/MATCH formula in cell D25 that will lookup the revenue for the months in C25:C36. Hint: Don’t hard key the text “Revenue” in the formula, but have it reference the appropriate cell. How should your first MATCH statement look?

Refer to the Final Exam Workbook. I want to create a dynamic INDEX/MATCH formula in cell D25:D36 that will lookup the revenue for the month’s in C25:C36. Rearrange the items below to create the correct formula in D25.

Refer to the image below. What value would the formula =MAX(E2:E8,45000) return?

Refer to the Final Exam Workbook. In cell D21, you’ll notice that the Operating Margin is negative (-2.2%). I want to modify the formula so the smallest the value can be is 0%. Which formula accomplishes this?

Match the below formulas with the correct definition.

Refer to the Final Exam Workbook. In cells F25:F36, create an IF +AND/OR statement that does the following. If the revenue in cells D25:D36 is greater than or equal to the revenue goal in I24, and if the operating margin in cells E25:E36 is greater than or equal to the operating goal in cell I25, then put “Yes”, otherwise put “No”. Which of the formulas below is correct?

The cells in column A contain the account number and account name. For instance, cell A1 contains the following text: “Revenue 10293 “. The account number will always be 5 digits but the account name is various lengths. I want to extract just the account name. Which TWO formulas would I use below to accomplish this?

Refer to the image below. In column D, how can you ensure that only a value between 1 and 5 is entered?

Refer to the Final Exam Workbook. In cell D24, create a dropdown menu that has “Revenue” and “Cost of Goods Sold” as the two options. When creating your list under Data Validation, when you select what values will be allowed (for example, “List”), you’ll see that the default is “Any Value”. What type of value is right below “Any Value”?

Refer to the Final Exam Workbook. In columns AD:AI, we see much of the same data from B:Z but presented in a different format to allow for PivotTables. Using this data, create a PivotTable with the Months in the rows and with one column showing the sales for each month and the next column showing the % of column total. Month 16 sales represents ____% of total sales.

Refer to the Final Exam Workbook. In columns AD:AI, we see much of the same data from B:Z but presented in a different format to allow for PivotTables. Using this data, create a PivotTable with the Months in the rows and with one column showing the sales for each month. Then create a Calculated Field that calculates the gross profit margin (Gross Profit divided by Revenue). Add this new calculated field to your PivotTable and format it as a percent. Which month had the lowest gross profit margin?

After you run a macro, which action can you NOT perform?

For this question, you need to record a macro. HINT: Before you record the macro, you should practice the action.

Refer to the Final Exam Workbook and select cells AI5:AI28. Now record a macro that applies conditional formatting to cells AI5:AI28. The formatting should color the cell a green fill with dark green text if the cell’s value is greater than $30,000. Stop recording your macro. On the Developer tab, click on Macros, select the macro you recorded, and click “Edit”. This will take you to the code.

In your code near the top, you should see a line that says something similar to “Selection.FormatConditions.Add Type:=xlCellValue, Operator:=”. What code comes immediately after “Operator:=”?

I want to apply some protections to my worksheet. I want to make it so a person cannot delete any worksheets and can only change certain cells in various worksheets. Which actions should I perform to accomplish this? Select all that apply.