77-427 Sample Questions & Answers
Advanced formulas, including date, time and statistical functions, carry the most weight, alongside building advanced charts and PivotTables, custom data formats and conditional formatting, and managing workbook settings, protection and sharing.
Launch the full 77-427 simulator →Showing 10 of 20 free samples.
- Question 1Intermediate
Manage and Share Workbooks · Maintain Shared Workbooks
True or False: The 'Compare and Merge Workbooks' feature can be used to merge changes from multiple copies of a shared workbook, even if those copies have been saved with different file names.
Show answer & explanation
Correct answer: A
The 'Compare and Merge Workbooks' command is designed for this exact purpose. As long as each copy originated from the same shared workbook and has change tracking enabled, Excel can merge the changes back into the original file, regardless of the file names of the copies. This allows a manager to distribute a file, have multiple people work on their own copies, and then consolidate all the changes.
- Question 2IntermediateSelect 2
Create Advanced Charts and Tables · Create Sparklines
A sales manager is creating a dashboard. They want to include a small, in-cell chart next to each salesperson's total sales figure to show their sales trend over the last 12 months. The chart should also highlight the highest and lowest sales months. Which combination of Excel features is best suited for this task? (Select TWO)
Show answer & explanation
Correct answers: B, C
Sparklines are miniature charts that reside in a single cell, perfect for providing a quick visual representation of data trends without taking up much space on a dashboard.
Within the Sparkline Tools Design tab, you can enable Markers for 'High Point' and 'Low Point'. This will automatically highlight the highest and lowest values in the sparkline's data range, fulfilling the requirement.
- Question 3Beginner
Apply Custom Formats and Layouts · Apply Custom Data Formats and Validation
An HR manager has a worksheet with employee data. They need to ensure that when a user enters a new employee's department in column D, the entry must be one of the values from a master list of departments located in the named range
DeptList. Additionally, when a user selects a cell in column D, an input message should appear instructing them to 'Select a department from the list'. Which feature should be configured on column D to meet all these requirements?Show answer & explanation
Correct answer: B
The Data Validation feature is designed for this exact scenario. On the 'Settings' tab, you can 'Allow' a 'List' and set the 'Source' to
=DeptList. On the 'Input Message' tab, you can enter the required instructional text. This restricts input to the list values and provides user guidance. - Question 4Beginner
Manage and Share Workbooks · Manage Workbook Review
You are auditing a complex workbook. In cell F10, there is a formula
=SUM(Sheet2!C5, Sheet3!D8). You need to quickly navigate to cell C5 on Sheet2 to examine its value. Which is the most direct method to do this using Excel's formula auditing tools?Show answer & explanation
Correct answer: D
While other auditing tools are useful, this is the most direct navigation method. Editing the cell (or using the formula bar) and highlighting a cell reference within the formula allows you to use the 'Go To' command (F5) to jump directly to that specific precedent cell, even if it's on another worksheet.
- Question 5Intermediate
Create Advanced Charts and Tables · Create and Modify Advanced Charts
A financial analyst needs to create a chart that compares monthly revenue (in millions of dollars) against the number of units sold (in thousands). Because the scales of these two data series are vastly different, plotting them on a single value axis makes the 'units sold' series appear almost flat and unreadable. What type of chart and feature should be used to visualize this data effectively?
Show answer & explanation
Correct answer: C
This scenario is the primary use case for a combination chart with a secondary axis. By creating a combo chart (e.g., column for revenue, line for units sold) and plotting the 'units sold' series on a secondary vertical axis, each series gets its own scale. This allows both trends to be clearly visible and comparable on the same chart.
- Question 6Beginner
Create Advanced Charts and Tables · Create and Modify PivotTables
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?
Show answer & explanation
Correct answer: B
PivotTables have a powerful built-in feature for grouping dates. By right-clicking any date in the row or column labels and selecting 'Group', you can choose to group by various time periods, including Days, Months, Quarters, and Years. This is the most efficient method as it doesn't require altering the source data and is fully integrated into the PivotTable's functionality.
- Question 7IntermediateSelect 2
Manage and Share Workbooks · Prepare Workbooks for Internationalization and Accessibility
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)
Show answer & explanation
Correct answers: B, C
By right-clicking an object (like a chart or image) and choosing 'Format...', you can access the properties pane where you can find and edit the 'Alt Text' (both Title and Description fields).
Found under File > Info > Check for Issues, the 'Check Accessibility' tool scans the entire workbook and provides a report of potential issues for people with disabilities, including missing alt text, unclear hyperlinks, and problematic structures.
- Question 8Intermediate
Create Advanced Formulas · Apply Functions in Formulas
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?
Show answer & explanation
Correct answer: C
The
IFERRORfunction is designed specifically for this purpose. It evaluates the first argument (the VLOOKUP formula). If the formula executes without an error,IFERRORreturns the result of the formula. If the formula returns any error value (including #N/A), it returns the second argument ("Not Found"). This is the most concise and efficient way to handle potential errors from a lookup. - Question 9Intermediate
Create Advanced Charts and Tables · Create and Manage Tables
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?Show answer & explanation
Correct answer: B
This formula correctly uses the
SUMIFfunction with structured references.SalesData[Region]refers to the entire Region column within the table, which is the range to check the criteria against."North"is the criterion.SalesData[Amount]is thesum_range—the corresponding values to sum when the criterion is met. This syntax is efficient and automatically adjusts as the table grows or shrinks. - Question 10Advanced
Manage and Share Workbooks · Apply Protection and Sharing Properties to Workbooks and Worksheets
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:- The overall structure of the workbook (adding, deleting, or renaming sheets) must be locked to prevent accidental changes.
- Schedulers should only be able to edit cells A2:F100 on the 'ScheduleInput' sheet. All other cells on that sheet must be locked.
- Dispatchers should only be able to edit column G (Status) on the 'DispatchLog' sheet. All other columns must be locked.
- All users should be able to view the 'SummaryReport' but not edit it at all.
- 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?
Show answer & explanation
Correct answer: B
This is the correct standard procedure. Cell protection is a two-step process. First, you must mark the cells that should be editable by unlocking them (via Format Cells > Protection tab). By default, all cells are locked. After unlocking the specified ranges, you apply worksheet protection to enforce the lock. This must be done for each of the three sheets. Finally, protecting the workbook structure prevents changes to the worksheets themselves, satisfying all requirements.
Ready for the real thing?
The full 77-427 simulator has every exam-style question, timed mode, and instant scoring.