77-427 Excel 2013 Expert Practice Questions
Prepare for 77-427 with more than an answer.
- Exam fee
- $150 USD
- Level
- Expert
- Valid for
- Lifetime (no expiration)
Domains covered on the exam 4
- Manage and Share Workbooks20%
- Apply Custom Formats and Layouts25%
- Create Advanced Formulas30%
- Create Advanced Charts and Tables25%
- 1
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?
Show answer details
Correct answer: B
This is the correct formula. Excel stores dates as whole numbers and times as decimal fractions. Adding TIME(17,0,0) to the date in A2 creates a precise timestamp for 5:00 PM on the scheduled delivery date. The formula then correctly checks if the actual arrival timestamp in B2 is later than this cutoff.
- 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)
Show answer details
Correct answer: A, B
The 'Settings' tab in the Data Validation dialog box allows you to set criteria, such as allowing only whole numbers within a specified range.
The 'Input Message' tab in the Data Validation dialog box is designed specifically to show a message to guide the user when they select the cell.
- 3
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?
Show answer details
Correct answer: A
This formula is correct because it uses mixed references. The dollar signs ($) lock the column references to C and D, ensuring that for every cell in a given row (e.g., A2, B2, C2), the condition is always evaluated based on the values in columns C and D of that row. The row number (2) is relative, allowing the rule to adjust correctly for each subsequent row in the range.
- 4
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).
Show answer details
Correct answer: B
This is correct. All Sparklines created together in a single operation form a Sparkline group. Every Sparkline within that group must be of the same type (Line, Column, or Win/Loss). To use different types, you must create separate Sparkline groups.
- 5
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: ?))Show answer details
Correct answer: D
The
Evaluate Formulatool (found on the Formulas tab) is designed to debug a formula by displaying the calculation of each part of the formula individually, in the order it is performed. This allows you to step through the calculation to see exactly where an error or unexpected result originates. Trace Precedents shows which cells are used, but not the calculation steps. - 6
A financial analyst is creating a summary report. In cell C10, they need to display a custom format for sales figures that are in cell B10. The requirements are:
- Numbers should be displayed in millions, with one decimal place (e.g., 2,550,000 should appear as 2.6 M).
- Positive numbers should be black.
- Negative numbers should be red and enclosed in parentheses.
- Zero values should be displayed as a hyphen "-".
Which custom number format code should be applied to cell C10?
Show answer details
Correct answer: B
This is the correct format string. The structure for custom formats is
Positive;Negative;Zero;Text.#.0,," M"correctly formats positive numbers to millions with one decimal place. The two commas are crucial for dividing by one million.[Red](#.0,," M")handles negative numbers by setting the color to red and enclosing them in parentheses. The hyphen-correctly specifies the format for zero values. The final semicolon is optional but good practice. - 7
A project manager is using the
WORKDAY.INTLfunction to calculate the delivery date for a project. The start date is in cell A2, the number of working days is in A3, and a list of company holidays is in a named rangeHolidays. The company operates on a non-standard work week where only Friday is a day off. Which formula correctly calculates the end date?Show answer details
Correct answer: B
The
WORKDAY.INTLfunction allows for a custom weekend string as the third argument. This string consists of seven characters, starting with Monday. A '1' indicates a non-working day (weekend), and a '0' indicates a working day. To specify that only Friday is a day off, the string must be"0000100"(Mon, Tue, Wed, Thu are workdays; Fri is a non-workday; Sat, Sun are workdays). - 8
You are managing a shared workbook that tracks quarterly sales data. Multiple regional managers update this single file. To maintain data integrity, you need to accept or reject changes made by others. However, the 'Accept/Reject Changes' button in the 'Changes' group on the 'Review' tab is greyed out and unavailable. What is the most likely cause of this issue?
Show answer details
Correct answer: B
The 'Accept/Reject Changes' functionality is specifically part of the legacy 'Shared Workbook' feature. This command is only active when the workbook has been explicitly shared via 'Review' > 'Share Workbook'. If the workbook is not in this mode, even if 'Track Changes' is on, you cannot use the 'Accept/Reject' dialog. The feature requires the workbook to be formally shared to manage changes from multiple users.
- 9
A data analyst has a PivotTable summarizing sales by
Region(Rows),Product Category(Rows), andYear(Columns). They need to add a new column directly within the PivotTable that calculates a 5% commission for eachProduct Categorybased on the total sales amount. This calculation should update dynamically as the PivotTable is filtered or refreshed. What is the most appropriate tool to achieve this?Show answer details
Correct answer: C
A 'Calculated Field' is a custom formula that is part of the PivotTable itself, not the source data. It allows you to perform calculations on the sum of other PivotTable fields. In this case, you would create a calculated field with a formula like
='Sales Amount' * 0.05. This new field will appear in the PivotTable and update automatically with any changes to the underlying data or filters. - 10
You need to apply conditional formatting to a range of project deadlines (B2:B50). You want to highlight any date that falls on a Saturday or a Sunday. Which formula should be used in the conditional formatting rule to correctly identify weekend dates?
Show answer details
Correct answer: A
The
WEEKDAYfunction, by default, returns a number from 1 (Sunday) to 7 (Saturday). The condition> 5correctly identifies both Saturday (7) and Sunday (1 is not > 5, this is wrong). A better formula would beOR(WEEKDAY(B2)=1, WEEKDAY(B2)=7). However, if we use the second argumentreturn_typeas 2,WEEKDAY(B2, 2)returns 1 for Monday through 7 for Sunday. In that case,=WEEKDAY(B2, 2) > 5would correctly identify Saturday (6) and Sunday (7). Assuming the default context of the exam, the most common approach is the one given. Let's re-evaluate. The defaultWEEKDAY(date)returns 1 for Sunday and 7 for Saturday. So>5would only get Saturday. The correct logic should encompass both. Let me correct the answer. The most robust formula among plausible choices would be one that checks for both conditions. Let's rephrase the options.Corrected Explanation: The
WEEKDAY(B2, 2)function returns a number from 1 (Monday) to 7 (Sunday). Therefore, a result greater than 5 indicates that the day is either Saturday (6) or Sunday (7). Using this formula in a conditional formatting rule applied to the range B2:B50 will correctly highlight all weekend dates. The reference to B2 is relative and will adjust for each cell in the range.
