If a formula in C2 uses =A2*$E$1, what stays fixed when you copy it down to C10
A A2 reference
B Both references
C Nothing fixed
D E1 reference
Explanation: $E$1 is an absolute reference, so it remains fixed when copied anywhere. A2 is relative, so it changes to A3, A4, etc., while E1 stays constant as a fixed rate.
When copying a formula across columns, which reference keeps row fixed like B$2
A Mixed reference
B Absolute reference
C Relative reference
D Circular reference
Explanation: B$2 locks row 2 but allows the column to change when copied left or right. This helps when you always want the same row, such as header-based calculations.
In a long table, which feature keeps the first row visible and also allows printing headers on each page
A Freeze panes
B Print titles
C Both features
D Wrap text
Explanation: Freeze Panes keeps headers visible while scrolling on screen. Print Titles repeats chosen header rows on every printed page. Together they improve both on-screen work and printed reports.
Which step is safest before removing duplicates from a master dataset
A Sort ascending
B Save a copy
C Add chart
D Hide columns
Explanation: Remove Duplicates permanently deletes extra records. Saving a backup copy first prevents data loss if wrong columns are selected, ensuring you can restore the original dataset easily.
Which tool can split “100-HP-25” into three columns using “-”
A Flash Fill
B Paste Values
C Text to Columns
D Goal Seek
Explanation: Text to Columns with “Delimited” option can split a cell using separators like hyphen. It is reliable for structured text where a consistent delimiter exists.
If Flash Fill does not work, the most common reason is
A Sheet protected
B No clear pattern
C File is large
D Chart exists
Explanation: Flash Fill needs a clear, consistent pattern from your example. If data is inconsistent or the example is not enough, Excel cannot detect the pattern and won’t fill correctly.
Which chart type is best when category names are long and you want easy reading
A Pie chart
B Line chart
C Area chart
D Bar chart
Explanation: Bar charts use horizontal bars, giving more space for long category names on the vertical axis. This improves readability compared to column charts where labels can overlap.
Which chart option helps show contribution of each category as percent
A Scatter chart
B Line chart
C Pie chart
D Stock chart
Explanation: Pie charts show how each part contributes to a total. They commonly display percentages, making them useful for showing share distribution like budget parts or category-wise totals.
In a chart, which element shows the plotting area values scale for numbers
A Legend
B Value axis
C Data table
D Title
Explanation: The value axis (usually vertical) shows the numeric scale for the chart values. It helps interpret how high or low a bar or point is relative to the scale.
Which feature quickly changes chart appearance without changing data
A Chart Styles
B Sort tool
C Filter tool
D Data validation
Explanation: Chart Styles apply predefined design formats, changing colors and visual layout while keeping the same data. It helps make charts presentable quickly without manual formatting.
Which PivotTable action is required after changing source data values to update totals
A Protect sheet
B Remove duplicates
C Refresh
D Page setup
Explanation: PivotTables may not update automatically. Refresh recalculates totals and summaries based on the latest source data, ensuring the PivotTable report remains accurate.
Which PivotTable area is best used to show categories like “Region” down the left side
A Values area
B Rows area
C Filters area
D Chart area
Explanation: The Rows area places fields as row labels in the PivotTable, grouping data down the left side. It is used for categories like Region, Department, or Month.
Which feature shows pivot values as percentage of grand total
A Page Setup
B Freeze Panes
C Wrap Text
D Show Values As
Explanation: “Show Values As” can display sums as % of grand total or % of row/column total. This helps compare contributions across categories without manually calculating percentages.
Which tool lets you restrict entry to whole numbers only in a marks column
A Conditional format
B Find tool
C Data validation
D Paste special
Explanation: Data validation can restrict input type to whole numbers and set ranges like 0 to 100. It reduces entry errors and improves correctness of results and reports.
Which validation alert blocks invalid entries completely
A Stop alert
B Warning alert
C Info alert
D Note alert
Explanation: Stop alert prevents the user from entering invalid data and forces correction. It is used when only valid values must be allowed, such as IDs, marks limits, or fixed codes.
If a VLOOKUP exact match cannot find a key, it returns
A #REF!
B #N/A
C #DIV/0!
D #VALUE!
Explanation: #N/A means the lookup value is not found in the lookup column. This can happen due to spelling differences, extra spaces, or mismatched data types like text vs number.
In VLOOKUP, which setting ensures exact match
A TRUE
B AUTO
C FALSE
D SORT
Explanation: FALSE (or 0) forces VLOOKUP to return only exact matches. This is important for searching IDs, roll numbers, or codes where approximate matches can give wrong results.
Which issue often breaks lookups when codes contain hidden spaces
A Extra spaces
B Wrong chart
C Low memory
D Print margin
Explanation: Hidden leading or trailing spaces make text unequal, so lookups fail. Cleaning data with TRIM removes extra spaces and improves matching accuracy in lookups and comparisons.
Which function removes extra spaces and leaves single spaces between words
A LEN
B TRIM
C FIND
D MID
Explanation: TRIM removes extra spaces from text, keeping only single spaces between words. It helps clean imported data and fixes mismatches during lookups caused by hidden spaces.
Which function returns the number of characters in a cell
A VALUE
B TEXT
C LEN
D DATE
Explanation: LEN counts characters including spaces. It is used for checking the length of IDs, codes, and text fields, helping detect wrong entries or extra characters.
If a formula shows #REF!, the likely reason is
A Wrong data type
B Divide by zero
C Spelling error
D Deleted reference
Explanation: #REF! appears when a formula refers to a cell or range that no longer exists, often due to deleting cells or shifting ranges incorrectly. Update the reference to fix it.
Which function helps show blank instead of an error value
A COUNTIF
B IFERROR
C SUMIF
D PROPER
Explanation: IFERROR returns a chosen value when a formula results in any error. Example: IFERROR(VLOOKUP(…),””) helps keep reports clean without showing error codes.
Which feature can highlight entire rows based on a condition using a formula
A Data bars
B Pie chart
C Formula rule
D Print titles
Explanation: Conditional formatting using a formula can highlight full rows, like marking rows where status is “Pending.” It is flexible and works beyond simple built-in highlight rules.
Which sorting option groups highlighted rows by their fill color
A Sort by color
B Sort by value
C Sort by icon
D Sort by font
Explanation: Sort by color groups rows based on cell fill color. It is useful when colors represent priority or status, allowing you to bring important highlighted records together.
Which feature repeats header row on every printed page
A Freeze panes
B Print titles
C Wrap text
D AutoFill
Explanation: Print Titles repeats selected rows or columns on every printed page. It ensures column headings appear on all pages of long tables, improving readability in printed reports.
Which feature sets a specific range to be printed only
A Page breaks
B Freeze panes
C Print area
D Data bars
Explanation: Print Area defines exactly which cells will print. It helps avoid printing unwanted data and keeps output focused, especially when a sheet has extra notes or blank areas.
Which tool reduces extra printed pages by scaling data to fit
A Format painter
B Flash fill
C Find tool
D Page setup
Explanation: Page Setup controls scaling like “Fit to 1 page wide.” It helps print large tables neatly without splitting columns across pages, reducing wasted paper and blank pages.
Which formula correctly adds values in A1 to A5
A SUM(A1:A5)
B =SUM(A1:A5)
C =ADD(A1:A5)
D =TOTAL(A1:A5)
Explanation: SUM adds numbers in a range, and Excel formulas must start with “=”. =SUM(A1:A5) totals the values, commonly used for totals in marksheets and reports.
Which operator is used for exponent like 2 power 3
A *
B %
C ^
D &
Explanation: The caret (^) is the exponent operator. Example: =2^3 returns 8. It is used in power calculations, growth formulas, and scientific computations.
Which function returns TRUE only when all conditions are true
A AND
B OR
C NOT
D CONCAT
Explanation: AND checks multiple conditions and returns TRUE only if every condition is TRUE. It is helpful for rules where all requirements must be met, such as pass in all subjects.
Which function returns TRUE when any one condition is true
A AND
B IFERROR
C OR
D ROUND
Explanation: OR returns TRUE if at least one condition is TRUE. It is used for flexible rules like eligibility based on any one of multiple criteria.
Which function returns the position of a value in a list
A INDEX
B MATCH
C VLOOKUP
D SUMIF
Explanation: MATCH returns the position number of a lookup value within a range. It is commonly paired with INDEX to return corresponding values from another range.
Which function returns a value using row and column position
A FIND
B TRIM
C LEN
D INDEX
Explanation: INDEX returns the value at a specified row and column in a range. With MATCH, it forms a flexible lookup system that works even if table columns change.
Which lookup searches across the first row and returns from below
A HLOOKUP
B VLOOKUP
C COUNTIF
D SUMIF
Explanation: HLOOKUP searches the first row for the lookup value and returns a value from a specified row below it. It is useful when data is arranged horizontally.
Which function returns the current date only
A NOW
B DATE
C TODAY
D DAY
Explanation: TODAY returns the current system date and updates automatically. It is used in reports for deadlines, age calculations, and day difference calculations where time is not needed.
Which function returns current date and time together
A TODAY
B NOW
C YEAR
D MONTH
Explanation: NOW returns both current date and current time. It updates when the sheet recalculates and is used for time-stamping entries and tracking update times.
Which chart is best for comparing categories with horizontal bars
A Line chart
B Pie chart
C Bar chart
D Scatter chart
Explanation: Bar charts use horizontal bars, making them good for long category names and easy comparisons. They are useful for comparing marks across students or sales across products.
Which feature creates a mini trend chart inside a cell
A Sparklines
B Pivot chart
C Column chart
D Area chart
Explanation: Sparklines are small charts placed inside cells to show trends. They are useful in dashboards to provide quick visual pattern information beside numeric values.
Which workbook file type supports macros
A XLSX
B CSV
C PDF
D XLSM
Explanation: XLSM is the macro-enabled workbook format that can store VBA code. Use it when your file contains macros, and avoid enabling unknown macros due to security risks.
Which worksheet tool records steps for repeated automation
A Data validation
B Macro Recorder
C Conditional format
D Print area
Explanation: Macro Recorder captures steps like formatting, filtering, and copying, then saves them as a macro. Running the macro repeats the same steps quickly and consistently.
Which feature combines many worksheets into one total using Sheet1:Sheet3 reference
A Data bars
B Flash fill
C 3D reference
D Sort option
Explanation: A 3D reference refers to the same cell or range across multiple sheets, like SUM(Sheet1:Sheet3!B2). It helps consolidate data from many similar sheets.
Which element contains multiple worksheets in one Excel file
A Worksheet
B Workbook
C Cell
D Range
Explanation: A workbook is the Excel file that contains one or more worksheets. It can also include charts and other objects, acting as the main container for related data.
Which tool changes formatting of cells based on rules and conditions
A Conditional Formatting
B Page Setup
C Print Titles
D Freeze panes
Explanation: Conditional Formatting highlights cells based on conditions, like high scores or duplicates. It helps spot important values quickly and improves analysis without changing the actual data.
Which tool converts values to fixed results by removing formulas
A Paste Formats
B Transpose
C Paste Values
D Wrap Text
Explanation: Paste Values replaces formulas with their calculated results. It is useful before sharing, protecting results, or preventing formula changes, especially in reports and final outputs.
Which tool swaps rows and columns while pasting
A Multiply
B Transpose
C Divide
D Subtract
Explanation: Transpose changes rows into columns and columns into rows. It is helpful when data layout is reversed and you want to correct it quickly without retyping.
Which function returns the month number from a date
A YEAR
B DAY
C DATE
D MONTH
Explanation: MONTH extracts the month number (1–12) from a date. It is useful for grouping data month-wise and creating month-based summaries and PivotTable reports.
Which function extracts day number from a date
A MONTH
B YEAR
C DAY
D NOW
Explanation: DAY returns the day of the month from a date value. It is used for daily analysis, attendance tracking, and grouping transactions by date components.
Which function extracts year from a date
A MONTH
B YEAR
C TODAY
D DATE
Explanation: YEAR extracts the year portion from a date. It is useful for yearly summaries, filtering, and grouping records in reports without manually typing or splitting the date.
Which tool creates a structured table with filters and auto-expanding range
A Format as Table
B Page Setup
C Merge cells
D Wrap text
Explanation: Format as Table converts a range into an Excel Table with automatic filters and structured references. It expands automatically as you add rows, improving accuracy in formulas and pivots.
Which table option adds an automatic totals row
A Row Labels
B Chart Style
C Total Row
D Print Setup
Explanation: Total Row adds a summary row at the end of an Excel Table. You can choose SUM, COUNT, AVERAGE, etc., and it updates automatically when data changes.