77-727 Sample Questions & Answers
Expect questions on summarizing data with functions, performing conditional operations, formatting text through formulas, formatting and creating charts plus other objects, managing tables and their styles, and basic worksheet and workbook setup.
Launch the full 77-727 simulator →Showing 10 of 20 free samples.
- Question 1Intermediate
Perform Operations with Formulas and Functions · Insert references
You are creating a budget spreadsheet. You need to reference the tax rate, located in cell H1, in multiple formulas across the worksheet. When you copy these formulas to other cells, the reference to H1 must not change. Which reference style should you use for cell H1?
Show answer & explanation
Correct answer: B
An absolute reference, denoted by dollar signs ($H$1), locks both the column (H) and the row (1). This ensures that when the formula is copied or filled to other cells, the reference to the tax rate in H1 remains constant. A relative reference (H1) would change as the formula is copied.
- Question 2Intermediate
Create and Manage Worksheets and Workbooks · Hide or unhide worksheets
True or False: Using the 'Hide Sheet' command makes the worksheet's data inaccessible to formulas on other visible sheets.
Show answer & explanation
Correct answer: B
Hiding a worksheet only affects its visibility in the user interface. Any formulas on other sheets that reference cells on the hidden sheet will continue to function correctly and access the data as needed. Hiding is a visual tool, not a data-blocking mechanism.
- Question 3AdvancedSelect 2
Create and Manage Worksheets and Workbooks · Configure worksheets and workbooks for distribution
To ensure a workbook created in Excel 2016 can be opened and fully functional in Excel 2003, which TWO actions should be performed before distribution? (Select TWO)
Show answer & explanation
Correct answers: B, C
- Question 4Intermediate
Manage Data Cells and Ranges · Insert sparklines
A marketing manager is analyzing campaign performance. The data is organized in an Excel table. To quickly see a visual representation of each campaign's performance directly within a cell next to its data, which feature should be used?
Show answer & explanation
Correct answer: C
Sparklines are miniature charts that fit inside a single cell, designed to provide a quick, visual representation of data trends next to the source data. They are ideal for this scenario. Conditional Formatting Data Bars fill the background of the source data cells themselves, while a full chart object is much larger and separate from the data cells.
- Question 5Intermediate
Perform Operations with Formulas and Functions · Perform logical operations by using the SUMIF function
You need to sum the total sales from column D, but only for transactions that occurred in the 'North' region, which is specified in column A. Which formula correctly calculates this conditional sum?
Show answer & explanation
Correct answer: C
The correct syntax for SUMIF is
SUMIF(range, criteria, [sum_range]). Therange(A:A) is where the criteria is checked. Thecriteria("North") is the condition to match. Thesum_range(D:D) is the range of cells to sum if the condition is met. Option A incorrectly swaps the range and sum_range. Option B is an array formula, which is more complex than needed. Option D uses the wrong function. - Question 6Advanced
Create and Manage Worksheets and Workbooks · Insert headers and footers
A company wants to add its logo to the top-right corner of every printed page of a workbook. The logo is saved as an image file. What is the proper method to achieve this?
Show answer & explanation
Correct answer: B
The correct way to have an image repeat on every printed page is to insert it into the header (or footer). By switching to Page Layout view, you can directly access the header sections (left, center, right) and use the Header & Footer Tools tab to insert a picture from a file.
- Question 7Advanced
Create Tables · Create an Excel table from a cell range
You have converted a range of data into an Excel table. What is the primary benefit of using structured references (e.g.,
=[@Sales]) in formulas within this table?Show answer & explanation
Correct answer: B
Structured references use table and column names instead of cell addresses (like A1), making formulas easier to read and understand. A key feature of tables is that formulas using structured references automatically propagate to any new rows added to the table, ensuring consistency.
- Question 8Intermediate
Create Charts and Objects · Add additional data series to charts
A sales manager has a chart showing monthly sales for four different regions. To make the chart less cluttered, she wants to temporarily remove the 'West' region's data series from the chart without deleting the source data. What is the most direct way to do this?
Show answer & explanation
Correct answer: B
The Chart Filters (funnel icon) tool, available when a chart is selected in Excel 2016, is designed specifically for this purpose. It allows you to interactively show or hide data series and categories directly on the chart without altering the source data. Hiding the source data column also works but is less direct than using the dedicated chart tool.
- Question 9Beginner
Manage Data Cells and Ranges · Modify cell alignment and indentation
A user has pasted a list of names into a single column, but the alignment is inconsistent. Some names are left-aligned, some are centered, and some are right-aligned. To standardize the entire column to be left-aligned with a small space from the cell border, what should the user do?
Show answer & explanation
Correct answer: C
First, applying 'Align Left' standardizes the horizontal alignment for all selected cells. Then, clicking 'Increase Indent' adds a uniform space from the left border of the cells, achieving the desired formatting in a consistent way for the entire column.
- Question 10Beginner
Create and Manage Worksheets and Workbooks · Hide or unhide worksheets
You have a workbook with sensitive financial data on a sheet named 'Q4-Internal'. You need to prevent other users from seeing this sheet, but still allow formulas on a 'Summary' sheet to reference its data. Which is the most appropriate action?
Show answer & explanation
Correct answer: B
Hiding the worksheet removes it from view but keeps the sheet and its data within the workbook. This allows other worksheets to continue referencing its cells for calculations while preventing casual users from seeing the sensitive information. Deleting the sheet would break the formulas.
Ready for the real thing?
The full 77-727 simulator has every exam-style question, timed mode, and instant scoring.