Show:
Conditional Functions
Conditional Min/Max
Logical Logic
Pivot Tables & Charts
Conditional Formatting
100

What is the correct syntax to sum the values in range B2:B10 where the corresponding category in A2:A10 is "Sales"?   

  • A) =SUMIF("Sales", A2:A10, B2:B10)

  • B) =SUMIF(A2:A10, "Sales", B2:B10)

  • C) =SUMIF(B2:B10, A2:A10, "Sales")

  • D) =SUMIF(A2:A10, B2:B10, "Sales")

B) =SUMIF(A2:A10, "Sales", B2:B10)

100

 What is the primary purpose of the MAXIFS function? 

  • A) To return the absolute maximum value in an entire workbook.

  • B) To return the maximum value among cells that meet one or more specified conditions.

  • C) To multiply the maximum value by a condition.

  • D) To count how many cells exceed the maximum value.

B) To return the maximum value among cells that meet one or more specified conditions.

100

What is the result of =IF(10 > 20, "Pass", "Fail")? 

  • A) Pass

  • B) Fail

  • C) TRUE

  • D) #VALUE!

B) Fail

100

What is the primary function of a Pivot Table in Excel? 

  • A) To automatically format cell colors.

  • B) To write VBA macros.

  • C) To insert complex mathematical functions into individual cells.

  • D)  To summarize, analyze, and group large amounts of data quickly.

D)  To summarize, analyze, and group large amounts of data quickly.

100

What is the primary purpose of Conditional Formatting? 

  • A) To change font family across the whole worksheet.

  • B) To lock cells so they cannot be edited. 

  • C)  To automatically apply visual formatting (colors, borders, fonts) based on cell values or rules.

  • D) To password protect a worksheet.


C)  To automatically apply visual formatting (colors, borders, fonts) based on cell values or rules.

200

You want to count how many cells in range C2:C20 contain a value strictly greater than 50. Which formula should you use? 

  • A) =COUNTIF(C2:C20, >50)

  • B) =COUNTIF(C2:C20, ">50")

  • C) =COUNTIF(C2:C20, C2:C20 > 50)

  • D) =COUNTIF(">50", C2:C20)

B) =COUNTIF(C2:C20, ">50")

200

What is the required first argument in the MINIFS function? 

  • A) min_range (the range from which the minimum value will be determined)

  • B) criteria_range1

  • C) criteria1

  • D) evaluation_range

  • A) min_range (the range from which the minimum value will be determined)

200

 The formula =AND(A1>5, B1<10) evaluates to TRUE only when: 

  • A) At least one of the conditions is true.

  • B) A1 is equal to B1..

  • C) Neither condition is true.

  • D) Both conditions are true at the same time.

D) Both conditions are true at the same time.

200

 By default, what calculation does a Pivot Table apply when a text field is dragged to the "Values" area? 

  • A) Sum

  • B) Average

  • C) Count

  • D) Max




C) Count

200

What happens when a cell meets the criteria of two conflicting Conditional Formatting rules? 

  • A) Excel throws an error.

  • B) The rule higher in the hierarchy order within the Rules Manager takes precedence.

  • C) The cell becomes transparent.

  • D) The most recently created rule always wins.

B) The rule higher in the hierarchy order within the Rules Manager takes precedence.

300

Question: What does the formula =AVERAGEIF(A1:A5, "<>", B1:B5) return? 

  • A) The average of B1:B5 excluding zero values.

  • B) A #VALUE! error. 

  • C) The average of B1:B5 for cells where A1:A5 are NOT blank.

  • D) The sum of A1:A5 divided by B1:B5.

C) The average of B1:B5 for cells where A1:A5 are NOT blank.

300

 In =MAXIFS(D2:D100, A2:A100, "North", B2:B100, "2024"), what is being evaluated? 

  • A) The maximum sum of range A2:A100 for the year 2024.

  • B) The maximum value in D2:D100 where the region is "North" AND the year is "2024".

  • C) The minimum value of the North-2024 combination.

  • D) The total count of "North" entries in 2024.

B) The maximum value in D2:D100 where the region is "North" AND the year is "2024".

300

What value does =OR(5 > 10, 2 = 2, 8 < 3) return? 

  • A) FALSE

  • B) TRUE

  • C) #N/A

  • D) 0

B) TRUE

300

What is the purpose of adding a "Secondary Axis" to a chart? 

  • A) To change the title formatting of the chart.

  • B) To plot two data series with significantly different scale ranges (e.g., $ Amounts and % Percentages).

  • C) To move the chart legend to the bottom.

  • D) To hide data series from view.

B) To plot two data series with significantly different scale ranges (e.g., $ Amounts and % Percentages).

300

 When using a formula rule to highlight an entire row across $A$2:$D$10 based on Column A, how should the column reference be formatted? 

  • A) Relative column and row (e.g., A2="Completed")

  • B) Relative column, absolute row (e.g., A$2="Completed") 

  • C) Absolute column and row (e.g., $A$2="Completed")

  • D) Absolute column, relative row (e.g., $A2="Completed")

D) Absolute column, relative row (e.g., $A2="Completed")

400

 If you want to use wildcards in COUNTIF to count how many cells in A1:A10 start with the letter "A", which criterion is correct? 

  • A) "A?"

  • B) "A#"

  • C) "A%"

  • D) "A*"

D) "A*"

400

 If no cells meet the specified criteria in a MINIFS formula, what result does Excel return? 

  • A) #N/A

  • B) #VALUE!

  • C) 0

  • D) An empty text string ""




C) 0

400

 What is the result of =NOT(OR(5 > 2, 3 = 3))? 

  • A) TRUE

  • B) NULL

  • C) #VALUE!

  • D) FALSE

D) FALSE

400

Which Pivot Table feature allows you to add custom calculations based on other existing fields? 

  • A) Slicer

  • B) Calculated Field

  • C) Timeline

  • D) Conditional Format

B) Calculated Field

400

Which of the following is NOT a built-in Conditional Formatting feature? 

  • A) Data Bars

  • B) Color Scales

  • C) Icon Sets

  • D) Sparklines

D) Sparklines

500

What is the main difference in argument order between SUMIF and SUMIFS? 

  • A) In SUMIF, the sum range comes last; in SUMIFS, the sum range comes first.

  • B) In SUMIFS, you do not specify a sum range.

  • C) They have the exact same argument structure.

  • D) In SUMIF, the first argument is always the text condition.

A) In SUMIF, the sum range comes last; in SUMIFS, the sum range comes first.

500

What happens if the criteria range (A1:A10) and min range (B1:B20) are not the same size in MINIFS? 

  • A) Excel automatically resizes the range.

  • B) It returns a #VALUE! error.

  • C) It evaluates only the first 10 cells without error.

  • D) It returns 0.

B) It returns a #VALUE! error.

500

You want to check if an employee gets a bonus: Sales > 1000 OR Tenure > 5 years, AND zero absences (C2 = 0). Which formula is correct? 

  • A) =IF(AND(OR(Sales>1000, Tenure>5), C2=0), "Bonus", "No Bonus")

  • B) =IF(OR(AND(Sales>1000, Tenure>5), C2=0), "Bonus", "No Bonus")

  • C) =IF(AND(Sales>1000, Tenure>5, C2=0), "Bonus", "No Bonus")

  • D) =IF(NOT(OR(Sales>1000, C2=0)), "Bonus", "No Bonus")

A) =IF(AND(OR(Sales>1000, Tenure>5), C2=0), "Bonus", "No Bonus")

500

When source data changes, what must you do to update the Pivot Table? 

  • A) Nothing, it updates automatically in real time.

  • B) Click "Refresh" or press ALT + F5.

  • C) Recreate the Pivot Table from scratch.

  • D) Fix the #REF! error.

B) Click "Refresh" or press ALT + F5.

500

What does checking the "Stop If True" box in Conditional Formatting do? 

  • A) Deletes remaining rules if an error occurs.

  • B) Prevents subsequent rules from being evaluated if the current rule condition is met.

  • C) Stops macro execution.

  • D) Locks the cell value.

B) Prevents subsequent rules from being evaluated if the current rule condition is met.