MS Excel — Set 3

Correct Answer: D. $A$1

• **$A$1** = a fully absolute cell reference where the dollar sign before the column letter (A) locks the column and the dollar sign before the row number (1) locks the row; this reference never changes when the formula is copied or filled to any other cell. • **Key fact** — the F4 key cycles through all four reference types in sequence: A1 (relative) → $A$1 (absolute) → A$1 (row-locked) → $A1 (column-locked) → back to A1; this shortcut eliminates the need to type dollar signs manually. • Absolute references are essential for formulas that refer to a fixed input cell shared by many calculations — such as a tax rate in B1 used across hundreds of rows — because copying the formula without $ would shift B1 to B2, B3, and so on incorrectly. • 💡 Option A (A1) is wrong because A1 is a purely relative reference — both the column and row shift when copied, so =A1 in cell C3 becomes =B2 if copied one cell up and to the right; Option B ($A1) is wrong because $A1 is a mixed reference with only the column locked — the row number still changes when copied up or down; Option C (A$1) is wrong because A$1 is another mixed reference with only the row locked — the column letter still changes when copied left or right.

Correct Answer: C. IF

• **IF** = Excel's primary decision-making function with the syntax =IF(logical_test, value_if_true, value_if_false); it evaluates a condition and delivers one of two outcomes — making it the foundation of conditional logic in any spreadsheet. • **Key fact** — up to 64 IF functions can be nested inside one another (=IF(A1>90,"A",IF(A1>80,"B","C"))) to handle multiple conditions, though for complex branching the newer IFS function (Excel 2019+) provides a cleaner syntax. • IF is frequently combined with AND and OR inside the logical_test argument — for example, =IF(AND(A1>0,B1>0),"Both positive","Check values") — to test multiple conditions simultaneously. • 💡 Option A (AND) is wrong because AND checks whether all specified conditions are TRUE and returns only TRUE or FALSE — it cannot return custom text or numbers based on a condition on its own; Option B (OR) is wrong because OR checks whether at least one condition is TRUE and also returns only TRUE or FALSE, not user-defined values; Option D (NOT) is wrong because NOT reverses a single logical value (NOT(TRUE) = FALSE) — it is a single-operator function with no branching capability.

Correct Answer: A. Alt + =

• **Alt + =** = the AutoSum shortcut that automatically inserts a =SUM() formula in the selected cell; Excel intelligently guesses the range of adjacent numbers above or to the left and pre-selects it, so the user just presses Enter to confirm. • **Key fact** — pressing Alt+= when a range of cells is already selected (rather than a single blank cell) immediately calculates and enters the sum without showing a preview, making it a one-keystroke total for highlighted data. • AutoSum also works for multiple non-contiguous columns at once: select the blank row below several columns of data and press Alt+= to insert individual SUM formulas in each column simultaneously. • 💡 Option B (Ctrl + S) is wrong because Ctrl+S is the universal Save shortcut — it saves the workbook to disk and has nothing to do with summing data; Option C (Alt + S) is wrong because Alt+S activates the Send menu in older Office versions or has no assigned function in standard Excel — it does not trigger AutoSum; Option D (Shift + A) is wrong because Shift+A simply types a capital "A" into the active cell — it is not a named Excel command shortcut.

Correct Answer: A. Data Validation

• **Data Validation** = a Data tab feature that enforces input rules on a cell, such as allowing only whole numbers between 1 and 100, dates within a range, text of a specific length, or selections from a custom drop-down list — rejecting or warning about any entry that violates the rule. • **Key fact** — Data Validation can display a custom Input Message that appears as a tooltip when the user clicks the cell (guiding correct entry before they type), and an Error Alert that pops up if the rule is violated, with Stop/Warning/Information severity levels. • Drop-down lists created with Data Validation (using the List source option) are one of the most common uses — they let users pick from predefined values, reducing typos and ensuring data consistency across large entry forms. • 💡 Option B (Data Consolidation) is wrong because Data Consolidation combines data from multiple ranges or worksheets into a single summary range — it processes existing data rather than restricting new input; Option C (Filter) is wrong because Filter hides rows that do not match selected criteria for viewing purposes — it does not prevent any data from being entered into cells; Option D (Sort) is wrong because Sort reorders existing rows based on cell values — it rearranges data but imposes no restriction on what can be entered.

Correct Answer: C. Workbook

• **Workbook** = the complete Excel file (saved as .xlsx, .xlsm, etc.) that acts as a container holding one or more individual worksheets (also called sheets or tabs); when you open or save an Excel file, you are working with the entire workbook. • **Key fact** — a single workbook can contain up to 255 worksheets by default (though this is limited by available memory), and each sheet has its own independent grid of rows and columns while sharing the same workbook-level named ranges, styles, and connections. • Workbooks support 3D references, allowing a formula in one sheet to pull data from the same cell across multiple sheets (e.g., =SUM(Sheet1:Sheet3!B5) sums cell B5 across three sheets), which is only possible because all sheets share one workbook. • 💡 Option A (Workfolder) is wrong because "workfolder" is not a Microsoft Excel term — it sounds plausible but has no defined meaning in the Excel object model or user interface; Option B (Worklist) is wrong because "worklist" is also not an Excel term — it is sometimes used informally in project management but refers to nothing specific within Excel; Option D (Workgroup) is wrong because "workgroup" is a networking term referring to a group of computers on a local network — it is unrelated to the Excel file structure.

Correct Answer: C. Data

• **Data** = the Ribbon tab dedicated to all data management operations; it contains the Sort & Filter group (with Sort A→Z, Sort Z→A, Advanced Filter), the Data Tools group (Data Validation, Remove Duplicates, Text to Columns), and the Forecast group (What-If Analysis, Forecast Sheet). • **Key fact** — while the Home tab does include basic sort/filter buttons in its Editing group for quick access, the full-featured sorting (multi-level sort by multiple columns) and advanced filter options are exclusively available in the Data tab. • The Data tab also houses the Get & Transform Data group (formerly Power Query), which allows importing and cleaning data from external sources like databases, web pages, and CSV files — making it the hub for all data pipeline work. • 💡 Option A (Home) is wrong because the Home tab is focused on formatting — fonts, alignment, number formats, styles, and basic find/sort shortcuts — not the complete data management toolkit; Option B (Insert) is wrong because the Insert tab handles adding objects to the sheet: charts, tables, pictures, shapes, PivotTables, and hyperlinks — not data sorting or filtering; Option D (Review) is wrong because the Review tab contains proofing tools (Spelling, Thesaurus), commenting features, and sheet/workbook protection options — it has no Sort or Filter commands.

Correct Answer: C. Cell width insufficient

• **Cell width insufficient** = the ###### display appears when the column is too narrow to show the full numeric value, date, or time stored in a cell; Excel fills the cell with hash symbols instead of showing partial or misleading data. • **Key fact** — ###### is not a calculation error — the underlying value is completely correct and intact; it is purely a display problem that disappears the instant the column is wide enough, which can be achieved by double-clicking the column border to auto-fit. • A less common cause of ###### is a negative date or time value: if a date formula produces a result before January 1, 1900 (Excel's minimum date), ###### appears and widening the column will not fix it. • 💡 Option A (Formula error) is wrong because formula errors in Excel have specific named codes like #VALUE!, #NAME?, or #N/A — not ######; Option B (Division by zero) is wrong because dividing by zero produces the specific error #DIV/0!, not ######; Option D (Reference deleted) is wrong because a deleted cell reference produces the error #REF!, which clearly indicates a broken reference — not a column width issue.

Correct Answer: D. CONCATENATE

• **CONCATENATE** = a text function that merges two or more text strings or cell values into one continuous string; the syntax is =CONCATENATE(text1, text2, ...) and it can join up to 255 arguments, each of which can be a cell reference, a literal string in quotes, or a number. • **Key fact** — Excel 2016 introduced CONCAT (without the "ENATE") and TEXTJOIN as modern replacements; TEXTJOIN is especially powerful because it accepts a delimiter (like a comma or space) and can automatically skip empty cells, unlike CONCATENATE which cannot. • The ampersand operator (&) performs the same joining operation as CONCATENATE without needing a function call — for example, =A1&" "&B1 joins first and last names with a space, and is preferred by most Excel users for its brevity. • 💡 Option A (JOIN) is wrong because JOIN is not a native Excel worksheet function — it exists in Google Sheets but not in Excel, and typing =JOIN() in Excel returns a #NAME? error; Option B (LINK) is wrong because LINK is not an Excel text function — this name does not exist in Excel's function library in any version; Option C (ADD) is wrong because ADD is not an Excel function either — addition in Excel is performed with the + operator or the SUM function, both of which work on numbers, not text strings.

Correct Answer: A. Ctrl + Space

• **Ctrl + Space** = the keyboard shortcut that selects all 1,048,576 cells in the entire column of the active cell, highlighting the column from row 1 to the last row — useful for applying column-wide formatting or inserting column operations. • **Key fact** — the complementary shortcut Shift+Space selects the entire row of the active cell; knowing both shortcuts together allows rapid selection of full rows or columns without touching the mouse or clicking row/column headers. • After pressing Ctrl+Space, pressing Ctrl+Shift+End extends the selection from the current column all the way to the last used cell in the worksheet, enabling efficient range operations across large datasets. • 💡 Option B (Ctrl + C) is wrong because Ctrl+C copies the selected cell or range to the clipboard — it is the universal copy shortcut and makes no selection changes; Option C (Shift + Space) is wrong because Shift+Space selects the entire row of the active cell, not the column — the two shortcuts are often swapped in memory; Option D (Ctrl + A) is wrong because Ctrl+A selects all cells in the current data region first, and pressing it again selects the entire worksheet — it never selects just one column.

Correct Answer: C. Macro

• **Macro** = a recorded or written sequence of actions stored in VBA (Visual Basic for Applications) that can be replayed with a single keystroke or button click; the macro recorder captures mouse clicks and keystrokes and converts them into VBA code automatically, requiring no programming knowledge to create basic macros. • **Key fact** — macros are stored inside .xlsm (macro-enabled) workbooks; if a workbook with macros is saved as .xlsx, all macro code is permanently deleted, which is why Excel always warns users before stripping macros on format change. • Macros can be assigned to custom buttons on the Quick Access Toolbar or the Ribbon, or triggered via Ctrl+shortcut keys, making them accessible to non-technical users who need to run complex multi-step processes in one click. • 💡 Option A (Template) is wrong because a Template is a pre-formatted workbook structure (.xltx) that provides a starting layout — it saves setup time but does not automate actions or record user interactions; Option B (Solver) is wrong because Solver is an optimization add-in that finds the best value for a target cell by changing variable cells within set constraints — it performs mathematical optimization, not task automation; Option D (Goal Seek) is wrong because Goal Seek works backwards from a desired formula result to find the required input value — it is a single-step what-if calculation tool, not a general-purpose task automator.