ExamDumpster

Free 77-427 sample questions

Here are free 77-427 sample questions on advanced Excel 2013, each with an answer and an explanation. They cover PivotTables, date and statistical functions, and custom number formats. Use them to review expert Excel skills before you move on to MO-211.

Try them in the simulator Same questions, with study, timed and flashcard modes.

Showing 10 of 20 free sample questions.

Question 1Choose one

A researcher has a dataset of experiment results. They have created a PivotTable to summarize the average result by 'Experiment Type' and 'Date'. The dates in the row labels are currently showing individual days (e.g., 2013-01-05, 2013-01-06, ...). They want to analyze trends by month and quarter. What is the most efficient way to achieve this within the PivotTable?

Question 2Choose 2

You are preparing a workbook template for your department. To ensure accessibility for users with screen readers, you must add descriptive alternative text to all charts and images. You also need to verify that there are no other accessibility issues, such as merged cells with no clear header or insufficient color contrast. Which two tools in Excel 2013 should you use to accomplish these tasks? (Select TWO)

Question 3Choose one

You have a table of product information where column A contains Product IDs and column G contains Supplier Names. You need to look up the Supplier Name for a specific Product ID entered in cell K1. However, sometimes the Product ID in K1 might not exist in the table, which would cause a VLOOKUP function to return an #N/A error. You want the formula to display the text "Not Found" instead of the error. Which formula correctly achieves this?

Question 4Choose one

A manager has a large table of sales data formatted as an Excel Table named `SalesData`. The table has columns for `[Date]`, `[Region]`, and `[Amount]`. The manager wants to see the total sales amount for the 'North' region only. Which formula correctly uses structured references to calculate this total?

Question 5Choose one

**Case Study:** A logistics company uses an Excel workbook to manage weekly shipment schedules. The workbook is used by three different roles: Schedulers, Dispatchers, and a Manager. **Current Setup:** The workbook contains three worksheets: 'ScheduleInput', 'DispatchLog', and 'SummaryReport'. Schedulers enter new shipment data into 'ScheduleInput'. Dispatchers update the status of shipments in 'DispatchLog'. The Manager reviews the 'SummaryReport' which pulls data from the other two sheets. **Requirements:** The Manager has outlined a new security and collaboration protocol: 1. The overall structure of the workbook (adding, deleting, or renaming sheets) must be locked to prevent accidental changes. 2. Schedulers should only be able to edit cells A2:F100 on the 'ScheduleInput' sheet. All other cells on that sheet must be locked. 3. Dispatchers should only be able to edit column G (Status) on the 'DispatchLog' sheet. All other columns must be locked. 4. All users should be able to view the 'SummaryReport' but not edit it at all. 5. The Manager needs to be able to remove all protections using a single password: `Q3Report`. What is the correct sequence of protection measures to apply to meet all requirements?

Question 6Choose one

A logistics company calculates penalties for late shipments. A penalty applies if a shipment arrives after 5:00 PM (17:00) on the scheduled delivery date. In a worksheet, the scheduled delivery date is in cell A2, and the actual arrival timestamp (which includes both date and time) is in cell B2. Which formula correctly returns TRUE if a penalty is applicable and FALSE otherwise?

Question 7Choose 2

A quality control manager is creating a data entry form for product testing. For a specific cell where a 'Test Result Code' is entered, they need to enforce multiple rules to ensure data integrity. Which TWO of the following requirements can be configured simultaneously using only the standard Data Validation dialog box for that single cell? (Select TWO)

Question 8Choose one

An event coordinator is tracking registrations against venue capacity in a worksheet. The data is laid out as follows: - Column C: Number of Registered Attendees - Column D: Venue Capacity The coordinator wants to highlight the entire row for any event where the number of registrations has reached 90% or more of the venue's capacity, but has not exceeded it. The formatting should apply to the range A2:G50. Which formula should be entered into the Conditional Formatting 'Use a formula to determine which cells to format' rule?

Question 9Choose one

True or False: When working with Sparklines, a single Sparkline group can be configured to display different Sparkline types (for example, a Line Sparkline in one cell and a Column Sparkline in another cell within the same group).

Question 10Choose one

A complex formula in cell A10 depends on several input cells across different worksheets. To understand how the final result is calculated, you want to see a step-by-step breakdown of how Excel computes each part of the formula. Which formula auditing tool is specifically designed for this purpose? graph TD subgraph Inputs Sheet1_C5[Sheet1!C5: 100] Sheet2_D8[Sheet2!D8: 25] Sheet3_F2[Sheet3!F2: 0.1] end Inputs --> A10{A10: =(Sheet1!C5-Sheet2!D8)*(1+Sheet3!F2)} A10 --> Result((Result: ?))

10 more free samples are waiting

Create a free account to unlock the whole 77-427 sample bank, or get full access to all 425 practice questions in the simulator.

Create account