Microsoft Office Specialist: Excel Associate (Microsoft 365 Apps) — Practice Set 4

Question 1

You are working in Microsoft Excel. Several worksheets in your workbook are hidden, and you need to unhide three of them at once instead of one at a time. What should you do?

Accepted Answer

The Unhide dialog lists all hidden sheets and, in current Excel versions, allows selecting multiple names with Ctrl+click before clicking OK to restore several sheets in one action, rather than repeating the process individually for each one.

Question 2

You are working in Microsoft Excel. Your worksheet contains 50 columns of data, but you only want columns A through J to be included when the sheet is printed. What should you do?

Accepted Answer

Selecting the desired range and using Page Layout > Print Area > Set Print Area restricts printed output to exactly that range (columns A-J), without needing to hide or delete any data, and the setting persists for future print jobs.

Question 3

You are working in Microsoft Excel. You want to prevent coworkers from inserting, deleting, renaming, or moving any worksheet tabs in a shared workbook, while still letting them edit cell values freely. Which two facts about Protect Workbook Structure are correct? Choose 2.

Accepted Answer

Review > Protect Workbook (Structure) locks the sheet tab arrangement — preventing insertion, deletion, renaming, moving, or hiding of sheets — independent of cell-level protection, and it can require a password to remove. It does not lock cell contents and does not restrict file opening; that requires a separate 'Encrypt with Password' setting.

Question 4

You are working in Microsoft Excel. You want to review your worksheet exactly as it will appear on paper, including margins, headers, and page breaks, while still being able to edit cells directly. Which view should you switch to?

Accepted Answer

Page Layout view (View > Page Layout) shows the worksheet as it will look when printed, including margins, headers/footers, and page boundaries, while still allowing full cell editing — unlike Page Break Preview, which is optimized for adjusting page breaks rather than everyday editing.

Question 5

A user is comparing data in row 5 of a worksheet against data in row 5,000 of the same worksheet, and repeatedly scrolling between them is slow and error-prone. They do not need to view a different sheet, just two distant parts of the same sheet at once. What is the best solution?

Accepted Answer

View > Split divides the current window into multiple resizable panes of the same worksheet, each of which can be scrolled independently, allowing the user to view row 5 in one pane and row 5,000 in another simultaneously without duplicating data.

Question 6

You are working in Microsoft Excel. You need to insert three new blank cells above an existing selection, shifting existing cells down, without disturbing data in other columns. What should you do?

Accepted Answer

Right-clicking a selected range and choosing Insert opens a dialog offering 'Shift cells down' (or right), which inserts blank cells at that location and pushes existing data down without affecting unrelated columns, unlike inserting an entire row.

Question 7

You are working in Microsoft Excel. You want a small bar to appear inside each cell of a column, scaled to that cell's value relative to others in the column, so you can compare magnitudes visually without reading the numbers. Which Conditional Formatting feature should you apply?

Accepted Answer

Data Bars fill each cell with a proportional horizontal bar based on its value relative to the range, giving an at-a-glance visual comparison of magnitude directly inside the cells.

Question 8

You are working in Microsoft Excel. You want negative numbers in a column to display in red and enclosed in parentheses, for example (500) instead of -500. Which two statements are correct? Choose 2.

Accepted Answer

A custom number format code with a semicolon-separated negative section, like #,##0;[Red](#,##0), controls how negative values display. Additionally, the built-in Number format category in Format Cells includes preset negative number styles, one of which shows red numbers in parentheses, without needing a formula, table, or Conditional Formatting.

Question 9

You are working in Microsoft Excel. A column contains product codes formatted inconsistently, some with lowercase letters like 'abc-100' and others uppercase like 'XYZ-200'. You want to quickly find and replace all instances of 'abc' with 'ABC' throughout the sheet. What is the best tool?

Accepted Answer

Find & Replace (Ctrl+H) with the 'Match case' option enabled lets you replace only the lowercase 'abc' occurrences with 'ABC' without altering entries that are already correctly formatted or unrelated text.

Question 10

A user has a long product description in cell B2 that needs to be indented slightly from the left edge of the cell and vertically centered within a tall row, for a cleaner catalog layout. Which settings should they adjust?

Accepted Answer

The Alignment tab in Format Cells lets you independently set Horizontal alignment (Left with an Indent value pushes text slightly inward from the left edge) and Vertical alignment (Center vertically positions text within a tall row), achieving the desired layout without merging or resizing columns.

Question 11

You are working in Microsoft Excel. Your Excel table currently occupies A1:D20, and you have new data to add in rows 21 through 25 directly below it. What is the simplest way to extend the table to include this new data?

Accepted Answer

Excel tables have an AutoExpansion behavior: typing data into the row immediately adjacent to the last table row automatically extends the table's range to include it, applying the table's formatting and structured references without any manual steps.

Question 12

You are working in Microsoft Excel. You have a large table and want to add interactive filter buttons that sit outside the table, so users can click a button to filter by Region without using the column header drop-downs. What should you insert?

Accepted Answer

Table Design > Insert Slicer adds a floating, clickable button panel for a chosen column (such as Region) that visually filters the table when a button is clicked — a more interactive alternative to the column header filter drop-down.

Question 13

You are working in Microsoft Excel. Your table has a Quantity column and a Price column, and you add a new calculated column called Total using the formula =[@Quantity]*[@Price] in the first data row. Which two statements about this behavior are correct? Choose 2.

Accepted Answer

When you enter a formula using structured references (like [@Quantity]) in one cell of a table column, Excel's AutoFill for Formulas feature automatically propagates it down the entire column. The @ symbol denotes 'this row', so [@Quantity] refers to the Quantity value in the same row as the formula — no manual copying, header removal, or errors involved.

Question 14

You are working in Microsoft Excel. You want the first column of your table (containing product names) to appear bold to distinguish it from the rest of the data, using a built-in table style option rather than manual formatting. What should you do?

Accepted Answer

The First Column checkbox in Table Style Options applies special formatting (typically bold) to the leftmost column of the table automatically, as part of the applied table style, without requiring manual bold formatting.

Question 15

A logistics coordinator has a table of shipments and wants to sort it so that the Priority column follows a custom order of 'Urgent', 'High', 'Normal', 'Low' rather than alphabetical order. How should they accomplish this?

Accepted Answer

The Sort dialog's Order drop-down includes a 'Custom List' option where you can define a custom sequence, such as Urgent, High, Normal, Low, which Excel will use instead of default alphabetical sorting for that column.

Question 16

You are working in Microsoft Excel. You need a formula that returns 'Pass' only if a student's score in A1 is at least 60 AND their attendance in B1 is at least 80%. Which function combination is appropriate?

Accepted Answer

AND(A1>=60,B1>=0.8) returns TRUE only when both conditions are met, and wrapping it in IF converts that TRUE/FALSE result into the text 'Pass' or 'Fail'. OR would incorrectly return 'Pass' if only one condition is true.

Question 17

You are working in Microsoft Excel. You need to total the Amount column but only for rows where Region equals 'West' AND Status equals 'Closed'. Which function should you use?

Accepted Answer

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...) supports multiple conditions. Placing the sum range first followed by each criteria range/value pair correctly sums Amount only where both Region is 'West' and Status is 'Closed'.

Question 18

You are working in Microsoft Excel. You need to count orders where Region is 'East' AND Amount is greater than 500. Which two elements must the COUNTIFS formula include to work correctly? Choose 2.

Accepted Answer

COUNTIFS requires a criteria_range/criteria pair for each condition — one for Region equaling 'East' and another for Amount greater than 500 — combined with implicit AND logic. Unlike SUMIFS, COUNTIFS takes no separate sum_range, and it fully supports multiple criteria pairs (note: E is also technically true about quoting, but the two essential structural requirements here are A and B).

Question 19

You are working in Microsoft Excel. Cell A1 contains '  Acme Corp  ' with extra leading, trailing, and internal double spaces. You want a clean version with only single spaces between words and no leading or trailing spaces. Which function should you use?

Accepted Answer

=TRIM removes leading and trailing spaces and reduces multiple internal spaces between words down to a single space, producing a clean text string. UPPER and PROPER only change letter casing, and LEN just returns the character count.

Question 20

You are working in Microsoft Excel. Column A has first names and column B has last names, all in mixed or lowercase, such as 'john' and 'SMITH'. You want column C to display each full name with only the first letter of each word capitalized, such as 'John Smith'. Which formula should you use?

Accepted Answer

=PROPER capitalizes the first letter of each word and lowercases the rest, regardless of the original casing. Combining it with A1&" "&B1 (joining first and last name with a space) produces 'John Smith' from any casing variation. UPPER would produce all-caps, and CONCATENATE alone wouldn't add a space or fix casing.

Question 21

You are working in Microsoft Excel. You want to compare exact numeric values for five products across four quarters using a chart, where categories are best shown as vertical bars. Which basic chart type is most appropriate?

Accepted Answer

A Clustered Column chart displays vertical bars grouped by category (products) with a separate bar for each series (quarters), making it ideal for comparing multiple values across categories side by side. Pie and Doughnut charts show parts of a whole, and Scatter charts are for showing correlation between two numeric variables.

Question 22

You are working in Microsoft Excel. You have two numeric columns, Advertising Spend and Units Sold, and you want to visualize whether there is a correlation between the two. Which chart type is best suited?

Accepted Answer

A Scatter chart plots pairs of numeric values as individual points, one axis per variable, making it the standard chart type for visualizing correlation or relationships between two continuous numeric measures like spend and units sold.

Question 23

You are working in Microsoft Excel. Your chart currently uses the default blue color theme, but you want to quickly switch it to a different built-in color palette that matches your company's branding, without manually recoloring each series. What should you do?

Accepted Answer

Chart Design > Change Colors offers a gallery of built-in color palettes that instantly recolor all data series in the chart consistently, which is far faster than manually recoloring each series or data point individually.

Question 24

You are working in Microsoft Excel. You need to reposition and resize a chart on the worksheet so it sits neatly over a specific range of cells for printing. Which two techniques accomplish this? Choose 2.

Accepted Answer

A selected chart can be moved by dragging its border and resized by dragging its corner or edge handles. For precise dimensions, the Format tab's Size group lets you type exact Height and Width values. Conditional Formatting and Data Validation apply to cell data, not chart objects.

Question 25

You are working in Microsoft Excel. You want to print only the selected chart by itself on a page, without printing the surrounding worksheet cells. What is the correct approach?

Accepted Answer

When a chart object is selected (clicked once so its border shows selection handles) before opening File > Print, Excel automatically detects the selection and prints only that chart, scaled to fit the page, without the surrounding worksheet cells.

MO-210 — Microsoft Office Specialist: Excel Associate (Microsoft 365 Apps)