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

Question 1

You are working in Microsoft Excel. You need to rename the current worksheet tab. What is the fastest way to do this?

Accepted Answer

Right-clicking a sheet tab displays a context menu with the Rename option, which is the standard and fastest method to rename a worksheet tab in Excel.

Question 2

You are working in Microsoft Excel. You need to prevent other users from inserting or deleting rows in a worksheet, but still allow them to edit cell contents. What should you do?

Accepted Answer

To allow cell editing but block row insertion/deletion, unlock all cells first (so users can edit), then protect the sheet via Review > Protect Sheet, deselecting Insert rows and Delete rows permissions.

Question 3

You are working in Microsoft Excel. You want to display two different worksheets from the same workbook side by side on screen at the same time. What should you do?

Accepted Answer

View > New Window creates a second window for the same workbook. Then View > Arrange All > Vertical (or Horizontal) tiles both windows side by side, allowing you to view different sheets simultaneously.

Question 4

You are working in Microsoft Excel. You need to save a workbook so that it can be opened by users who have Excel 2007. Which file format should you choose?

Accepted Answer

Excel 97-2003 Workbook (.xls) format is compatible with Excel versions prior to 2007. While .xlsx is the current default, Excel 2007 can open .xlsx files; however, for guaranteed compatibility with Excel 2007 and earlier, .xls is the safest choice. Note: Excel 2007 introduced .xlsx, so .xlsx would also work, but .xls ensures the broadest backward compatibility.

Question 5

You are working in Microsoft Excel. You need to move Sheet2 so it appears before Sheet1 in the workbook. What is the easiest way to do this?

Accepted Answer

You can reorder worksheet tabs by dragging them, or by right-clicking the tab and using Move or Copy to specify the new position. Both methods work correctly.

Question 6

You are working in Microsoft Excel. You need to apply a number format that displays values as currency with two decimal places and a dollar sign. Which format should you apply?

Accepted Answer

The Currency format in Excel displays values with a currency symbol (e.g., $) and two decimal places by default, placed immediately next to the number. This is the standard format for displaying monetary values.

Question 7

You are working in Microsoft Excel. You have a column of dates and you want to highlight all dates that fall in the next 7 days with a yellow background automatically. Which feature should you use?

Accepted Answer

Conditional Formatting allows you to apply formatting such as cell background color automatically based on rules. You can create a rule using a formula like =AND(A1>=TODAY(), A1<=TODAY()+7) to highlight dates in the next 7 days.

Question 8

You are working in Microsoft Excel. You need to fill a series of cells efficiently without manually typing each value. Which two methods can you use to automatically fill a series of consecutive numbers in a column? Choose 2.

Accepted Answer

Dragging the fill handle after selecting two seed values allows Excel to detect and continue the pattern. Alternatively, Home > Fill > Series lets you specify start value, step, and stop value to generate a series automatically. Ctrl+D copies the same value rather than creating a series.

Question 9

You are working in Microsoft Excel. You need to display long text in a single cell without it overflowing into adjacent cells. The row height should adjust automatically. What should you do?

Accepted Answer

Enabling Wrap Text causes the text to wrap within the cell boundaries. AutoFit Row Height (Home > Format > AutoFit Row Height) then automatically adjusts the row height to display all the wrapped text. Shrink to Fit makes text smaller but does not adjust row height.

Question 10

A user is building a data entry form in Microsoft Excel. They want to restrict a cell so that only whole numbers between 1 and 100 can be entered, and an error message appears if an invalid value is typed. What feature should they use?

Accepted Answer

Data Validation (Data > Data Validation) lets you define allowed input types. By choosing Whole Number and setting Minimum to 1 and Maximum to 100, invalid values are blocked. You can configure a custom error alert message on the Error Alert tab.

Question 11

You are working in Microsoft Excel. You have a range of data and you want to convert it to a formal Excel table. Which keyboard shortcut creates an Excel table from the selected range?

Accepted Answer

Both Ctrl+T and Ctrl+L open the Create Table dialog in Excel, allowing you to convert a range to a formatted Excel table with built-in filtering, sorting, and structured references.

Question 12

You are working in Microsoft Excel. You have an Excel table and you want to show only rows where the Region column equals 'North'. What is the quickest way to do this?

Accepted Answer

Excel tables automatically include AutoFilter drop-down arrows on each column header. Clicking the Region drop-down and selecting 'North' instantly filters the visible rows to show only matching records without deleting or hiding rows manually.

Question 13

You are working in Microsoft Excel. You have an Excel table with a Total Row. You want the Total Row for the Sales column to show the average instead of the sum. What should you do?

Accepted Answer

When the Total Row is enabled for an Excel table, each cell in the Total Row has a drop-down that lets you select from summary functions: Average, Count, Max, Min, Sum, etc. Selecting Average from the drop-down replaces the Sum with an AVERAGE subtotal function.

Question 14

A user has an Excel table named SalesData with columns: OrderID, Region, and Amount. They type a formula outside the table: =SUM(SalesData[Amount]). What does this formula calculate?

Accepted Answer

SalesData[Amount] is a structured reference that refers to all data cells in the Amount column of the SalesData table (excluding the header and total row). =SUM(SalesData[Amount]) therefore sums all data values in that column and is perfectly valid outside the table.

Question 15

You are working in Microsoft Excel. Cell A1 contains 150 and cell B1 contains 200. You want cell C1 to display the larger of the two values. Which formula should you enter in C1?

Accepted Answer

All three formulas return the larger value. =MAX(A1,B1) is the most direct. =IF(A1>B1,A1,B1) uses a logical test. =LARGE(A1:B1,1) returns the 1st largest value in the range. All are valid approaches.

Question 16

You are working in Microsoft Excel. You need a formula that looks up a product code in column A of a table and returns the corresponding price from column C. Which function is best suited for this task?

Accepted Answer

VLOOKUP searches vertically in the first column of a table and returns a value from a specified column number. For example, =VLOOKUP(productCode, A:C, 3, FALSE) searches column A for the product code and returns the value from column C (3rd column).

Question 17

You are working in Microsoft Excel. You want to count the number of cells in the range B2:B50 that contain a value greater than 100. Which formula should you use?

Accepted Answer

=COUNTIF(range, criteria) counts cells matching a condition. =COUNTIF(B2:B50, ">100") counts all cells in B2:B50 where the value exceeds 100. COUNT counts numbers only with no criteria; SUMIF sums rather than counts; COUNTA counts non-empty cells with no criteria support in this syntax.

Question 18

You are working in Microsoft Excel. You have a formula =B2/C2 but cell C2 might be blank or zero, causing a #DIV/0! error. Which two approaches can you use to suppress this error and display 0 instead? Choose 2.

Accepted Answer

=IFERROR(B2/C2, 0) is the cleanest approach — it returns 0 if the expression produces any error. =IF(C2=0, 0, B2/C2) explicitly checks for zero before dividing. Option E also works but is more verbose. ISERROR and ISNUMBER alone return TRUE/FALSE, not the desired 0 value.

Question 19

You are working in Microsoft Excel. You need to extract the year from a date stored in cell A1. Which formula should you use?

Accepted Answer

=YEAR(date) returns the year component of a date value as a 4-digit integer. This is the correct and direct function for extracting the year. =TEXT(A1,"YYYY") would return the year as text, not a number, which may cause issues in calculations.

Question 20

You are working in Microsoft Excel. You have selected a data range and inserted a chart. You now want to switch the rows and columns in the chart so that the data series are plotted differently. What should you do?

Accepted Answer

The Switch Row/Column button on the Chart Design tab (appears when the chart is selected) instantly transposes how data series are plotted — swapping what appears on the horizontal axis versus what is represented as series.

Question 21

You are working in Microsoft Excel. You have a column chart and you want to add data labels showing the exact value above each bar. What is the quickest way to do this?

Accepted Answer

The Chart Elements button (+) appears to the right of a selected chart. Checking Data Labels adds labels to all data points simultaneously and is the fastest method. Right-clicking individual bars works but is slower. There is no Insert > Data Labels menu.

Question 22

A user has a chart embedded in a worksheet and needs to move it to its own chart sheet so it prints separately. What should they do?

Accepted Answer

Right-clicking an embedded chart and selecting Move Chart opens the Move Chart dialog, where you can select 'New sheet' to move the chart to a dedicated chart sheet. This is the correct built-in method for converting an embedded chart to a chart sheet.

Question 23

You are working in Microsoft Excel. You have a bar chart showing sales figures. You want to add a secondary vertical axis to also display profit margin percentages on the same chart. What should you do?

Accepted Answer

To add a secondary axis for a specific data series, right-click that series in the chart and choose Format Data Series. In the Format Data Series pane, under Series Options, select Secondary Axis. This plots the series against a new right-side vertical axis, ideal for data with different scales.

Question 24

You are working in Microsoft Excel. You need to change the appearance of a chart. Which two actions can you perform using the Chart Design tab? Choose 2.

Accepted Answer

The Chart Design tab provides options to Change Chart Type (converting between bar, line, pie, etc.) and to apply Chart Styles (pre-built formatting combinations). Editing worksheet data is done in the sheet itself; formatting axis label fonts is done via the Format tab or Format pane.

Question 25

A manager receives an Excel workbook with a chart but notices the chart title still shows 'Chart Title'. They want the chart title to automatically display the text from cell A1 which contains the report name. What is the best approach?

Accepted Answer

You can link a chart title to a cell by clicking the chart title text box, then typing = in the formula bar and clicking the desired cell (A1). This creates a dynamic link so the chart title automatically updates whenever cell A1 changes.

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