"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!
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.
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!!
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.
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??
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.
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.
@@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"
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?
@@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
"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!
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.
You give us valuable and exclusive advice that makes us eager for more. We wish you success and brilliance
love them "Formula" names... seems fitting 😀 Go go McLaren!
Great vid, btw! Keep up the good work 👍
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?
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!!
Great Sir, Thank you very much... very helpful
OMG that soooo helpful, i use a bunch of code in vba to do what a simple little symbol can do. thank you sooo much 🙏🙏🙏
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!
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.
Hi Kenji. this was indeed a very useful solution for my files
Great to hear!
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.
Useful tips and interesting F1 driver names 😊
Loved the driver names 😂
An opportunity was missed by naming Checo into Sergio Pemex! But hilarious notwithstanding!
Hi #Kenji, instead of complex formula counta(index(F11#,0,1)), you can use countif(F11#,G4), G4 being cell reference for subject
Great information to know. I'll use it in the future.
Thank!! Superb explanation. Will try it the soonest
very cool, thanks for sharing! 😊
Learned new awesome technique ❤❤❤
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.
(not at a computer I can actually check for myself) does the =UNIQUE() function not work inside the data validation list?
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.
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
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.
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.
Great Sir..
Wow, very resourceful
Thanks!! Great video!!
thank you!
Ty for this!
Thank you!
Does this work on google sheets as well?
Could you put the unique function in the validation formula directly?
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"
Is this available in older version
Hi Kenji, it is good and nice.
8:37 Can you type =UNIQUE formula that you just created?
You don't need to create new cell and hide it.
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?
I see a fellow F1 fan!
Hahah glad you noticed!!
In which version of excell this # command is working
Hash formula is not working in the free version of MSoffice.
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
That was great. Thanks. I'm not happy about Lewis having the lowest score, but that's OK. 😂
😂😂 Next year, will have higher grades.
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?
How to do numbering but skip blank cells in gsheet?
so how do you tell excel to continue day input in one cell in one worksheet based on the previous entry in another worksheet
does that work in google sheets?
Amazing
Thank you!
Thank you! I was just looking for this. Great timing.
amazing
Glad it was helpful!
On a separae topic, anyone know how to change source of multiple objects in powerpoint at once?
The best part of the video is the students name's.
what's the meaning by arrays ?
Thumbs up!
Thank you! Cheers!
Love how all the students are plays on F1 drivers names
Phaha glad you noticed 😂
Very helpful video, indeed. Though I feel obliged to point out a certain irony at 9:40 - two UNIQUE values... 😅😏
Not working brother please help ...Microsoft office 2021
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
Dynamic array # (hash) operator explanation
It will work on latest version I think
# 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.
How did you come up with “90% of excel users don’t know this”?
He used dynamic array
Hmmm. Did not work for me unfortunately.
1st
Let’s go!
Gonna go with formulas over formulae, huh? Yeah, this is TH-cam after all
Not hugs, that's hacks!
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.
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.
💌💌💌💌💌💌💌💌
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.