MS Excel — Set 2

Correct Answer: A. Shift + F11

• **Shift + F11** = the keyboard shortcut that instantly inserts a new blank worksheet directly to the left of the currently active sheet tab, without requiring any mouse interaction or menu navigation. • **Key fact** — the new sheet is automatically named Sheet followed by the next available number (Sheet2, Sheet3, etc.); users can rename it by double-clicking the tab, and the sheet order can be rearranged by dragging tabs. • An alternative method is to click the + (plus) icon to the right of all sheet tabs at the bottom of the workbook, but Shift+F11 is faster when working in a keyboard-driven workflow. • 💡 Option B (Ctrl + N) is wrong because Ctrl+N opens a brand new, separate workbook in a new window — it does not add a sheet to the existing workbook; Option C (Alt + W) is wrong because Alt+W activates the View tab on the Ribbon in Excel — it does not insert anything; Option D (Ctrl + Shift + W) is wrong because this key combination has no standard assigned function in Excel and will not insert a worksheet.

Correct Answer: D. #######

• **#######** = a display indicator (not a calculation error) that Excel shows when a cell's column width is too narrow to fully render the number, date, or time stored inside; the actual value is intact and unaffected. • **Key fact** — fixing ###### requires only a visual adjustment: double-clicking the right edge of the column header auto-fits the width, or dragging the column border wider reveals the value immediately. • This indicator also appears for negative date/time values, because Excel's date system cannot display dates before January 1, 1900, and widening the column does not resolve that case. • 💡 Option A (#VALUE!) is wrong because #VALUE! appears when a formula uses the wrong data type — for example, trying to add a number to text like =5+"apple"; Option B (#REF!) is wrong because #REF! appears when a formula references a cell that has been deleted or moved out of range; Option C (#DIV/0!) is wrong because #DIV/0! appears when a formula attempts to divide a number by zero or by an empty cell.

Correct Answer: A. Pivot Table

• **Pivot Table** = an interactive data summarization tool that lets users drag and drop field names into rows, columns, values, and filters to instantly reorganize thousands of records into meaningful cross-tabulations, totals, or averages — without writing a single formula. • **Key fact** — Pivot Tables are dynamic: changing the source data and clicking Refresh updates all calculations automatically, making them ideal for recurring monthly or quarterly reports where the data volume changes. • A Pivot Table does not modify the source data; it creates a separate summary in a new area, so the original dataset remains untouched and can be analyzed from multiple angles simultaneously. • 💡 Option B (Clip Art) is wrong because Clip Art is an image library for inserting decorative pictures into documents — it has no data analysis capability; Option C (Smart Art) is wrong because Smart Art creates visual diagrams like org charts and process flows from typed text — it does not process numerical data; Option D (Hyperlink) is wrong because a Hyperlink creates a clickable link to a URL, file, or cell address — it is a navigation tool, not a data analysis feature.

Correct Answer: D. F2

• **F2** = the shortcut key that activates edit mode for the active cell, placing the text cursor at the end of the cell's content so the user can modify it directly without needing to double-click or use the Formula Bar. • **Key fact** — when F2 is pressed, Excel enters Edit mode (visible in the Status Bar at the bottom left), and arrow keys then move the cursor within the cell content instead of navigating to other cells — an important behavior change. • F2 also works inside the Formula Bar: pressing F2 while the Formula Bar is focused highlights the referenced cell ranges in the formula with color-coded borders, making formula auditing much easier. • 💡 Option A (F1) is wrong because F1 opens the Excel Help pane — it does not enable editing of any cell; Option B (F4) is wrong because F4 in edit mode toggles a cell reference between relative (A1), mixed ($A1, A$1), and absolute ($A$1) — it does not initiate editing; Option C (F3) is wrong because F3 opens the Paste Name dialog box for inserting named ranges into formulas — it does not activate cell editing.

Correct Answer: A. MAX

• **MAX** = a statistical function that evaluates all numeric values in the specified range and returns the single largest one; it automatically ignores empty cells, text strings, and logical values (TRUE/FALSE) within the selection. • **Key fact** — MAX and LARGE are related but distinct: MAX always returns the single highest value (equivalent to LARGE(range,1)), while LARGE(range, n) can return the nth-largest value, such as the 2nd or 3rd highest. • The paired function MIN finds the lowest value; using both together gives the data range span, and their difference (MAX−MIN) is the statistical range of the dataset. • 💡 Option B (LARGE) is wrong because LARGE requires two arguments — the range and the rank number (e.g., =LARGE(A1:A10,1)) — whereas MAX simply takes the range; LARGE is used when you need ranks beyond the top value; Option C (MIN) is wrong because MIN is the exact opposite of MAX — it returns the smallest numeric value in a range; Option D (HIGH) is wrong because HIGH is not a real Excel function — it does not exist in Excel's function library and will return a #NAME? error.

Correct Answer: D. Freeze Panes

• **Freeze Panes** = a View tab feature that locks specified rows at the top and/or columns on the left so they remain visible on screen no matter how far the user scrolls down or to the right through a large dataset. • **Key fact** — Excel offers three Freeze options: "Freeze Top Row" locks only row 1, "Freeze First Column" locks only column A, and "Freeze Panes" (custom) locks everything above and to the left of the selected cell simultaneously. • To unfreeze, go to View → Freeze Panes → Unfreeze Panes; there is no keyboard shortcut, but the operation is quick and does not affect any data or formulas. • 💡 Option A (Split View) is wrong because Split View divides the worksheet window into two or four independent scrollable panes — both panes still scroll, so headers in one pane can disappear as you scroll in another; Option B (Hide) is wrong because Hide makes rows or columns invisible entirely — they cannot be seen at all, and the data is inaccessible until unhidden; Option C (Zoom) is wrong because Zoom only changes the magnification level of the entire view — it does not lock any rows or columns in place.

Correct Answer: A. Inserts Current Date

• **Inserts Current Date** = pressing Ctrl+; stamps today's date as a static, hardcoded value into the active cell; the date comes from the computer's system clock and is written in the default date format set in Excel's regional settings. • **Key fact** — the date inserted by Ctrl+; is frozen at the moment of entry and will not update the next day, making it ideal for timestamping records or audit logs where the entry date must remain permanent. • To insert both the current date and time, users can press Ctrl+; then Space then Ctrl+Shift+: in sequence, combining both shortcuts into a single cell entry. • 💡 Option B (Inserts Current Time) is wrong because the current time shortcut is Ctrl+Shift+: (colon), not Ctrl+; — these two shortcuts are often confused but insert different things; Option C (Clears Cell) is wrong because the shortcut to clear a cell's content is the Delete key — Ctrl+; adds data rather than removing it; Option D (Bold Text) is wrong because the bold toggle shortcut is Ctrl+B — the semicolon shortcut has no text-formatting function.

Correct Answer: D. Pie Chart

• **Pie Chart** = a circular chart divided into slices where each slice represents a category's proportional share of the total (100%); it is the standard choice when the goal is to visualize how individual parts contribute to a whole. • **Key fact** — pie charts work best with 5 or fewer categories; with more slices, the chart becomes cluttered and individual proportions become hard to distinguish, which is why data analysts often switch to a bar chart for larger category sets. • A Donut Chart is a variant of the pie chart with a hollow center, used in modern dashboards; it conveys the same part-to-whole relationship while allowing a summary label in the center. • 💡 Option A (Bar Chart) is wrong because bar charts compare discrete categories against each other along an axis — they show magnitude differences between categories, not each category's share of a total; Option B (Line Chart) is wrong because line charts show trends over time by connecting data points with a continuous line — they are not designed to represent proportional composition; Option C (Scatter Plot) is wrong because scatter plots display the correlation or relationship between two numeric variables as points on an XY grid — they have nothing to do with part-to-whole proportions.

Correct Answer: A. VLOOKUP

• **VLOOKUP** = Vertical Lookup; it searches down the leftmost column of a table array for a specified lookup value and then returns the value from a chosen column in the same row — the "V" stands for vertical, reflecting that the search travels down rows. • **Key fact** — VLOOKUP has four arguments: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]); setting the fourth argument to FALSE (or 0) forces an exact match, while TRUE performs an approximate match that requires the lookup column to be sorted ascending. • VLOOKUP can only search the first column of a table and retrieve values to the right; to search from any column or retrieve values to the left, the newer XLOOKUP or INDEX/MATCH combination must be used. • 💡 Option B (LOOKUP) is wrong because LOOKUP searches a single row or column and has a simpler, less flexible syntax — it always performs approximate matching and cannot do exact-match lookups the way VLOOKUP can; Option C (HLOOKUP) is wrong because HLOOKUP is Horizontal Lookup — it searches across the top row of a table and retrieves values from rows below, which is the opposite direction from VLOOKUP; Option D (SEARCH) is wrong because SEARCH finds the position of one text string within another text string — it has no table-lookup or data-retrieval capability.

Correct Answer: D. Copies or extends a series

• **Copies or extends a series** = the Fill Handle either copies the selected cell's content to adjacent cells when dragged, or intelligently extends recognized patterns — such as Mon→Tue→Wed, Jan→Feb→Mar, 1→2→3 — by detecting the sequence and continuing it automatically. • **Key fact** — when a formula is dragged with the Fill Handle, relative cell references inside the formula automatically adjust to match each new position (e.g., =A1+B1 becomes =A2+B2 in the next row), while absolute references ($A$1) remain fixed. • Double-clicking the Fill Handle instead of dragging it auto-fills the formula downward to match the length of the adjacent data column, which is far faster when filling thousands of rows. • 💡 Option A (Deletes data) is wrong because dragging the Fill Handle over existing cells replaces them with the copied value — it writes new data rather than erasing content; the Delete key is used to remove data; Option B (Protects the cell) is wrong because cell protection is configured through Format Cells → Protection or the Review tab — the Fill Handle has no security or lock function; Option C (Changes font size) is wrong because font size is controlled through the Home tab Ribbon or the Format Cells dialog — the Fill Handle exclusively handles copying and series-filling operations.