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
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 details
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.
- 2
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 details
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.
- 3
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. - 4
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. - 5
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.
- 6
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.
- 7
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).
- 8
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.
- 9
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.
- 10
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.
