Excel Mastery Quiz — Quiz

Data cleaning, dynamic arrays, validation, Power Query and dashboard building. · 10 questions

Question 1 of 10

Which function removes leading and trailing spaces but keeps single spaces between words?

aCLEANbTRIMcSUBSTITUTEdPROPER

Question 2 of 10

Which shortcut triggers Flash Fill?

aCtrl+TbCtrl+Shift+LcCtrl+DdCtrl+E

Question 3 of 10

Which formula returns a distinct list of states from a column?

a=UNIQUE(Sales[State])b=DISTINCT(Sales[State])c=SORT(Sales[State])d=COUNTIF(Sales[State],"*")

Question 4 of 10

In Data Validation, which Allow option creates a dropdown list in a cell?

aDecimalbCustomcListdText length

Question 5 of 10

In the formula below, what role does the `*` play?

=FILTER(Sales[Invoice], (Sales[State]="Rajasthan")*(Sales[Taxable]>50000), "None")

aIt multiplies the invoice valuesbIt joins the two text conditionscIt acts as a logical AND between the two conditions, row by rowdIt is a wildcard for any state

Question 6 of 10

Cell D2 holds the formula below and D5 already contains text. What happens?

=SORT(UNIQUE(Sales[City]))

aExcel returns #SPILL! because a cell in the spill range is occupiedbExcel overwrites D5cExcel returns only the first citydExcel returns #NAME?

Question 7 of 10

The LAMBDA below is saved in Name Manager as `GSTAmount`. What does `=GSTAmount(50000,18)` return?

=LAMBDA(taxable, rate, ROUND(taxable * rate / 100, 2))

a900b59000c#VALUE!d9000

Question 8 of 10

A conditional formatting rule applied to `$A$2:$H$500` should colour the whole row when column E exceeds 1,00,000. Which formula is correct?

a=E2>100000b=$E2>100000c=$E$2>100000d=E$2>100000

Question 9 of 10

In Power Query, a text column of dates like `13/04/2026` errors when you change its type to Date on a US-English machine. What is the correct fix?

aRemove the error rowsbSplit the column and rebuild the datecReplace the slashes with hyphens firstdUse Change Type With Locale and choose Date, English (India)

Question 10 of 10

Your dashboard has three PivotTables from the same Table, but the State slicer filters only one of them. What fixes it?

aCopy and paste the slicer twicebConvert the slicer into a TimelinecRight-click the slicer, open Report Connections and tick all three PivotTablesdMove all three PivotTables onto the dashboard sheet