fx Analysis & Visualization

Dynamic Arrays

60 min6 lessons18 quiz questions

Lessons

1. Spill ranges and the # operator10 min 2. FILTER — a live filtered table with a formula10 min 3. UNIQUE, SORT, SORTBY10 min 4. SEQUENCE, RANDARRAY, TOCOL/TOROW10 min 5. LET — making formulas readable and fast10 min 6. LAMBDA basics — your own custom function10 min

Practice file

⬇ Download analysis-visualization-practice.zip

Analysis & Visualization practice bundle

Extract the ZIP before opening files. Start with pivot-practice.xlsx: its Sales sheet contains the original 16-row tblSales table for pivot and dynamic-array exercises. Grand total: ₹10,70,000. Keep an unchanged copy as your baseline.

capstone/data/ contains 24 monthly sales CSVs, totaling 50,000 synthetic orders from April 2024 through March 2026. Stores, Products and Targets CSVs sit outside that folder so a sales-only folder import does not combine incompatible schemas.

analysis-reference.xlsx contains a Wide Targets exercise and independently calculated reference summaries for checking your work. These are plain tables, not completed PivotTables or a Power Pivot model; build those in Excel as directed in the lessons. solutions-en.md and solutions-hi.md provide exercise answers.

calendar.csv contains daily dates for 2024–2026, FY labels and fiscal month numbers. Load it into the model and mark its Date column as the date table. answer-key.json and the optional standard-library audit_capstone.py let you reconcile the CSVs without Excel. README.md explains setup and filter scope.

Use a supported Windows desktop Excel edition with Power Pivot for the complete model exercises. The modern dynamic-array lessons specify their own Excel version requirements. The download contains no macros, credentials, or external connections.

Module quiz (18)

1. What does H2# refer to?

The whole spill range of the formula in H2

2. #SPILL! most common cause?

Cells in the spill area aren't empty

3. Can a dynamic array spill inside an Excel Table?

4. How do you write AND in FILTER?

Multiply the conditions

5. Result when nothing matches and no if_empty?

#CALC!

6. West AND Furniture total in the practice data?

1,20,000

7. Function to sort by a column that isn't returned?

SORTBY

8. Count distinct products?

=COUNTA(UNIQUE(...))

9. Top salesperson in the practice data?

Amit, 4,19,000

10. 1 to 12 across one row?

=SEQUENCE(1,12)

11. Best way to list month starts?

EDATE with SEQUENCE

12. How do you stop RANDARRAY changing?

Copy → Paste Values

13. What is the last argument of LET?

The calculation/result

14. Why can LET make a formula faster?

Repeated parts are calculated once

15. Is FY26 a valid LET name?

No — it looks like a cell reference

16. Where do you save a LAMBDA with a name?

Name Manager

17. Result of a LAMBDA typed alone in a cell?

#CALC!

18. Which helper applies a LAMBDA to each value?

MAP