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.
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!
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.
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.
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.
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!!
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.
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??
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.
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.
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?
@@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. 😉
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"
@@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
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.
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.
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.
🚀 FREE 50 Excel Hacks Template from Hubspot: clickhubspot.com/ew96
love them "Formula" names... seems fitting 😀 Go go McLaren!
Great vid, btw! Keep up the good work 👍
You give us valuable and exclusive advice that makes us eager for more. We wish you success and brilliance
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.
Glad it was helpful!
Thank you Kenji. I have been converting formulas to use the # tag.
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!
Wow, thank you!
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.
Been rinsing and repeating this marketing trick since the early 2000s.
I won’t be back at this channel.
Nothing is free buddy. 😅
@@Basic-wm8ufFunny that, because I saw the sponsorship in the video. You’d be forgiven for thinking that would cover it.
Same. Pretty sad, but that’s where Jenny steps in.
GREAT video, just what I needed, btw my cousin Jake is building a new PC for work, and needs Office, any rec??
BNH Software with that, btw which type of PC is he building? Intel o Ryzen?
that one is great, and worth it, My brother Paul works with PC do you need any help?
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.
I’ve been looking for this my whole life. Thanks!
Great Sir, Thank you very much... very helpful
Hi Kenji. this was indeed a very useful solution for my files
Great to hear!
Thank you for the great video Kengi. There was a lot of useful #, table and function information in that.
thank you, I've learned a lot. I missed the part when you add a new dataline for the table or the structured database.
Yes, more common to add extra rows to a table than alter values of existing rows.
love it! this is a game changer! kudos Kenji!
Thank you !
Excellent presentation style. Very clear and concise. Thanks.
Useful tips and interesting F1 driver names 😊
Loved the driver names 😂
An opportunity was missed by naming Checo into Sergio Pemex! But hilarious notwithstanding!
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.
I had the same issue! On a static range it will not work... Why MS, why??!
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!!
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.
Very helpful. Great examples 🙏🏻
Glad it was helpful!
Thanks a lot!
Thanks, that was really well presented and will be very handy for me in the future.
Hi #Kenji, instead of complex formula counta(index(F11#,0,1)), you can use countif(F11#,G4), G4 being cell reference for subject
very cool, thanks for sharing! 😊
(not at a computer I can actually check for myself) does the =UNIQUE() function not work inside the data validation list?
Great information to know. I'll use it in the future.
Learned new awesome technique ❤❤❤
Thank!! Superb explanation. Will try it the soonest
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.
hey ! in example 2 instead of COUNTA + # + INDEX Function, using COUNT + # can get easily same result. Thank You !
Technically, but that's just because there's only one column containing numbers. The index is safer :)
@@CavanHagan2 oh thanks for sharing !
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??
Than you ! thank you ! thank you ! sooooo much !!
# sign is very powerful. thanks
Very helpful video, indeed. Though I feel obliged to point out a certain irony at 9:40 - two UNIQUE values... 😅😏
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.
That was great. Thanks. I'm not happy about Lewis having the lowest score, but that's OK. 😂
😂😂 Next year, will have higher grades.
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.
I see a fellow F1 fan!
Hahah glad you noticed!!
Wow, very resourceful
Great Sir..
Ty for this!
Thank you!
Could you put the unique function in the validation formula directly?
Thanks!! Great video!!
thank you!
8:37 Can you type =UNIQUE formula that you just created?
You don't need to create new cell and hide it.
Keep an eye out for the upcoming TRIMRANGE too.
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?
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?
Nice! Although in your real world example, all of that is built into the table already... Just use the built-in table column totals.
😉 The reason for this is explained at 9:26
@@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. 😉
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.)
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"
Hi Kenji, it is good and nice.
so how do you tell excel to continue day input in one cell in one worksheet based on the previous entry in another worksheet
Thank you! I was just looking for this. Great timing.
In which version of excell this # command is working
Hash formula is not working in the free version of MSoffice.
The best part of the video is the students name's.
On a separae topic, anyone know how to change source of multiple objects in powerpoint at once?
does that work in google sheets?
Amazing
Thank you!
Thumbs up!
Thank you! Cheers!
Not working brother please help ...Microsoft office 2021
amazing
Glad it was helpful!
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
what's the meaning by arrays ?
How can one application have so many functions ???
# is not working in my excel. Can you help me ?
Probably because you have one of the older Excel versions. For reference, I am using Microsoft 365
@@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
It will work on latest version I think
Dynamic array # (hash) operator explanation
# not working in excel 2016
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.
Hmmm. Did not work for me unfortunately.
How did you come up with “90% of excel users don’t know this”?
He used dynamic array
Not hugs, that's hacks!
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.
Gonna go with formulas over formulae, huh? Yeah, this is RUclips after all
💌💌💌💌💌💌💌💌
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.
1st
Let’s go!
1,000 years far from google sheet, it is not 100% dynamic because at first you have to make table at column B
Hi. Sorry. Could you explain what you mean in more detail. I'm really interested in this?
I dont like to use dynamic arrays
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.