Subscribe: Apple Podcasts | Android | Google Podcasts | Stitcher | TuneIn | Spotify | RSS
Have you ever wondered what an “array-entered formula” is? It’s an intermediate/advanced concept in Excel but in late 2018, Microsoft released dynamic array functions and formulas that “spill” into the cells below your current cell with a function. This makes writing formulas easier and less prone to human error, but there are some tradeoffs to using these formulas which I discuss in this episode.
Implicit intersection: what Excel does behind the scenes without you knowing
This is not meant to cause fear as in Excel is doing something “behind your back.” Many Excel users don’t know that Excel does some magic behind the scenes for formulas where the input may be a range of cells but the formula is not necessarily a formula that is meant to accept a range of cells. Excel does something called Implicit Intersection.
With dynamic array functions turned on in your workbook, you may have to start using the “@” operator to tell Excel to keep implicit intersection “on.” There are a lot of edge cases where you would need to use the “@” operator so I’d recommend reading this blog post if you would like to learn more.
Bringing array formulas to the masses
I argue that dynamic array functions and spill formulas are giving new Excel users a way to quickly calculate, filter, and sort their data sets without needing to go through a myriad of menus in the toolbar. Given that more jobs these days require working with large data sets and familiarity with various data models (SQL, NoSQL, GraphQL), knowing how to quickly manipulate data that’s structured in one of these database models is becoming more important than ever.
I think that advanced Excel and SQL users will notice that Excel is getting closer to how PivotTables and SQL operate. With PivotTables, you have calculated fields which are similar to dynamic array functions in that you write the formula once and it applies to your entire PivotTable no matter how you slice and dice your data. In SQL, you are pretty much writing your own user-defined fields and aggregating data from other columns.
Other Podcasts & Blog Posts
In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:
- SheetsCon: Not a podcast, but a great virtual conference all about Google Sheets and all the replays are free to watch
- devMode.fm #66: Interviewing for a Webdev Job in 2020
Top comments (0)