When you copy a formula from B2 to C2, which part changes in a relative reference like A2
A Column only
B Row only
C Both change
D Nothing changes
Explanation: Copying from B2 to C2 moves one column right. A relative reference adjusts by the same shift, so A2 becomes B2. Row stays same because you didn’t move vertically.
In Excel, what does the Name Box show by default when you click a cell
A Formula result
B File location
C Cell address
D Sheet password
Explanation: The Name Box (left of the formula bar) displays the active cell reference like D5. It can also show a named range and helps jump to a specific cell quickly.
Which option is a valid mixed reference that locks only the column
A A1
B $A$1
C A$1
D $A1
Explanation: $A1 locks the column A but allows the row number to change when copied. This is useful when you always want the same column, but different rows in calculations.
Which function returns the second largest value when combined properly
A LARGE
B RANK
C MAX
D MIN
Explanation: LARGE(range, k) returns the k-th largest value. For second largest, use LARGE(A1:A10,2). It is useful for finding top performers without sorting the data.
Which function returns the second smallest value when used with k
A MIN
B AVERAGE
C SMALL
D COUNT
Explanation: SMALL(range, k) returns the k-th smallest value. For second smallest, use SMALL(A1:A10,2). This helps identify low values quickly for analysis and comparison.
If a formula shows #DIV/0!, what is the most common reason
A Wrong chart type
B Divide by zero
C Text in cell
D Missing sheet tab
Explanation: #DIV/0! appears when a formula tries to divide by zero or a blank cell treated as zero. Fix it by ensuring the divisor cell has a valid non-zero number.
What does #VALUE! usually mean in a formula
A File not saved
B Chart missing
C Sheet protected
D Wrong data type
Explanation: #VALUE! occurs when Excel expects a number but gets text or an invalid type in a calculation, like adding “ABC” to a number. Correct the input or use proper conversion.
Which error appears when a referenced cell is deleted
A #NAME?
B #N/A
C #REF!
D #NUM!
Explanation: #REF! means an invalid cell reference. It commonly happens if a formula refers to a cell or range that was deleted, causing Excel to lose the reference location.
Which function is best to round up a value
A ROUNDUP
B ROUND
C ROUNDDOWN
D INT
Explanation: ROUNDUP always rounds a number upward, away from zero. For example, ROUNDUP(2.11,1) gives 2.2. It helps when you must not underestimate values like charges.
Which function removes decimals by rounding down
A ROUND
B ROUNDDOWN
C ROUNDUP
D CONCAT
Explanation: ROUNDDOWN rounds a number down toward zero. Example: ROUNDDOWN(5.99,0) gives 5. It is useful when only the lower whole value is allowed.
In Excel, which operator is used for power/exponent
A *
B %
C ^
D =
Explanation: The caret (^) is used for exponentiation. Example: =2^3 returns 8. It is used in growth calculations, squares, cubes, and scientific computations.
Which action converts a selected range into an Excel Table
A Format as Table
B Insert Chart
C Text to Columns
D Remove Duplicates
Explanation: Format as Table turns a data range into a structured table with filtering, banded rows, and table references. Tables automatically expand as you add new rows.
What is a key benefit of converting data to a Table
A Deletes blanks
B Locks workbook
C Auto expands formulas
D Prints only headers
Explanation: In an Excel Table, formulas and formatting automatically extend to new rows. This reduces manual copying and keeps calculations consistent when data grows.
Which feature can quickly create totals row in a Table
A Goal Seek
B Flash Fill
C Page Setup
D Total Row
Explanation: Total Row adds a summary row at the bottom of an Excel Table. You can easily pick SUM, AVERAGE, COUNT, etc., making quick totals without writing formulas manually.
Which option is used to lock a range name for easier formulas
A Print Area
B Named Range
C Gridlines
D Custom sort
Explanation: Named ranges let you assign a meaningful name to a cell or range, like “TaxRate”. This makes formulas easier to read, reduces mistakes, and helps reuse the same range.
How do you refer to cell A1 on Sheet2 from Sheet1
A Sheet2:A1
B Sheet2.A1
C Sheet2!A1
D Sheet2/A1
Explanation: Excel uses the exclamation mark for sheet references. Sheet2!A1 points to cell A1 on Sheet2. This is used in linking sheets and building summary sheets.
If a sheet name has spaces, how is it referenced
A ‘Sheet Name’!A1
B Sheet Name!A1
C “Sheet Name”!A1
D (Sheet Name)!A1
Explanation: When a sheet name contains spaces, Excel uses single quotes. Example: ‘Sales Data’!B2. This ensures Excel reads the sheet name correctly in formulas.
Which tool helps split “Full Name” into first and last name using spaces
A Data validation
B Goal Seek
C Protect sheet
D Flash Fill
Explanation: Flash Fill detects patterns from your example entries and fills remaining rows automatically. It can separate names, extract codes, or format text without using complex formulas.
Which feature can apply multiple conditions to filter results
A Freeze panes
B Wrap text
C Advanced Filter
D Sparklines
Explanation: Advanced Filter can filter data using a criteria range and multiple conditions, even with AND/OR logic. It can also copy filtered results to another location.
When sorting numbers, what does ascending order mean
A Smallest to largest
B Largest to smallest
C Random order
D By cell color
Explanation: Ascending sort arranges numeric values from lowest to highest. For text, it sorts A to Z. It is commonly used for ranking lists, dates, and sequential data.
Which option keeps rows together while sorting a table with headers
A Sort left to right
B My data has headers
C Sort by cell color
D Sort by icon
Explanation: Selecting “My data has headers” prevents Excel from sorting the header row into the data. It ensures only actual records are sorted while headings remain fixed at top.
What does “Sort by Cell Color” do
A Group by fill color
B Sort by numbers
C Remove all colors
D Add new colors
Explanation: Sort by cell color rearranges rows based on fill color used in cells. This is useful when colors represent categories like priority, status, or highlighted items.
Which conditional formatting rule highlights duplicate values
A Greater Than
B Data Bars
C Duplicate Values
D Icon Sets
Explanation: Duplicate Values rule highlights repeated entries in a selected range. It helps find repeated roll numbers, duplicate emails, or repeated IDs quickly for cleaning data.
Which feature quickly removes duplicates but needs a selected column
A Filter
B Remove Duplicates
C Sort
D Wrap Text
Explanation: Remove Duplicates checks selected columns and deletes repeated records. You must choose the correct columns, otherwise you may remove valid rows that differ in other fields.
Which chart element shows values against faint horizontal lines
A Legend
B Title
C Sheet tabs
D Gridlines
Explanation: Gridlines on charts help read values by providing reference lines across the plot area. They make it easier to estimate data points without reading exact labels.
In a column chart, what does the vertical axis usually represent
A Categories
B Sheet names
C Values scale
D Print pages
Explanation: The vertical axis (Y-axis) typically shows numeric scale like marks or sales amount. The horizontal axis shows categories like names or months, helping interpret comparisons correctly.
What is the main purpose of a trendline in charts
A Show general trend
B Add colors
C Rename series
D Protect chart
Explanation: A trendline shows the overall direction of data, such as rising or falling sales. It helps in basic forecasting and understanding patterns, especially in line and scatter charts.
Which chart type is most suitable for two numeric variables
A Pie chart
B Column chart
C Scatter chart
D Bar chart
Explanation: Scatter charts plot points using two numeric variables, like height vs weight. They help identify relationships, clusters, and outliers, which is harder to see in category charts.
What does a data series represent in a chart
A One worksheet only
B One print page
C One table row
D One dataset group
Explanation: A data series is a set of related values plotted on a chart, like “Sales” across months. Multiple series allow comparison, such as “Sales” vs “Profit” together.
Which tool makes a small trend chart inside a cell
A Pivot Chart
B Sparklines
C Pie chart
D Icon sets
Explanation: Sparklines are tiny charts inside cells that show trends across a row of data. They are useful for dashboards because they show patterns without taking much space.
Which option creates a dropdown from a cell range
A List validation
B Color scale
C Print titles
D Data bars
Explanation: In Data Validation, choosing “List” allows creating a dropdown using items or a range. This standardizes entries like department names and prevents spelling mistakes.
Which Data Validation setting shows a message before entry
A Error alert
B Filter rule
C Input message
D Print preview
Explanation: Input message appears when the cell is selected, guiding users on what to enter. It reduces errors and improves data quality in shared worksheets.
Which Data Validation option blocks invalid data entry
A Allow blank
B Stop alert
C Show dropdown
D Ignore blank
Explanation: A “Stop” error alert prevents the user from entering invalid values. It is the strictest validation type and ensures only allowed entries are saved in the cell.
What does Protect Sheet mainly restrict
A Editing actions
B File saving
C Chart printing
D Column width only
Explanation: Protect Sheet restricts actions like editing cells, inserting rows, formatting, and deleting. It helps protect important formulas and structure when multiple people use the file.
In PivotTable, what is a Row Label area used for
A Calculate totals
B Print margins
C Group categories
D Format cells
Explanation: Row Labels contain fields used to group data into rows, like “Department” or “Month.” This creates a structured summary where values can be totaled or counted.
In PivotTable, Values area usually contains
A Text headings
B Sheet names
C Print settings
D Calculations like SUM
Explanation: The Values area shows summarized calculations such as Sum, Count, Average, Max, or Min. It provides the numeric summary for the groups created by row or column labels.
Which PivotTable feature refreshes when source data changes
A Merge cells
B Refresh
C Wrap text
D Freeze panes
Explanation: When source data is updated, a PivotTable may not update automatically. Using Refresh recalculates and shows the latest summary values, keeping analysis accurate.
Which tool groups totals for each category in a sorted list
A CONCAT
B ROUND
C Subtotal
D COUNTBLANK
Explanation: Subtotal inserts summary rows like sum or count for each group after sorting. It’s useful for totals per department or per item, making grouped reports easy to read.
Which function searches horizontally across the first row conceptually
A HLOOKUP
B VLOOKUP
C MATCH
D INDEX
Explanation: HLOOKUP searches for a value in the first row of a table and returns a value from a specified row below. It is useful when data is arranged in rows instead of columns.
In VLOOKUP concept, the lookup value is searched in
A Last column
B Middle row
C Any chart
D First column
Explanation: VLOOKUP searches the lookup value in the first column of the selected table range, then returns related data from a column to the right in the same row.
INDEX-MATCH is often preferred because it
A Needs no formulas
B Prints faster
C Looks left or right
D Colors cells
Explanation: INDEX with MATCH can return values from any column, even to the left of the lookup column. It also avoids fixed column numbers, making lookups more flexible than VLOOKUP.
Which function can count cells meeting “>=50” condition
A COUNT
B COUNTIF
C COUNTA
D COUNTBLANK
Explanation: COUNTIF counts cells that meet a specified condition. For example, COUNTIF(A1:A10,”>=50″) counts how many values are 50 or more, helpful for pass counts.
SUMIF is useful when you need to
A Sum with criteria
B Join text
C Round to nearest
D Split columns
Explanation: SUMIF sums values that meet a condition, like adding sales only for “North.” It helps create category-wise totals without filtering manually each time.
Which text function joins with a chosen separator easily
A CONCATENATE
B TRIM
C LEN
D TEXTJOIN
Explanation: TEXTJOIN combines text from multiple cells with a delimiter like comma or space and can ignore blanks. It is very useful for creating clean combined lists and labels.
Which file format stores sheets without formulas, as plain text with commas
A XLSX
B PDF
C CSV
D PPTX
Explanation: CSV saves data as comma-separated text. It usually stores values only, not formulas, charts, or formatting. It is widely used for sharing data between different software.
In CSV import, which issue is common with leading zeros
A Zeros removed
B Extra colors
C Charts added
D Sheets locked
Explanation: When importing CSV, Excel may treat codes as numbers and remove leading zeros, like 0012 becoming 12. To avoid this, import the column as text.
Which function returns the day number from a date
A MONTH
B DAY
C YEAR
D DATE
Explanation: DAY extracts the day of the month from a date. For example, DAY(15-01-2026) returns 15. It helps in date-based reporting and grouping.
Which function returns month number from a date
A YEAR
B NOW
C MONTH
D TEXT
Explanation: MONTH returns the month number (1 to 12) from a date value. It is commonly used for monthly summaries, grouping transactions, and creating month-wise reports.
Which function returns year from a date
A YEAR
B DATE
C TODAY
D VALUE
Explanation: YEAR extracts the year from a date value, like 2026. It helps in yearly reports, age calculations, and sorting or grouping data by year.
Macros in Excel are mainly used to
A Change print size
B Increase chart types
C Create new fonts
D Automate repeated tasks
Explanation: Macros record or run VBA code to automate repeated steps, such as formatting reports or cleaning data. They save time, reduce manual errors, and ensure consistent actions.