Excel 2016: Core Data Analysis, Manipulation, and Presentation Practice Questions
Prepare for 77-727 with more than an answer.
- Exam fee
- $100 USD
- Level
- Core
- Valid for
- Lifetime (does not expire)
Domains covered on the exam 5
- Create and Manage Worksheets and Workbooks
- Manage Data Cells and Ranges
- Create Tables
- Perform Operations with Formulas and Functions
- Create Charts and Objects
- 1
Case Study: A small retail business, 'Urban Blooms', tracks its weekly sales in an Excel workbook. The workbook contains a separate sheet for each month.
The owner wants to create a 'YearEndSummary' sheet to consolidate key metrics. Specifically, on this summary sheet, she needs to calculate the total annual revenue by summing cell F50 (which contains the monthly total) from every monthly sheet ('Jan' through 'Dec').
Which formula construction is the most efficient and scalable for calculating the grand total on the 'YearEndSummary' sheet?
Show answer details
Correct answer: B
This formula uses a 3D reference (
Jan:Dec!F50), which is the most efficient way to sum the same cell across multiple, contiguous worksheets. It tells Excel to take cell F50 from the 'Jan' sheet, the 'Dec' sheet, and every sheet in between, and sum them. This is far more efficient and less error-prone than manually adding each cell. - 2
You have a list of product codes in the format 'CATEGORY-ID-YEAR', for example, 'ELE-A45-2023'. You need to extract only the middle part, the 'A45' ID. The ID is always 3 characters long and starts at the 5th character. Which formula will accomplish this?
Show answer details
Correct answer: C
The MID function is used to extract a specific number of characters from the middle of a text string. The syntax is
MID(text, start_num, num_chars). In this case,MID(A1, 5, 3)tells Excel to start at the 5th character of the text in cell A1 and extract 3 characters. - 3
A financial analyst is preparing a report and needs to prevent any changes to a worksheet containing final calculations. However, they want to allow users to still view the data. Which protection feature is most suitable?
Show answer details
Correct answer: B
The 'Protect Sheet' feature (found on the Review tab) is designed to prevent changes to the contents of a worksheet. By default, it locks all cells, making them read-only. This allows users to view the data but not edit it. 'Encrypt with Password' prevents opening the file, 'Protect Workbook Structure' prevents adding/deleting sheets, and 'Mark as Final' is a soft suggestion that can be easily bypassed.
- 4
When using the Remove Duplicates tool on an Excel table, the tool permanently deletes the entire row where a duplicate value is found.
Show answer details
Correct answer: A
The Remove Duplicates tool works by identifying rows that have identical values in the selected columns and then deleting those entire rows from the dataset, keeping only the first unique instance it encounters. This is a destructive action that permanently alters the table.
- 5
A data analyst needs to organize a large dataset of customer transactions into a hierarchical view, allowing them to expand and collapse details for each region. The data is sorted by Region, then by City. What is the most appropriate feature to create this collapsible structure?
graph TD subgraph AllData [All Transactions] subgraph NorthAmerica [Region: North America] CityNY[City: New York] CityLA[City: Los Angeles] end subgraph Europe [Region: Europe] CityLON[City: London] CityPAR[City: Paris] end endShow answer details
Correct answer: B
The 'Group' command, part of the Outline feature on the Data tab, is specifically designed to create hierarchical levels that can be expanded or collapsed. This allows the user to view summary data (like regions) and then drill down into the details (like cities and individual transactions).
- 6
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?
Show answer details
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.
- 7
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?
Show answer details
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.
- 8
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?
Show answer details
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.
- 9
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?
Show answer details
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.
- 10
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?
Show answer details
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.
