A formula uses =SUM(Sheet1:Sheet6!D5). What must be true for this 3D reference to work correctly
A Sheets are protected
B Chart sheet exists
C Sheets are consecutive
D Cells are merged
A 3D reference like Sheet1:Sheet6 includes all sheets between them in workbook order. The sheets must exist and be consecutive in the tab sequence so Excel can sum the same cell across them.
In a multi-sheet workbook, what is the safest way to keep a constant like GST usable even if rows/columns move
A Relative reference
B Named Range
C Text to Columns
D Page Break
Named ranges refer to a cell by name rather than location. If you insert rows or columns, the named range still points to the correct value, reducing formula break risk in reports.
When VLOOKUP returns a wrong record in TRUE mode, the most common reason is
A Table has colors
B Sheet is protected
C File is XLSM
D Lookup column unsorted
TRUE mode uses approximate match and assumes the first column is sorted ascending. If it is not sorted, Excel may return an incorrect nearest match, causing wrong records in reports.
A VLOOKUP exact match fails even though the code looks same. A common hidden reason is
A Low RAM
B Wrong chart
C Extra spaces
D Page margins
Hidden leading or trailing spaces make text values different, so a lookup fails. Using TRIM to remove extra spaces or cleaning imported data often fixes such matching problems.
For a table where the return value column is left of the lookup column, which approach works best
A VLOOKUP FALSE
B INDEX-MATCH
C HLOOKUP TRUE
D SUMIF only
INDEX-MATCH can look up in one column and return values from any column, including columns to the left. This makes it more flexible than VLOOKUP for many layouts.
If you add new rows beyond a PivotTable’s fixed source range, what is required so Pivot includes them
A Change data source
B Edit chart title
C Freeze panes
D Add print area
PivotTables based on a fixed range do not automatically include new rows outside that range. You must change the data source or convert the range to a Table for auto expansion.
Which PivotTable setup most reduces future “missing new rows” problems
A Use merged cells
B Use Print Titles
C Use Excel Table
D Use page breaks
Using an Excel Table as the Pivot source lets the range expand automatically as new rows are added. This reduces errors and keeps Pivot summaries complete without frequent source updates.
A conditional formatting rule highlights wrong area. Which setting should be checked first
A Chart legend
B Print preview
C Sheet tab name
D Applies to range
Conditional formatting affects only the “Applies to” range. If formatting spreads unexpectedly, the applied range may be too large or incorrect. Fixing this range usually corrects the issue.
To highlight an entire row when the “Status” cell equals “Pending,” which type is most suitable
A Duplicate values
B Formula-based rule
C Data bars
D Color scales
A formula-based conditional formatting rule can lock the status column and apply formatting across the whole row. This is more flexible than built-in rules for row-level highlighting.
In Remove Duplicates, why is selecting the correct columns critical
A Wrong rows deleted
B Charts get removed
C Macros stop running
D Fonts reset
Remove Duplicates deletes rows based on selected columns. If you select too few columns, different records may look identical and be removed incorrectly, causing loss of valid distinct data.
When Text to Columns splits data, what common risk can damage existing data
A Changing chart type
B Adding sheet tabs
C Locking workbook
D Overwriting adjacent cells
Text to Columns writes split results into neighboring columns. If those columns already contain data, they may be overwritten. Always ensure empty columns or choose a safe destination first.
Why does Excel sometimes remove leading zeros from codes during CSV import
A Treated as date
B Treated as chart
C Treated as number
D Treated as macro
Excel often auto-detects numeric-looking codes as numbers, removing leading zeros. Importing the column as Text preserves codes like 0012 exactly, which is crucial for IDs and lookups.
Which approach best preserves phone numbers with leading zeros and long digits
A Store as Date
B Store as Text
C Store as Percent
D Store as Currency
Phone numbers and IDs should be stored as Text to prevent leading zeros from dropping and to avoid scientific notation. This keeps the value unchanged for display and matching.
If a chart shows wrong categories after adding rows, what should you adjust
A Page margins
B Sheet protection
C Freeze panes
D Chart data range
Charts use a fixed data range unless updated. When you add new rows, you must update the chart range or use an Excel Table so the chart expands automatically with the data.
A Pivot Chart is best described as
A Chart linked to Pivot
B Chart without data
C Chart for printing
D Chart for macros
A Pivot Chart is connected to a PivotTable. When pivot fields or filters change, the chart updates automatically. This makes it ideal for interactive dashboards and quick analysis.
Which scenario most benefits from Goal Seek
A Count duplicates
B Split text cells
C Find needed input
D Rename workbook
Goal Seek adjusts one input cell to reach a target formula result, like required marks to reach a target percentage. It is useful for planning when output is fixed and input is unknown.
A workbook is protected for structure. Which action is restricted
A Change cell value
B Add new sheet
C Sort a table
D Use AutoSum
Protect Workbook structure prevents adding, deleting, moving, or renaming sheets. It does not automatically block editing cell values unless the sheet itself is protected separately.
In sheet protection, to allow users to type only in specific cells, what must you do before protecting
A Rename the sheet
B Add print area
C Add data bars
D Unlock input cells
By default, all cells are locked. You must unlock allowed input cells first, then protect the sheet. Users can then edit only unlocked cells while formulas remain protected.
IFERROR is commonly used in dashboards mainly to
A Increase file size
B Hide error codes
C Change chart colors
D Enable macros
IFERROR replaces errors like #N/A or #DIV/0! with a chosen value such as blank or 0. This makes dashboards cleaner and prevents error messages from confusing users.
Which error indicates Excel cannot understand a typed function or name
A #REF!
B #DIV/0!
C #NAME?
D #NUM!
#NAME? appears when a function name is misspelled, quotes are missing, or a named range is invalid. Fixing the formula text usually resolves the error.
Which function helps return “Not Found” instead of #N/A from a lookup
A ROUND
B LEN
C MAX
D IFERROR
Wrapping a lookup inside IFERROR allows showing a friendly message like “Not Found” when the lookup fails. This improves report clarity and avoids raw error codes.
In VLOOKUP, which argument decides exact vs approximate match
A Col index
B Range lookup
C Lookup value
D Table array
The last argument controls match type. FALSE gives exact match, TRUE gives approximate match. Choosing the correct match type is crucial for accuracy when working with IDs or slabs.
In VLOOKUP, which argument is most sensitive to inserting new columns inside the table
A Col index
B Lookup value
C Range lookup
D Sheet name
Col index is a number that counts columns from the start of the table array. If you insert a column inside the table, the index may point to the wrong return field.
Which lookup combination avoids “fixed column number” problems
A VLOOKUP TRUE
B HLOOKUP TRUE
C INDEX-MATCH
D COUNTIF only
INDEX-MATCH uses dynamic positions rather than a fixed column index. If columns are inserted or moved, MATCH still finds the correct position, keeping the lookup result accurate.
A chart needs to show values for each bar directly. Which element should be enabled
A Gridlines
B Legend
C Axis title
D Data labels
Data labels show actual numbers on chart elements. They improve readability when viewers need exact values quickly, especially in printed reports or presentation slides.
Which chart type is generally best for many categories and easy comparison
A Pie chart
B Bar chart
C Doughnut chart
D Radar chart
Bar charts handle many categories well and allow easy comparison of values. Pie charts become cluttered with many slices, making them less readable for large category lists.
To ensure charts and pivots auto-expand with new rows, best practice is
A Merge header cells
B Convert to Table
C Hide gridlines
D Use Print Titles
Excel Tables expand automatically when you add rows. Charts and PivotTables linked to a Table range are less likely to miss new data, improving accuracy and reducing maintenance.
Which function rounds to nearest multiple like 0.05 in pricing
A ROUNDUP
B INT
C MIN
D MROUND
MROUND rounds a number to a specified multiple, useful for pricing steps like nearest 0.05. This keeps amounts aligned to allowed increments without manual rounding.
Which function rounds a number to the nearest specified multiple
A ROUNDUP
B ROUNDDOWN
C MROUND
D INT
In an Excel Table, why do formulas often stay consistent automatically
A Print area locks cells
B Structured references expand
C Freeze panes edits rows
D Filter deletes data
Tables use structured references and automatically extend formulas to new rows. This reduces missed calculations and keeps logic consistent as the dataset grows over time.
When sorting by one column, what option prevents only that column from moving
A Expand selection
B Sort left-right
C Clear filter
D Wrap text
Expand selection ensures the whole table sorts together. If not used, Excel might sort a single column alone, causing mismatched rows and wrong data relationships across columns.
Which feature repeats a header row on each printed page but does not affect screen scrolling
A Freeze Panes
B Wrap Text
C Print Area
D Print Titles
Print Titles affects printing only, repeating selected rows/columns on each page. Freeze Panes affects screen view only. Using the correct one avoids confusion in print vs view behavior.
Which feature keeps headers visible while scrolling but does not repeat in printing
A Print Titles
B Print Area
C Freeze Panes
D Page Setup
Freeze Panes locks rows/columns on screen for easier viewing while scrolling. It does not automatically repeat headers during printing, which requires Print Titles.
Which tool ensures only valid list values like “Yes/No” can be entered
A Conditional formatting
B Data validation list
C Remove duplicates
D Chart filter
Data Validation with List restricts inputs to defined items or a range. It prevents spelling mistakes and ensures consistent entries for filtering, pivoting, and accurate summaries.
In Data Validation, which alert type warns but allows invalid value if chosen
A Stop
B Block
C Lock
D Warning
Warning alert notifies the user about invalid entry but offers a choice to continue. It is useful when flexibility is needed and strict blocking may disrupt legitimate exceptions.
What is the key benefit of Advanced Filter over normal filter for unique list creation
A Add chart automatically
B Format cells fast
C Copy unique records
D Protect worksheet
Advanced Filter can extract unique records and copy them to another location, creating a clean unique list without deleting anything from the original dataset.
Which operation can accidentally break formulas by shifting referenced columns
A Changing row height
B Inserting columns
C Printing sheet
D Wrapping text
Inserting columns can change relative references and column index numbers in VLOOKUP. Using absolute references, named ranges, or INDEX-MATCH helps reduce such formula break issues.
A formula contains =A1/B1 and B1 is blank. Which error appears
A #DIV/0!
B #REF!
C #VALUE!
D #N/A
Dividing by a blank cell is treated as dividing by zero in many cases, causing #DIV/0!. Add checks like IF(B1=0,””,A1/B1) to avoid this error.
A formula shows #VALUE! after adding text and numbers. What is likely wrong
A Sheet missing
B Printer offline
C Chart deleted
D Wrong data type
#VALUE! occurs when a calculation expects numbers but receives text or incompatible types. Cleaning inputs or converting text numbers using VALUE can help fix the issue.
Which formula tool helps identify the exact cell used inside a formula quickly
A Print preview
B Trace precedents
C Wrap text
D Freeze panes
Trace Precedents shows arrows from cells that feed into a formula. It helps audit formulas and verify data sources, useful for checking errors in complex spreadsheets.
Which formula tool shows where a cell’s value is used elsewhere
A Clear rules
B Goal seek
C Trace dependents
D Data bars
Trace Dependents draws arrows to cells that depend on the selected cell. It helps understand impact before changing a key input, reducing risk of breaking important reports.
When a sheet name has spaces, which reference is correct for cell C3
A My Sheet!C3
B “My Sheet”!C3
C (My Sheet)!C3
D ‘My Sheet’!C3
Excel uses single quotes around sheet names containing spaces. This avoids formula errors and ensures correct reference to the intended worksheet and cell.
Which function returns current date and time and updates on recalculation
A TODAY
B NOW
C DATE
D DAY
NOW returns both date and time and updates when recalculated. It is useful for timestamps, tracking last update times, and logging when a report was refreshed.
Which function returns current date only, without time portion
A TODAY
B NOW
C YEAR
D MONTH
TODAY returns only the current date. It updates automatically and is commonly used in reports for deadlines, day counting, and date-based calculations where time is not required.
Which function converts numeric-looking text to a real number for calculation
A TEXT
B TRIM
C VALUE
D PROPER
VALUE converts text like “123” into a numeric value that Excel can calculate. It helps fix imported datasets where numbers are stored as text and formulas fail.
Which text cleanup function is most useful before doing lookups on imported codes
A LEN
B MID
C RIGHT
D TRIM
TRIM removes extra spaces that cause matching failures. Cleaning codes with TRIM before using VLOOKUP or MATCH improves lookup accuracy and reduces “not found” errors.
Which paste option converts formulas to fixed numbers without changing formatting
A Paste Formats
B Paste Values
C Paste Link
D Paste Comment
Paste Values replaces formulas with their displayed results while keeping formatting. It is useful for final reports where calculations should not change if inputs are edited later.
Which format supports saving macros inside the workbook
A XLSX
B CSV
C XLSM
D PDF
XLSM is the macro-enabled workbook format that stores VBA code. Use it when macros are required, and follow security practices since macros can contain unsafe code.
Which feature records a sequence of actions for automation
A Pivot Table
B Data Validation
C Text to Columns
D Macro Recorder
Macro Recorder captures actions like formatting and filtering. The recorded macro can be run later to repeat the exact steps, saving time and reducing manual mistakes.
Which issue most often makes Text to Columns split incorrectly
A Wrong theme
B Wrong printer
C Wrong delimiter
D Wrong sheet color
Text to Columns depends on choosing the correct delimiter such as comma, space, or hyphen. Selecting the wrong delimiter causes incorrect splitting, so check sample preview before finishing.