If you want the same fixed tax rate cell to stay constant in many formulas, which reference should be used
A Relative reference
B Mixed reference
C Sheet tab reference
D Absolute reference
Absolute references lock both row and column using $ signs, like $E$1. This keeps the tax rate cell fixed when copying formulas, preventing wrong shifting and ensuring accurate calculations.
When copying a formula from B2 to B6, which part changes in a relative reference like A2
A Column only
B Both change
C Row only
D Nothing changes
Copying down changes the row number in relative references. A2 becomes A6 when copied to row 6. This helps apply the same formula pattern across multiple records.
Which feature is best for creating a reusable constant like “GST” used in many formulas
A Named Range
B Page Setup
C Text to Columns
D Print Titles
Named ranges assign meaningful names to cells like a GST rate. Using names makes formulas easier to understand, reduces mistakes, and keeps references stable even if sheet layout changes.
Which formula correctly links a value from Sheet2 cell B5 into Sheet1
A Sheet2:B5
B Sheet2.B5
C Sheet2!B5
D Sheet2/B5
Excel uses the exclamation mark for sheet references. Sheet2!B5 pulls the value from cell B5 on Sheet2, commonly used when building summary sheets from multiple worksheets.
If a sheet name contains spaces, which reference format is correct
A Sales Data!A1
B ‘Sales Data’!A1
C “Sales Data”!A1
D (Sales Data)!A1
Sheet names with spaces must be enclosed in single quotes. For example, ‘Sales Data’!A1 ensures Excel reads the sheet name correctly and avoids reference errors.
Which tool is best to convert a range into a structured table with automatic filters
A Format as Table
B Freeze Panes
C Print Area
D Goal Seek
Format as Table converts data into an Excel Table with built-in filter arrows, banded rows, and structured references. It also expands automatically when new rows are added.
In an Excel Table, which feature adds a quick summary row at the bottom
A Print Titles
B Flash Fill
C Total Row
D Text Split
Total Row adds a bottom row where you can choose SUM, COUNT, AVERAGE, etc. It updates automatically as table data changes, helping fast totals without manual formulas.
When sorting a full table by one column, which option should be selected to avoid record mismatch
A Shrink selection
B Hide selection
C Lock selection
D Expand selection
Expand selection ensures Excel sorts the entire dataset together, keeping rows aligned across columns. This prevents mismatching names and marks, which happens if only one column is sorted.
Which sorting type orders values using a custom list like Mon–Sun
A Custom Sort
B Descending sort
C Ascending sort
D Filter sort
Custom Sort can use custom lists, such as days or months, to sort in logical order rather than alphabetical order. This is useful for schedules and month-wise reports.
Which filter feature can copy filtered results to another place without changing original data
A Clear Filter
B Text Filter
C Advanced Filter
D Color Filter
Advanced Filter can filter using criteria and then copy results to another range. This helps create a separate clean list, like unique records, without deleting or altering the original data.
Which conditional formatting option uses a formula to decide formatting
A Data bars
B Formula rule
C Color scales
D Duplicate values
A formula-based conditional formatting rule lets you apply formatting based on custom logic, such as highlighting full rows where status is “Pending” or marks are below passing value.
In conditional formatting, which setting shows the target range where the rule applies
A Rule name
B Data label
C Applies to
D Axis title
“Applies to” defines the range of cells affected by the conditional formatting rule. Setting it correctly prevents formatting unwanted areas and ensures rules work only on intended data.
Which chart type is best for showing relationship between two numeric variables
A Scatter chart
B Pie chart
C Column chart
D Bar chart
Scatter charts plot two numeric values on X and Y axes. They help identify relationships, trends, and outliers, such as marks vs attendance or height vs weight.
Which chart option helps display percentages on pie slices
A Axis labels
B Gridlines
C Data labels
D Trendline
Data labels on a pie chart can show percentages, values, or category names. Showing percentages helps quickly understand each part’s share of the total without manual calculations.
Why are pie charts avoided for many categories
A Needs time data
B No legend support
C No labels possible
D Too many slices
With many categories, pie slices become too small and labels overlap. This reduces readability. Bar or column charts are better for comparing many categories clearly.
Which feature creates tiny trend charts inside cells for dashboards
A Sparklines
B Pivot Charts
C Area charts
D Stock charts
Sparklines are small in-cell charts showing trends across a row of values. They are useful in dashboards to quickly visualize patterns without using large charts.
In PivotTable, which feature groups dates into months or years
A Refresh
B Drill down
C Group
D Subtotal
PivotTable Grouping can group date fields into months, quarters, or years. This converts daily records into meaningful summaries, saving time and avoiding extra helper columns.
Which PivotTable area is used to filter the whole pivot report by a field
A Rows area
B Filters area
C Values area
D Data area
The Filters area applies a filter to the entire PivotTable report. Selecting a value like Year or Region changes the full summary output without touching the source data.
Which PivotTable action updates totals after source data is edited
A Refresh
B Print Preview
C Wrap Text
D Protect Sheet
PivotTables may show old results after data changes. Refresh recalculates the summary based on the updated source data, ensuring correct totals and accurate analysis.
Which option shows PivotTable values as % of grand total
A Report Layout
B Pivot Style
C Show Values As
D Change Chart
“Show Values As” converts pivot totals into percentages of grand total or row/column totals. This helps compare category contributions without adding separate percentage formulas.
Which Data Validation alert blocks invalid input completely
A Warning
B Information
C Soft alert
D Stop
Stop alert prevents entry of invalid data. It forces the user to correct input, which is important for strict fields like marks range, IDs, or allowed list values.
Which Data Validation alert warns but still allows entry if user chooses
A Stop
B Block
C Warning
D Deny
Warning alert shows a message for invalid input but allows the user to continue. It is used when guidance is needed but strict blocking might not be required.
Which function helps avoid showing any error by returning a custom result
A IF
B IFERROR
C ROUND
D CONCAT
IFERROR catches any formula error and returns a chosen output like blank or 0. It keeps dashboards clean and prevents error codes from confusing users in reports.
Which error appears when Excel cannot recognize a function name
A #NAME?
B #REF!
C #N/A
D #DIV/0!
#NAME? occurs when Excel does not recognize text in a formula, often due to misspelled function names, missing quotes, or incorrect named ranges. Correcting the formula usually fixes it.
Which error appears when a lookup value is not found
A #VALUE!
B #REF!
C #N/A
D #NUM!
#N/A means the requested value is not available. In lookups, it usually appears when the lookup key is missing, has extra spaces, or is stored as text vs number.
Which error appears when a formula divides by zero
A #REF!
B #NAME?
C #VALUE!
D #DIV/0!
#DIV/0! appears when a formula attempts to divide by zero or a blank cell treated as zero. Use IF or IFERROR to handle this safely and keep output clean.
Which error appears when a referenced cell is deleted
A #REF!
B #N/A
C #DIV/0!
D #NAME?
#REF! indicates an invalid reference, often caused by deleting cells or ranges used in a formula. Fix it by restoring the range or updating the reference.
Which VLOOKUP argument specifies the column number to return
A Lookup value
B Table array
C Col index
D Range lookup
The column index number tells VLOOKUP which column’s value to return from the table array. Choosing the correct index is important; wrong index returns incorrect fields.
In VLOOKUP exact match, which setting should be used
A TRUE
B FALSE
C Approx
D Sorted
FALSE forces exact matching in VLOOKUP. It is necessary for IDs and codes where approximate results can be wrong, and it ensures you get a match only when key is exact.
Approximate match VLOOKUP generally requires the lookup column to be
A Unsorted list
B Hidden cells
C Sorted ascending
D Mixed types
Approximate match (TRUE) assumes the lookup column is sorted ascending. Excel returns the nearest lower match, so unsorted data can produce incorrect results in slab-based lookups.
Which method can return values from left side of lookup key, unlike VLOOKUP
A INDEX-MATCH
B HLOOKUP
C SUMIF
D COUNTIF
INDEX-MATCH can look up in one column and return from any column, even to the left. It is more flexible and less dependent on fixed column numbers than VLOOKUP.
Which function returns the position number of a value in a range
A INDEX
B FIND
C MATCH
D SEARCH
MATCH returns the position of a value in a range, such as 5th item. It is used to locate where a value exists and is often combined with INDEX for lookups.
Which file type saves data in plain text with commas, often losing formatting
A XLSM
B XLSX
C PPTX
D CSV
CSV stores data as comma-separated plain text. It is good for data exchange but usually does not keep formulas, formatting, or charts, so it is used mainly for import/export.
Which common CSV issue changes codes like 0012 into 12
A Leading zeros lost
B Extra columns added
C Charts inserted
D Rows hidden
Excel treats codes as numbers and removes leading zeros. Import the column as text or format it as Text to preserve exact codes for IDs and reliable lookups.
Which feature stops users from editing locked formula cells but allows data entry in unlocked cells
A Protect Workbook
B Page Setup
C Protect Sheet
D Freeze panes
By unlocking input cells and keeping formula cells locked, then enabling Protect Sheet, you allow safe data entry while preventing accidental changes to formulas and structure.
Which workbook protection mainly prevents adding or deleting sheets
A Protect Cell
B Protect Workbook
C Protect Print
D Protect Chart
Protect Workbook structure blocks adding, deleting, renaming, or moving worksheets. It keeps workbook organization stable when shared, while users can still view and work within sheets.
Which printing feature repeats header row on each printed page
A Print Area
B Freeze Panes
C Print Titles
D Wrap Text
Print Titles repeats selected header rows or columns on each printed page. This is important for long printed reports so headings remain visible for every page.
Which printing feature defines only a selected range to print
A Print Area
B Gridlines
C Wrap Text
D Flash Fill
Print Area limits printing to a specified cell range. It avoids printing extra unused areas and helps create clean outputs like reports, tables, and selected summaries.
Which page tool reduces extra pages by fitting columns to one page width
A Cell merge
B Data bars
C Fit to width
D Text split
Fit to width (in Page Setup scaling) adjusts the print scaling so all columns fit within one page wide. It prevents columns from spilling onto extra pages.
Which feature fills a numeric pattern like 2,4,6 quickly using drag
A Sort tool
B Text filters
C Data labels
D AutoFill series
AutoFill uses the fill handle to extend patterns. If you provide 2 and 4, Excel detects the step and continues 6, 8, 10, saving manual typing time.
Which formula uses a 3D reference to sum B2 across Sheet1 to Sheet3
A SUM(Sheet1!Sheet3!B2)
B SUM(Sheet1:Sheet3!B2)
C SUM(Sheet1-Sheet3!B2)
D SUM(Sheet1/Sheet3!B2)
A 3D reference uses Sheet1:Sheet3 to include all sheets between them. SUM(Sheet1:Sheet3!B2) adds the same cell B2 across those sheets, useful for consolidations.
Which function returns a valid date from parts year, month, day
A DATE
B NOW
C TODAY
D TEXT
DATE builds a proper date from numbers like DATE(2026,2,23). It helps create correct dates when parts are stored separately and avoids region-related text date issues.
Which function rounds to nearest multiple like 5
A ROUNDUP
B ROUNDDOWN
C MROUND
D INT
MROUND rounds a number to the nearest specified multiple. It is useful in billing or packaging where values must match steps, like rounding amounts to nearest 5 or 10.
Which function removes decimal part by rounding down for positives
A ROUND
B MAX
C MIN
D INT
INT rounds a number down to the nearest integer. For positive numbers, it removes decimals, like INT(9.8)=9. It is used when only whole units are allowed.
Which operator is used for exponent calculations
A *
B &
C ^
D %
The caret (^) raises a number to a power. Example: =3^2 gives 9. It is used in power formulas, growth calculations, and scientific computations.
Which tool converts formulas into fixed results for safe sharing
A Paste Values
B Paste Formats
C Transpose
D Merge cells
Paste Values replaces formulas with their results. This prevents changes when sharing and keeps totals fixed, especially when the receiving user might edit related input cells.
Which paste option switches rows and columns while pasting
A Multiply
B Transpose
C Divide
D Subtract
Transpose swaps rows with columns during paste. It is useful when data is oriented incorrectly and you want to restructure quickly without manual rewriting of the table.
Which function formats a number into text with leading zeros
A VALUE
B LEN
C TEXT
D TRIM
TEXT converts numbers into formatted text. Example: TEXT(7,”0000″) returns “0007”. It is useful for display-ready codes and labels that must keep leading zeros.
Which feature records steps to automate repeated formatting and actions
A Macro Recorder
B Pivot Table
C Data Validation
D Print Area
Macro Recorder records your actions and saves them as a macro. Running it repeats the same steps quickly, improving consistency and saving time in repetitive worksheet tasks.
Which macro file format must be used to save recorded macros
A CSV
B XLSX
C XLSM
D PDF
XLSM is the macro-enabled workbook format that stores VBA code. XLSX cannot save macros, so you must use XLSM when your workbook contains automation macros.