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

Question 1

You are working in Microsoft Excel. You want to visually organize your workbook by giving the 'Q1 Results' sheet tab a red color so it stands out from other tabs. What should you do?

Accepted Answer

Right-clicking a sheet tab and choosing Tab Color lets you assign a color directly to that tab for quick visual identification. Font Color and themes affect cell text or overall styling, not the tab itself.

Question 2

You are working in Microsoft Excel. Your worksheet has header labels in row 1 and product names in column A. As you scroll down and to the right through hundreds of rows and columns, you want both row 1 and column A to remain visible at all times. What should you do?

Accepted Answer

Selecting cell B2 (just below the header row and to the right of the first column) and applying View > Freeze Panes > Freeze Panes locks everything above and to the left of the selection — row 1 and column A both stay visible while scrolling.

Question 3

You are working in Microsoft Excel. You are preparing a worksheet for a clean printed handout and want to control what displays on screen versus what actually prints. Which two elements can be independently shown or hidden using the View tab's checkboxes for Gridlines and Headings? Choose 2.

Accepted Answer

The View tab's Gridlines checkbox toggles the faint on-screen cell grid, and Headings toggles the row/column labels (1,2,3 / A,B,C). Manually applied borders remain regardless. Print output is separately controlled by Page Layout > Sheet Options; 'Headings' here refers to row/column labels, not worksheet column titles.

Question 4

You are working in Microsoft Excel. You need every printed page of a report to show the company name in the top-left corner and the page number in the bottom-right corner. What should you configure?

Accepted Answer

Insert > Text > Header & Footer opens Page Layout view with editable header/footer sections divided into left, center, and right. You can type the company name in the left header box and insert the Page Number field in the right footer box, and both repeat automatically on every printed page.

Question 5

A user's computer crashed while they were editing a workbook that had not been saved in the past 15 minutes. When they reopen Excel, what feature is most likely to help them recover unsaved changes?

Accepted Answer

AutoRecover periodically saves temporary snapshots of open workbooks in the background. After an unexpected shutdown, Excel typically offers a Document Recovery pane on reopening, listing the most recent AutoRecover versions available for restoration.

Question 6

You are working in Microsoft Excel. You use the range C2:C50 in many formulas throughout the workbook, and typing $C$2:$C$50 repeatedly is error-prone. What should you do to reference this range more reliably?

Accepted Answer

Defining a named range (Formulas > Define Name) lets you reference C2:C50 using a memorable name like 'Prices' in formulas, e.g. =SUM(Prices), improving readability and reducing reference errors compared to typing absolute cell addresses repeatedly.

Question 7

You are working in Microsoft Excel. You want a title 'Quarterly Report' to appear centered visually across cells A1:E1 as a single unit, rather than just left-aligned in A1. What should you do?

Accepted Answer

Merge & Center combines the selected cells (A1:E1) into a single merged cell and centers the text within the merged area, producing a clean title banner that spans multiple columns.

Question 8

You are working in Microsoft Excel. You want a data entry cell to offer a drop-down list of only three valid choices: 'Open', 'In Progress', and 'Closed'. Which two configurations correctly set this up using Data Validation? Choose 2.

Accepted Answer

Data Validation's List option accepts either a comma-separated list typed directly into the Source box, or a reference to a range of cells containing the allowed values — both produce a drop-down arrow limiting entries to those choices. Conditional Formatting only changes appearance and does not restrict input.

Question 9

You are working in Microsoft Excel. You have a column of sales figures and want cells to shade from red (lowest) to green (highest) automatically to visualize magnitude at a glance. Which Conditional Formatting option should you apply?

Accepted Answer

Color Scales apply a graduated two- or three-color gradient across a range based on relative value, such as red for the lowest values fading to green for the highest, giving an immediate visual sense of magnitude across the whole column.

Question 10

A user has a row of 12 monthly totals in B1:M1 and wants to quickly rearrange this data into a single column A1:A12 instead, without retyping any values. Which technique accomplishes this?

Accepted Answer

Paste Special's Transpose option flips the orientation of copied data, converting a horizontal row into a vertical column (or vice versa) in one paste action, without manual retyping.

Question 11

You are working in Microsoft Excel. You have an Excel table and need to insert a new column named 'Commission' immediately to the right of the existing 'Sales' column. What is the easiest way to do this?

Accepted Answer

Right-clicking within a table column and choosing Insert > Table Column to the Left (relative to the column to the right of Sales) adds a new column inside the table structure, which you then rename by typing a new header, such as 'Commission'.

Question 12

You are working in Microsoft Excel. In your table, several rows in the Status column have been manually highlighted yellow to flag them for review. You want to filter the table to show only the yellow-highlighted rows. What should you do?

Accepted Answer

The table's AutoFilter drop-down includes a Filter by Color option that lists any cell fill colors present in that column, letting you display only rows with the matching yellow highlight without altering the underlying data.

Question 13

You are working in Microsoft Excel. You want to change the visual appearance of your table using one of the built-in gallery designs. Which two statements about Table Styles are correct? Choose 2.

Accepted Answer

The Table Styles gallery on the Table Design tab offers many pre-built color and shading combinations. Selecting a new style only changes the visual formatting — the table's data, filters, and structured references remain unchanged. It does not convert the table to a range, does not require Header Row to be off, and works offline.

Question 14

You are working in Microsoft Excel. Your table currently displays a Header Row with column titles, but you want to temporarily hide the header row while keeping the table's filtering functionality intact for later. What should you do?

Accepted Answer

Unchecking the Header Row checkbox in Table Style Options removes the visible header row (and its filter arrows temporarily disappear from view) while the table structure and data remain intact; re-checking it restores the header row with filtering.

Question 15

A user imported a list of contacts into an Excel table and suspects there are duplicate entries based on the Email column only, even though other columns like Notes may differ. How should they remove these duplicates?

Accepted Answer

The Remove Duplicates dialog (available from Table Design or Data tab) lets you choose exactly which column(s) determine a duplicate. Checking only Email means rows with matching emails are treated as duplicates regardless of differences in other columns like Notes, and Excel removes the extras automatically.

Question 16

You are working in Microsoft Excel. You have a formula =B2*C2 in cell D2 and you copy it down to D3. In D3, the formula automatically becomes =B3*C3. What type of cell reference does this behavior describe?

Accepted Answer

A relative reference (like B2 or C2, with no dollar signs) automatically adjusts to match the new row or column position when a formula is copied elsewhere, which is why =B2*C2 becomes =B3*C3 one row down.

Question 17

You are working in Microsoft Excel. You need to total the Amount column (C2:C100) but only for rows where the Department column (B2:B100) equals 'Marketing'. Which formula should you use?

Accepted Answer

=SUMIF(criteria_range, criteria, sum_range) checks B2:B100 for the value 'Marketing' and sums the corresponding cells in C2:C100. Option D would only count matching rows rather than summing their amounts.

Question 18

You are working in Microsoft Excel. Cell A1 contains 'INV-2026-0451'. You need to extract just the last four digits '0451' into another cell. Which two formulas correctly return this result? Choose 2.

Accepted Answer

=RIGHT(A1,4) returns the last 4 characters of the 14-character text, which is '0451'. =MID(A1,10,4) starts at the 10th character and takes 4 characters, also returning '0451'. LEFT would return characters from the start ('INV-'), and the other options return incorrect substrings.

Question 19

You are working in Microsoft Excel. Cell A1 contains a date value. You need a formula that returns just the month number (1-12) for use in another calculation. Which function should you use?

Accepted Answer

=MONTH(date) returns the month component as a number from 1 to 12, suitable for further numeric calculations. =TEXT(A1,"MM") would return the month as a two-character text string, not a usable number for math operations.

Question 20

You are working in Microsoft Excel. You have a horizontal price list where product codes are in row 1 (A1:F1) and prices are in row 2 (A2:F2). You need a formula to look up a product code and return its price from the row below it. Which function is best suited?

Accepted Answer

=HLOOKUP searches horizontally across the first row of a range and returns a value from a specified row number below it. Since the product codes are arranged horizontally in row 1 and prices in row 2, HLOOKUP with row_index 2 is the correct function, whereas VLOOKUP is designed for vertical (column-based) lookups.

Question 21

You are working in Microsoft Excel. You have created a pie chart showing market share by company, and you want each slice to display its percentage of the whole directly on the chart. What should you do?

Accepted Answer

Enabling Data Labels through Chart Elements (+) and choosing Percentage in the label options automatically calculates and displays each slice's share of the total directly on the pie chart, without manual typing.

Question 22

You are working in Microsoft Excel. You created a column chart, but after reviewing the data you decide a line chart would communicate the trend more clearly. You do not want to recreate the chart from scratch. What should you do?

Accepted Answer

Right-clicking an existing chart and choosing Change Chart Type opens a dialog where you can switch to any chart type, including Line, while keeping the same data source, formatting settings, and chart position.

Question 23

You are working in Microsoft Excel. Your chart's legend currently sits below the plot area, but you want more vertical space for the chart itself. Which two changes can you make regarding the legend? Choose 2.

Accepted Answer

In the Chart Elements (+) menu, hovering over Legend reveals position options like Right, Top, or Bottom, letting you reposition it. Unchecking Legend entirely removes it from the chart, freeing up plot area space. The legend is a chart object, not tied to worksheet cells or Page Layout themes.

Question 24

You are working in Microsoft Excel. Your line chart currently shows only major horizontal gridlines, but you want to add lighter minor gridlines between them for finer visual reference. What should you do?

Accepted Answer

The Gridlines option under Chart Elements (+) lets you independently toggle Primary Major and Primary Minor gridlines for both horizontal and vertical axes. Checking Primary Minor Horizontal adds finer gridlines between the existing major ones.

Question 25

You are working in Microsoft Excel. You want to copy a chart into a Word document so that it automatically updates whenever the source data in Excel changes. What should you do when pasting into Word?

Accepted Answer

Paste Special in Word offers a 'Paste Link' option (or a Paste Options icon for 'Keep Source Formatting & Link Data') that embeds a live link to the Excel chart, so it automatically refreshes when the source workbook's data changes. A normal paste embeds a static copy, and screenshots or PDFs do not update at all.

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