This Excel Trick Makes Your Tables Update Themselves
From MyOnlineTrainingHub Ā· Ā· 8 min

The player loads from YouTube only when you click. It uses YouTube's privacy-enhanced mode.
If the video does not play, the owner may have turned off embedding. Watch it on YouTube instead.
Description
How to write one Excel formula that fills a whole column by itself. ā¬ļø Download the example file here and follow along: https://bit.ly/darrays26file š©āš« Learn more with my Advanced Excel Formulas course: https://bit.ly/darrays26course
š¬10 Excel Formula Symbols You Wish You Knew Years Ago: https://youtu.be/WBuS4b2rujg
Copying a formula down leaves you with a column of separate formulas, and any one of them can be overwritten with a typed value without anything on screen telling you. A figure stops recalculating, nobody clicks through the sheet cell by cell to check, and the report is wrong.
Dynamic arrays work differently. You write one formula in the top cell and it spills down as far as the data goes, growing when a row is added and shrinking when one is deleted. Overwrite a result and you get a visible #SPILL error rather than a silent wrong answer.
SEQUENCE numbers the list without you typing a single number. Replacing its hard-coded count with COUNTA makes the numbering adjust as tasks are added or removed. Wrapping SEQUENCE in TEXT with a "000" format and joining a prefix with & turns those into IDs like T-001. For due dates, one formula adds the entire start date column to the entire duration column, and the dot operator after the colon trims the trailing blanks so the result stops where the data stops.
The hash operator is what ties it together. Put # after a reference and your formula points at the whole spilled range instead of one cell, so a days left calculation, an IFS returning Overdue, Due soon or On track, and a COUNTIF summarising those statuses all expand and contract on their own. A formula referencing a spilled range doesn't have to return one the same size, which is why that COUNTIF spills into just three cells.
Now, the obvious question about Excel Tables. Tables do copy formulas down for you, which is useful, but you still get one separate formula per row and each can be overwritten. Keep your data in a table and put the dynamic array formulas outside it.
Most of this needs Excel 2021 or Microsoft 365. The dot operator is Microsoft 365 only.
LEARN MORE ===========
š° EXCEL NEWSLETTER - join 450K+ subscribers here: https://www.myonlinetraininghub.com/excel-newsletter-sign-up
šÆ FOLLOW me on LinkedIn: https://www.linkedin.com/in/myndatreacy/
š¬ EXCEL QUESTIONS: Get help on our Excel Forum: https://www.myonlinetraininghub.com/excel-forum
ā² TIMESTAMPS ============== 0:00 Why copying formulas down causes problems 0:35 Modern way of writing formulas in Excel 1:10 What you need 1:27 Number a list automatically with SEQUENCE 1:46 How spill ranges work 2:19 Make the numbering grow and shrink with COUNTA 2:49 Custom ID numbers with TEXT 3:26 Due dates for a whole column at once 3:42 Trim the blank rows with the dot operator 4:15 The hash operator for spilled ranges 4:46 If you want to write better Excel formulas 5:23 Using the hash operator 6:36 Dynamic arrays vs Excel Tables 8:07 The Excel trick I use every day
#Excel #ExcelFunctions #ExcelFormulas