UUS Practice Tests⌕ Search
IT Certifications · 77-728

Exam 77-728: Microsoft Excel Expert (Office 2016) Practice Test

Excel 2016 expert-level work involves building reliable formulas, analyzing structured data, auditing calculations, and shaping workbook output. The available practice material touches on tasks such as extracting date components, combining text, using table formulas, grouping PivotTable dates, and changing chart series. These skills support accurate reporting and efficient maintenance of complex spreadsheets.

Exam overview

Exam providerMicrosoft
Exam code77-728
CategoryIT Certifications

About the Exam 77-728: Microsoft Excel Expert (Office 2016) Exam

Effective workbook work requires choosing functions according to how they treat numbers, text, and logical values, and constructing formulas that return the intended result. Structured references can make table calculations easier to extend across rows, while formula-auditing tools help trace dependencies when a result is incorrect. PivotTable grouping can convert individual dates into useful calendar periods, and chart controls let you adjust plotted series without reorganizing source data. Other practical decisions include selecting a save format that retains formulas or macro-enabled procedures, and pasting calculated results as values when formulas are no longer needed. Together, these tasks involve balancing accuracy, maintainability, and clear presentation in workbooks used for analysis and reporting.

Sample Practice Questions

Review a selection from this exam's published US Practice Tests practice bank.

Question 1

In a PivotTable, a date field appears as individual dates. Which command can group those dates into calendar months?

  1. AGroup
  2. BRefresh
  3. CShow Values As
  4. DReport Layout
Show answerCorrect answer: A

The Group command can group date items into periods such as months, quarters, or years. Refresh updates the PivotTable from its source, while the other commands affect value calculations or layout.

Question 2

A cell contains a formula that returns an error, and you want to identify which cells feed into that formula. Which formula-auditing command should you use?

  1. ATrace Precedents
  2. BTrace Dependents
  3. CRemove Arrows
  4. DError Checking Options
Show answerCorrect answer: A

Trace Precedents draws arrows from cells referenced by the selected formula, helping identify its inputs. Trace Dependents instead shows cells that use the selected cell.

Question 3

In Excel 2016, which function returns the largest value in a range while excluding logical values and text entered directly as arguments?

  1. AMAX
  2. BCOUNT
  3. CMEDIAN
  4. DLARGE
Show answerCorrect answer: A

MAX returns the largest numeric value in its arguments. For a range reference, it ignores text and logical values in the cells. LARGE can also return a high value, but requires a rank argument.

Question 4

A workbook contains a macro-enabled procedure you want to preserve when saving. Which file format is appropriate?

  1. A.xlsm
  2. B.xlsx
  3. C.csv
  4. D.txt
Show answerCorrect answer: A

The Excel Macro-Enabled Workbook format (.xlsm) supports saving VBA macros. The standard .xlsx format does not preserve VBA macro code.

Question 5

A worksheet has a formula in cell G4 that should display the text from A4 followed by the text from B4, with no additional characters between them. Which formula is suitable?

  1. A=A4&B4
  2. B=SUM(A4,B4)
  3. C=CONCATENATE(A4," ",B4)
  4. D=TEXT(A4,B4)
Show answerCorrect answer: A

The ampersand joins text values directly, without inserting a separator. The CONCATENATE option explicitly inserts a space, and the other functions do not perform the requested text join.

Question 6

A workbook contains formulas that reference worksheets in the same workbook. Which file format preserves those formulas and supports standard Excel workbook features?

  1. A.csv
  2. B.txt
  3. C.xlsx
  4. D.png
Show answerCorrect answer: C

The .xlsx format is Excel's standard workbook format and preserves formulas, worksheets, and other workbook features. CSV and text formats store plain tabular text rather than workbook formulas and structure.

Question 7

You need to add a new column to an Excel table that calculates the line total by multiplying each row’s Quantity by its Unit Price. Which formula is best suited for entering in the first data row of that table column?

  1. A=[@Quantity]*[@[Unit Price]]
  2. B=SUM(Quantity, [Unit Price])
  3. C=Quantity*Unit Price
  4. D=PRODUCT(Table1[Quantity],Table1[Unit Price])
Show answerCorrect answer: A

Structured references such as [@Quantity] refer to the values in the current table row. Multiplying the current row’s Quantity by its Unit Price calculates a line total, and Excel tables typically propagate a calculated-column formula to other rows.

How to use this practice test

The free practice session draws up to 20 random questions from the available 50-question bank. Answer questions on Excel formulas, tables, PivotTables, charts, auditing, and workbook handling, then review the results shown after the session to identify topics that need more targeted practice.

Preparation tips

  • Practice selecting functions by checking how they handle text, logical values, and numeric arguments; compare results on small test ranges.
  • Build calculated columns in Excel tables using structured references, then verify that the formula fills correctly for additional rows.
  • Use formula-auditing commands to trace precedent cells, and test how edits to those cells affect the dependent formula.
  • Group PivotTable date fields into months and change chart series assignments using chart controls rather than rearranging source data.
  • Compare workbook save formats for retaining standard formulas and macro-enabled procedures; practice pasting formula results as values.
Official sourceOfficial exam page ↗