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

Question 1

You are working in Microsoft Excel. You need to duplicate the entire 'Template' worksheet, including its formatting and formulas, into the same workbook so you can customize the copy for a new client. What is the fastest way?

Accepted Answer

Right-clicking a tab and choosing Move or Copy, then checking 'Create a copy' before clicking OK duplicates the entire sheet — including formatting, formulas, and structure — in one step, faster and more reliable than copy-pasting all cells manually.

Question 2

You are working in Microsoft Excel. Your worksheet is too wide to fit on one printed page in Portrait orientation, but you don't want to change the paper size. What is the best first step to try to fit it on a single page?

Accepted Answer

Switching to Landscape orientation provides more horizontal page width, and combining it with Page Layout > Scale to Fit (Width: 1 page) shrinks the content proportionally to fit on a single printed page without deleting any data.

Question 3

You are working in Microsoft Excel. You use tab colors to organize a workbook with 20 sheets: green for 'complete', yellow for 'in progress', and no color for 'not started'. Which two statements about tab colors are correct? Choose 2.

Accepted Answer

Tab color is a purely cosmetic organizational aid with no effect on data, calculations, or sorting, and it can be changed at any time via right-click > Tab Color. It doesn't lock renaming, doesn't update automatically based on values, and has no relationship to gridlines.

Question 4

You are working in Microsoft Excel. You've built a standard invoice layout and want to reuse it repeatedly for new invoices without risking accidental changes to the original layout file. What should you do when saving it?

Accepted Answer

Saving a workbook as an Excel Template (.xltx) means that double-clicking it in File Explorer opens a new, unsaved copy based on the template, protecting the original layout from being overwritten — unlike a regular .xlsx file, which opens and saves as itself by default.

Question 5

Two team members are comparing the 'Actuals' sheet and the 'Forecast' sheet from the same workbook and want to scroll through both simultaneously so that when one scrolls down, the other view moves in sync for easy row-by-row comparison. What Excel feature enables this?

Accepted Answer

Opening a second window with View > New Window, then arranging both with View Side by Side, enables Synchronous Scrolling by default so that scrolling in one window moves the other window's view identically, making row-by-row comparison of two sheets (or workbooks) straightforward.

Question 6

You are working in Microsoft Excel. You typed a note in cell B5 but decide it is no longer needed. You want to remove only the text from the cell while keeping its yellow background fill and border formatting intact. What should you do?

Accepted Answer

Clear Contents (or the Delete key) removes only the cell's value/text while leaving all formatting — fill color, borders, fonts — untouched. Clear All would remove both content and formatting, and Clear Formats would strip formatting while keeping the text, the opposite of what is needed.

Question 7

You are working in Microsoft Excel. You want a data entry cell restricted to a drop-down list of department names, and if the list changes in the future you want the drop-down to update automatically without editing the validation rule again. What should the Source reference?

Accepted Answer

Referencing a named range in Data Validation's List Source means that if the underlying list of values changes (items added or removed within the named range), the drop-down automatically reflects those changes, unlike a hard-coded comma-separated list which must be manually edited.

Question 8

You are working in Microsoft Excel. You want to visually flag inventory levels using small icons: a green circle for high stock, yellow triangle for medium, and red diamond for low stock, directly inside the cells. Which two statements about Icon Sets are correct? Choose 2.

Accepted Answer

Icon Sets are one of the Conditional Formatting options, and their default thresholds can be customized through Manage Rules > Edit Rule to fit specific value ranges. The underlying numeric value remains fully usable in formulas — only the display gains an icon. Icon Sets work on any range (table or not), and several icon sets offer four or five icons, not only three.

Question 9

You are working in Microsoft Excel. You want to attach a small explanatory note to cell C10 that appears when a user hovers over the cell, without altering the cell's actual displayed value. What should you insert?

Accepted Answer

Inserting a Comment or Note (Review tab) attaches hover-triggered explanatory text to a cell without changing its displayed value or interfering with formulas that reference it.

Question 10

A user has a column of prices in B2:B20 and wants to increase every value by 10% in place, without writing a formula in another column. What Paste Special technique can achieve this?

Accepted Answer

Paste Special offers mathematical operations (Add, Subtract, Multiply, Divide) that apply to the destination range using the copied value. Copying 1.1 and using Paste Special > Multiply on B2:B20 multiplies every existing price by 1.1 in place, effectively increasing each by 10% without extra formulas.

Question 11

You are working in Microsoft Excel. You want your Excel table's alternating column shading to run vertically (banded columns) instead of the default horizontal row banding. What should you do?

Accepted Answer

The Table Style Options group on the Table Design tab includes both Banded Rows and Banded Columns checkboxes, which can be toggled independently to shade alternating rows, alternating columns, or both.

Question 12

You are working in Microsoft Excel. You want to filter your table's Product Name column to show only rows where the name contains the word 'Pro' anywhere in the text. What is the correct approach?

Accepted Answer

The column filter drop-down's Text Filters submenu includes a 'Contains' option, letting you type 'Pro' to display only rows where that text appears anywhere within the Product Name field, regardless of position.

Question 13

You are working in Microsoft Excel. Your table's Total Row currently shows the Sum for the Quantity column, but you want it to instead show the Count of Numbers for that column. Which two statements about the Total Row are correct? Choose 2.

Accepted Answer

Each cell in the Total Row has its own drop-down list of summary functions, so different columns can use different aggregations simultaneously (e.g., Sum for one column, Count Numbers for another). The Total Row is toggled on/off via the Table Style Options checkbox on Table Design. It does not require a PivotTable and can be changed anytime.

Question 14

You are working in Microsoft Excel. Your organization has a specific set of brand colors, and none of the built-in Table Styles match them. What should you do to apply your own custom color scheme as a reusable table style?

Accepted Answer

The Table Styles gallery includes a 'New Table Style' option that opens a dialog for defining custom formatting (colors, borders, fonts) for each table element (header row, banded rows, total row, etc.), creating a reusable custom style you can apply to any table.

Question 15

A user has a table with a Quantity column and wants to know how many rows actually contain a numeric quantity (ignoring any blank or text entries that may have been mistakenly entered). Which Total Row function should they select for that column?

Accepted Answer

The Total Row's 'Count Numbers' function counts only cells containing numeric values, excluding blanks and text entries — precisely what's needed to verify how many rows have valid numeric quantities. 'Count' in the Total Row dropdown for a numeric column behaves the same as Count Numbers, but 'Count Numbers' is the explicitly correct, unambiguous choice offered for this purpose.

Question 16

You are working in Microsoft Excel. You need a formula in D2 that multiplies the Quantity in B2 by a tax rate stored in a fixed cell F1, and you plan to copy the formula down to D3:D20. Which formula is correctly written for copying down?

Accepted Answer

=B2*$F$1 uses a relative reference for B2 (which should change per row) and an absolute reference for $F$1 (which should stay fixed on the tax rate cell as the formula is copied down). Without the dollar signs on F1, copying the formula down would incorrectly shift the tax rate reference to F2, F3, etc.

Question 17

You are working in Microsoft Excel. You need a formula that assigns a shipping cost tier based on weight in A1: 'Small' for under 5, 'Medium' for 5 to 20, and 'Large' for over 20. Which formula correctly implements this three-tier logic?

Accepted Answer

A nested IF first checks whether A1 is less than 5 for 'Small'; if not, it checks whether A1 is 20 or less for 'Medium'; otherwise it falls through to 'Large' for anything above 20. IF only accepts three arguments (test, true, false), so options with four plain arguments or invalid OR syntax inside IF are not valid.

Question 18

You are working in Microsoft Excel. You need to round a value in A1 (e.g., 12.378) for two different purposes: one formula should round it to 2 decimal places for display (12.38), and another should always round it up to the next whole number regardless of the decimal (13). Which two formulas correctly achieve this? Choose 2.

Accepted Answer

=ROUND(A1,2) rounds to 2 decimal places using standard rounding rules, giving 12.38. =ROUNDUP(A1,0) always rounds up to the nearest whole number regardless of the decimal portion, giving 13. TRUNC simply cuts off digits without rounding, ROUNDDOWN always rounds toward zero, and ROUND with 0.5 digits is not a valid use of the num_digits argument.

Question 19

You are working in Microsoft Excel. You have a list of 200 survey responses in column B, and some cells were left blank by respondents who skipped the question. You need a formula that counts how many responses were left blank. Which function should you use?

Accepted Answer

=COUNTBLANK counts the number of empty cells within a range, making it the direct function for counting skipped survey responses. COUNTA counts non-empty cells (the opposite), and COUNT only counts numeric cells.

Question 20

You are working in Microsoft Excel. Cell A1 contains a birth date, and you need a formula in B1 that calculates the person's current age in complete years based on today's date. Which formula is most appropriate?

Accepted Answer

=DATEDIF(A1,TODAY(),"Y") calculates the number of complete years between the birth date and today, correctly accounting for whether the birthday has occurred yet this year. Simply subtracting years (option C) would overcount by one if the birthday hasn't occurred yet this calendar year, and TODAY()-A1 returns total days elapsed, not years.

Question 21

You are working in Microsoft Excel. You have monthly expense categories and want a chart that clearly shows each category's proportion of the total monthly budget as slices of a circle. Which chart type should you choose?

Accepted Answer

A Pie chart divides a circle into slices representing each category's proportion of the whole total, making it the standard choice for visualizing part-to-whole relationships like expense categories within a total budget.

Question 22

You are working in Microsoft Excel. Your chart's vertical axis shows numeric values, but it isn't clear to viewers what those numbers represent (dollars, units, etc.). What should you add to clarify this?

Accepted Answer

Enabling Axis Titles through Chart Elements (+) adds editable text boxes next to each axis, where you can type a label such as 'Revenue ($)' on the vertical axis to clarify what the numeric values represent.

Question 23

You are working in Microsoft Excel. You want viewers to be able to see the exact underlying numbers directly beneath the chart's plot area, in a grid format, instead of having to refer back to the worksheet cells. What should you add?

Accepted Answer

Enabling Data Table under Chart Elements (+) displays a small grid of the exact source values directly beneath the chart's plot area, letting viewers see precise numbers alongside the visual chart without switching back to the worksheet.

Question 24

You are working in Microsoft Excel. You want to decide between a Bar chart and a Column chart for displaying rankings of ten long product names against their sales totals. Which two statements correctly describe the difference? Choose 2.

Accepted Answer

Bar charts orient categories vertically along the left axis with horizontal bars extending rightward, which accommodates long text labels (like full product names) more comfortably. Column charts orient categories horizontally with vertical bars, better suited for shorter labels or time-based data. They are not identical, and neither is limited by category count as described in C, D, or E.

Question 25

You are working in Microsoft Excel. You need to insert a chart into a PowerPoint presentation such that it displays a static snapshot of current Excel data and will never change even if the source workbook is later edited. What paste option should you use in PowerPoint?

Accepted Answer

Pasting a chart as a Picture creates a completely static image with no connection to the source Excel data, so it will never update — exactly what is needed for a snapshot that must remain fixed regardless of future changes. The other options explicitly maintain a live link back to the source workbook.

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