Excel 2016: Core Data Analysis, Manipulation, and Presentation Free Sample Questions

Description

Covers creating and formatting worksheets and workbooks, inserting, formatting and summarizing data in cells and ranges, creating and managing tables, and creating and formatting charts and objects.

20 free sample questions155 in the full practice test

Try simulator

77-727 Sample Questions

  1. Question 1

    Beginner

    Create and Manage Worksheets and Workbooks · Display repeating row and column titles on multipage worksheets

    A project manager is preparing a multi-page sales report for printing. To ensure that the column headers in row 1 are visible on every printed page, which Page Setup option must be configured?

    Answer and explanation

    Correct answer: B

    The 'Print Titles' feature, found in the Page Setup dialog box under the Sheet tab, is specifically designed to repeat specified rows or columns at the top or left of each printed page. This is essential for readability in long reports. Headers and Footers are for page numbers or document titles, Freeze Panes only affects the on-screen view, and Set Print Area defines the range to be printed.

  2. Question 2

    Beginner

    Perform Operations with Formulas and Functions · Format and modify text by using functions

    You are cleaning a dataset of customer feedback. Column C contains comments in all uppercase letters. To convert these comments to title case (e.g., "CUSTOMER IS VERY SATISFIED" becomes "Customer Is Very Satisfied"), which function should you use in column D?

    Answer and explanation

    Correct answer: C

    The PROPER function converts a text string to proper case, where the first letter in each word is capitalized and all other letters are lowercase. LOWER converts all text to lowercase, UPPER converts all text to uppercase, and MID extracts characters from the middle of a string.

  3. Question 3

    Intermediate

    Create Charts and Objects · Create a new chart

    A data analyst needs to visualize the daily temperature fluctuations over a month. The data is in two columns: 'Date' and 'Temperature'. Which chart type is most appropriate for displaying this time-series data to show trends?

    Answer and explanation

    Correct answer: C

    A Line Chart is the best choice for showing trends over time (time-series data). It connects data points chronologically, making it easy to see patterns, increases, and decreases. A Pie Chart shows parts of a whole, a Bar Chart compares discrete categories, and a Scatter plot shows the relationship between two numeric variables.

  4. Question 4

    Intermediate

    Create Tables · Filter and sort tables

    You have a large table of employee data and have applied a filter to show only employees from the 'Sales' department. You now need to sort this filtered view by 'Hire Date' from oldest to newest. What is the correct procedure?

    Answer and explanation

    Correct answer: C

    Excel is designed to sort only the visible (filtered) data. There is no need to clear the filter or move the data. Simply applying a sort to a column while a filter is active will correctly reorder the currently displayed records without affecting the hidden rows.

  5. Question 5

    Intermediate

    Manage Data Cells and Ranges · Format cells by using Format Painter

    A user wants to copy cell formatting (font color, fill color, and border) from cell A1 to a non-adjacent range C5:E10. What is the most efficient way to accomplish this?

    Answer and explanation

    Correct answer: D

    Double-clicking the Format Painter button 'locks' it, allowing the user to apply the copied format to multiple selections (cells or ranges) until the tool is deactivated by pressing Esc or clicking the button again. A single click only allows for one application. While Paste Special works, double-clicking the Format Painter is generally considered the most efficient method for this task.

  6. Question 6

    Intermediate

    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?

    Answer and 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.

  7. Question 7

    Intermediate

    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.

    Answer and 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.

  8. Question 8

    AdvancedMultiple answers

    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)

    Answer and explanation

    Correct answers: B, C

  9. Question 9

    Intermediate

    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?

    Answer and 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.

  10. Question 10

    Intermediate

    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?

    Answer and explanation

    Correct answer: C

    The correct syntax for SUMIF is SUMIF(range, criteria, [sum_range]). The range (A:A) is where the criteria is checked. The criteria ("North") is the condition to match. The sum_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.

Register free for 10 more questions

Or unlock all 155 77-727 questions with explanations, timed mode and flashcards.