MO-211 Practice Questions

The SUM function is the designated Excel formula for calculating the total of a set of values. It adds all the numbers specified as arguments, which can be individual numbers, cell references, or ranges. Its syntax is SUM(number1, [number2], ...), where number1 is required and subsequent numbers are optional. For example, the formula =SUM(A1:A10) adds all the numerical values in the cell range from A1 to A10, providing their total sum. This function is fundamental for performing addition across multiple cells efficiently.

B. MAX: This function is incorrect as it returns the largest value within a specified range, not the sum of all values.

C. AVERAGE: This function is incorrect because it calculates the arithmetic mean (the average) of the numbers in a range, not their total sum.

D. MIN: This function is incorrect as it returns the smallest value within a specified range, not the sum of all values.

1. Microsoft Corporation. (n.d.). SUM function. Microsoft Support. Retrieved from https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89

Reference Point: The first paragraph states

"The SUM function adds values. You can add individual values

cell references or ranges

or a mix of all three."

2. Microsoft Corporation. (n.d.). MAX function. Microsoft Support. Retrieved from https://support.microsoft.com/en-us/office/max-function-e0506005-93ab-4ce2-8644-43f8d2eb5d1b

Reference Point: The article summary states

"Returns the largest value in a set of values."

3. University of California

Berkeley Library. (n.d.). Excel: Formulas & Functions. Data Lab. Retrieved from https://guides.lib.berkeley.edu/excel/formulas

Reference Point: In the "Common Functions" section

SUM() is described as the function to "Add up a range of cells." The same section correctly defines AVERAGE()

MAX()

and MIN() with their respective purposes.

A line chart is the most effective type for displaying trends over time. It plots data points at regular intervals (such as days, months, or years) and connects them with a line, which clearly illustrates the progression and fluctuations of the data. This continuous visual representation makes it easy to identify patterns, such as increases, decreases, or volatility, across a time series. This is its primary and intended function in data visualization.

A. Pie Chart: Incorrect. Pie charts represent the proportions of a whole at a single point in time; they cannot show how data changes over a period.

C. Scatter Chart: Incorrect. Scatter charts are used to show the relationship and correlation between two different numerical variables, not necessarily a trend over a continuous time axis.

D. Bar Chart: Incorrect. While bar charts can display data over time periods, they are better for comparing discrete categories rather than showing a continuous trend.

1. Microsoft Corporation. (n.d.). Available chart types in Office. Microsoft Support. Retrieved from https://support.microsoft.com/en-us/office/available-chart-types-in-office-a6187218-807e-4103-9e0a-27cdb19afb90.

Section: Line charts: "Line charts can display continuous data over time

set against a common scale

and are therefore ideal for showing trends in data at equal intervals."

2. MIT Libraries. (n.d.). Data Visualization: Chart Types. MIT.edu. Retrieved from https://libguides.mit.edu/data-visualization/chart-types.

Section: Line Graph: "Line graphs are used to track changes over short and long periods of time. When smaller changes exist

line graphs are better to use than bar graphs."

3. University of Washington. (n.d.). Choosing the Right Chart Type. Center for Social Science Computation and Research (CSSCR).

Section: Line Graphs: "Line graphs are used to display data that changes continuously over time. The line in a line graph connects a series of points that represent the data."

The standard formula to calculate the percentage change is (New Value - Old Value) / Old Value. In Excel, this translates to =(B2-A2)/A2, where B2 contains the new value and A2 contains the original (old) value. This formula first calculates the difference between the two values and then divides that difference by the original value to find the proportional change. The result must then be formatted as a percentage to be displayed correctly (e.g., 0.25 displayed as 25%).

B. =(A2-B2)/B2 is incorrect because it subtracts the new value from the old and, more importantly, divides by the new value as the base, which is mathematically incorrect for percentage change.

C. =PERCENTCHANGE(A2, B2) is incorrect as Excel does not have a built-in function named PERCENTCHANGE. This formula would result in a #NAME? error.

D. =B2/A2 is incorrect because this formula calculates the ratio of the new value to the old value, not the percentage change between them. For example, it would return 1.25 instead of 25%.

1. Microsoft Corporation. (n.d.). Calculate percentages. Microsoft Support. Retrieved from https://support.microsoft.com/en-us/office/calculate-percentages-c8627c31-0b45-4543-a4ea-9493fda6c6cd. See the section: "Calculate the percentage of change between two numbers

" which explicitly provides the formula: =(newvalue-oldvalue)/oldvalue.

2. University of Illinois Springfield. (n.d.). Excel: Formulas. LibGuides at Brookens Library. Retrieved from https://libguides.uis.edu/excel/formulas. The guide outlines fundamental calculations

where the principle for percentage change (New-Old)/Old is a standard arithmetic operation applied within Excel formulas.

A slicer is a visual filtering tool in Microsoft Excel that enhances the interactivity of data analysis. It provides a set of buttons that enable users to quickly filter data in PivotTables, PivotCharts, and formatted Excel tables. Instead of using traditional drop-down filter lists, slicers allow for a more intuitive, one-click filtering experience. They also visually indicate the current filtering state, making it easy to understand what data is being displayed in the connected report or chart.

A. This describes Excel functions, which are formulas used for performing calculations, not interactive filtering components.

B. This describes a Column or Bar chart, a type of data visualization, whereas a slicer is a control for filtering data.

D. Slicers are a native, built-in feature of Excel and do not require a separate add-in to be installed.

1. Microsoft Corporation. (n.d.). Use slicers to filter data. Microsoft Support. Retrieved from https://support.microsoft.com/en-us/office/use-slicers-to-filter-data-249f966b-a9d5-4b0f-b31a-12651785d29d.

Reference Point: The first paragraph states

"Slicers provide buttons that you can click to filter tables

or PivotTables. In addition to quick filtering

slicers also indicate the current filtering state

which makes it easy to understand what exactly is shown in a filtered PivotTable." This directly supports the correct answer.

2. University of Wisconsin-Madison

Division of Information Technology. (2023). Excel - Using Slicers. UW-Madison KnowledgeBase. KB0021103.

Reference Point: The document's summary states

"Slicers are visual filters. Using a slicer

you can filter your data (or PivotTable) by clicking on the type of data you want." This confirms that a slicer's primary function is to act as a filter.

3. University of California

Berkeley. (n.d.). Excel: Pivot Tables - Slicers. Library Data Lab.

Reference Point: In the "Slicers" section

it is described as: "Slicers are interactive controls that allow you to visualize and filter your data in a PivotTable." This reinforces that slicers are interactive filtering controls for PivotTables.

Both Shift + F11 and Alt + Shift + F1 are officially documented keyboard shortcuts in Microsoft Excel to insert a new worksheet into the active workbook. These shortcuts add a new sheet to the left of the currently selected sheet tab. The shortcut Ctrl + N, listed as option C, performs a different function; it creates an entirely new, blank workbook file, not a new worksheet within the existing file. Therefore, options B and D are the correct answers to the question.

A. Ctrl + Shift + N: This shortcut is not a default command in Excel for inserting a new worksheet. It is commonly used in other applications, like creating a new folder in File Explorer.

C. Ctrl + N: This shortcut creates a new, blank workbook (a separate .xlsx file), not a new worksheet within the currently open workbook.

1. Microsoft Corporation. (n.d.). Keyboard shortcuts in Excel. Microsoft Support. Retrieved from https://support.microsoft.com/en-us/office/keyboard-shortcuts-in-excel-1798d9d5-842a-42b8-9c99-9b7213f0040f.

Specific Location: Under the "Ctrl key combination shortcuts" section

it lists Ctrl+N for "Create a new

blank workbook."

Specific Location: Under the "Function key shortcuts" section

it lists Shift+F11 to "Insert a new worksheet." It also lists Alt+Shift+F1 for the same action.

2. University of Wisconsin-Madison

Division of Information Technology (DoIT). (n.d.). Excel: Keyboard Shortcuts (PC). KB User's Guide. Retrieved from https://kb.wisc.edu/showcase/page.php?id=93553.

Specific Location: In the "Worksheet" section

the document lists Shift + F11 to "Insert a new worksheet." In the "Workbook" section

it lists Ctrl + N to "Create a new workbook."

The shortcut Ctrl + D is used for the "Fill Down" command in Excel. This command copies the contents and formatting from the topmost cell of a selected range into all the cells below it. When the top cell contains a formula, Ctrl + D effectively copies that formula down the column, automatically adjusting relative cell references as needed. This is a highly efficient, single-step method for replicating a formula across multiple rows, which is a core competency tested in the MO-211 exam.

A. Ctrl + V: This is the shortcut for "Paste." It is used to insert content that has already been copied to the clipboard; it does not perform the copy action itself.

B. Ctrl + C: This is the general "Copy" command. It places the selected cell's content onto the clipboard but does not complete the action of duplicating the formula into another cell.

C. Ctrl + F: This shortcut opens the "Find" dialog box. It is used for searching for data within the worksheet and is unrelated to copying formulas.

1. Microsoft Corporation. (n.d.). Keyboard shortcuts in Excel. Microsoft Support. Retrieved from https://support.microsoft.com/en-us/office/keyboard-shortcuts-in-excel-1798d9d5-842a-42b8-9c99-9b7213f0040f.

Reference Specifics: In the section "Ctrl key shortcuts

" the documentation explicitly states: "Ctrl+D: Use the Fill Down command to copy the contents and format of the topmost cell of a selected range into the cells below." It also defines Ctrl+C as "Copy the selected cells" and Ctrl+V as "Insert the contents of the Clipboard."

2. University of California

Berkeley. (n.d.). Excel: Shortcuts (PC). bConnected. Retrieved from https://kb.berkeley.edu/page.php?id=31337.

Reference Specifics: Under the "Editing" section

the keyboard shortcut Ctrl + D is listed with the description "Fill Down

" confirming its function to copy content from the cell above.

3. Duke University

Fuqua School of Business. (n.d.). Excel Efficiency Guide.

Reference Specifics: In a publicly available courseware document on Excel efficiency

under the "Filling" section (page 4)

it lists: "Ctrl+D: Fill Down. Copies the contents of the cell above." This distinguishes it from the general "Ctrl+C" (Copy) command listed on page 3.

To apply a custom number format in Excel, you must use the "Custom" category within the "Format Cells" dialog box. This option allows users to define their own format codes to control how numbers, dates, times, and text are displayed in a cell. This provides maximum flexibility beyond the standard, built-in formats like "Number" or "General." Users can specify elements such as the number of decimal places, leading zeros, color, and specific text characters to appear with the number.

A. Use the “Conditional Formatting” format: This feature applies formatting based on rules or criteria that a cell's value must meet, not for applying a static, custom number display format.

C. Use the “Number” format: This is a standard, built-in category for general numeric display with options for decimal places and separators, but it is not for creating user-defined formats.

D. Use the “General” format: This is the default Excel format, which displays numbers as entered without any specific formatting rules applied.

1. Microsoft Support. "Create or delete a custom number format." Microsoft Office Support Documentation. Accessed May 20

2024. In the section "Create a custom number format

" Step 3 explicitly states

"In the Category list

click Custom."

2. Microsoft Support. "Format numbers." Microsoft Office Support Documentation. Accessed May 20

2024. The article details the "Format Cells" dialog box

showing "Custom" as the category for user-defined formats

distinct from "Number" and "General."

3. University of Wisconsin-Madison

Division of Information Technology (DoIT). "Excel: Formatting Numbers." KnowledgeBase. Accessed May 20

2024. The document guides users to the "Format Cells" dialog box and explains under the "Custom Number Formats" section how to select the "Custom" category to create unique formats.

In Visual Basic for Applications (VBA), a conditional statement is a control structure that allows a program to execute a specific block of code only if a predefined condition evaluates to true. The primary conditional structures in VBA are If...Then...Else and Select Case. These statements first test a condition; based on the outcome of that test, the program's execution path is altered. The code block associated with the condition is executed after the condition has been evaluated and found to be true.

A. This describes a loop structure, such as Do While...Loop or For...Next, which repeatedly executes code as long as a condition is met, rather than a one-time decision.

C. The condition is evaluated before the code block is executed to determine if it should run, not a condition that must be met within the block itself.

D. This is incorrect for the primary conditional statements (If...Then). The condition is always evaluated before the associated code block is executed.

1. Microsoft Corporation. (2023). If...Then...Else statement (VBA). Microsoft Learn. Retrieved from learn.microsoft.com. In the "Syntax" and "Remarks" sections

the documentation explains that the statements are executed only If condition Is True. This confirms the condition is checked before execution.

2. Microsoft Corporation. (2023). Using If...Then...Else statements. Microsoft Learn. Retrieved from learn.microsoft.com. This document states

"You can use the If...Then...Else statement to run a specific statement or a block of statements

depending on the value of a condition." This reinforces that the execution depends on a pre-evaluated condition.

3. Halvorson

M. (2010). Microsoft Visual Basic 2010 Step by Step. Microsoft Press. Chapter 5

"Making Decisions

" pages 101-104

describes how If...Then decision structures work by evaluating a conditional expression and executing code only when the expression is true.

The feature designed to split the contents of a cell into multiple columns is officially named "Text to Columns" in Microsoft Excel. This tool parses data within a single column based on a specified delimiter (such as a comma, space, or tab) or a fixed width and distributes the resulting pieces of data into adjacent columns. Option A, "Split cells," while not the official feature name, accurately describes the outcome of this operation and is the only plausible choice among the given options.

B. Merging cells: This action is the opposite; it combines two or more cells into a single, larger cell.

C. Formatting data: This changes the visual appearance of data (e.g., font, color, number style) but does not alter its location or split it across cells.

D. Wrapping text: This feature displays long text on multiple lines within the same cell to improve readability, without splitting the data into new columns.

1. Microsoft Corporation. (n.d.). Split text into different columns with the Text to Columns Wizard. Microsoft Support. Retrieved from https://support.microsoft.com/en-us/office/split-text-into-different-columns-with-the-text-to-columns-wizard-30b14928-5550-41f5-97ca-7a3e9c363ed7.

Reference Point: The document's title and first paragraph explicitly state

"You can take the text in one or more cells

and spread it out across multiple cells. This is the opposite of concatenation... For example

you can split a column of full names into separate first and last name columns." This directly supports the function described in the question.

2. University of Wisconsin-Madison. (n.d.). Excel - Splitting Text into Multiple Columns/Cells. KnowledgeBase. Retrieved from https://kb.wisc.edu/showcase/page.php?id=92519.

Reference Point: The "Overview" section describes the "Text to Columns" feature as the primary method for "splitting the contents of a cell into multiple cells." This aligns with the correct answer's function.

3. MIT Libraries. (n.d.). Excel 2016: Text to Columns. Data Management Services. Retrieved from https://libguides.mit.edu/c.php?g=176258&p=1159402.

Reference Point: The guide explains

"Text to Columns is a feature in Excel that allows you to split a column of text into multiple columns." This confirms the purpose of the feature.

The most direct method to remove a filter applied to a column or an entire dataset in Excel is by using the "Clear" command. This command is located on the Data tab within the Sort & Filter group. When a filter is active, clicking "Clear" removes all filtering criteria from the current data range or table, making all rows visible again. This function is specifically designed for this purpose, distinguishing it from other data manipulation tools.

B. Use the “Advanced Filter” function: The "Advanced Filter" is used for applying complex criteria from a separate criteria range, not for clearing existing simple filters.

C. Use the “Sort” function: The "Sort" function is used to arrange data in a specific order (e.g., alphabetically or numerically) and does not remove filter criteria.

D. Use the “Find and Replace” function: This tool is for locating and changing cell contents and has no functionality related to applying or removing data filters.

1. Microsoft Corporation. (n.d.). Filter data in a range or table. Microsoft Support. Retrieved from https://support.microsoft.com/en-us/office/filter-data-in-a-range-or-table-01832226-31b5-4568-8806-38c37dcc180e.

Reference Point: Under the section "Clear a filter

" the document states

"To remove all filters from a worksheet

on the Data tab

in the Sort & Filter group

click Clear."

2. University of Wisconsin-Madison

Division of Information Technology (DoIT). (2016). Excel 2016: Filtering Data. Software Training for Students.

Reference Point: In the section "Clearing Filters

" the guide instructs: "To clear all filters from a worksheet

click the Data tab

then click the Clear button in the Sort & Filter group."

3. GCFGlobal.org. (n.d.). Excel: Filtering Data. Goodwill Community Foundation

Inc. Retrieved from https://edu.gcfglobal.org/en/excel/filtering-data/1/.

Reference Point: In the lesson on filtering

under the subsection "To clear a filter

" it states: "You can also click the Clear command on the Data tab to remove all filters from your worksheet."