Make Excel Formulas Dynamic with the This Trick

[@KenjiExplains]

🚀 FREE 50 Excel Hacks Template from Hubspot: clickhubspot.com/ew96

[@ahmadzamil9507]

Thank you for this! Love it! Will be very useful for me as Excel is my bread and butter. For the dynamic list I've been using OFFSET(Cell1,0,0,COUNTA(Column)) until today. Now I can just use the Hash.

[@KenjiExplains]

Glad it was helpful!

[@ChocaZed]

love them "Formula" names... seems fitting 😀 Go go McLaren! Great vid, btw! Keep up the good work 👍

[@abdelhafidassadiki2124]

You give us valuable and exclusive advice that makes us eager for more. We wish you success and brilliance

[@markpodesta4605]

Thank you Kenji. I have been converting formulas to use the # tag.

[@angelviloria4966]

I’m an expert in tech ed (by education + decades of experience) and your short dense video hit so well on so many dimensions. This is the first time I’ve seen one of your videos and I immediately subscribed. Excellent!

[@KenjiExplains]

Wow, thank you!

[@FayeM9]

GREAT video, just what I needed, btw my cousin Jake is building a new PC for work, and needs Office, any rec??

[@JohnJacobson555]

BNH Software with that, btw which type of PC is he building? Intel o Ryzen?

[@Ann_779]

that one is great, and worth it, My brother Paul works with PC do you need any help?

[@MogensBLaursen]

Thanks for sharing ! It seems that the spill operator only works on range that themselves are dynamic arrays. I have tried the #-operator on a column of static values and it returns an #REF! error. If I generate an intermediate column based on the static range, and then references the intermediate column using the #-operator, it works.

[@ChocaZed]

I had the same issue! On a static range it will not work... Why MS, why??!

[@ChocaZed]

BTW i should have added how i curcumvent this issue, as a rule of thumb i always use tables instead of cell ranges. On the validation list formula, use =indirect("TableName[Table Header Name]") this will be dynamically updated when something else is added to the table etc. Hope it helps!!

[@marwinreyes314]

What version of MS Excel are you using? I'm Using MS Office LTSC Professional Plus 2021 and the # sign does not work. Thank you.

[@markzalavari1780]

thank you, I've learned a lot. I missed the part when you add a new dataline for the table or the structured database.

[@richlawrence4160]

Yes, more common to add extra rows to a table than alter values of existing rows.

[@shabbirk2147]

Hi #Kenji, instead of complex formula counta(index(F11#,0,1)), you can use countif(F11#,G4), G4 being cell reference for subject

[@sarreqteryx]

(not at a computer I can actually check for myself) does the =UNIQUE() function not work inside the data validation list?

[@pascalejacquelinepetit5131]

Always informative and exceptionally well presented! Thank you Kenji for great videos. The pace is perfect to follow along, and the reasoning for why things work/don't work, helps us understand how Excel works.

[@Withanagehub]

hey ! in example 2 instead of COUNTA + # + INDEX Function, using COUNT + # can get easily same result. Thank You !

[@CavanHagan2]

Technically, but that's just because there's only one column containing numbers. The index is safer :)

[@Withanagehub]

@@CavanHagan2 oh thanks for sharing !

[@colleen5236]

this was very interesting :) but I couldnt get my # operator to work. I think I missed the part that the #operator only works on a data range that is a spilled array and not on just any data set or table? Or it should work on a table? I think in your example you were able to use the Unique function on the table set. Mine only works on a spilled array. yikes confused.

[@gyges2375]

Very helpful video. When I tried the "free" download, I started getting required questions like where I work, my phone number, how many employees we have etc. This is a marketing survey and not free. It's also nobody's business. A big disappointment to an otherwise really good video.

[@grbadalamenti]

Been rinsing and repeating this marketing trick since the early 2000s.

[@k.moyers4746]

I won’t be back at this channel.

[@Basic-wm8uf]

Nothing is free buddy. 😅

[@bobtahoma]

@@Basic-wm8ufFunny that, because I saw the sponsorship in the video. You’d be forgiven for thinking that would cover it.

[@pjserny]

Same. Pretty sad, but that’s where Jenny steps in.

[@43543191]

love it! this is a game changer! kudos Kenji!

[@KenjiExplains]

Thank you !

[@pagedinesh]

Great Sir, Thank you very much... very helpful

[@fernandosousa5480]

Very helpful. Great examples 🙏🏻

[@KenjiExplains]

Glad it was helpful!

[@TheGbelcher]

I’ve been looking for this my whole life. Thanks!

[@richlawrence4160]

Excellent presentation style. Very clear and concise. Thanks.

[@bjornclaeys3266]

Hi Kenji. this was indeed a very useful solution for my files

[@KenjiExplains]

Great to hear!

[@nsanch0181]

Thank you for the great video Kengi. There was a lot of useful #, table and function information in that.

[@shoppersdream]

Nice, thanks! Quick Question? Why didn't you use Count for Student Count? It works fine and we don't need to make it more complicated than is required.

[@johannnagel9005]

Hi Kenji, thank you for your excellent work! I would enjoy a video about saving ressources with excel. My spreadsheet with different macros and tables are slowly as hell because of many sort and filter-functions. Do you know any helping hacks for that? Thak you and best wishes from germany.

[@RikMaxSpeed]

Useful tips and interesting F1 driver names 😊

[@RobiBue]

Loved the driver names 😂 An opportunity was missed by naming Checo into Sergio Pemex! But hilarious notwithstanding!

[@ezio27me]

Hello, I follow your videos. I have a question that let assume i have lots of data arranged year wise and that year is the top row of my data, which is use to forecast model to forecast future data. When i convert those data into "Table" the year becomes the "Header of the table". Then it become useless as i cant use the year to forecast model!! Is there any other way to work in a table??

[@TechnoEveryday]

Learned new awesome technique ❤❤❤

[@bejoscha]

Thanks, that was really well presented and will be very handy for me in the future.

[@Athena621]

Can you not extend the array by inserting a column within the array, say between B and C? Does the # array extension work in Pivot tables?

[@PercyNPC]

8:37 Can you type =UNIQUE formula that you just created? You don't need to create new cell and hide it.

[@MMed7]

Than you ! thank you ! thank you ! sooooo much !!

[@techlon]

very cool, thanks for sharing! 😊

[@Kevin-g3g1v]

Thanks a lot!

[@Wintermute4]

Could you put the unique function in the validation formula directly?

[@chrisneedhamc3]

Very helpful video, indeed. Though I feel obliged to point out a certain irony at 9:40 - two UNIQUE values... 😅😏

[@someguy-k2h]

Great information to know. I'll use it in the future.

[@AHKtipszone]

# sign is very powerful. thanks

[@NirRobinson]

Thank!! Superb explanation. Will try it the soonest

[@Tharkz]

Keep an eye out for the upcoming TRIMRANGE too.

[@SubairAbulHudhasl]

In which version of excell this # command is working

[@SubairAbulHudhasl]

Hash formula is not working in the free version of MSoffice.

[@HopeElayne]

Ty for this!

[@KenjiExplains]

Thank you!

[@SpaaacceCore]

Nice! Although in your real world example, all of that is built into the table already... Just use the built-in table column totals.

[@CAKimberlyLewis]

😉 The reason for this is explained at 9:26

[@SpaaacceCore]

​@@CAKimberlyLewis No, not really. He says functions such as UNIQUE and FILTER don't work in table columns, however they are irrelevant for tables so they don't need to work. Tables already have a built-in filter on every column. Then, adding the built-in totals row using the SUBTOTAL function allows sum, avg, min, max, count, etc. all dynamically based on the applied filter. 😉

[@afmutw]

In the real world you need to be able to send your work to someone who uses an older version of excel. (Tables are older so may still work.)

[@marshallc6215]

That requires that you use a table, though. I can imagine several use cases for me for this, maintaining legacy code where nobody converted to tables, and because the data is dumped from vba, it's not a simple "select all - convert to table"

[@Kostyashym]

thank you!

[@rabiulhaque172]

Great Sir..

[@sallyho146]

I don’t have dynamic array so i couldn’t use the hash operator. I just have a list of ID# of customer names I need to count. How can I use the hash operator to count the total customer names?

[@jamjardavies]

I see a fellow F1 fan!

[@KenjiExplains]

Hahah glad you noticed!!

[@wallywechs4694]

Wow, very resourceful

[@billybank2012]

so how do you tell excel to continue day input in one cell in one worksheet based on the previous entry in another worksheet

[@jfoetidnwo56]

does that work in google sheets?

[@camlex6310]

Thanks!! Great video!!

[@mikemitchell9227]

That was great. Thanks. I'm not happy about Lewis having the lowest score, but that's OK. 😂

[@gioyfa]

😂😂 Next year, will have higher grades.

[@sivareddy3745]

Hi Kenji, it is good and nice.

[@oswaldo.g]

The best part of the video is the students name's.

[@ahmedshalaby9343]

what's the meaning by arrays ?

[@pyrraca478]

8:39 in a validation list coming from a table, excel automatically made the list with unique values. You don't need to use any kind of formula

[@drewbny78]

On a separae topic, anyone know how to change source of multiple objects in powerpoint at once?

[@Cirruna]

Thank you! I was just looking for this. Great timing.

[@jerry171460]

Thumbs up!

[@KenjiExplains]

Thank you! Cheers!

[@RajuAdhikari-y8r]

Not working brother please help ...Microsoft office 2021

[@Daniel-jx5zt]

Amazing

[@KenjiExplains]

Thank you!

[@yashgupta6812]

# is not working in my excel. Can you help me ?

[@KenjiExplains]

Probably because you have one of the older Excel versions. For reference, I am using Microsoft 365

[@yashgupta6812]

@@KenjiExplains yes i am also using 365. i am using sum function of three numbers. =sum(H2#), let say 1st number is in H2 cell, after click on #, it should select whole data range but it is not working

[@alvaropallete]

amazing

[@KenjiExplains]

Glad it was helpful!

[@josephleslie007]

It will work on latest version I think

[@Anti-socialSocialClub]

How can one application have so many functions ???

[@m.ssharma535]

Dynamic array # (hash) operator explanation

[@SHEERAZ-ALI]

# not working in excel 2016

[@donireland6218]

Good tip. One thing I'd like to see is how to deal with a column of numbers in which you don't know the number of rows. For example: a budget. At the top, I might have Sum(A2:A100). But what if something happens and I need to add a row? I'd really like to find a way to tell it to sum ALL THE CELLS below A1.

[@kevinperez7555]

Hmmm. Did not work for me unfortunately.

[@jerryoltremari317]

How did you come up with “90% of excel users don’t know this”?

[@kazhaaa]

He used dynamic array

[@rosslynross6380]

💌💌💌💌💌💌💌💌

[@FirstLast-is9xe]

Not hugs, that's hacks!

[@willzinner8813]

1st

[@KenjiExplains]

Let’s go!

[@dennisk5500]

Gonna go with formulas over formulae, huh? Yeah, this is KZbin after all

[@joserosa5342]

ITs easy to use some functions when u are using the right version of Excell to do ir. U cant use those fuctions in Excell below 2019. So sad.

[@d1m18]

Thank you for sharing. May I suggest you don't show us the bad way to do formulas? Like when you did average formula and let it find the only numeric column - this is very poor practice and of no use to learn the wrong way to do it.

[@realbangkok]

1,000 years far from google sheet, it is not 100% dynamic because at first you have to make table at column B

[@karllinguistik7244]

Hi. Sorry. Could you explain what you mean in more detail. I'm really interested in this?

[@stanTrX]

I dont like to use dynamic arrays

[@WindyLion]

The hash operator is great when you are displaying the data in a dashboard with numeric fields, but as soon as you want to put a dynamic array into a graph, it stops working. The solution is to reference the dynamic array in the name manager, e.g. tableData =OFFSET( DataSheet!$A$1, 1, 0, COUNTA( DataSheet!$A$A ) - 1, COUNTA( DataSheet!$1$1 )) tableFilter =FILTER( tableData, INDEX( tableData, , 2) = "criterion" ) tableX = INDEX( tableFilter, , 3 ) tableY1 = INDEX( tableFilter, , 6 ) tableY2 = INDEX( tableFilter, , 8 ) Then in the data selection interface of the table, reference workbookName.xlsx!tableX for the x values and workbookName.xlsx!tableY1 and workbookName.xlsx!tableY2 for the y values. Now, if you add rows to the data sheet or if you change the filter criteria, the graph will update automatically.