Microsoft Office Specialist: Excel Associate (Office 2019) — Practice Set 1

Question 1

You receive a plain text file (.txt) from a vendor that contains tab-delimited sales data. You need to bring this data into a new Excel workbook. Which action should you take?

Accepted Answer

The correct approach is to use Excel's Data tab import tools (Get Data / From Text/CSV), which launches the Text Import Wizard-style experience and lets you control delimiters and column data types before the data lands in the worksheet. Opening the .txt file directly (A) launches it as a separate, unformatted file rather than importing it into your existing workbook. Pasting through Word (C) is unnecessary and can corrupt delimiters and formatting. Simply renaming the extension (D) does not convert the file format and will corrupt or fail to open the file.

Question 2

A workbook contains a named range called 'Q3_Sales' on a worksheet named 'Regional'. You are currently on a different worksheet and need to jump directly to that named range. What is the fastest way to do this?

Accepted Answer

Typing a defined name into the Name Box (to the left of the Formula Bar) and pressing Enter immediately navigates to that named cell or range, even across worksheets. Manually scrolling (A) is slow and error-prone. Find & Replace (C) searches cell content/values, not defined names, so it would not locate the range unless the literal text 'Q3_Sales' appears in a cell. Renaming a sheet tab (D) has nothing to do with navigating to a named range.

Question 3

You are preparing a large budget workbook for printing. Rows 1-2 contain company branding and column A contains account labels. You want these to remain visible on screen as you scroll through hundreds of rows and many columns of monthly data. Which feature should you use?

Accepted Answer

Freeze Panes locks specified rows and/or columns in place so they remain visible on screen while you scroll through the rest of the worksheet, which is exactly what's needed here. Split (B) divides the window into separate scrollable panes but does not lock content in the same way and both panes still scroll independently, which is a different use case. Setting a print area (C) only controls what prints, not what is visible on screen while working. Hiding rows and columns (D) removes them from view entirely rather than keeping them visible.

Question 4

You have copied a cell that contains a formula with formatting you do not want to bring along, only the calculated result. Which Paste option should you use?

Accepted Answer

Paste Special > Values pastes only the calculated result of a formula, stripping out both the formula itself and any source formatting. Paste Special > Formulas (B) would bring the formula along, which is not desired here. Paste Special > Formats (C) pastes only formatting, not values, which is the opposite of what's needed. Regular Paste (D) brings the formula, value, and formatting all together.

Question 5

Cell A1 contains 'Monday'. You want to quickly fill cells A2 through A7 with 'Tuesday' through 'Sunday' without typing each one. What is the most efficient technique?

Accepted Answer

Excel recognizes built-in lists such as days of the week and months, so dragging the fill handle from a cell containing 'Monday' automatically continues the sequence through the rest of the week. Typing manually (A) works but is inefficient. Copying and pasting (C) would just repeat 'Monday' in every cell rather than incrementing the day. Find & Replace (D) is used to substitute existing text, not to generate a new sequence.

Question 6

You want to combine four cells (B2:C3) into a single larger cell to serve as a report title, and also ensure long text wraps onto multiple lines within a cell instead of overflowing. Which two actions accomplish this? (Select two.)

Accepted Answer

Merge & Center (A) combines the selected cells into one and centers the content, which is appropriate for a report title spanning several columns. Wrap Text (B) forces long text to wrap onto multiple lines within the cell's boundaries instead of overflowing or being cut off. Format Painter (C) only copies formatting from another cell and would not merge cells or enable wrapping unless the source cell already had those properties, making it an indirect and unreliable choice here. A Currency format (D) affects how numbers display and has nothing to do with merging or wrapping text.

Question 7

You frequently refer to the range D2:D50 in formulas across the workbook by typing 'SUM(D2:D50)' each time. You want to instead reference this range using a meaningful name such as 'MonthlyExpenses' to make formulas easier to read and maintain. What should you do first?

Accepted Answer

Selecting the range and typing a name into the Name Box (or using Formulas > Define Name) creates a named range that can then be used in formulas like =SUM(MonthlyExpenses) instead of the cell reference. Renaming the worksheet tab (B) changes the sheet's name, not a range reference. Cell styles (C) control visual formatting only and are unrelated to naming ranges. A text box label (D) is purely visual annotation and has no functional link to the range for use in formulas.

Question 8

You want to quickly visualize the sales trend for each product row directly inside the cell next to that row's monthly figures, without inserting a full chart object that would take up a large amount of worksheet space. What feature should you use?

Accepted Answer

Sparklines are small, in-cell charts designed to show a data trend within the space of a single cell, which is exactly the compact visualization requested. A full-size Line chart (B) is a separate floating object that takes up significant worksheet space, contradicting the requirement. Conditional formatting color scales (C) shade cells based on value but do not show a trend line across multiple data points. A PivotChart (D) is a large chart object tied to a PivotTable and is not designed to fit inside a single cell.

Question 9

You applied a conditional formatting rule that highlights cells in a range red when their value is below a target, but the rule is now causing confusion because it overlaps with a second rule you applied afterward for the same range. You want to remove only the conditional formatting rules from this range while keeping the cells' regular formatting (fill color, font) untouched. What should you do?

Accepted Answer

Conditional Formatting > Clear Rules > Clear Rules from Selected Cells removes only the conditional formatting rules while leaving the cells' manually applied formatting (like fill color and font) and their values intact. Clear > Clear Formats (B) removes all formatting, both manual and conditional, which goes further than required. Pressing Delete (C) only clears cell contents/values, not formatting or conditional rules. Clear > Clear All (D) removes contents, formatting, and comments entirely, which is far more than the task calls for.

Question 10

You have a range of cell data with column headers (Name, Region, Sales) and want to convert it into a structured Excel table so you can take advantage of automatic filtering, banded rows, and structured references. What should you do?

Accepted Answer

Insert > Table converts a cell range into a structured Excel table, automatically adding filter arrows, banded row formatting, and enabling structured (table-based) references in formulas. Applying a Cell Style to just the header row (B) changes visual appearance only and does not create a functional table object. Merge & Center (C) combines cells and is unrelated to creating a table. Manually adding borders (D) is purely cosmetic and provides none of a table's functional benefits like filtering or the Total Row.

Question 11

You have an Excel table named 'SalesTable' and want to display a Sum for the 'Revenue' column automatically at the bottom of the table, with the option to switch it to Average or Count later. What should you enable?

Accepted Answer

Enabling the Total Row from the Table Design tab adds a row at the bottom of the table with a dropdown per column, letting you switch between Sum, Average, Count, and other aggregate functions without rewriting formulas. Manually typing a SUM formula below the table (B) works but does not move with the table if rows are added/removed, and doesn't offer the convenient dropdown to switch functions. AutoSum on the whole worksheet (C) is not table-aware and does not create the dynamic, per-column dropdown behavior. Freeze Panes (D) only affects on-screen scrolling visibility, not calculations.

Question 12

A table lists employees with columns for Department and Hire Date. You need to view only employees hired after January 1, 2023, in the Marketing department. What is the most direct way to do this within the table?

Accepted Answer

Using the built-in filter arrows on each relevant column header lets you apply multiple criteria simultaneously (Department equals Marketing, and Hire Date after a certain date), showing only matching rows while keeping all data intact and reversible. Manually deleting rows (B) is destructive and irreversible, permanently losing the excluded data. Sorting by Department only (C) reorders rows but does not filter out non-Marketing rows or apply the date criteria. Conditional formatting (D) visually highlights matching rows but does not hide the non-matching ones, so it does not fulfill the requirement to 'view only' those employees.

Question 13

You need to sort a table of orders first by Region (A to Z), and within each region, by Order Total from highest to lowest. Which approach correctly achieves this?

Accepted Answer

The Sort dialog box supports multiple sort levels; setting Region as the primary (first) level and Order Total as a secondary ('Then By') level correctly sorts by region first and then by order total within each region group, all in one operation. Sorting by Region alone (B) does not automatically apply a secondary numeric sort — Order Total order within each region would remain unsorted or retain its prior order. Sorting by Order Total only and manually rearranging (C) is inefficient, error-prone, and not how Excel's built-in multi-level sort should be used. Filtering per region and sorting each view separately (D) is a needlessly manual, repetitive workaround when a single multi-level sort accomplishes the same result directly.

Question 14

You have a formula in cell C2 that is =A2*B1. You want to copy this formula down through C10 so that column A's reference changes with each row, but B1 always stays fixed since it holds a single tax rate value. How should the formula be written before copying?

Accepted Answer

Using =A2*$B$1 makes A2 a relative reference (so it changes to A3, A4, and so on as the formula is copied down) while $B$1 is an absolute reference (locked with dollar signs on both the column and row) so it always points to B1 regardless of where the formula is copied. Option B locks A2 instead, which is backwards for this scenario since A2 needs to change per row. Option C with no locking would cause B1 to shift to B2, B3, etc. as it's copied, producing incorrect calculations. Option D mixes the locking incorrectly, keeping the row of A fixed and the column of B relative, which does not match the requirement.

Question 15

You need to calculate the average of the values in cells B2 through B20, ignoring any blank cells. Which formula should you use?

Accepted Answer

The AVERAGE() function automatically calculates the mean of the numeric values in the range while ignoring blank cells, which is exactly what's needed. Dividing the SUM by a fixed count of 20 (B) would produce an incorrect average if any cells are blank, since it always divides by 20 regardless of how many cells actually contain data. COUNT() (C) only returns how many cells contain numeric values, not an average. MAX() (D) returns the single largest value in the range, not the mean.

Question 16

Column D contains customer feedback scores, and some cells are intentionally left blank because no feedback was given. You need a formula that counts only the cells that are empty in D2:D100. Which function should you use?

Accepted Answer

COUNTBLANK() specifically counts the number of empty cells within a range, which directly answers the question. COUNT() (B) counts only cells containing numeric values, not blanks. COUNTA() (C) counts non-blank cells, which is the opposite of what's requested. SUM() (D) adds up numeric values and has nothing to do with counting blank cells.

Question 17

You want a formula in cell C2 that displays 'Pass' if the value in B2 is 70 or greater, and 'Fail' otherwise. Which formula is correct?

Accepted Answer

The IF() function follows the syntax =IF(logical_test, value_if_true, value_if_false), so =IF(B2>=70,"Pass","Fail") correctly tests whether B2 is 70 or greater and returns the appropriate text. Option B has the arguments in the wrong order and includes an extra, invalid fourth argument. Option C misuses COUNTIF(), which is meant to count cells matching a single criterion within a range, not to perform conditional branching with two possible text outputs. Option D reverses the logical test and results, placing text values where the condition belongs, which does not match IF()'s required syntax.

Question 18

Cell A2 contains the product code 'EX-2024-0091'. You need a formula that extracts just the last 4 characters ('0091') into another cell. Which function should you use?

Accepted Answer

RIGHT(text, num_chars) extracts a specified number of characters starting from the rightmost end of a text string, so RIGHT(A2,4) correctly returns the last four characters, '0091'. LEFT(A2,4) (B) would return the first four characters from the left ('EX-2'), not the end of the string. MID(A2,1,4) (C) also extracts from the beginning (starting at position 1), returning 'EX-2', not the last four characters. LEN() (D) returns the total number of characters in a string as a single number and does not accept a second argument to extract a substring, so this formula is invalid for this purpose.

Question 19

You have first names in column A and last names in column B, and you want to combine them into a single cell as 'LastName, FirstName' with a comma and space between them, without using the older CONCATENATE() function. Which formula correctly achieves this?

Accepted Answer

CONCAT(B2,", ",A2) joins the last name, then a literal comma and space, then the first name, producing exactly 'LastName, FirstName' as required. Option B joins the first and last names directly with no separator or comma at all, producing something like 'JohnSmith' instead. Option C misuses TEXTJOIN(), whose correct syntax requires a delimiter as the first argument and an ignore_empty argument, e.g. =TEXTJOIN(", ",TRUE,B2,A2) — using it as written with only two cell arguments and no delimiter is invalid. Option D places the comma and space at the end of the string rather than between the last and first names, producing 'SmithJohn, ' instead of the required format.

Question 20

You have a small table of quarterly revenue by product and want to visually compare the values with a bar-style chart embedded directly on the current worksheet next to the data. What is the most direct way to do this?

Accepted Answer

Selecting the data range and choosing a chart type (such as Clustered Column) from the Insert tab creates an embedded chart object directly on the current worksheet, right next to the source data, which matches the requirement. Pressing F11 (B) creates the chart on a brand-new dedicated chart sheet rather than embedding it alongside the data on the current worksheet. Conditional formatting data bars (C) shows bar-like shading within the cells themselves, but it's not a true chart object and offers far less flexibility for comparison and labeling. Manually drawing shapes (D) is not an accurate, data-driven representation and would not automatically update if the underlying figures change.

Question 21

You created a chart, but the current arrangement shows each product as a separate data series (one line per product) when you actually want each month to instead be shown as a series so that you can compare products within a month more easily. What should you do?

Accepted Answer

Switch Row/Column, found on the Chart Design tab, instantly swaps which dimension (rows or columns of the source data) is plotted as the data series versus the category axis, without needing to touch the underlying worksheet data. Deleting and manually re-entering the data (B) is unnecessary, time-consuming, and risks introducing data entry errors. Changing the chart type to Pie (C) fundamentally changes the visualization type and does not address which dimension is used for series versus categories, and pie charts can't even show multiple series this way. Applying a different chart style (D) only changes visual formatting like colors, not the underlying series/category arrangement.

Question 22

After creating a column chart, you decide you want the data labels displayed above each column and want to reposition the legend to the bottom of the chart, without recreating the chart from scratch. What is the best way to do this?

Accepted Answer

The Chart Elements (+) button (or the Add Chart Element menu on the Chart Design tab) lets you toggle and reposition individual chart components such as Data Labels and the Legend directly on the existing chart, without rebuilding it. Deleting and recreating the chart (B) is unnecessary extra work when the existing chart can simply be modified. Manually inserting text boxes (C) is a static workaround that will not update automatically if the underlying data changes, unlike true data labels. Switching to a Pie chart (D) changes the entire chart type and visualization, which is a much bigger and unwanted change just to reposition a legend and show labels on a column chart.

Question 23

Your organization requires all charts included in reports to be accessible to users relying on screen readers. What should you add to each chart to meet this requirement?

Accepted Answer

Adding Alternative Text (Alt Text) to a chart provides a text description that screen readers can announce to users with visual impairments, directly supporting accessibility. A brighter color scheme (B) may help some users with visual distinctions but does not provide any information to a screen reader, which cannot interpret color. A larger title font (C) may improve visual readability slightly but still provides nothing to a screen reader and does not describe the chart's data or purpose. A drop shadow effect (D) is a purely decorative visual enhancement with no accessibility benefit at all.

Question 24

You want to quickly change the overall visual appearance of a chart, including its colors and effects, by choosing from a gallery of predefined designs, rather than manually formatting each element individually. What should you use?

Accepted Answer

The Chart Styles gallery on the Chart Design tab offers a set of predefined visual designs (colors, effects, and formatting combinations) that can be applied to an entire chart with a single click. Cell Styles (B) apply formatting to worksheet cells, not chart objects. Conditional Formatting (C) applies formatting rules to cell values based on conditions and has no effect on charts. The Format Painter (D) copies formatting from one object or cell to another but does not provide access to a gallery of predefined chart designs.

Question 25

You built a chart summarizing regional sales, and later your manager asks for the exact same chart to be shown as its own dedicated sheet in the workbook (rather than embedded on the data worksheet) so it can be easily located in the sheet tabs and printed on its own page. What should you do?

Accepted Answer

Selecting the chart and using Chart Design > Move Chart > New Sheet moves the existing chart to its own dedicated chart sheet (given its own tab), while keeping it fully linked to the source data for automatic updates. Pasting the chart as a picture (B) creates a static image that will no longer update if the underlying data changes, defeating the purpose of a live chart. Printing to PDF and inserting as an object (C) is a convoluted workaround that also produces a static, non-interactive result rather than a proper chart sheet. Renaming the worksheet tab (D) does not move the chart to its own sheet; it only changes the name of the existing worksheet the chart is embedded on.

MO-200 — Microsoft Office Specialist: Excel Associate (Office 2019)