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

Question 1

You are working in Microsoft Excel. You want to hide a worksheet named Budget so it no longer appears as a tab, but keep the data intact for later use. What should you do?

Accepted Answer

Right-clicking a sheet tab and choosing Hide removes it from view without deleting any data. The sheet can later be restored via right-click > Unhide. Ctrl+H opens Find & Replace, not sheet hiding.

Question 2

You are working in Microsoft Excel. Your worksheet spans 10 printed pages, and you need row 1 (the column headers) to appear at the top of every printed page. What should you do?

Accepted Answer

Page Layout > Print Titles opens the Page Setup dialog where you can specify 'Rows to repeat at top', so the header row automatically prints on every page. Freeze Panes only affects on-screen scrolling, not printed output.

Question 3

You are working in Microsoft Excel. You need to select several worksheet tabs together so you can enter data on all of them simultaneously (Group mode). Which two methods let you select multiple tabs? Choose 2.

Accepted Answer

Ctrl+click adds individual non-adjacent tabs to the selection, while Shift+click selects a contiguous range of tabs. Both place the workbook in Group mode, where edits apply to every selected sheet at once.

Question 4

You are working in Microsoft Excel. You want to add a Title and Author to a workbook's metadata so it can be found easily by colleagues. Where should you do this?

Accepted Answer

File > Info displays the Properties panel where you can add or edit Title, Author, Tags, and other document metadata. This information helps with document search and organization.

Question 5

A user prints a large worksheet and finds that page breaks split a table awkwardly in the middle of related rows. They want to see and manually adjust exactly where each page break occurs before printing. Which view should they use?

Accepted Answer

Page Break Preview (View > Page Break Preview) displays dashed and solid blue lines representing page breaks, which the user can drag to reposition. This gives direct manual control over where pages split, unlike Normal or Page Layout view.

Question 6

You are working in Microsoft Excel. You have formatted cell A1 with bold text, a blue fill, and a border. You want to apply the exact same formatting to cell D1 without redoing each step. What tool should you use?

Accepted Answer

Format Painter (Home tab, paintbrush icon) copies all formatting attributes from one cell and applies them to another with a single click, making it the fastest way to duplicate formatting exactly.

Question 7

You are working in Microsoft Excel. Column B contains formulas that calculate commission amounts. You want to paste only the calculated results (not the formulas) into column D so the values remain fixed even if column B changes later. What should you do?

Accepted Answer

Paste Special > Values pastes only the resulting calculated numbers, stripping out the underlying formulas. A normal paste would carry the formulas over, which would still reference column B and change if it is edited.

Question 8

You are working in Microsoft Excel. You have a list of 500 customer records with several exact duplicate rows. Which two statements about removing duplicates are correct? Choose 2.

Accepted Answer

The Remove Duplicates dialog lets you select which columns to compare, and it permanently deletes matching rows, so backing up data first is wise. It works on both plain ranges and tables, doesn't require pre-sorting, and doesn't create a separate sheet.

Question 9

You are working in Microsoft Excel. Column A contains full names like 'John Smith', and you want column B to automatically show just the first name 'John' for every row, based on a pattern you demonstrate in the first cell. Which feature should you use?

Accepted Answer

Flash Fill recognizes a pattern from an example you type (such as extracting the first name) and automatically fills the rest of the column to match once you start typing the second entry or press Ctrl+E.

Question 10

A financial analyst wants large revenue numbers like 1,250,000 to display as '1,250k' in a cell, while the underlying value remains unchanged for calculations. Which approach should they use?

Accepted Answer

A custom number format like #,##0,"k" uses a trailing comma to scale the displayed value by one thousand and appends the literal text 'k', changing only the display while the stored numeric value remains 1,250,000 for accurate calculations.

Question 11

You are working in Microsoft Excel. You want every other row of your Excel table to have a light gray fill to make it easier to read across rows. Which table option should you enable?

Accepted Answer

The Banded Rows checkbox (Table Design > Table Style Options) alternates shading between rows automatically, improving readability without manual formatting.

Question 12

You are working in Microsoft Excel. Your cursor is in the last cell of the last row of an Excel table, and you need to add one more row of data below it. What is the quickest way to do this?

Accepted Answer

Pressing Tab while in the last cell of a table's last row automatically inserts a new row and moves the cursor to its first cell, extending the table range in one step.

Question 13

You are working in Microsoft Excel. Your table is currently named 'Table1' and you want to rename it to 'SalesData' so formulas are easier to read. Which two locations let you rename the table? Choose 2.

Accepted Answer

The Table Name box on the Table Design tab lets you type a new name directly. The Name Manager also lists all named tables and ranges, and you can edit the name from there. The Quick Access Toolbar, status bar, and Page Setup dialog have no renaming function.

Question 14

You are working in Microsoft Excel. You no longer need the filtering and banding features of an Excel table, but you want to keep the data and its formatting as a normal range. What should you do?

Accepted Answer

Table Design > Convert to Range removes the table functionality (filter buttons, structured references, banding rules tied to the table object) while preserving the data and its visual formatting as a plain range.

Question 15

A regional manager has an Excel table with Region and Sales columns. They want to sort the table so records are grouped by Region alphabetically, and within each Region, Sales are sorted highest to lowest. What is the correct approach?

Accepted Answer

The Sort dialog supports multiple sort levels via Add Level. Setting the first level to Region (A to Z) and the second level to Sales (Largest to Smallest) sorts records by Region alphabetically, then by Sales descending within each Region group.

Question 16

You are working in Microsoft Excel. You have a formula in B2 that references A1, and you want to copy the formula down to B10 while keeping the reference to A1 fixed (not changing to A2, A3, etc.). How should you write the reference to A1?

Accepted Answer

Adding dollar signs before both the column letter and row number ($A$1) creates an absolute reference, which stays fixed when the formula is copied to other cells. A plain A1 is a relative reference that would adjust automatically.

Question 17

You are working in Microsoft Excel. Range A1:A20 contains a mix of numbers and text entries, including some blank cells. You want to count how many cells are non-empty, regardless of whether they contain text or numbers. Which function should you use?

Accepted Answer

=COUNTA counts all non-empty cells regardless of data type (text, numbers, dates). =COUNT only counts numeric cells, ignoring text entries, so it would undercount in this scenario.

Question 18

You are working in Microsoft Excel. You need a formula that assigns a letter grade based on a score in A1: 'A' for 90 or above, 'B' for 80-89, and 'C' for below 80. Which two formulas correctly produce this result? Choose 2.

Accepted Answer

A nested IF correctly checks A1>=90 first, then A1>=80, defaulting to C. IFS works similarly with condition/value pairs and a catch-all TRUE case. Option C uses invalid syntax with semicolons, and D's SWITCH would only match exact scores rather than ranges; E is not valid VLOOKUP syntax.

Question 19

You are working in Microsoft Excel. Cell A1 contains 'Maria' and cell B1 contains 'Lopez'. You want cell C1 to display 'Maria Lopez' with a single space between them, and you want the formula to ignore any cells in the join that happen to be empty. Which function is best suited?

Accepted Answer

=TEXTJOIN(delimiter, ignore_empty, text1, text2, ...) lets you specify a space as the delimiter and set ignore_empty to TRUE so blank arguments are skipped automatically. CONCATENATE and the & operator would require you to manually insert the space and don't skip blanks.

Question 20

You are working in Microsoft Excel. You have a table of expenses with columns Category (B) and Amount (C). You need the average Amount for only the rows where Category is 'Travel'. Which formula should you use?

Accepted Answer

=AVERAGEIF(criteria_range, criteria, average_range) evaluates the criteria range for matches and averages the corresponding values in the average range — exactly the tool for a single-condition conditional average. Option D would produce an incorrect result because it divides by the count of all rows, not just Travel rows.

Question 21

You are working in Microsoft Excel. You want to add a small line chart inside a single cell next to each row of monthly figures, to show the trend at a glance without inserting a full chart object. What feature should you use?

Accepted Answer

Sparklines (Insert > Sparklines > Line) are tiny charts that fit inside a single cell, ideal for showing a quick trend for each row of data without taking up the space of a full chart object.

Question 22

You are working in Microsoft Excel. You have created a chart but want to quickly switch its overall layout so the legend, axis titles, and data labels arrange into one of several pre-built configurations. What should you use?

Accepted Answer

Quick Layout (Chart Design tab) offers a gallery of pre-built layout combinations for legend placement, titles, and labels, letting you restyle the whole chart's structure in one click instead of adjusting each element manually.

Question 23

You are working in Microsoft Excel. You want a single chart that shows monthly Revenue as columns and Growth Rate (%) as a line on the same plot area, since they use very different scales. What chart type should you insert?

Accepted Answer

Insert > Charts > Combo Chart lets you plot one data series as columns and another as a line, and easily assign the line series to a secondary axis, making it ideal for comparing two measures with very different scales.

Question 24

You are working in Microsoft Excel. Your chart currently plots four product categories, but you want to temporarily hide one category from the chart without deleting its data from the worksheet. Which two statements about the Chart Filters feature are correct? Choose 2.

Accepted Answer

The funnel-shaped Chart Filters icon appears next to a selected chart and lets you check or uncheck series and categories to control what displays, without altering or deleting the source data. It works on any chart type, not just pie charts, and is not part of Format Cells.

Question 25

You are working in Microsoft Excel. You have a line chart of monthly sales and want to add a line showing the overall linear trend direction across the months. What should you do?

Accepted Answer

Clicking Chart Elements (+) on a selected chart and enabling Trendline (choosing Linear) adds a calculated trend line to the data series, showing the overall direction. There is no built-in 'Trend chart' type, and Shapes or Conditional Formatting do not perform trend calculations.

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