A formula in Excel must always begin with an equals sign (=).
Correct Answer
True
The equals sign is the primary indicator to Excel that the cell contains a formula or function, signaling that the content needs to be calculated.
Question 2
To keep a cell reference constant when copying a formula to other cells, a relative reference like A1 should be used.
Correct Answer
False
An absolute reference, indicated by dollar signs (e.g., $A$1), is used to keep a cell reference constant when copying a formula. A relative reference (A1) adjusts based on the new cell's position.
Question 3
The Excel formula =5+2*3 will calculate to 21 due to left-to-right evaluation.
Correct Answer
False
Excel follows the standard order of operations (PEMDAS/BODMAS), so multiplication is performed before addition. The correct calculation is 5 + (2*3) = 5 + 6 = 11.
Question 4
The SUM function can only add numbers from a single, contiguous range of cells.
Correct Answer
False
The SUM function can add numbers from multiple non-contiguous ranges, individual cells, or a combination of both, separated by commas.
Question 5
The IF function in Excel requires a logical test, a value to return if true, and a value to return if false as its minimum arguments.
Correct Answer
True
These three arguments (logical_test, value_if_true, value_if_false) are the essential components for the IF function to operate correctly and provide a result for both true and false conditions.
Question 6
The AVERAGE function includes empty cells and text values when calculating the mean of a specified range.
Correct Answer
False
The AVERAGE function ignores empty cells and text values, only considering numerical values when calculating the mean of a range.
Question 7
The CONCATENATE function is primarily used to combine text strings from different cells into a single cell.
Correct Answer
True
CONCATENATE (or the newer CONCAT function and the & operator) is designed to join multiple text items or strings into one combined string.
Question 8
VLOOKUP can retrieve data from columns located to the left of the lookup_value column in the table_array.
Correct Answer
False
VLOOKUP is designed to look up a value in the leftmost column of a table and return a value from a specified column to the right of the lookup column. It cannot look left.
Question 9
For an exact match using VLOOKUP, it is mandatory for the lookup table's first column to be sorted in ascending order.
Correct Answer
False
Sorting is only required for an approximate match (range_lookup set to TRUE or omitted). For an exact match (range_lookup set to FALSE), the lookup column does not need to be sorted.
Question 10
The combination of the INDEX and MATCH functions offers greater flexibility for lookup operations than VLOOKUP, including looking left.
Correct Answer
True
INDEX and MATCH can perform lookups in any direction (left, right, up, down) and are not restricted by the position of the lookup column relative to the return column, unlike VLOOKUP.
Question 11
If a VLOOKUP function fails to find the lookup_value in the table_array, it will return a #REF! error.
Correct Answer
False
If VLOOKUP cannot find a match for the lookup_value, it returns a #N/A error, indicating that the value is 'Not Available'.
Question 12
A PivotTable automatically updates its displayed data whenever changes are made to its source data.
Correct Answer
False
PivotTables require a manual refresh (or a programmed refresh using VBA) to reflect any changes made to their underlying source data.
Question 13
To create a PivotTable effectively, the source data should be organized as a tabular range with unique column headers and no entirely blank rows or columns within the data.
Correct Answer
True
Well-structured source data with consistent headers and no blank rows or columns is crucial for PivotTables to correctly identify fields and summarize information.
Question 14
Calculated Fields within a PivotTable can reference cells or ranges outside of the PivotTable's original data source.
Correct Answer
False
Calculated Fields in a PivotTable can only perform calculations using the fields that are already part of the PivotTable's data model, derived from its source data.
Question 15
A pie chart is an effective chart type for visualizing trends or changes in data over a continuous period.
Correct Answer
False
Pie charts are designed to show proportions of a whole at a single point in time. Line charts or column charts are more suitable for displaying trends over time.
Question 16
A scatter chart is primarily used to display the relationship or correlation between two sets of numerical data.
Correct Answer
True
Scatter charts plot individual data points based on two variables, making them ideal for identifying patterns, clusters, or correlations between those variables.
Question 17
A chart title is a mandatory element that must be present on all Excel charts.
Correct Answer
False
Chart titles are optional elements. While often useful for clarity, they can be added, removed, or modified based on the chart's purpose and design preferences.
Question 18
Conditional formatting in Excel allows cells to automatically change their appearance (e.g., color, font) based on specified rules or criteria.
Correct Answer
True
This is the core functionality of conditional formatting, enabling dynamic visual cues for data analysis and highlighting specific data points based on conditions.
Question 19
Applying a custom number format like '0.00%' to a cell changes the actual underlying numerical value stored in that cell.
Correct Answer
False
Number formatting only changes how the data is displayed, not the actual value stored in the cell. A cell containing 0.5 formatted as '0.00%' will display '50.00%', but its underlying value remains 0.5 for calculations.
Question 20
The 'Wrap Text' feature in Excel automatically adjusts the column width to ensure all text in a cell is visible without overflowing.
Correct Answer
False
'Wrap Text' adjusts the row height to display all text within the current column width. It does not adjust the column width; AutoFit Column Width would do that.
Question 21
The 'Remove Duplicates' feature in Excel will delete all instances of a duplicate row, including the first occurrence, leaving no record of the duplicated data.
Correct Answer
False
The 'Remove Duplicates' feature deletes subsequent duplicate rows, but it always preserves the first unique instance of the data, not deleting all records.
Question 22
The TRIM function in Excel removes all spaces from a text string, including single spaces between words.
Correct Answer
False
The TRIM function removes all leading, trailing, and excess spaces between words, but it leaves a single space between words to maintain readability.