Free MO-211 Practice Test Questions and Answers (2026)

The .xltx file format is the standard extension for an Excel template in versions of Microsoft Excel 2007 and later. When a user saves a workbook as an .xltx file, it creates a template that can be used as a starting point for new workbooks. Opening a .xltx file creates a new, unsaved workbook (.xlsx) based on the template's layout, formatting, and formulas, leaving the original template file unchanged. This ensures consistency and efficiency when creating similar documents repeatedly.

A. .pdf (Portable Document Format) is a file format used for presenting and exchanging documents reliably, independent of software, hardware, or operating system. It is not an Excel template format.

C. .mdb is a file extension for a Microsoft Access Database, a database management system. It is unrelated to Excel templates.

D. .pot is a file extension for a Microsoft PowerPoint template (specifically for versions prior to 2007). It is used to create presentations, not Excel workbooks.

1. Microsoft Corporation. (n.d.). File formats that are supported in Excel. Microsoft Support. Retrieved from https://support.microsoft.com/en-us/office/file-formats-that-are-supported-in-excel-0943ff2c-6014-4e8d-aaea-b83d51d46247.

Reference Point: In the section "Excel file formats

" the table explicitly lists .xltx with the description

"Excel Template. The default Excel template file format for an Excel workbook."

2. University of Wisconsin-Madison

Division of Information Technology (DoIT). (2020

July 22). Microsoft - File Extensions for Office 2016/2013/2010/2007. UW-Madison KnowledgeBase. Retrieved from https://kb.wisc.edu/helpdesk/page.php?id=9398.

Reference Point: The table under the "Excel" section identifies .xltx as the "Excel Template" file extension.

3. Indiana University

University Information Technology Services (UITS). (2018

April 18). File formats for saving documents in Office 2016. UITS Knowledge Base. Retrieved from https://kb.iu.edu/d/aofp.

Reference Point: The section on "Excel 2016" lists .xltx and describes it as "Excel Template." It also correctly identifies .potx (and by extension

the legacy .pot) as a PowerPoint Template.

The primary function of the Table Styles feature in Excel is to apply a predefined collection of formatting attributes—such as colors, fonts, borders, and banded rows—to a structured table. This allows for the quick and efficient application of a uniform and professional appearance. By using the same style across multiple tables within a workbook or across different workbooks, users can ensure visual consistency, which improves readability and adherence to branding or reporting standards. Users can also create custom table styles for reuse.

A. This is too general. While styles do create a visual representation, their specific function is to apply a consistent, pre-packaged set of formatting rules efficiently.

B. This is incorrect. Charts and graphs are entirely separate features in Excel used for data visualization; they are not created or managed by the Table Styles feature.

D. This is inaccurate. Table Styles are designed specifically for Excel Table objects (created via Insert > Table or Ctrl+T), not for arbitrary areas or ranges on a worksheet.

1. Microsoft 365 Support Documentation. In the article "Format an Excel table

" it states

"Excel has several predefined table styles that you can use to quickly format a table." It further explains how to modify and create custom styles

emphasizing the application of a consistent format. (Microsoft Corporation. (n.d.). Format an Excel table. Microsoft Support. Retrieved from support.microsoft.com)

2. Microsoft 365 Support Documentation. The article "Create or delete a custom table style" details the process of creating a new style that can be reused. It notes

"After you create a custom table style

it is available... to apply to any new tables that you create in that workbook." This directly supports the function of applying consistent formatting. (Microsoft Corporation. (n.d.). Create or delete a custom table style. Microsoft Support. Retrieved from support.microsoft.com)

3. University of Wisconsin-Madison

Courseware. In Excel training materials

the purpose of table styles is described as a way to "quickly add professional-level formatting to a table of data." The reusability of these styles is a key benefit highlighted for maintaining a consistent look. (University of Wisconsin-Madison. (n.d.). Excel: Tables. Software Training for Students. Retrieved from sts.doit.wisc.edu/excel-tables/)

The AVERAGE function is a fundamental statistical function in Microsoft Excel. Its purpose is to calculate the arithmetic mean of a set of numeric values. The function sums the numbers provided as arguments (which can be numbers, cell references, or ranges) and then divides by the count of those numbers. It is important to note that the function ignores text, logical values, and empty cells, but it does include cells containing the value zero in its calculation.

A. This describes the functionality of the MATCH function, which is used to find the relative position of an item in a range.

C. This describes the purpose of lookup functions like VLOOKUP or XLOOKUP, which search for a value and retrieve a corresponding value from another column.

1. Microsoft Corporation. (n.d.). AVERAGE function. Microsoft Support. Retrieved from https://support.microsoft.com/en-us/office/average-function-047bac88-d466-426c-a32b-8f33eb960cf6

Reference Detail: In the "Description" section

the document states

"Returns the average (arithmetic mean) of the arguments."

2. University of California

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

Reference Detail: In the "Commonly Used Formulas" table

the AVERAGE function is defined as: "Finds the average of the numbers in a range of cells. =AVERAGE(A2:A10)".

The keyboard shortcut Ctrl + Shift + # is used in Microsoft Excel to apply the Date format to the selected cell or range of cells. This command formats the cell's content to display as a date in the dd-mmm-yy format. For example, if a cell contains the serial number 45000, applying this shortcut will change the display to 17-Jul-23. This is a quick method for formatting numerical data into a standard date representation without navigating through the Format Cells dialog box.

B. The shortcut to apply the Time format is Ctrl + Shift + @.

C. The shortcut to apply the Currency format is Ctrl + Shift + $.

D. The shortcut to apply the Number format (with two decimal places and a thousands separator) is Ctrl + Shift + !.

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

Reference Location: Under the section "Ctrl combination shortcut keys

" the table lists Ctrl+Shift+# with the description "Applies the Date format with the day

month

and year."

2. Indiana University. (2023

October 25). Excel keyboard shortcuts. University Information Technology Services Knowledge Base. Retrieved from https://kb.iu.edu/d/aacc.

Reference Location: In the "Formatting" section

the table entry for "Date format" specifies the shortcut Ctrl+Shift+#.

3. University of Wisconsin-Madison. (2020

July 20). Excel - Keyboard Shortcuts. Division of Information Technology (DoIT) KnowledgeBase. Retrieved from https://kb.wisc.edu/showcase/page.php?id=92544.

Reference Location: Under the "Formatting" subsection

the shortcut Ctrl+Shift+# is listed for applying the "Date format (dd-mm-yy)."

The keyboard shortcut Ctrl + ; (Control key plus the semicolon key) is the designated command in Microsoft Excel to insert the current system date into the active cell. This action inserts the date as a static value, meaning it will not update automatically like the TODAY() function. This is a fundamental and widely documented data entry shortcut within the application.

B. Ctrl + Shift + ;

This shortcut inserts the current system time, not the date, into the active cell.

C. Shift + ;

This key combination simply types a colon (:) character into the cell.

D. Alt + ;

This shortcut is used to select only the visible cells within a selected range, ignoring any hidden rows or columns.

1. Microsoft Corporation. (n.d.). Keyboard shortcuts in Excel. Microsoft Support. Retrieved from support.microsoft.com. In the section "Ctrl key combination shortcuts

" the documentation explicitly states: "Ctrl+; (semicolon) Enter the current date." and "Ctrl+Shift+; (semicolon) Enter the current time."

2. Indiana University. (2023

October 24). Keyboard shortcuts for Microsoft Excel. University Information Technology Services Knowledge Base. Retrieved from kb.iu.edu/d/acik. The "Data entry" section lists: "Ctrl+; Insert the current date."

3. University of Wisconsin-Madison. (2019

July 1). Excel - Keyboard Shortcuts. Division of Information Technology (DoIT). Retrieved from kb.wisc.edu/showcase/page.php?id=93310. The document lists "Ctrl + ;" with the description "Insert current date."

The "Remove Duplicates" function is the designated tool in Microsoft Excel for permanently deleting rows that contain duplicate values. Located on the 'Data' tab within the 'Data Tools' group, this feature allows the user to select one or more columns to check for duplicate information. When executed, Excel removes the entire row for all duplicate entries found after the first instance, thus cleaning the dataset.

B. The “Fill Handle” function is used to copy cell contents or to continue a data series into adjacent cells; it does not remove data.

C. The “Find and Replace” function is used to locate specific values and substitute them with others, not to identify and remove duplicate rows.

D. The “Filter” function temporarily hides rows that do not meet specified criteria but does not permanently delete the duplicate data from the worksheet.

1. Microsoft Corporation. (n.d.). Find and remove duplicates. Microsoft Support. Retrieved from https://support.microsoft.com/en-us/office/find-and-remove-duplicates-00e35321-5e25-404d-9199-6e7353882713. (This official documentation directly describes the "Remove Duplicates" command under the "Data Tools" group on the "Data" tab.)

2. University of California

Berkeley Library. (n.d.). Excel: Remove Duplicates. Data-services. Retrieved from https://data-services.hosting.berkeley.edu/training/excel-remove-duplicates. (This university guide explicitly details the step-by-step process for using the "Remove Duplicates" feature to clean data.)

3. MIT Libraries. (n.d.). Excel 2016: Data Cleaning and Preparation. LibGuides at MIT Libraries. Retrieved from https://libguides.mit.edu/c.php?g=559451&p=3848248. (Under the "Removing Duplicates" section

this resource confirms that the "Remove Duplicates" tool on the Data tab is the standard method for this task.)

In Microsoft Excel, the caret symbol ^ is an arithmetic operator used for exponentiation (raising a number to a power). It is not a logical, or comparison, operator. Logical operators are used to compare values and result in a Boolean value of TRUE or FALSE. The symbols >, >=, and < are all valid logical operators in Excel used for "greater than," "greater than or equal to," and "less than" comparisons, respectively.

B: The > (greater than) symbol is a valid logical comparison operator used to test if one value is greater than another.

C: The >= (greater than or equal to) symbol is a valid logical comparison operator in Excel.

D: The < (less than) symbol is a valid logical comparison operator used to test if one value is less than another.

1. Microsoft Corporation. (n.d.). Calculation operators and precedence in Excel. Microsoft Support. Retrieved from https://support.microsoft.com/en-us/office/calculation-operators-and-precedence-in-excel-48be406d-4975-4d31-b2b8-7af9e0e2878a.

Reference Details: Under the section "Types of operators

" the article lists ^ (caret) as an "Arithmetic operator" for "Exponentiation." The operators >

>=

and < are listed under the "Comparison operators" section.

2. Indiana University. (2023

October 10). Operators in Excel formulas. University Information Technology Services (UITS) Knowledge Base. Retrieved from https://kb.iu.edu/d/afrq.

Reference Details: The document provides a table of operators. It lists ^ as the "Exponent" operator. It separately lists >

>=

and < in the "Comparison" operators category.

3. University of Wisconsin-Madison. (n.d.). Excel - Formulas and Functions - The Basics. Software Training for Students. Retrieved from https://sts.doit.wisc.edu/wp-content/uploads/sites/133/2020/01/Excel-Formulas-and-Functions-The-Basics.pdf.

Reference Details: On page 2

under the "Operators" section

the document lists "Comparison Operators" (=

>

<

>=

<=

) and "Arithmetic Operators" (+

-

/

%

^)

clearly distinguishing the caret ^ as arithmetic

not logical/comparison.

The SUMIF function is used to sum values in a range that meet a specific criterion. This criterion can be expressed in relation to a percentage. For example, the formula =SUMIF(A1:A10, ">50%") would sum all the values in the range A1:A10 that are greater than 0.5. This directly addresses the "add values based on a specified percentage" part of the question. While no single function listed performs both addition and subtraction in this context, SUMIF is the only one that performs conditional summation based on criteria, which can include percentages.

A. AVERAGEIF: This function calculates the conditional average of values that meet a specific criterion; it does not sum or subtract them.

C. PMT: This is a specialized financial function for calculating loan payments based on an interest rate (a percentage), not for general conditional arithmetic.

D. PERCENTILE: This statistical function finds the value at a specific percentage rank within a dataset (e.g., the 90th percentile). It does not add or subtract values.

1. Microsoft 365 Support Documentation. The official documentation for the SUMIF function states its purpose is to "sum the values in a range that meet criteria that you specify." The examples demonstrate its use with logical operators

which can be applied to percentage criteria.

Source: Microsoft Corporation. (n.d.). SUMIF function. Microsoft 365 Support. Retrieved from https://support.microsoft.com/en-us/office/sumif-function-169b8c99-c05c-4483-a712-1697a653039b

2. Microsoft 365 Support Documentation. The documentation for the PERCENTILE.INC function clarifies that it "Returns the k-th percentile of values in a range

where k is in the range 0..1

inclusive

" confirming its purpose is for statistical ranking

not arithmetic operations like adding or subtracting.

Source: Microsoft Corporation. (n.d.). PERCENTILE.INC function. Microsoft 365 Support. Retrieved from https://support.microsoft.com/en-us/office/percentile-inc-function-680f9539-45eb-410b-9953-74f40290a86b

3. University of Washington

Foster School of Business. Course materials for "Excel for Business" (e.g.

OPMGT 301) describe the use of conditional functions. They explain that SUMIF is used for "summing cells in a range that meet a single criterion

" distinguishing it from arithmetic formulas used for increasing or decreasing a value by a percentage.

Source: Foster School of Business. (n.d.). Excel 2016: Core Part 3: Formulas and Functions. University of Washington. (Specific course materials illustrate the distinct applications of SUMIF vs. basic percentage calculations).

The shortcut Ctrl+X executes the "Cut" command in Excel. This action removes the content and formatting of the selected cell(s) and places it on the clipboard. While the primary shortcut to open the "Delete" dialog box (which removes the cell itself and shifts adjacent cells) is Ctrl+- (Control and the minus sign), this option is not provided. Among the given choices, the "Cut" command is the only one that involves removing the cell's contents from its original location, making it the best fit for the question's intent.

A. Ctrl+C: This is the shortcut for "Copy." It duplicates the cell's content to the clipboard without altering the original cell.

B. Ctrl+Z: This is the shortcut for "Undo." It reverses the last action performed and is not a primary deletion command.

C. Ctrl+D: This is the shortcut for "Fill Down." It copies the content and format from the top cell of a selected range into the cells below it.

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 Point: In the "Frequently used shortcuts" section

the documentation explicitly states: Ctrl+X is used to "Cut the selected cells." It also lists Ctrl+C for "Copy

" Ctrl+Z for "Undo

" and Ctrl+D to "Use the Fill Down command."

2. University of Wisconsin-Madison. (n.d.). Excel: Keyboard Shortcuts. Software Training for Students.

Reference Point: The courseware document lists common editing shortcuts

identifying Ctrl + X as "Cut

" Ctrl + C as "Copy

" and Ctrl + Z as "Undo

" confirming their distinct functions from deletion.

A macro in Microsoft Excel is a set of instructions or a recorded sequence of user actions (such as keystrokes, mouse clicks, and commands) that can be saved and executed repeatedly. The primary purpose is to automate repetitive or complex tasks, thereby saving time, reducing the potential for human error, and improving efficiency. By recording a series of steps, a user can perform that entire sequence with a single command, such as a button click or a keyboard shortcut.

A. Inserting a hyperlink is a specific, singular action that can be done without a macro; it is not the fundamental purpose of the macro feature.

B. Sorting data is a built-in Excel function. While a macro can include a sorting step, its core purpose is to automate a sequence of tasks, not just one.

C. Illustrating data with charts is a primary function of Excel's charting tools, not the macro feature itself, which is designed for automation.

1. Microsoft Corporation. (n.d.). Automate tasks with the Macro Recorder. Microsoft Support. Retrieved from support.microsoft.com. In the introductory paragraph

it states

"If you have tasks in Microsoft Excel that you do repeatedly

you can record a macro to automate those tasks. A macro is an action or a set of actions that you can run as many times as you want."

2. University of Washington

Foster School of Business. (n.d.). Introduction to Macros. In the section "What is a Macro?"

it is defined as: "A macro is a series of commands and functions that are stored in a Microsoft Visual Basic module and can be run whenever you need to perform the task."

3. University of Wisconsin-Madison. (n.d.). Excel - Getting Started with Macros. DoIT Academic Technology. In the "What is a macro?" section

it states

"A macro is a tool that allows you to automate tasks... You can record a sequence of actions in Excel to create a macro."

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 question is technically flawed as all listed options are valid chart types in modern versions of Excel (2019/Microsoft 365). However, the question likely distinguishes between fundamental and specialized chart types. Pie, Bar, and Line charts are primary chart types, each with a dedicated icon in the main "Charts" group of the Insert tab. A Histogram is a specialized statistical chart, available since Excel 2016, which is located in the "Insert Statistic Chart" sub-menu. Given this distinction in access and classification within the Excel interface, the Histogram is the intended answer as it is not a primary chart type in the same category as the others.

A. Pie Chart: This is a fundamental, built-in chart type in Excel used to show the proportional distribution of data in a single series.

B. Bar Chart: This is a standard, primary chart type in Excel used to compare values across different categories, with horizontal bars.

C. Line Chart: This is a core chart type in Excel, commonly used to display trends in data over time or ordered categories.

1. Microsoft Support. (n.d.). Available chart types in Office. Microsoft. Retrieved from https://support.microsoft.com/en-us/office/available-chart-types-in-office-a6187218-807e-4103-9e0a-272112e7b08e. This official documentation lists Pie

Bar

Line

and Histogram charts among the available types

confirming all are valid in Excel.

2. Microsoft Support. (n.d.). Create a histogram. Microsoft. Retrieved from https://support.microsoft.com/en-us/office/create-a-histogram-85680173-064b-4024-b39d-85f53b5f2695. This document outlines the specific steps for creating a histogram

noting it is found by clicking "Insert" > "Insert Statistic Chart" > "Histogram

" which differentiates its access from primary chart types like Pie or Line.

The SUMIF() function is specifically designed to calculate the sum of values in a range that meets a single, specified condition or criterion. The function's syntax is SUMIF(range, criteria, [sumrange]), where it evaluates the range against the criteria and sums the corresponding cells in the sumrange. This directly addresses the question's requirement to sum cells based on "a certain condition" (singular).

A. SUMIFS() is designed to sum cells based on multiple conditions or criteria, making it less precise for the single-condition scenario described in the question.

B. While both functions can sum cells, SUMIF() is the specific function for a single condition. The question asks for "the formula," implying the most direct tool.

D. AVERAGEIF() and AVERAGEIFS() are incorrect because they calculate the average of cells that meet criteria, not the sum.

1. Microsoft Corporation. (n.d.). SUMIF function. Microsoft Support. Retrieved from https://support.microsoft.com/en-us/office/sumif-function-169b8c99-c05c-4483-a712-1697a653039b

Reference Detail: The official documentation states

"Use SUMIF

one of the statistical functions

to sum the values in a range that meet criteria that you specify." This confirms its use for a single criterion.

2. Microsoft Corporation. (n.d.). SUMIFS function. Microsoft Support. Retrieved from https://support.microsoft.com/en-us/office/sumifs-function-c9e748f5-7ea7-455d-9406-611cebce642b

Reference Detail: The documentation for SUMIFS states

"The SUMIFS function adds all of its arguments that meet multiple criteria." This highlights its primary purpose for handling more than one condition.

3. University of Wisconsin-Madison. (n.d.). Excel - SUMIF and SUMIFS Functions. Division of Information Technology (DoIT). Retrieved from https://kb.wisc.edu/showcase/page.php?id=93515

Reference Detail: The article explicitly distinguishes the two functions: "The SUMIF function is used to conditionally sum values based on a single criterion." and "The SUMIFS function is used to conditionally sum values based on multiple criteria."

The IF function is a fundamental logical function in Microsoft Excel used for decision-making. It performs a logical test on a condition you specify. Based on the outcome of this test, it returns one of two possible values. If the condition evaluates to TRUE, the function returns the first specified value (valueiftrue); if the condition evaluates to FALSE, it returns the second specified value (valueiffalse). Its syntax is IF(logicaltest, valueiftrue, valueiffalse).

A. The NOW() and TODAY() functions are used to display the current date and time, not the IF function.

B. The ROUND() function and its variations (ROUNDUP, ROUNDDOWN) are used for rounding numbers, not the IF function.

D. Inserting a picture is a feature accessed via the 'Insert' tab or, in newer versions, the IMAGE function, not the IF function.

1. Microsoft Corporation. (n.d.). IF function. Microsoft 365 Support. Retrieved from https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2.

Reference Point: The "Description" section states

"The IF function... allows you to make logical comparisons between a value and what you expect. So an IF statement can have two results. The first result is if your comparison is True

the second if your comparison is False."

2. MIT Libraries. (n.d.). Excel 2016: The Basics - Logical Functions. MIT LibGuides. Retrieved from https://libguides.mit.edu/c.php?g=175963&p=1159043.

Reference Point: Under the "Logical Functions" heading

the guide describes the IF function: "The IF function returns one value if a condition you specify evaluates to TRUE

and another value if that condition evaluates to FALSE."

3. University of Wisconsin-Madison. (n.d.). Excel - The IF Function. UW-Madison KnowledgeBase. Retrieved from https://kb.wisc.edu/showcase/page.php?id=2545.

Reference Point: The introductory paragraph states

"The IF function allows you to test a condition and returns a specific value if the condition is TRUE

or another value if the condition is FALSE."

The vertical spacing between data points (e.g., in a bar chart) is adjusted using the "Gap Width" and "Series Overlap" options. These settings are located in the "Format Data Series" task pane. This pane is accessed by selecting the data series and then clicking the "Format Selection" button on the Format tab, or by double-clicking the data series directly.

While the question incorrectly places the option on the "Design" tab, "Format Selection" is the only command listed that opens the necessary controls for this specific formatting task. The other options apply broader, predefined changes to the chart's overall structure or appearance.

A. Add Chart Element: This command is used to add, remove, or position chart components like titles, legends, and gridlines, not to adjust data point spacing.

C. Chart Styles: This applies a predefined gallery of visual styles, including colors and effects, to the entire chart, but does not offer direct control over data spacing.

D. Choose Chart Layout: This option (known as "Quick Layout") applies a predefined arrangement of chart elements, not the granular spacing between individual data points in a series.

1. Microsoft Support Documentation: In the article "Change the gap width in a chart

" the official procedure is detailed: "Right-click a data series

and then click Format Data Series." The "Format Data Series" pane that opens is the same one launched by the "Format Selection" command on the Format tab. This document confirms that neither the Design tab nor the other options are used for this task.

Source: Microsoft 365 Support

"Change the gap width in a chart

" Section: "Change the gap width and overlap."

2. Microsoft Learn (Official MO-211 Path): The learning path for the MO-211 exam

"Excel: Creating and formatting charts

" specifies the method for detailed formatting: "To format any element of a chart

you can double-select it to open a formatting task pane." This action opens the same pane as "Format Selection" and is the correct procedure for modifying series options like gap width.

Source: Microsoft Learn

Module: "Excel: Creating and formatting charts

" Unit: "Format charts."

3. University of Wisconsin-Madison Courseware: In their Excel 2019 course materials

the process for modifying chart data series is outlined. It specifies using the "Format" tab and the "Format Selection" button or right-clicking the element to access the format pane where gap width can be changed.

Source: University of Wisconsin-Madison

"Excel 2019 - Part 4: Charts

" Page 7

Section: "Formatting a Data Series."

The officially documented keyboard shortcut to insert a new worksheet in Microsoft Excel is Shift+F11. This command adds a new sheet to the left of the currently active sheet. An alternative shortcut for the same action is Alt+Shift+F1. The other options listed are not standard, default shortcuts for this function within Excel.

A. Ctrl+Alt+N is not a default keyboard shortcut for inserting a new sheet or any other common function in Microsoft Excel.

C. Ctrl+Shift+K is not a recognized default shortcut for inserting a new worksheet within the standard Excel environment.

D. Ctrl+Shift+N is a common shortcut in Windows File Explorer to create a new folder, not for inserting a new sheet in Excel.

1. Microsoft Support Documentation: "Keyboard shortcuts in Excel". Under the section "Work with worksheets

" the official documentation lists Shift+F11 and Alt+Shift+F1 as the shortcuts to "Insert a new worksheet."

2. University of Wisconsin-Madison

Division of Information Technology: "Excel: Keyboard Shortcuts (PC)". This university resource lists Shift + F11 under the "Worksheet" category with the description "Insert a new worksheet."

The Compatibility Checker is a diagnostic tool in Microsoft Excel designed to identify potential issues when a workbook is saved in an older file format (e.g., .xls for Excel 97-2003). It scans the workbook for features, functions, and formatting that are not supported or will function differently in the selected earlier version. The tool generates a report detailing any "significant loss of functionality" or "minor loss of fidelity," allowing the user to make informed decisions before saving to ensure the file remains usable for recipients with older software.

A. Creating a digital signature is handled by the 'Add a Digital Signature' feature, which is used for security and authentication.

C. Highlighting cells with errors is the function of the 'Error Checking' tool or can be achieved with conditional formatting rules.

D. Protecting a workbook from editing is accomplished using features like 'Protect Sheet,' 'Protect Workbook,' or 'Mark as Final.'

1. Microsoft Corporation. (n.d.). Check file compatibility with earlier versions of Excel. Microsoft Support. Retrieved from support.microsoft.com.

Reference Details: The article explicitly states

"When you create a workbook in a newer version of Excel

but it will be opened in an earlier version of Excel

it's a good idea to run the Compatibility Checker... The Compatibility Checker finds potential compatibility issues and helps you create a report so that you can resolve them."

2. University of Michigan. (n.d.). Saving Microsoft Excel Workbooks for Backwards Compatibility. ITS Documentation.

Reference Details: In the section on saving for older versions

the documentation explains that Excel automatically runs a "Compatibility Checker" to warn users about features that may be lost or changed when saving to the Excel 97-2003 (.xls) format. This confirms the tool's purpose is to check for version-specific feature compatibility.

The ROWS function is designed to return the total number of rows within a specified range, array, or table reference. When applied to a structured Excel table (e.g., ROWS(TableName)), it accurately returns the count of all data rows. This count directly corresponds to the index number of the last row in that table. This value is then frequently used as an argument within other functions, like INDEX, to dynamically reference the contents of the table's final row. Therefore, ROWS is the fundamental function for identifying the last row's position.

A. INDEX retrieves a value from a specified row number; it does not determine which row is the last one on its own.

B. COUNTA counts non-empty cells and is unreliable for finding the last row if the table contains blank cells within its data body.

C. VLOOKUP is a lookup function that searches for a specific value in a column, not one that references a row by its position.

1. Microsoft Corporation. (n.d.). ROWS function. Microsoft Support. Retrieved from https://support.microsoft.com/en-us/office/rows-function-b592593e-3f42-4620-b60b-20bdba34ae37. The documentation states

"Returns the number of rows in a reference or array

" which is the core requirement for identifying the last row's index in a table.

2. Microsoft Corporation. (n.d.). Using structured references with Excel tables. Microsoft Support. Retrieved from https://support.microsoft.com/en-us/office/using-structured-references-with-excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e. This official guide explains how functions operate on table objects

where ROWS(Table1) would count the data rows.

3. Microsoft Corporation. (n.d.). INDEX function. Microsoft Support. Retrieved from https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd. The syntax INDEX(array

rownum

[columnnum]) shows it requires the rownum as an input

which the ROWS function would provide to target the last row.

The COUNTIF function is the correct formula for this task. It is designed to count the number of cells within a specified range that meet a single, user-defined criterion. The syntax is COUNTIF(range, criteria), where 'range' is the group of cells to evaluate and 'criteria' is the condition, such as specific text, that a cell must meet to be counted. For example, the formula COUNTIF(A2:A10, "London") counts how many cells in the range A2:A10 contain the exact text "London".

A. COUNT() only counts cells that contain numerical values and ignores any cells containing text.

B. COUNTA() counts all non-empty cells in a range, regardless of their content (text, numbers, etc.), and cannot filter by specific criteria.

C. COUNTIFS() is used to count cells that meet multiple criteria across one or more ranges; COUNTIF is the more direct function for a single criterion.

1. Microsoft Corporation. (n.d.). COUNTIF function. Microsoft Support. Retrieved from https://support.microsoft.com/en-us/office/countif-function-e0de10c6-f885-4e71-abb4-1f464816df34.

Reference Point: The first paragraph states

"The COUNTIF function counts the number of cells that meet a criterion..." The article provides syntax and examples for counting cells with specific text.

2. Microsoft Corporation. (n.d.). COUNT function. Microsoft Support. Retrieved from https://support.microsoft.com/en-us/office/count-function-a59cd7fc-b623-4d93-87a4-d23bf411294c.

Reference Point: The "Syntax" section and initial description clarify that this function only counts cells containing numbers.

3. University of Wisconsin-Madison. (n.d.). Excel: Formulas - Using COUNTIF. Software Training for Students. Retrieved from https://it.wisc.edu/services/software-training-for-students-sts/self-study-training-resources/excel-formulas/using-countif/.

Reference Point: The section "Using COUNTIF with Text" explicitly demonstrates how to use the function to count cells based on a text criterion

stating

"The COUNTIF function can also be used to count the number of cells that contain a certain text."

The keyboard shortcut Ctrl + N is used to create a new, blank workbook in Microsoft Excel. A new workbook, by default, contains at least one new worksheet. While the specific shortcut to insert a new worksheet into an existing workbook is Shift + F11, this option is not provided. Among the given choices, Ctrl + N is the only command that results in the creation of a new worksheet, making it the correct answer in this context.

A. Ctrl + A: This shortcut selects all cells and objects in the active worksheet; it does not create a new one.

B. Ctrl + W: This shortcut closes the active workbook window.

C. Ctrl + S: This shortcut saves the active 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. (See the section "Ctrl key combination shortcuts

" which lists Ctrl+N for "Create a new

blank workbook.")

2. MIT Sloan School of Management. (2016). Excel 2016 Core: Part 1. Massachusetts Institute of Technology. Page 6

"File Tab & Backstage View

" lists Ctrl+N as the shortcut for "New Workbook."

3. University of Wisconsin-Madison. (n.d.). Excel: Keyboard Shortcuts. Software Training for Students. Retrieved from https://it.wisc.edu/services/software-training-for-students-sts/. (This resource

under its Excel materials

confirms Ctrl+N for creating a new workbook.)