Level 4 exam — Microsoft Excel

Exam — fresh questions

12 fresh questions (different from the flashchecks), covering all of level 4: Power Query, data model, dynamic formulas, VBA and advanced functions.
Answer everything, then click Check: your score and explanations will appear.

4.1 · Recall

1. What does Power Query produce from a folder of monthly files?

A direct modification of the source files A frozen, non-editable chart An alphabetical sort of the file names A transformed and combined copy, refreshable via Refresh

Power Query reads the sources without modifying them and produces a combined table; Refresh replays the steps on the new files.

4.1 · Application

2. Wassim receives the same file to clean every month. What does he do?

He builds the steps once in Power Query then Refreshes each month He deletes the old months before importing He randomly renames the source each time He redoes the cleaning by hand in the sheet

Steps recorded once, replayed via Refresh: the monthly cleaning is no longer redone by hand.

4.1 · Application

3. To consolidate 12 identical monthly files from a folder, which feature?

Power Query ▸ From a Folder ▸ Combine A SUM on the file names A pivot alone without importing An XLOOKUP copied everywhere

Power Query stacks (Append) the files of a folder into a single table; a 13th file dropped in is included after Refresh.

4.2 · Recall

4. What is a DAX measure?

A recorded macro A reusable and contextual calculation that adapts to the pivot's filters A pivot color A chart type

A measure (e.g. SUM(Sales[Amount])) lives in the model and recalculates according to the filter context (city, month…).

4.2 · Application

5. Wassim has Sales and Products with a common key. How to cross them without multiplying XLOOKUPs?

Sort both tables alphabetically Write a single big SUM over everything Create a relationship in the data model then a multi-table pivot Flatten everything by hand into one giant table

The model relates the tables by the key; a pivot then crosses several related tables as one.

4.3 · Recall

6. What does the #SPILL! error mean?

The formula contains a typo A dynamic formula can't spill because a target cell is occupied The file is corrupted A parenthesis is missing

The formula wants to fill several cells but the spill space is blocked; you free the cells below/right.

4.3 · Application

7. Wassim wants the sorted list of distinct regions, which updates by itself. Which formula?

=XLOOKUP(Region) =COUNT(Region) =SUM(Region) =SORT(UNIQUE(Sales[Region]))

UNIQUE removes duplicates, SORT orders; the result spills and automatically follows the data.

4.3 · Arbitration

8. Wassim applies the 19% VAT in ten formulas. What good practice?

Name a LAMBDA function (e.g. VAT19) and reuse it Record a macro for each cell Sort the column of rates Copy 1.19 hard-coded into each formula

LAMBDA defines a reusable custom function (VAT19): a single rate to maintain, zero repetition.

4.4 · Application

9. To format a report identically each week, which fast solution?

Redo it entirely by hand An XLOOKUP on the colors Record a macro and replay it (.xlsm workbook) A pivot with no formatting

The recorder memorizes the gestures as VBA; one click replays the whole formatting (.xlsm workbook, test on a copy).

4.4 · Arbitration

10. Wassim receives a workbook with macros from an unknown sender. What does he decide?

He enables everything then undoes with Ctrl+Z if needed He converts to .xlsx to keep the macros He tests on a copy but enables anyway without checking He doesn't enable it: untrusted source, enable with full awareness only

Macros = virus risk: enable with full awareness, trusted sources only; Ctrl+Z doesn't undo a macro, and .xlsx loses macros.

4.5 · Application

11. A column contains #N/A and the table is filtered; Wassim wants to sum the visible without crashing. Which function?

OFFSET alone AGGREGATE (ignore errors and hidden rows) A simple SUM INDIRECT alone

AGGREGATE combines an operation (9=sum) and options (ignore errors / hidden), where SUM would return #N/A.

4.5 · Arbitration

12. Wassim's closing workbook is slow, riddled with OFFSET and INDIRECT. What does he decide?

Add even more volatile functions Remove AGGREGATE to go back to SUM Limit the volatiles and prefer INDEX / non-volatile structured tables Rename the sheets to break INDIRECT

OFFSET and INDIRECT are volatile (constant recalculation): you limit them and prefer non-volatile alternatives where possible.

What's next?

Score ≥ 80%: move on to Level 5 — full pro cases, and industrialize what you've learned (refreshable Power Query flows, model + DAX, living formulas, careful macros, robust AGGREGATE).
Score 50-79%: revisit the failed sections (Power Query 4.1, model 4.2, dynamics 4.3, VBA 4.4, advanced 4.5), re-read the explanations above, then retake the test.
Score < 50%: restart the level from 4.1, practicing each gesture (flow + Refresh, relationship + measure, FILTER/SORT/UNIQUE + LAMBDA, macro on a copy .xlsm, AGGREGATE vs volatiles), then retry the exam with a clear head.