Duration: 26:26
🧾 Analytical Summary
Vans from the Spreadsheet/BI team delivers the "second most wishlisted talk" on dashboard performance optimization. Performance expectations: <10K elements (instantaneous), 10K-100K (acceptable), >100K (problems start).
Array Formulas: Turn repeated formulas into single array formulas (vectorization)—dramatically reduces snapshot file size. Example: 15 individual formulas become 2 array formulas, dividing file size by 7.5x.
Irregularity Map: Color-codes formula patterns to spot errors and identify vectorization opportunities—same color = same formula shape. Makes mistakes visually obvious.
Dynamic Pivots: Replace cell-by-cell pivot definitions with single =PIVOT(id) formula spanning all results—one formula writing many values.
Data Tables: Use data tables for formatting instead of individual cell styling—defined once, applied to ranges.
Duplicate vs Copy-Paste: Duplicating sheets creates tiny revisions; copy-pasting creates massive revisions (potentially breaking spreadsheets at 600MB limit). Always duplicate, never copy-paste large ranges.
Remove Unused Data Sources: If not referenced, delete them—reduces browser memory and processing load.
VLOOKUP Optimization: Sort data and use =VLOOKUP(value, range, column, TRUE) for binary search (much faster than linear).
Dependencies Matter: Example showing 9,000 cells taking 10 seconds to fill due to excessive dependency chains (formula referencing range, which references another range, creating exponential dependencies). Hardcode where possible to break dependency chains.
Key Insight: One file optimized from 160KB to <10KB without losing features—through vectorization, dynamic pivots, and data tables.
🧠 Viewpoint: Odoo Perspective
Spreadsheet performance requires understanding architecture—not just using features, but knowing how they're implemented. The irregularity map tool exemplifies our philosophy: build tools that make best practices obvious. When color reveals formula patterns, optimization becomes visual rather than analytical. And the 160KB → 10KB optimization proves that with correct techniques, scalability isn't a feature limitation—it's a knowledge gap we can close through education.
🏢 Viewpoint: Competitors
The performance guidance is sound, though the complexity required (array formulas, manual vectorization, understanding dependency graphs) suggests the abstraction layers haven't fully shielded users from implementation details. Enterprise BI tools typically optimize these patterns automatically—users shouldn't need to understand binary search vs linear search for lookups. That said, power users appreciate control, and the educational approach empowers sophisticated users to push boundaries further than automatic optimization ever could.
Disclaimer: This article contains AI-generated summaries and fictionalized commentaries for illustrative purposes.