Make Excel Formulas Dynamic with the Hash Sign

[@KenjiExplains]

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

[@Chris-ch5ei]

"Thanks for your videos, Kenji! They've been a huge help to me, and they continue to make a difference. Every time I watch one of your videos, I can see how much better I'm getting. I really appreciate your help!

[@abdelhafidassadiki2124]

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

[@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!

[@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!

[@ChocaZed]

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

[@43543191]

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

[@KenjiExplains]

Thank you !

[@richlawrence4160]

Excellent presentation style. Very clear and concise. Thanks.

[@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?

[@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.

[@pagedinesh]

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

[@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!

[@fernandosousa5480]

Very helpful. Great examples 🙏🏻

[@KenjiExplains]

Glad it was helpful!

[@TheGbelcher]

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

[@bjornclaeys3266]

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

[@KenjiExplains]

Great to hear!

[@chrisneedhamc3]

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

[@nsanch0181]

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

[@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.

[@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.

[@TechnoEveryday]

Learned new awesome technique ❤❤❤

[@bejoscha]

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

[@Kevin-g3g1v]

Thanks a lot!

[@shabbirk2147]

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

[@someguy-k2h]

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

[@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!!

[@MMed7]

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

[@techlon]

very cool, thanks for sharing! 😊

[@NirRobinson]

Thank!! Superb explanation. Will try it the soonest

[@AHKtipszone]

# sign is very powerful. thanks

[@Kostyashym]

thank you!

[@rabiulhaque172]

Great Sir..

[@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 !

[@wallywechs4694]

Wow, very resourceful

[@HopeElayne]

Ty for this!

[@KenjiExplains]

Thank you!

[@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.

[@sivareddy3745]

Hi Kenji, it is good and nice.

[@camlex6310]

Thanks!! Great video!!

[@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"

[@Tharkz]

Keep an eye out for the upcoming TRIMRANGE too.

[@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.

[@PercyNPC]

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

[@sarreqteryx]

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

[@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.

[@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??

[@jamjardavies]

I see a fellow F1 fan!

[@KenjiExplains]

Hahah glad you noticed!!

[@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.

[@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

[@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.

[@Wintermute4]

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

[@Cirruna]

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

[@oswaldo.g]

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

[@Daniel-jx5zt]

Amazing

[@KenjiExplains]

Thank you!

[@SubairAbulHudhasl]

In which version of excell this # command is working

[@SubairAbulHudhasl]

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

[@alvaropallete]

amazing

[@KenjiExplains]

Glad it was helpful!

[@jerry171460]

Thumbs up!

[@KenjiExplains]

Thank you! Cheers!

[@jfoetidnwo56]

does that work in google sheets?

[@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?

[@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?

[@josephleslie007]

It will work on latest version I think

[@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

[@ahmedshalaby9343]

what's the meaning by arrays ?

[@m.ssharma535]

Dynamic array # (hash) operator explanation

[@drewbny78]

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

[@Anti-socialSocialClub]

How can one application have so many functions ???

[@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.

[@RajuAdhikari-y8r]

Not working brother please help ...Microsoft office 2021

[@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

[@SHEERAZ-ALI]

# not working in excel 2016

[@kevinperez7555]

Hmmm. Did not work for me unfortunately.

[@FirstLast-is9xe]

Not hugs, that's hacks!

[@rosslynross6380]

💌💌💌💌💌💌💌💌

[@jerryoltremari317]

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

[@kazhaaa]

He used dynamic array

[@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.

[@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.

[@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.