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

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.

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.

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.

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.

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.

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.

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.

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.

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.