D388 Fundamentals of Spreadsheets and Data Presentations - Set 5 - Part 2

Correct Answer: Everything above row 4 and to the left of column C is frozen while scrolling

Freeze Panes locks the rows above and the columns to the left of the active cell. With C4 selected, rows 1 to 3 and columns A to B remain visible as you scroll down and right. This keeps headers and label columns in view. Freeze Top Row and Freeze First Column are quick presets that lock only that single row or column.

Correct Answer: Freeze Panes locks rows/columns so they stay visible; Split divides the window into independently scrollable panes

Freeze Panes keeps chosen rows and columns fixed on screen while the rest scrolls, and the frozen area cannot be scrolled. Split divides the window into two or four panes, each with its own scroll bars, so you can view and scroll different parts of the same sheet simultaneously. You can drag or remove the split bars freely.

Correct Answer: To remove leading, trailing, and extra internal spaces that can cause matching and lookup errors

Imported data often contains stray spaces at the start or end of entries, or multiple spaces between words, which cause lookups and comparisons to fail even when values look identical. TRIM removes leading and trailing spaces and collapses internal runs of spaces to single spaces, standardizing text so joins, filters, and VLOOKUP-style matches work correctly.

Correct Answer: PROPER

PROPER capitalizes the first letter of each word and lowercases the rest, turning jOHN smith into John Smith, which is ideal for names and titles. UPPER and LOWER force a single case throughout, and TRIM handles spacing rather than capitalization. Combining PROPER with TRIM is a common step when normalizing messy imported text.

Correct Answer: Clustered column chart

A clustered column chart is ideal for comparing discrete values across multiple categories, allowing side-by-side visual comparison of each product category. A pie chart only shows parts of a single whole, a scatter plot displays correlation between two numeric variables, and a histogram shows the frequency distribution of a single continuous variable rather than comparing categories.

Correct Answer: Scatter plot

A scatter plot plots two continuous numeric variables against each other, making it the best choice for revealing correlation or the strength and direction of a relationship between advertising spend and sales. Pie and stacked bar charts show composition, and a line chart is best for showing a single variable's trend over ordered time intervals rather than the relationship between two independent variables.

Correct Answer: Histogram

A histogram is designed to display the frequency distribution of a continuous variable grouped into bins or intervals, making it perfect for showing how ages fall into ranges. Unlike a bar chart, a histogram's bars represent continuous intervals and typically touch. Pie charts show proportions of a whole, line charts show trends over time, and scatter plots show relationships between two variables.

Correct Answer: Combo chart with a secondary axis

A combo chart (for example, columns for revenue and a line for profit margin) with a secondary vertical axis lets you plot two data series that have very different scales and units on the same visual, keeping both readable. A single-axis chart would flatten the smaller-scale series, and pie or doughnut charts cannot represent two distinct metrics tracked over time.

Correct Answer: Legend

The legend serves as a key, mapping each color, pattern, or marker to the data series it represents, which is essential when a chart contains more than one series. Gridlines are reference lines behind the plot area, data labels show the exact value of individual data points, and an axis title labels what a given axis measures.

Correct Answer: To display the exact numeric value of individual data points directly on the chart

Data labels display the precise value (or category name or percentage) of specific data points directly on or next to the plotted elements, letting readers read exact numbers without estimating against the axis. Identifying series colors is the legend's job, horizontal reference lines are gridlines, and describing the chart's subject is the chart title's role.

Correct Answer: A small, cell-sized chart that shows a trend for a single row or range of data

A sparkline is a tiny chart contained within a single cell that provides a compact visual representation of the trend in an adjacent range of data, such as monthly sales across a row. It is ideal for dashboards and tables where space is limited. It is not a full separate chart, a formatting rule, or a pivot table feature.

Correct Answer: Win/Loss sparkline

A Win/Loss sparkline displays each value as an identical-height marker pointing up or down depending only on whether the value is positive or negative, making it ideal for at-a-glance profit/loss or gain/loss patterns. Line and column sparklines emphasize the magnitude and trend of values, and Excel does not offer an area sparkline type.

Correct Answer: Place them in the top-left area where viewers naturally look first

Because viewers in left-to-right reading cultures naturally scan starting from the top-left, the most important KPIs should be placed there so they are seen first. Burying critical metrics at the bottom, hiding them, or scattering them randomly undermines the dashboard's goal of communicating key information quickly and clearly.

Correct Answer: Removing unnecessary gridlines, borders, and decorative elements

Removing non-essential elements, sometimes called reducing chart junk, increases the data-to-ink ratio so the actual data stands out and the message is clearer. Adding 3-D effects, using excessive colors, or cramming in every series all increase cognitive load and distract from the information the chart is meant to convey.

Correct Answer: It exaggerates the visual difference between values, distorting the reader's interpretation

Truncating the value axis so it does not start at zero exaggerates the proportional difference between bars, causing readers to perceive a much larger gap than actually exists. For bar and column charts, the axis should generally start at zero so the length of each bar accurately represents its value. The other options are unrelated to axis scaling.

Correct Answer: Use color purposefully to highlight key data and rely on muted tones for context

Effective color use is purposeful: a distinct highlight color draws attention to the most important data, while muted or neutral tones provide supporting context, creating a clear visual hierarchy. Saturating everything eliminates emphasis, relying only on red and green creates problems for color-blind viewers, and inconsistent color meaning confuses readers.

Correct Answer: The human eye struggles to compare the areas and angles of many similar slices

People are poor at accurately judging and comparing the angles and areas of pie slices, especially when there are many slices of similar size, making differences hard to perceive. A bar or column chart, whose lengths are much easier to compare, is a better choice when there are many categories. Pie charts have no hard limit of five values and do not use secondary axes or time series.

Correct Answer: It improves readability and reduces cognitive load without altering the actual data

Applying a display format such as abbreviated millions or thousands makes large numbers easier to scan and interpret while leaving the actual stored value unchanged, so formulas still calculate correctly. Number formatting affects only how the value appears, not the value itself, and it does not convert numbers to text or affect unrelated cells.

Correct Answer: To concisely describe what the chart is showing so the audience understands its subject

A chart title gives the audience immediate context about the chart's subject and purpose, ideally summarizing the key takeaway. Identifying series by color is the legend's function, showing exact point values is done with data labels, and reference lines across the plot area are gridlines.

Correct Answer: Emphasize the key insight visually and structure the narrative around it

Effective data storytelling focuses the audience on a clear key insight, using visual emphasis (such as a highlight color or annotation) and a logical narrative structure to guide interpretation toward the intended conclusion. Dumping raw data, overusing chart types, or omitting helpful annotations all make it harder for the audience to grasp the message.