In Excel, which feature keeps a header row visible while scrolling a long dataset for checking values
A Freeze Panes
B Print Titles
C Page Setup
D Wrap Text
Freeze Panes locks selected rows or columns on screen while you scroll. It helps you keep headers visible, so you can compare entries correctly in large tables without losing column names.
When you copy a formula down, which reference type keeps row fixed but allows column to change, like A$1
A Relative reference
B Absolute reference
C Mixed reference
D Circular reference
A mixed reference locks either the row or the column. In A$1, the row 1 stays fixed, but the column adjusts when copied across, useful for row-based constants.
Which tool helps ensure only valid dates are entered in a “DOB” column
A Conditional Formatting
B Flash Fill
C Pivot Chart
D Data Validation
Data Validation can restrict inputs to a date range or date type only. It reduces wrong entries like text in date fields and improves data accuracy for filtering and calculations.
If you want a dropdown for “Yes/No” in cells, which feature is used
A Sort tool
B Print Area
C List validation
D Text to Columns
List validation is set through Data Validation. You provide items like Yes, No or a cell range. Users then pick from a dropdown, preventing spelling mistakes and keeping entries consistent.
Which function is best for returning a value when a condition is true, otherwise another value
A SUM
B IF
C MAX
D COUNT
IF tests a condition and returns one result for TRUE and another for FALSE. Example: IF(A2>=33,”Pass”,”Fail”). This is widely used in grading and eligibility checks.
Which function counts cells meeting a condition like “Delhi” in a city column
A COUNTIF
B COUNT
C COUNTA
D COUNTBLANK
COUNTIF counts cells that match a criterion. Example: COUNTIF(B2:B100,”Delhi”) counts how many entries have Delhi. It helps in quick category counts and reports.
Which function adds values only when a condition matches, like summing sales for “North”
A SUM
B AVERAGE
C SUMIF
D MIN
SUMIF sums a range based on one condition. Example: SUMIF(Region,”North”,Sales). It is used for category totals without manually filtering each time.
In Excel, what does a pivot table mainly help you do with large data
A Draw shapes
B Print faster
C Add passwords
D Summarize quickly
Pivot tables group and summarize data by fields like department, month, or category. They can calculate totals, counts, and averages, making analysis fast without complex formulas.
Which area of a PivotTable is used to place fields for totals like Sum or Count
A Values
B Rows
C Columns
D Filters
The Values area performs calculations such as SUM, COUNT, or AVERAGE on selected numeric fields. This is where the main summarized numbers of the PivotTable are displayed.
Which feature helps show only records matching criteria while hiding others temporarily
A Subtotal
B Filter
C Merge cells
D AutoFill
Filter shows only rows that meet selected conditions, like a specific city or marks range. Other rows are hidden, not deleted, so you can remove the filter anytime.
Which option sorts numbers from largest to smallest
A Ascending sort
B Custom format
C Descending sort
D Advanced filter
Descending sort arranges values from highest to lowest. It is useful for ranking lists, showing top performers, highest sales, or maximum values first for quick review.
What does “My data has headers” ensure during sorting
A Protects header row
B Deletes headers
C Converts to chart
D Adds new columns
This setting tells Excel the first row contains headings, so it won’t be mixed into the sorted data. Headings stay at top, keeping records sorted correctly below.
Which conditional formatting option highlights repeated values in a list
A Icon Sets
B Duplicate Values
C Data Bars
D Color Scales
Duplicate Values rule highlights repeated entries. It helps quickly detect duplicate roll numbers, IDs, or email addresses for data cleaning before reporting or importing.
Which feature can display colored bars inside cells based on values
A Print Area
B Wrap Text
C Data Bars
D Named Range
Data Bars are conditional formatting visuals that fill cells with bars proportional to values. They help compare numbers quickly without reading every value, useful in dashboards.
Which chart type is best to show a trend across months
A Pie chart
B Doughnut chart
C Radar chart
D Line chart
Line charts connect points over time, making them ideal for showing trends like monthly sales or temperature changes. They clearly show increases, decreases, and patterns.
Which chart type is best for showing parts of a whole
A Pie chart
B Scatter chart
C Column chart
D Bar chart
Pie charts show how each category contributes to a total, like expense distribution. They work best with a few categories and when the total represents 100%.
Which chart element explains which color belongs to which series
A Axis
B Gridlines
C Legend
D Title
The legend identifies data series by color or marker. It helps readers understand what each line or bar represents, especially when multiple series appear in one chart.
Which chart option displays values on bars or points
A Chart title
B Axis labels
C Data labels
D Sheet tabs
Data labels show actual values directly on chart elements, like bars or slices. This improves readability and reduces the need to look back at the data table.
What does Text to Columns mainly do to a cell like “Amit,Delhi”
A Adds commas
B Splits into columns
C Removes duplicates
D Protects data
Text to Columns splits one cell’s content into multiple columns using delimiters like comma, space, or tab. It is useful when imported data comes in combined form.
Which feature extracts patterns automatically after you type a few examples
A Goal Seek
B Subtotal
C Protect Workbook
D Flash Fill
Flash Fill identifies patterns in your examples and fills remaining rows automatically. It can split names, combine text, or format numbers quickly without writing formulas.
Which file format is best for plain-text data exchange with commas
A CSV
B XLSX
C PDF
D DOCX
CSV stores values separated by commas in plain text. It is widely used for exporting and importing data across systems, though it usually does not preserve formulas or formatting.
Which problem often happens in CSV import with codes like 0012
A Adds extra zeros
B Creates charts
C Drops leading zeros
D Locks worksheets
Excel may treat codes as numbers and remove leading zeros. To preserve them, import the column as text or format cells as text before opening the CSV.
Which function returns the current date without time
A NOW
B TODAY
C DATE
D DAY
TODAY returns the system date only and updates automatically. It is useful for reports, deadlines, and calculating days between dates when time is not needed.
Which function returns date and time together
A TODAY
B YEAR
C MONTH
D NOW
NOW returns both current date and current time. It updates automatically and is useful for time-stamping updates, logs, or tracking when a workbook was last recalculated.
Which function returns the month number from a date
A DAY
B YEAR
C MONTH
D TEXT
MONTH extracts the month (1 to 12) from a date value. It helps in monthly grouping, reporting, and creating summaries like month-wise sales or attendance.
Which function returns the year from a date
A YEAR
B DATE
C NOW
D VALUE
YEAR extracts the year part from a date. It is used in yearly summaries, age calculation, and filtering data by year in reports or PivotTables.
Which error appears when a formula divides by zero
A #VALUE!
B #REF!
C #NAME?
D #DIV/0!
#DIV/0! occurs when a divisor is zero or blank treated as zero. Fix it by checking the denominator cell, adding validation, or using IF to avoid division when value is zero.
Which error occurs after deleting a referenced cell or range
A #N/A
B #REF!
C #NUM!
D #NULL!
#REF! means the reference in a formula is invalid, often due to deleted cells or moved ranges. Update the formula to point to the correct existing range.
Which function returns the k-th largest value, like 2nd highest
A MAX
B RANK
C LARGE
D AVERAGE
LARGE(range,k) returns the k-th largest value. Using k=2 returns the second highest. This is helpful in finding top ranks without sorting the dataset.
Which function returns the k-th smallest value
A SMALL
B MIN
C COUNT
D ROUND
SMALL(range,k) returns the k-th smallest value. It is useful for finding lowest ranks, smallest amounts, or minimum values beyond the absolute minimum, like 3rd smallest.
Which operator is used for exponent in Excel formulas
A *
B %
C ^
D &
The caret (^) raises a number to a power, like =3^2 for 9. It is used in scientific calculations, square/cube computations, and growth models.
Which tool turns a selected range into a structured table with filters
A Page Setup
B Format as Table
C Wrap Text
D Print Titles
Format as Table converts data into an Excel Table with automatic filters, banded rows, and structured references. It helps manage expanding data and improves readability.
In a Table, which feature adds a quick summary row at bottom
A Total Row
B Freeze Panes
C Flash Fill
D Remove duplicates
Total Row provides quick calculations like SUM, AVERAGE, COUNT for a column. It updates automatically when new rows are added, making totals easy and reliable.
Which tool helps define a reusable name like “TaxRate” for a cell
A Print Area
B Custom sort
C Named Range
D Data bars
Named ranges assign a meaningful name to cells or ranges. They make formulas easier to understand, reduce wrong references, and are helpful when linking across sheets.
What does Protect Workbook mainly prevent
A Editing a cell
B Changing font
C Adding formulas
D Structural changes
Protect Workbook mainly restricts sheet-level structure changes like adding, deleting, moving, or renaming sheets. It helps keep workbook organization stable when shared with others.
Which feature ensures only numbers within a limit are entered, like 0 to 100
A Custom sort
B Data Validation
C Conditional format
D Sparkline
Data Validation can restrict numeric entries to a range, such as 0 to 100 for marks. It prevents invalid data entry, improving accuracy for calculations and analysis.
Which Data Validation alert blocks entry strongly
A Information
B Warning
C Stop
D Note
A Stop alert prevents saving invalid data into the cell. It is strict and useful when wrong entries must be completely avoided, such as roll numbers or fixed codes.
Which feature tests how changing one input changes a final result
A What-if analysis
B Print area
C Merge cells
D Wrap text
What-if analysis helps you change input values to see different outcomes, useful for budgets, targets, and planning. Goal Seek is one simple tool under this idea.
Which tool finds an input needed to reach a target formula result
A Sort
B Filter
C Text to Columns
D Goal Seek
Goal Seek changes one input cell until the formula reaches a desired result. For example, it can find required marks to achieve a target percentage, useful for planning.
VLOOKUP searches the lookup value in which part of the table range
A Last row
B Any column
C First column
D Chart legend
VLOOKUP looks for the lookup value in the first column of the selected table array. It then returns a value from a specified column to the right in that row.
HLOOKUP searches in which direction conceptually
A Vertical lookup
B Horizontal lookup
C Circular lookup
D Random lookup
HLOOKUP searches across the first row of a table and returns a value from a row below. It is used when data is arranged in rows instead of columns.
MATCH returns what kind of result
A Matched value
B Sum total
C Position number
D Text length
MATCH returns the position of a lookup value within a range, like 4th item. It is commonly paired with INDEX to fetch the corresponding value from another range.
INDEX returns what, using row and column position
A Cell value
B Cell color
C Sheet name
D Print page
INDEX returns the value at a specified row and column within a range. Combined with MATCH, it becomes a flexible lookup method that works even when columns move.
Which function combines text with a separator and can ignore blanks
A CONCAT
B TRIM
C LEN
D TEXTJOIN
TEXTJOIN joins text with a delimiter like comma and can skip blank cells. It is useful for creating clean combined strings such as lists of selected items or full addresses.
Which function converts text like “123” into a number
A TEXT
B UPPER
C VALUE
D PROPER
VALUE converts numeric-looking text into a real number so Excel can calculate it. It helps when imported data stores numbers as text and calculations otherwise fail.
Which function returns number of characters including spaces
A LEN
B TRIM
C RIGHT
D MID
LEN counts all characters in a cell, including spaces. It is useful for checking code length, validating IDs, and detecting extra characters that may cause mismatches.
Which chart is best for showing relationship between two numeric fields
A Pie chart
B Scatter chart
C Column chart
D Bar chart
Scatter charts plot two numeric variables on X and Y axes, helping show relationships and outliers. They are commonly used for correlation-type analysis like height vs weight.
Which chart line helps show overall direction in data
A Gridlines
B Legend
C Trendline
D Title
A trendline shows the general pattern of data points, such as upward growth or downward decline. It helps in basic forecasting and understanding overall movement in charts.
Which is the best use of Sparklines in reports
A Mini trend indicator
B Large presentation chart
C Sheet protection tool
D Print page numbering
Sparklines are small charts placed inside cells to show trends. They are useful in dashboards where you need quick visual trend indicators next to numbers without big charts.
Macros are mainly used in Excel to
A Add new sheets
B Print faster
C Count cells
D Automate steps
Macros automate repeated tasks by recording actions or using VBA code. They help save time, reduce manual mistakes, and ensure the same steps are applied consistently in reporting workflows.