H
Certification Courses
Want a structured paid program with certificate and roadmap?
Which formula adds the values in cells B2 through B8?
`SUM` accepts a range such as B2:B8 and returns the total.
In `=$A$1*B2`, what happens to `$A$1` when the formula is copied to another cell?
Dollar signs before both the column and row make an absolute reference.
Which formula returns `Pass` when C2 is at least 50 and `Review` otherwise?
`IF` tests a condition and returns its second argument when true, otherwise its third argument.
Which function sums D2:D100 only for rows where A2:A100 equals `West` and B2:B100 is at least 100?
`SUMIFS` takes the sum range first, followed by each criteria range and criterion.
Which function counts rows where A2:A100 is `West` and B2:B100 is at least 100?
`COUNTIFS` counts rows meeting all paired range-and-criterion conditions.
What limitation of VLOOKUP is avoided by XLOOKUP in current Excel versions?
XLOOKUP separates lookup and return arrays, so the return array need not be to the right.
In `=VLOOKUP(E2,A2:C100,3,FALSE)`, what does `FALSE` request?
The fourth VLOOKUP argument `FALSE` requests exact-match behavior.
Which combination can return a value from column C for the row where E2 is found in column A, even when the return column is left of the lookup column?
`MATCH` finds the row position using exact match mode 0, and `INDEX` returns the value at that position.
You want to inspect only records for the East region without deleting other rows. What should you use?
Filtering temporarily limits visible rows while retaining the other records.
What is a good use of conditional formatting?
Conditional formatting changes appearance based on rules; it does not replace the cell data.
What is the main purpose of a PivotTable?
A PivotTable aggregates and rearranges data for analysis while keeping the source table separate.
Before creating a chart, what should you verify first?
Clear labels and a well-selected range help produce a chart that represents the intended comparison.
A column contains dates stored inconsistently as text and date values. What is the best first data-cleaning step?
Data cleaning should preserve valid records and verify that conversions produce consistent underlying values.
Which Excel feature is designed to compare outputs when one or more input assumptions change?
What-If Analysis explores how changing inputs affects a formula result.