I've not used most of the functions in the video - quite the eye opener! And thank you for showing how to use them so clearly!! Your slogan should be "if you think Excel can't do, just Chandoo!"
Outstanding video! You explain this very well and I appreciate your expertise and keen insight. All your videos have proven to be invaluable in my learning of Excel. Thank you, sir!
GREAT Question. Donut for you 🍩 It will still work. The named range approach is useful if you want to use the Spill range in a more controlled manner (or with charts). But for Data Validation, that step is totally optional (at least with insider version of Excel I am using).
@@chandoo_ Thank you. I have applied these spill functions like unique, sort, filter etc in real world professional financial model already. This is a revolution.
Heloo Chando, Can we use "#" with "&" to refer some ranges? As far as I know, we can use "#" and "&" together inside Indirect function and I tried it, it works. (=Indirect("cell address&"#") However, without indirect, it doesn't work. Do you know about some usage of "#"and "&" together or is it possible?
Interesting question. It seems any function that returns a reference (for ex. INDEX, OFFSET, XLOOKUP, INDIRECT) can all use # at the end. Assuming you have a spill range in B1, any of these formulas can RETURN it. =INDEX(A1:C1,2)# =OFFSET(A1,,1)# =INDIRECT("B1")# =INDIRECT("B1#") TBH, this is really mind-boggling and can open doors for some crazy implementations. Kudos to Microsoft Engineers who thought thru this possiblity.
With Chandoo you feel like you Can Do anything with Excel! (In this sentence I made a play on words using the creator's name to make a humourous and affirming comment)
Great information as always. Usually I would use COUNTIF, SUMIF, etc. for achieving the same result. Is there an advantage to using COUNT or SUM of the spilled data?
If you need to present the data as well as COUNT / SUM then spill ranges are great. I use them in reporting situations where we need both summary and detail.
Using the Unique function in conjunction with the # operator for a dynamic data validation list which captures all the unique variables in that particular dataset is quite helpful... cheers!
This sounds like a great tool, and as always your vids are super informative. But I wonder about that 2nd example: might the spill range might also change its width if you make changes to your table? Shouldn't there be a better way than to specify column 2, perhaps do Index for a search for the column titled Salary?
Thank you. I touch upon these points towards the end of the video. Spill ranges can be quite volatile (hence the name spillberg 😂) and you should give them ample space when used in worksheets.
Omg, thank you so much. I ran into this by accident (youtube is reading my mind). I have a list based off a list. So from one option in first list I have 2 results. But second option has maybe 10. So my second list has to have maximum of 10 fields. Meaning, when you select first option you get 2 results and 8 white spaces. And this thing solves it and makes it so neat 😍😍😍 Thank you very much for this video!
Great video, very informative. You are one of my top “go to” Excel YT resources, hands-down. Q (nothing to do with Excel): what is the name of the background music in this video? That guitar piece sounds very familiar. Thanks!
@chanoo, tried this on excel build 2210 (15726.202002) but define name works but throws evaluation error when creating the drop down. I can see the define name if used in formula
Your Vids are very informative.. can you solve this, i have 3 column data, a list of stores and sales, in USD and sales in EUR , i converted to pivot,, now i can sales by storewise in USD or EUR, or both. fine, Now is there any option like radio button like "select currency USD or EUR" where pivot shows only that currency sales .
I created a spreadsheet to audit a bunch of network switches a few years back which used some of the features you've highlighted here...this has got me thinking...I wonder if I could use some of this to make it more efficient? 🤔 Maybe, I could send it you to 'roast' on your channel? 🤭😉
how to create duplicate data with same row & column data. e.g. one row have data with duplicate with same in 10 rows & more counts depends on requirement. if is it possible without macros & VBA?. please help us sir.
Sir how I can get the start date and end date of employees in Excel if keep manual for 365 days (60 days) straight 2months vacation and get in last column as summary of start date and end date of vacation
Mr Chandoo I have an excel based data based question to solve. Kindly respond or let me know how can I reached you to solve one particular problem I have in excel.
Hi ..can you please explain why few people uses +sign before starting any formula in excel. what is the logic to use +sign instead of = sign? can you please answer
One more query i have been searching video for the active cell bordr colour in excel 2007 the active cell border use to be dark black and is visible but as and when the excel got updated the active cell border getting thiner and light and get difficult to locate is there any way where i can have the active cell as dark black like in 2007
This is just what I needed for a sheet that holds all shifts in my job, and the shifts have been changing and I have to manually add them and then manually change the lists and data values. I am going to put it in practice and impress my boss! 😁
How can we use this # operator at the row level instead of array level, where a row contains values around 20 to 25 enteis to transpose the row values into a column.
I have two sheets. I want to copy data from one sheet to the other. is there an excel function for this? or an excel function copy from one cell to the other. Thank you
Assuming your data is in sheet 1 range A1:C100, Go to the next sheet, select a cell where you want your data to begin. type =Sheet1!A1:C100 and press enter. You will get a copy of the data linked to first sheet.
@@chandoo_ , thanks so much. However, I want the data for only one year. I have a panel data for 1990 to 2021 and I want to get only those for data for only 2006 for all firms.
Interesting question. For the dept.names range, you can type "ALL" on the first cell and type your UNIQUE formula in the next cell. Then set up the named range to include the "ALL" cell too (note: You will need to update the name every time you add departments, you can automate this bit too) For the FILTER formula, you can use =IF(selectedcell="ALL", staff, FILTER(staff, staff[dept]=selectedcell) Hope that helps.
Thanks dear for sharing. If I am in any random cell of spill range, is there any option to go to the main cell where the formula is defined... Shortcut key etc. Thanks
This is awsm.. good info.. it wld be nice if we you can do a video on the data analysis tools goal seek, data tables and adding the right column names in data tables..
The symbol you call HASH. is really named HATCH………. it is a contraction of the printing term…cross-hatch………….. a printers way of shading an infill which is not black or half-tone. It has another symbolic assignation to mean number……..as in #1 means no.1. Your incorrect use signifies the distressing situation we now have, where ignorance redefines meaning to accomodate ignorance. Sorry to be so negative….but that’s the way it is. Regards, Stavros
Thanks for the insight Steve. I never heard anyone use the "hatch" term for this. Language is what we use. There is no point getting hung up about something that no one else is using.
This is a good new feature and you explained it perfectly. Spillburgs, LOL.
Thank you Kevin 😀
That was hilarious haha
I've not used most of the functions in the video - quite the eye opener! And thank you for showing how to use them so clearly!! Your slogan should be "if you think Excel can't do, just Chandoo!"
That is a good one :)
I’m kind of shocked by how much I just learned..
I am tattooing a # symbol on my chest just to celebrate how important this operator is for us Excel analysts 🔥
I am sure the blood won't spill when you tattoo this 😂
Outstanding video! You explain this very well and I appreciate your expertise and keen insight. All your videos have proven to be invaluable in my learning of Excel. Thank you, sir!
Agree. He does a great job of zooming in to the relevant area of the screen and using on-screen arrows, titles, etc
Thank you Douglas. My mission is to make one million people awesome in their work every month.
What if we dont create named range of a spill column to use into data validation list. I haven't tried yet but cant we use # directly in list?
GREAT Question. Donut for you 🍩
It will still work. The named range approach is useful if you want to use the Spill range in a more controlled manner (or with charts). But for Data Validation, that step is totally optional (at least with insider version of Excel I am using).
@@chandoo_ Thank you. I have applied these spill functions like unique, sort, filter etc in real world professional financial model already. This is a revolution.
Heloo Chando,
Can we use "#" with "&" to refer some ranges? As far as I know, we can use "#" and "&" together inside Indirect function and I tried it, it works. (=Indirect("cell address&"#")
However, without indirect, it doesn't work.
Do you know about some usage of "#"and "&" together or is it possible?
Interesting question. It seems any function that returns a reference (for ex. INDEX, OFFSET, XLOOKUP, INDIRECT) can all use # at the end.
Assuming you have a spill range in B1, any of these formulas can RETURN it.
=INDEX(A1:C1,2)#
=OFFSET(A1,,1)#
=INDIRECT("B1")#
=INDIRECT("B1#")
TBH, this is really mind-boggling and can open doors for some crazy implementations. Kudos to Microsoft Engineers who thought thru this possiblity.
With Chandoo you feel like you Can Do anything with Excel! (In this sentence I made a play on words using the creator's name to make a humourous and affirming comment)
Thanks dear chandoo
today you could use the choosecols function
Choosecols (A12#,2)
Superb and very useful. Your videos have become my reliable reference for excel queries instead of googling it :)
Recently i used it with filter for validation. It saved me huge amount of manual tables. Doing so i just achieved a custom RLS in excel. Just magical.
Would love to see the example of RLS in excel
Great information as always. Usually I would use COUNTIF, SUMIF, etc. for achieving the same result. Is there an advantage to using COUNT or SUM of the spilled data?
If you need to present the data as well as COUNT / SUM then spill ranges are great. I use them in reporting situations where we need both summary and detail.
Been following your tutorials since ages now. You never fail to amaze me. As clear as sunshine, that explanation! Beautiful insights.. Thank you.
Wow, thank you!
Why I'm Not Able to use the # Function on me latest Excel @@chandoo_
Omg !!! Thank you !! Thank you !!! Great !! (I would have used volatile offset function . ,). Thanks for sharing. !!
Glad it helped!
Kill the stupid background music!!! If I want to listen to music, I'll open a music video. I open your video because i want to listen to YOU.
Thanks for the suggestion Michael. I experiment with different things in the video. Some people "LOVED" the music, some "DON'T".
Instead of using Index(), the ChooseCols function generates the same output. Plz comment.
It does. CHOOSECOLS and CHOOSEROWS were not available at the time I recorded this video. But these days, I use CHOOSECOLS for such things.
Outstanding video. Your knowledge and skill of delivering is awesome. Thank you so much sir for the information.
Thanks and welcome
Using the Unique function in conjunction with the # operator for a dynamic data validation list which captures all the unique variables in that particular dataset is quite helpful... cheers!
Hello Chandoo, great video. Superb examples. Awesome explanation 👏.
This sounds like a great tool, and as always your vids are super informative. But I wonder about that 2nd example: might the spill range might also change its width if you make changes to your table? Shouldn't there be a better way than to specify column 2, perhaps do Index for a search for the column titled Salary?
Thank you. I touch upon these points towards the end of the video. Spill ranges can be quite volatile (hence the name spillberg 😂) and you should give them ample space when used in worksheets.
So glad to see this # operator. Before I was thinking google sheets might be better than excel..
Excel is still the best :).
Thanks Chandoo!
Although Google Sheets has many exciting and powerful features, Excel is by far my fav too.
@@chandoo_ thanks for the videos and all the excel knowledge
Get rid of the default captions. The user can turn on the built in UA-cam captions.
Hmm.. there are no captions...
Very nice Chandoo! Thanks for the Index function tip especially! Loved the "Spillberg" pun! 🤣
You are welcome Vijay.
Omg, thank you so much. I ran into this by accident (youtube is reading my mind). I have a list based off a list. So from one option in first list I have 2 results. But second option has maybe 10. So my second list has to have maximum of 10 fields. Meaning, when you select first option you get 2 results and 8 white spaces. And this thing solves it and makes it so neat 😍😍😍 Thank you very much for this video!
Excellent Presentation as usual. Thanks. By the way I have a concern in programming a draw for a Carrom tournament. Could you help?
Ok I’m subscribing because of spillburg
Oh well, got you in the HOOK
Great video, very informative. You are one of my top “go to” Excel YT resources, hands-down.
Q (nothing to do with Excel): what is the name of the background music in this video? That guitar piece sounds very familiar. Thanks!
Muy buen video, gracias por compartir tu conocimiento. Saludos desde Punta Arenas, Chile.
@chanoo, tried this on excel build 2210 (15726.202002) but define name works but throws evaluation error when creating the drop down.
I can see the define name if used in formula
FFAAAAANNNTTTAAAASSSSTTIICCC STUFF CHANDOO!! Dhanyawaad!
Thank you Cherian. 😎
Your Vids are very informative.. can you solve this, i have 3 column data, a list of stores and sales, in USD and sales in EUR , i converted to pivot,, now i can sales by storewise in USD or EUR, or both. fine, Now is there any option like radio button like "select currency USD or EUR" where pivot shows only that currency sales .
I created a spreadsheet to audit a bunch of network switches a few years back which used some of the features you've highlighted here...this has got me thinking...I wonder if I could use some of this to make it more efficient?
🤔
Maybe, I could send it you to 'roast' on your channel?
🤭😉
Master,
From now on, I will think of Spillberg when I use the # function and think of your tutorials when I see the hashtag.
#Chandoo
Liked for the pun
how to create duplicate data with same row & column data. e.g. one row have data with duplicate with same in 10 rows & more counts depends on requirement. if is it possible without macros & VBA?. please help us sir.
Sir how I can get the start date and end date of employees in Excel if keep manual for 365 days (60 days) straight 2months vacation and get in last column as summary of start date and end date of vacation
I am using office 365 excel but I didn't get # option so please give me advice how to use this #, option working
Mr Chandoo I have an excel based data based question to solve. Kindly respond or let me know how can I reached you to solve one particular problem I have in excel.
Can hash operator be used with Xlookup? If yes, can someone give a syntax example?
Hi ..can you please explain why few people uses +sign before starting any formula in excel. what is the logic to use +sign instead of = sign? can you please answer
When copying a worksheet with formulas to another workbook, it copies with link to the original workbook. How to overcome this problem?
Thanks Chandoo, right now my heart is G# (I mean spilling with gratitude ;-))
One more query i have been searching video for the active cell bordr colour in excel 2007 the active cell border use to be dark black and is visible but as and when the excel got updated the active cell border getting thiner and light and get difficult to locate is there any way where i can have the active cell as dark black like in 2007
Great skills, great video. Countless global productivity.
Awesome. Exactly the type of problem I need to solve on Monday. Oh happy day!!!
Can you incorporate the # into a SIMIFS formula please???
Thanks, this will come in very handy. I appreciate your hard work on these videos. :)
This is just what I needed for a sheet that holds all shifts in my job, and the shifts have been changing and I have to manually add them and then manually change the lists and data values. I am going to put it in practice and impress my boss! 😁
The great Chandoo. Great feature Nicely explained.
How can we use this # operator at the row level instead of array level, where a row contains values around 20 to 25 enteis to transpose the row values into a column.
Your videos are amazing man! I love this! It is super helpful, explained nicely and quite calming to watch in fact. Thank you so much 🙌🏻
Glad you like them!
Hello, thanks for this tutorial.But, is it possible to use wildcards with filter function?
I have two sheets. I want to copy data from one sheet to the other. is there an excel function for this? or an excel function copy from one cell to the other. Thank you
Assuming your data is in sheet 1 range A1:C100,
Go to the next sheet, select a cell where you want your data to begin.
type =Sheet1!A1:C100 and press enter.
You will get a copy of the data linked to first sheet.
@@chandoo_ , thanks so much. However, I want the data for only one year. I have a panel data for 1990 to 2021 and I want to get only those for data for only 2006 for all firms.
Holy crap. I just had someone write some vba code for me to do some of this! Can’t wait to use these functions instead.
Super useful, but I probably won't be using excel for this.
Thanks Chandoo - you explained it really clearly. Was wondering... Is there a way to have e.g. All as well as the unique Department drop downs? Thanks
Interesting question.
For the dept.names range, you can type "ALL" on the first cell and type your UNIQUE formula in the next cell. Then set up the named range to include the "ALL" cell too (note: You will need to update the name every time you add departments, you can automate this bit too)
For the FILTER formula, you can use =IF(selectedcell="ALL", staff, FILTER(staff, staff[dept]=selectedcell)
Hope that helps.
Awesome content. Love your videos
Thanks dear for sharing. If I am in any random cell of spill range, is there any option to go to the main cell where the formula is defined... Shortcut key etc. Thanks
You can use CTRL arrows to go the edges of spill range.
This is awsm.. good info.. it wld be nice if we you can do a video on the data analysis tools goal seek, data tables and adding the right column names in data tables..
✋ stop do you know man what type of your tone of speaking to teach people Dr A P J Kalam Sahab
Hi sir, I'm using Excel for my finance business transactions, can you please make a video of Excel how I can better use...
Wait.. I never realized that you could leave the row blank in the index function
I subscribed the moment you said "Spillburgs" lol
Nice function please does the # work only in Office 365?
Looks great! Spill, get a burger and enjoy your day! :-))
Possible to pick more departments? Thank you 👏
Possible to pick more departments? Thank you 👏
Great leason
Thanks for joining the class M T.
Thanks my friend. Once again a great video and picked up cool tips and tricks. Regards from South Africa.
You are welcome Chris :)
that is INCREDIBLE, THANK YOU
I am following your lessons and teaching at least from 13 years ago.
Thanks for your kind teaching.
Thank you so much for being with me all this while Sebghatulbary ❤
1:58 killed me
I'm using 2019, i can't use spill operator
Chandoo sir what's the cursor you use I like it, with the red ripple effect
Chandu Garu, Thank you so much for your amazing tips!
Thanks for nice video. What to do if we want to filter and sort the data fetched using spill.
# function only works on microsoft excel 360 or on lower version also??
Great Videos Chandoo! Very helpful, as ususal.
Super👌, thank you for sharing this concept
how to preserve grid lines in excel when column is highlighted in colour???
Great explanation! Thanks Chandoo!!
Great stuff spill range...Thank you...
I was just looking for a good vidoe on # and you came out with it... Taught with real simplicity. Looking forward to more of such videos
Glad it was helpful! Do let me know how you are using the # operator in your work.
Wow! What a clear explanation with simple examples. Plus your examples cover a myriad of uses. Not overwhelming or intimidating.
Thank ABQB... :)
While adding the dropdown list am getting the error can you help on this
Great video - I get it now. Great tip on using the Index function!
sir video is so good but your teaching speed so fast, please do slow.
Using spilled ranges is definitely a more efficient Excel practice!
Thank you for putting all this info together. It will help simplify and perfect a spreadsheet I recently created that's poorly done with pivot tables.
Glad it was helpful!
Great explanation... This will be very useful!
Love your contents Chandoo, keep it up sir🍻
That Filter function is DAXy
There is nothing more glorious than working in excel, taking a break, and having the EXACT how-to YT video pop up. This video is outstanding!
Thanks Rick.
music is very much attractive, may I get the music link?
The symbol you call HASH. is really named HATCH………. it is a contraction of the printing term…cross-hatch………….. a printers way of shading an infill which is not black or half-tone. It has another symbolic assignation to mean number……..as in #1 means no.1.
Your incorrect use signifies the distressing situation we now have, where ignorance redefines meaning to accomodate ignorance.
Sorry to be so negative….but that’s the way it is.
Regards,
Stavros
Thanks for the insight Steve. I never heard anyone use the "hatch" term for this. Language is what we use. There is no point getting hung up about something that no one else is using.
Heh and i thought i was pretty good with Excel)))
Crazy.. Chandoo I am new follower ❤️💐
My company uses the latest Excel (Office 365 build 2201) but the unique function isn't available. Why is that?
Hmm.. try again? It has been in general release for a long time.
hope I should be perfect by u to learn excellent excel commend
Amazing use of multiple functions!