Basic and Advance Flashcards

Which function returns the number of cells in a range that meet a given condition?

Answer: COUNTIF

COUNTIF counts cells within a range that satisfy a single specified criterion.

What does the $ symbol do when placed before a column letter or row number in a formula?

Answer: Anchors the reference so it does not change when copied

A $ before a column letter or row number creates an absolute reference that stays fixed when the formula is copied.

Which Excel feature automatically suggests a formula pattern based on adjacent data you have already entered?

Answer: AutoFill

AutoFill extends a pattern (numbers, dates, or formulas) by dragging the fill handle across adjacent cells.

What is the result of =MOD(17, 5)?

Answer: 2

MOD returns the remainder after division; 17 divided by 5 is 3 remainder 2.

Which keyboard shortcut applies bold formatting to the selected cells?

Answer: Ctrl+B

Ctrl+B toggles bold formatting on or off for the selected text or cells.

When you sort a table by a column in ascending order, how does Excel sort text values?

Answer: A to Z

Ascending sort orders text alphabetically from A to Z.

Which function would you use to round 4.567 to two decimal places?

Answer: =ROUND(4.567,2)

ROUND(number, num_digits) rounds a number to the specified number of decimal places using standard rounding rules.

What does VLOOKUP's fourth argument (range_lookup) do when set to FALSE?

Answer: Returns an exact match only

Setting range_lookup to FALSE forces VLOOKUP to find an exact match and return #N/A if none exists.

Which Excel chart type is best suited for showing the proportion of parts to a whole?

Answer: Pie chart

A pie chart displays each category as a slice of the whole circle, making part-to-whole relationships immediately visible.

What does the CONCATENATE function (or & operator) do in Excel?

Answer: Joins two or more text strings into one

CONCATENATE (or &) combines multiple text values into a single string.

Which function extracts a specified number of characters from the left side of a text string?

Answer: LEFT

LEFT(text, num_chars) returns the first num_chars characters from the beginning of a text string.

In a PivotTable, which area determines what values are calculated and displayed in the data cells?

Answer: Values area

The Values area contains the fields that are summarized (summed, averaged, counted, etc.) and shown in the body of the PivotTable.

What error does Excel display when a formula attempts to divide by zero?

Answer: #DIV/0!

#DIV/0! appears whenever a formula divides a number by zero or by an empty cell.

Which function returns the current date and time that updates each time the worksheet recalculates?

Answer: =NOW()

NOW() returns the current date and time as a serial number and refreshes every time Excel recalculates.

What is the purpose of the IFERROR function in Excel?

Answer: It returns a custom value when a formula produces an error

IFERROR(value, value_if_error) evaluates an expression and returns a specified result if any error occurs, keeping the spreadsheet clean.

Which keyboard shortcut inserts the current date as a static value in a cell?

Answer: Ctrl+;

Ctrl+; inserts today's date as a fixed value that does not change when the workbook is recalculated.

What does the SUMPRODUCT function do?

Answer: Multiplies corresponding elements of arrays and returns the sum of those products

SUMPRODUCT multiplies corresponding values in two or more arrays and then sums all those products in a single step.

How do you freeze the top row in Excel so it remains visible while scrolling down?

Answer: View tab → Freeze Panes → Freeze Top Row

On the View tab, clicking Freeze Panes and then Freeze Top Row locks row 1 in place during vertical scrolling.

Which Excel tool applies different formatting to cells automatically based on the values they contain?

Answer: Conditional Formatting

Conditional Formatting changes a cell's appearance (color, font, borders) dynamically based on rules tied to the cell's value.

What does the INDEX function return when given an array, a row number, and an optional column number?

Answer: The value at the specified row and column intersection

INDEX(array, row_num, [col_num]) returns the value at the intersection of the given row and column within the specified range.