Excel Magic Trick 1340: Create Expandable Range in an Excel Table (2 Methods)

Download Files:

Start File and Finished File:

See how to create expandable range in an Excel Table as part of a COUNTIFS formula that calculates a running count of names as there are entered into the table:
1. (00:12) Introduction
2. (00:35) Example of how Normal Expandable Range will NOT work
3. (02:44) INDEX Formula solution
4. (05:19) Expandable Range made with One Mixed Cell Reference (First Cell in Range) and Table Formula Nomenclature (Structured Reference) Relative Cell Reference (Second Cell in Range). This solution is thanks to Puneet Gogia at YouTube.
5. (06:21) Summary

Excel Table: Expandable Range, Dynamic Range, Number Incrementors

[wayneedmondson]

Hi Mike. Needed this solution and found your EMT 1340 video. As always, you saved the day with not 1 but 2 solutions. Thanks for helping me earn my living.. haha! Thumbs up!!

[khaleel_as]

This piece of information is rare to find. Thank you!! saved me a big chunk of frustration.

[krismaly]

Awesome Excel Guru...

I enjoyed watching this video and recommend others

Thanks for educating the community and appreciate your volunteer ship

[eCabinetstipsandtricks]

Yet another great video. Thank you for taking the time to share your knowledge.

[Barhomopolis]

OMG, Mike, you just saved my (***) big time with this trick!!! THANK YOU!

[jasonm]

Good info with the index.
Love me some Excel tables until I have to reference something from within the same table.

[pmsocho]

You can get "Normal Expandable Range" working when you put it as a named range :)

[skriya]

Hi Sir, i'm Really thankful to you.
please give me some respons, i ' m your die hard fan, give me some tips how can i learn excel and vba as like you,

Regards,
Sarab

[excel]

Hello Mike,

thank you for this beautiful formula. Is there any possible way to use this formula for the conditional formating, so that every value which is greater than 1 get´s red marked. I only know the formula =COUNTIF(A$1:A1;A1)>1 which get´s the job done in a datasheet without a table. But to keep it dynamic it would be great if your formula could be used for the condional formating...

[nishantkumar]

I need to add Serial no incremented automatically in excel table. Could you please help me out?
Thank you.

[MySpreadsheetLab]

Thank you for these solutions mike! I still find table formula syntax tricky. Do you think there could be performance issues with these methods in large tables (ie 30000+ rows) ? I normally use a different solution in large datasets to solve this.

[LotfyKozman]

Same problem can be seen also with ROWS() in a Table

[bigboyjpa]

Hi Mike, I know this question isn't related to this video but I"ve been looking for an answer and cannot find it anywhere. I have a formula on sheet two from the data in cell B9 from sheet one. I am trying to make a formula so that every row in sheet two gets data from every ninth row from sheet one. For example, sheet 2 row 1 data from b9 sheet 1, row 2 b18, row 3 b27, etc. Can you help me with this or point me to a video that you have already done. I would really appreciate it and would save me a lot of time.

[derthiel]

Great, but totally useless for people that just want a working function because nobody new knows how you put those things in the function.

[ameetkoomar]

Please check your commnets in video #358

formula doesn't work for any1. at least reply