Microsoft Office Specialist: Excel Expert (Microsoft 365 Apps) — Practice Set 1

Question 1

You are working in Microsoft Excel. You need to configure the workbook so that it automatically calculates formulas only when you press F9, instead of recalculating after every change. What setting should you change?

Accepted Answer

Setting Calculation Options to Manual (Formulas > Calculation Options > Manual) prevents Excel from recalculating formulas automatically. Pressing F9 triggers a manual recalculation. This is useful for large workbooks where automatic recalculation slows performance.

Question 2

You are working in Microsoft Excel. You want to make a custom function available in all new workbooks you create. What should you do?

Accepted Answer

PERSONAL.XLSB is a hidden workbook that opens automatically with Excel and is available globally. Storing macros and functions there makes them accessible in all workbooks. While Add-Ins and XLSTART also work, PERSONAL.XLSB is the standard approach for personal macros.

Question 3

You are working in Microsoft Excel. You need to encrypt a workbook with a password so that it cannot be opened without the correct password. Where is this option found?

Accepted Answer

File > Info > Protect Workbook > Encrypt with Password sets an open password that encrypts the entire workbook file using AES-256 encryption. Option D (Save As > Tools > General Options) also works but is less accessible. Option A protects workbook structure, not the file itself.

Question 4

You are working in Microsoft Excel. You have a column of full names formatted as 'First Last' and you want to split them into two separate columns. Which feature should you use?

Accepted Answer

Data > Text to Columns lets you split a column of text into multiple columns using a delimiter (such as a space) or fixed width. For 'First Last' names split by a space, the Delimited option with Space as the delimiter will produce separate First and Last columns.

Question 5

You are working in Microsoft Excel. You have applied a filter to a dataset but you want to see only the unique values from a specific column across all records. Which feature provides the most efficient way to extract a unique list?

Accepted Answer

Advanced Filter (Data > Advanced) with 'Copy to another location' and 'Unique records only' checked extracts a de-duplicated list to a specified location. This is the built-in Excel method to get a unique values list without formulas or Power Query.

Question 6

You are working in Microsoft Excel. You want to create a conditional formatting rule that highlights any cell in the range A1:A100 where the value is in the top 10% of all values in that range. Which conditional formatting option should you choose?

Accepted Answer

Home > Conditional Formatting > Top/Bottom Rules > Top 10% automatically highlights cells whose values fall in the top 10 percent of all values in the range. 'Top 10 Items' highlights the top 10 individual items by count, not percentage.

Question 7

A financial analyst is working in Microsoft Excel. She has imported data that contains many duplicate transaction rows. She needs to remove all duplicate rows based on the combination of two columns: TransactionID and Date. What should she do?

Accepted Answer

Data > Remove Duplicates opens a dialog where you can select which columns to consider when identifying duplicate rows. Selecting both TransactionID and Date ensures that only rows where both values are identical are treated as duplicates and removed.

Question 8

You are working in Microsoft Excel. You need to import external data from a text file and refresh it automatically each time the workbook opens. Which two steps are required to achieve this? Choose 2.

Accepted Answer

Importing via Data > Get Data > From Text/CSV creates a Power Query connection rather than static data. In Query Properties (right-click the query > Properties), you can check 'Refresh data when opening the file', which triggers an automatic refresh every time the workbook is opened.

Question 9

You are working in Microsoft Excel. You have a large dataset and want to group rows by region and show subtotals for sales automatically. Which feature should you use?

Accepted Answer

Data > Subtotal inserts automatic subtotal rows at each group change. After sorting by Region, applying Subtotal with the SUM function on the Sales column will insert a subtotal row after each region group and a grand total at the bottom.

Question 10

You are working in Microsoft Excel. You need a formula that returns the sum of sales in column C only for rows where column A equals 'East' AND column B is greater than 500. Which formula is correct?

Accepted Answer

SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2) sums values meeting multiple conditions. =SUMIFS(C:C, A:A, "East", B:B, ">500") sums column C where column A is 'East' AND column B exceeds 500. SUMIF only supports one condition.

Question 11

You are working in Microsoft Excel. You need to look up a value in a two-dimensional table where the row is determined by a product name and the column by a month name. Which combination of functions is most appropriate?

Accepted Answer

=INDEX(array, MATCH(row_value, row_range, 0), MATCH(col_value, col_range, 0)) performs a two-way lookup. INDEX returns the value at the intersection of the row and column positions identified by the two MATCH functions. This is more flexible than VLOOKUP, which requires fixed column numbers.

Question 12

You are working in Microsoft Excel. You want to create a macro that runs whenever the user changes a cell value in column A. Where should the VBA code be placed?

Accepted Answer

The Worksheet_Change(ByVal Target As Range) event procedure in the worksheet's code module fires automatically when any cell on that sheet changes. You can then check if Target.Column = 1 to restrict action to column A changes.

Question 13

You are working in Microsoft Excel. You recorded a macro but want to review and edit the VBA code it generated. How do you open the Visual Basic Editor?

Accepted Answer

The Visual Basic Editor (VBE) can be opened with the keyboard shortcut Alt+F11 or via the Developer tab > Visual Basic button. Both methods open the same VBE environment where you can view and edit recorded or manually written VBA code.

Question 14

A data analyst is working in Microsoft Excel. She needs a formula to calculate a running total in column B, where each row adds the current row's value from column A to the previous row's running total. She enters this in B2 and copies it down: =SUM($A$2:A2). What does this formula do?

Accepted Answer

=SUM($A$2:A2) uses a mixed reference: $A$2 is absolute (always row 2) and A2 is relative (changes as you copy). In B3, it becomes =SUM($A$2:A3); in B4, =SUM($A$2:A4), etc. This expanding range creates a cumulative running total from row 2 to the current row.

Question 15

You are working in Microsoft Excel. You need to work with text data and want to clean it up. Which two functions would you use to remove extra spaces from a cell and convert all text to proper case (first letter of each word capitalized)? Choose 2.

Accepted Answer

TRIM(text) removes leading, trailing, and extra internal spaces from a text string. PROPER(text) converts text to proper case (first letter of each word capitalized, rest lowercase). Combined as =PROPER(TRIM(A1)), they clean and capitalize the text.

Question 16

You are working in Microsoft Excel. You need to use the XLOOKUP function to find a product name in column A and return the corresponding value from column D. If not found, it should return 'Not Found'. Which formula is correct?

Accepted Answer

XLOOKUP has a built-in [if_not_found] optional parameter: =XLOOKUP(lookup_value, lookup_array, return_array, if_not_found). Option A uses this directly. Option C wraps in IFERROR for the same result. Both approaches are valid, making D correct.

Question 17

You are working in Microsoft Excel. You have a large sales dataset and want to create a summary that lets you pivot and rearrange data to analyze totals by region and product category. Which Excel feature should you use?

Accepted Answer

PivotTables are designed specifically for interactive data summarization. You can drag and drop fields to rows, columns, values, and filters to dynamically reorganize and aggregate data. They are the most powerful and flexible tool for summarizing large datasets in Excel.

Question 18

You are working in Microsoft Excel. You have a PivotTable and you want to display each region's sales as a percentage of the grand total rather than the raw value. What should you do?

Accepted Answer

Right-clicking a value cell in the PivotTable and choosing Show Values As > % of Grand Total converts all value cells to show their percentage of the overall grand total. This is a built-in PivotTable calculation option and requires no manual formulas.

Question 19

You are working in Microsoft Excel. You want to create a chart directly from a PivotTable that dynamically updates when the PivotTable data changes. What type of chart should you create?

Accepted Answer

A PivotChart is directly connected to its associated PivotTable. When the PivotTable is filtered, refreshed, or rearranged, the PivotChart updates automatically to reflect the changes. You can create one via PivotTable Analyze > PivotChart.

Question 20

A business analyst is working in Microsoft Excel. She has a PivotTable that shows total revenue by salesperson. She needs to add a calculation showing the revenue of each salesperson as a difference from the average revenue of all salespersons. What should she use?

Accepted Answer

In PivotTable > Fields, Items & Sets > Calculated Field, you can create a new field with a formula like =Revenue - AVERAGE(Revenue) to compute each salesperson's revenue deviation from the overall average. This is the correct PivotTable approach for this calculation.

Question 21

You are working in Microsoft Excel. You want to insert a Sparkline in cell E2 to show the trend of values in cells A2:D2. What type of Sparkline best shows a trend over time?

Accepted Answer

A Line Sparkline is best for showing trends over time as it draws a small line graph within a single cell. Column Sparklines show relative magnitude with bars; Win/Loss Sparklines show positive/negative results. There is no Pie Sparkline in Excel.

Question 22

You are working in Microsoft Excel. You have a PivotTable and need to group date fields by month and year. You right-click a date value in the PivotTable. What option should you choose?

Accepted Answer

Right-clicking a date field in a PivotTable and selecting Group (or 'Group Field') opens the Grouping dialog. You can select multiple grouping levels simultaneously — such as Months and Years — to create a hierarchical date grouping in the PivotTable.

Question 23

You are working in Microsoft Excel. You have a PivotTable and want to allow users to filter the data interactively without using the standard drop-down filters. Which two tools can you insert to provide interactive filtering? Choose 2.

Accepted Answer

Slicers (PivotTable Analyze > Insert Slicer) provide visual, clickable filter buttons for any field. Timelines (PivotTable Analyze > Insert Timeline) provide a visual date-range selector for date fields. Both give users an interactive, user-friendly filtering interface for PivotTables.

Question 24

A team manager is working in Microsoft Excel. She has a list of employee ages and wants to apply color scales to visually show relative values — green for high values and red for low values. What feature should she use?

Accepted Answer

Conditional Formatting > Color Scales applies a gradient color scheme across a range, where color intensity reflects relative value. A Red-Yellow-Green scale shows low values as red and high values as green, giving an at-a-glance heat map of the data.

Question 25

You are working in Microsoft Excel. You have a PivotTable and need to display a forecast trend line on the associated PivotChart. Which chart type supports trend lines in Excel?

Accepted Answer

Trendlines in Excel can only be added to non-stacked 2D charts: line, bar, column, area, scatter, and bubble charts. They are not available for pie, doughnut, stacked, or 3D charts. For trend analysis, a Line or Scatter chart is most appropriate and directly supports trendline options.

MO-211 — Microsoft Office Specialist: Excel Expert (Microsoft 365 Apps)