I used Excel years ago. And now after about 20 years I am using it again. One thing I found to be very irritating is this version of Excel fights with me. I needed to insert about 200 columns in a spreadsheet I was working on. Excel would follow my commands for a while, then it would refuse to do it and was trying to tell me a faster way of doing it {I think}. And I had to fake it out and do some random thing and sneak back to insert the columns. This was a cat and mouse game. It was exhausting to have to fight with this software. In your examples you never show the software doing this. How do I stop it from doing this?
Before watching videos I use to think I am good in Excel.. now I feel I have not even started . Thanks a lot for such new and amazing videos.. lots of love from India ...
You're one of the best excel instructor, I have enrolled to your various courses, I am deeply thankful to you and your team for always bringing out the best for all of us... lots of love from me and from India🙏❤
Everytime you explain something it becomes clear that you are more interested in people *learning* *new* *skills* rather than people *admiring* *your* *skills* . (Just as is your 'german counterpart' Andreas Thehos - if you don't mind me to mention him here.)
what i actually learned from this video is the UNIQUE function :) This will be super helpful, because what i used to do is to copy the entire range of data to another new worksheet, and then use the remove duplicate function to leave me with unique value. This UNIQUE function going to make my process steps more efficient in the future. Thank you.
This tutorials are pure gold. I don’t work with excel normally, but this is not only clear and informative but enjoyable . I am going to share it to the moon. Thanks again!
Great video as usual. I always learn something from your videos. Thanks! One thing I just discovered: You can now enter a range, with out a surrounding function--that is, all by itself, (like =A4:B10) and it will act like all the other spillable functions, copying the range. Nothing earth-shaking about that, but you can also do things like: (A4:A10 + B4:B10), and Excel will do the operation for every cell in the range(s) and put the results into a new spilled range.
Hi Ken - Yes - this is the new calculation engine of Excel. Before you used to get an error or just one value because Excel couldn't "show" the other ones on the grid, but now because of the spill behavior, it can spill every cell below.
Thankyou Leila, your voice is very easy to listen to, although I have completed some excel courses years ago I know very little and I am enjoying playing with excel again in my retirement. I am using excel to automate the starting grids at our local speedway. I am sure that after watching a few more of your lessons I will be able to make a lot of improvements Thanks again Lindsay
3 ปีที่แล้ว +2
I am improving on Excel, thank you Leila, you are genius.
Lol.. I never knew hash in excel, though excel is my primary platform and I see it million times at my work. Now I know!!! Loads of thanks to you...👍👍🙂
This is excellent work and thank you for it. I did have trouble following because I didn't know what a Spilled Range was at the start. Next time, you might want to stop and clarify before making points that require that understanding. You're an excellent speaker and the illustrations are marvelous - I subscribed right away.
I enjoy your videos although it frequently annoys me that MS introduces stuff from Access from last century in new package and people suddenly get crazy about them. ;)
Thank you! Another great video. What I like in particular are your teaching skills, your pace and that you show us a practical example. And I could go on and on 😉: The time stamps, the hidden details and features you show us, your clear understandable English ( I´m not a native speaker) etc........
One thing I must say leila we can see the hard work you put into your videos you audio quality is clear, you always look great in your video, you cover every part of the topic when you make the video and the videos are available in highest quality. You are an real perfectionist 😄😄😇😊
@LeilaGharani, why Excel has not a key, like Microsof Word, to show special features like spill formula's cells. You push the "¶"(pilcrow symbol) and the cells with formulas and spilled formulas get some kind of formatation to show it easyer. Same as formated cells, for example blank formated cells and so on. I love using {} inside collumns of INDEX function to select which collumns to show from a spilled range and the orther to show them.
you prolly dont care but does someone know of a tool to get back into an Instagram account..? I somehow lost my login password. I would love any assistance you can offer me!
Well, I was happy too soon. Counting unique fields ( with MAX(SEQUENCE()) ) in a TABLE column doesn't work well when the column is filtered. It keeps counting the whole of the column in the table. I've been looking for a solution to calculate average cost per month, based on the number of months in the selection. Back to the drawing board :-)
Phenomenal, regarding the range (5:02), what if we would like to use “ dynamic range”? I ‘ve got also a question, what is your best video for dynamic range in your tutorials?
Hey, this time around I could not understand the productivity of the #? Like =index(unique(),,?) Is simple enough, the utility of # is still unclear to me...
Hi, I have just created a complete dynamic dashboard using MS FORMs and Excel. Thanks to your simple and structured videos. Looking forward to learn more. Please make a series of videos on power query
You won’t belieeeeeeve it!! The blue board thing?? I had it in my vlookup formulas yesterday!! My boss was checking my works remotely because I could not delete the formulas! She deletes the formulas as she write comments to the column next to it. She was confused thinking, “I don’t know what you’ve done in there! I mean, what is this blue board around it? “ 😂 I am learning vlookup for my supplier accounts reconciliation. I need to uplift my skills and it has not been easy. I literally googled “why my vlookup does not work” then i had lots of answer such as 5, 7, 10 reasons that my vlookup doesn’t work and so on! I didn’t even know I have to make sure my data is in a clean “writing” that excel understands! I now need to look into trim and other commands to help me to prepare the data. The problem is that I’m using data that I copied from pdf files (statements) and use OCR to do convert as I don’t have pdf full version. . Anyway! I’m so so pleased I’m subscribed to your channel ! You help me to be a bit of a smart ass! 🤪 Aw I wish!! 😂
Wow, thank you. I did not even know excel had multi-value returns. Its interesting that different programming languages added this over the years and now excel has it also... it just dumps multi-value returns to new columns and rows. Now I got to train my brain on how to use it. BTW, Excel is like another programming language itself to me.
اسلوب التقدمة رائع و مبسط خالي من التعقيد ، اسلوب فريد ليس له مثيل تستحقين عليه الف نجمة ، شكرا ليلى على المشاركة ، كنت عونا متميزا لدخولنا لعالم الاكسل .
Thank you for this introduction! I love your teaching style. You do a great job of anticipating questions and answering them just as I think of them :)
Congratz for your channel and all provided information! I love your tips and the clear way you explain everything! About this video, you had a base table and referenced it from a spill formula (UNIQUE). How can we transform the spilled result into another table to further manipulation? Thank you!
Great video on the hash function. I was trying to find a way to get an auto expanding two-variable table where the 'x' and 'y' values of my data table were created by SEQUENCE function but didn't know the HASH operator existed until now.
Definitely useful...I am just picking up on a lot of Excel functionalities lately...wanna step up my Excel game for the future (taken up your Udemy course on VBA, too😊)....BUT...that bonus tip...Leila....that's simply awesome...I am still in awe of how there are so many Excel tricks and tools that APPEAR so simple...but the time and effort they can save us...really really great! Thanks for this.👍🏻🙂
Big fan. This is powerful. I'm a long time excel user and have a great grasp of macros etc. A bit hesitant to implement tables BUT is there a way to make dynamic charts extend when new data is added to a table? It's tedious to change chart series every time new data is added to a row in a table.....I tried with macros for a long time but never found a good solution.
The pound sign (or number sign or octothorpe) is used to trigger the calculation of a hash to index a string of characters, forming a "hash tag". There is no "hash sign" (and there is no "forward slash"). ;-)
Depends how old you are. # to me is always *hash* and has been since I programmed the Apple ][ and CBM PET. Apple ][ - PR#6 is read as P-R-hash-six PET - PRINT#5,A is read as print-hash-five-a. You're obviously of the generation that calls an answerphone "voice-mail" despite the fact that you can't CC (carbon copy) the message to someone else that you can with ordinary snail-mail and email (amongst other features of mail).
Thanks a lot for the video. As you presented Unique and # with the same results, finally I an not sure to understand in which situation I should need # (instead of any function)
Hi Leila. Thanks for making this explanation easy and understandable but I'm stuck in the mud using the # in a formula referencing a spilled range. In my formula I'm getting an error. Let me explain below In cell A3 the formula is: =SORT(FILTER(VSTACK(January:December!B9:T1009),VSTACK(January:December!A9:A1009)""),{1,2},{1,1}) - (Learned this from your video) Formula in cell T3 to get the worksheet name from where the VSTACK function above got the data: =MID(CELL(“filename”, INDIRECT(“A3#”&ROW()-2)), FIND(“]”, CELL(“filename”, INDIRECT(“A3#”&ROW()-2)))+1, 256) After entering the formula in cell T3 I got an error highlighting “A3#” in the formula bar. I was told this could be a dynamic array or spill range bug or I cannot use the formula on a VSTACK result/formula. Excel 365 version: Microsoft® Excel® for Microsoft 365 MSO (Version 2311 Build 16.0.17029.20028) 64-bit
9:10 That would be extremely powerful, it would make storing 65,536 bytes in a single cell far easier, and if added I wouldn't be surprised to see it everywhere, big bulky blocks of data are often inefficient for the sheet memory, this is because if you have two of these table things where one is 5*300 long and the other is 5*100 long then you'd optimally need only 2000 cells, however this isn't really feasible instead you are more likely to have 10*300 cells or in other words 3000 which is 1.5 times more than 2000 and thus quite memory inefficient.
Grab the file I used in the video from here 👉 pages.xelplus.com/hash-formula-file
I used Excel years ago.
And now after about 20 years
I am using it again.
One thing I found to be very irritating is this version of
Excel fights with me.
I needed to insert about 200
columns in a spreadsheet
I was working on.
Excel would follow my commands for a while, then it would refuse to do it and was trying to tell me a faster way of doing it {I think}.
And I had to fake it out and do some random thing and sneak back to insert the columns.
This was a cat and mouse game.
It was exhausting to have
to fight with this software.
In your examples you never
show the software doing this.
How do I stop it from doing this?
I am considered an expert at work, but this teacher shows me that I don't know anything about XL.
Yes, same here but in addition this teacher makes me feel that I’m stupid.
Right!!
Same here without a doubt 😅
Whenever I watch her videos ,I feel like I have learnt nothing about excel.
Half of the stuff she talks about I can't use. Because i only have non-365 office.
Before watching videos I use to think I am good in Excel.. now I feel I have not even started . Thanks a lot for such new and amazing videos.. lots of love from India ...
You're one of the best excel instructor, I have enrolled to your various courses, I am deeply thankful to you and your team for always bringing out the best for all of us... lots of love from me and from India🙏❤
Wow, thank you!
My feelings are mutual!
Everytime you explain something it becomes clear that you are more interested in people *learning* *new* *skills* rather than people *admiring* *your* *skills* . (Just as is your 'german counterpart' Andreas Thehos - if you don't mind me to mention him here.)
Thank you for the kind words. Andreas has a great channel. I've also had the pleasure of meeting him in person.
what i actually learned from this video is the UNIQUE function :)
This will be super helpful, because what i used to do is to copy the entire range of data to another new worksheet, and then use the remove duplicate function to leave me with unique value.
This UNIQUE function going to make my process steps more efficient in the future.
Thank you.
Glad you found something useful!
Ditto for me. I have to do the remove duplicates function often and using unique will make things simpler.
Leila is a game changer with her special tips from Excel. Thank you
Glad you think so!
This tutorials are pure gold. I don’t work with excel normally, but this is not only clear and informative but enjoyable . I am going to share it to the moon. Thanks again!
You're very welcome, James! I'm glad you like the tutorials.
Great video as usual. I always learn something from your videos. Thanks!
One thing I just discovered: You can now enter a range, with out a surrounding function--that is, all by itself, (like =A4:B10) and it will act like all the other spillable functions, copying the range. Nothing earth-shaking about that, but you can also do things like: (A4:A10 + B4:B10), and Excel will do the operation for every cell in the range(s) and put the results into a new spilled range.
Hi Ken - Yes - this is the new calculation engine of Excel. Before you used to get an error or just one value because Excel couldn't "show" the other ones on the grid, but now because of the spill behavior, it can spill every cell below.
So its like matrix algebra!? 🤔 that opens a huge door to new possibilities
Thankyou Leila, your voice is very easy to listen to, although I have completed some excel courses years ago I know very little and I am enjoying playing with excel again in my retirement. I am using excel to automate the starting grids at our local speedway. I am sure that after watching a few more of your lessons I will be able to make a lot of improvements
Thanks again Lindsay
I am improving on Excel, thank you Leila, you are genius.
Lol.. I never knew hash in excel, though excel is my primary platform and I see it million times at my work. Now I know!!!
Loads of thanks to you...👍👍🙂
This is excellent work and thank you for it. I did have trouble following because I didn't know what a Spilled Range was at the start. Next time, you might want to stop and clarify before making points that require that understanding. You're an excellent speaker and the illustrations are marvelous - I subscribed right away.
I'm brand new to this too - but found the explanation of spilled tables in this video adequately covered that.
This lady is the flippen no.1. Thanks for everything Leila
Leila’s videos are great! You can always learn something new, useful, and fun! I would recommend checking out her courses.
Being a student who wants to up my Excel skills, your contents are pure gold! Thank you very much! 🙏🏼
I thought I'm an Excel expert, but this is all new stuff - lambda, spills, etc. Thanks for helping me keep updated. Great video.
Thanks for watching!
I enjoy your videos although it frequently annoys me that MS introduces stuff from Access from last century in new package and people suddenly get crazy about them. ;)
Leila is always a pleasure to see your videos, they learn many new things.
Happy to hear that, Paolo!
Thank you! Another great video. What I like in particular are your teaching skills, your pace and that you show us a practical example. And I could go on and on 😉: The time stamps, the hidden details and features you show us, your clear understandable English ( I´m not a native speaker) etc........
Wow, thank you Birgit!
You are
by far the best presenter for videos like this... and the content is great.
Wow, thank you Dejan!
Always here in Africa#Uganda studying from your channel. Thanx our dear Leila
Wonderful! Greetings to Uganda!
Köszönjük!
Thank you!
The best excel masterclass on youtube. Thank you Leila.
Thank you! I'm glad you found this useful :)
One thing I must say leila we can see the hard work you put into your videos you audio quality is clear, you always look great in your video, you cover every part of the topic when you make the video and the videos are available in highest quality. You are an real perfectionist 😄😄😇😊
I started using # in the dynamic data validations based on your suggestions in your previous video. It is really a cool function
Great to hear!
@LeilaGharani, why Excel has not a key, like Microsof Word, to show special features like spill formula's cells. You push the "¶"(pilcrow symbol) and the cells with formulas and spilled formulas get some kind of formatation to show it easyer. Same as formated cells, for example blank formated cells and so on.
I love using {} inside collumns of INDEX function to select which collumns to show from a spilled range and the orther to show them.
Before I start watching a video, I click on Like icon, I am sure Ms. Leila will not disappoint me. Thank you for your great efforts 👍👍👍
Leila, many thanks for being there for us ❤️ your tutorials make my life a lot easier
you prolly dont care but does someone know of a tool to get back into an Instagram account..?
I somehow lost my login password. I would love any assistance you can offer me!
Fantastic, a subject I didn't know, now a new world has opened up in front of me. Really good.
Your videos are addictive.
Learned how to sum the range obtained from FILTER formula using Sum, Index and # after watching this. Great learning experience. 🙏🙏🙏
Thanks!
Thank you, Mike!
This video solved a problem for me which I couldn't figure out myself after spending hours fighting Excel. Thank you!
Well, I was happy too soon. Counting unique fields ( with MAX(SEQUENCE()) ) in a TABLE column doesn't work well when the column is filtered. It keeps counting the whole of the column in the table. I've been looking for a solution to calculate average cost per month, based on the number of months in the selection. Back to the drawing board :-)
TBH this channel is amazing, fantastic content and stunning speech.
Phenomenal, regarding the range (5:02), what if we would like to use “ dynamic range”? I ‘ve got also a question, what is your best video for dynamic range in your tutorials?
1:06 I would guess that you'd create a =OFFSET(A3:D18,0,0,16,4) to make a spilled range identical to the range you have.
I do school district state report and your videos had show me new formulas that make my job more easy
Hey, this time around I could not understand the productivity of the #? Like =index(unique(),,?) Is simple enough, the utility of # is still unclear to me...
To get back a specific part of the spilled range, can also use the FILTER function:
=FILTER(J3#,{1,0}) or =FILTER(J3#,{0,1}) 🤗
That looks a better way to do
Thanks for the explanation of using the # operator. Can we use this table (derived from # operator) to calculate % changes in the table
You got me when you showed the practical example. Thank you for the video!
You are the best teacher. 🙌🏻🙌🏻🙌🏻🙌🏻🙌🏻🙌🏻
Hi, I have just created a complete dynamic dashboard using MS FORMs and Excel. Thanks to your simple and structured videos. Looking forward to learn more. Please make a series of videos on power query
What a clever and beautiful young woman! I'm impressed! I remember the first MS Excel, how far the thing has gone!
Audio, Video Quality is excellent!!!
You won’t belieeeeeeve it!! The blue board thing?? I had it in my vlookup formulas yesterday!! My boss was checking my works remotely because I could not delete the formulas! She deletes the formulas as she write comments to the column next to it. She was confused thinking, “I don’t know what you’ve done in there! I mean, what is this blue board around it? “ 😂 I am learning vlookup for my supplier accounts reconciliation. I need to uplift my skills and it has not been easy. I literally googled “why my vlookup does not work” then i had lots of answer such as 5, 7, 10 reasons that my vlookup doesn’t work and so on! I didn’t even know I have to make sure my data is in a clean “writing” that excel understands! I now need to look into trim and other commands to help me to prepare the data. The problem is that I’m using data that I copied from pdf files (statements) and use OCR to do convert as I don’t have pdf full version. . Anyway! I’m so so pleased I’m subscribed to your channel ! You help me to be a bit of a smart ass! 🤪 Aw I wish!! 😂
Haha! Well, I'm glad to have you here 😊
It blew my mind. I want to use it all the time.
I've been questioning this # for years. It's clear now. Awesome presentation, thanks!
Wow, thank you. I did not even know excel had multi-value returns. Its interesting that different programming languages added this over the years and now excel has it also... it just dumps multi-value returns to new columns and rows. Now I got to train my brain on how to use it. BTW, Excel is like another programming language itself to me.
this person is a monster!
(in the positive sense of the word)
Thank you so much for the valuable learnings!
My pleasure!
Okay, Ms. Gharani, you convinced me to subscribe. I hope to learn lots more because I use Excel daily.
Welcome aboard, Robert!
اسلوب التقدمة رائع و مبسط خالي من التعقيد ، اسلوب فريد ليس له مثيل تستحقين عليه الف نجمة ، شكرا ليلى على المشاركة ، كنت عونا متميزا لدخولنا لعالم الاكسل .
Your voice is awesome...very easy to understand..
Thank you!
Very helpful. You're videos are always well done in content and instructional method.
Glad you think so, Marty!
Great Practical Example
The way you explain which is quite interesting 👌
Your way of explanation is the best in Arabic انت الاروع
I am from India, your explanation is awesome,
Thank you for this introduction! I love your teaching style. You do a great job of anticipating questions and answering them just as I think of them :)
You are so welcome!
Thanks Leila. These tips are very helpful. Keep up the great work.
Thank you, Matt! Will do!
Hi Leila, I love your videos and recommend them to my team at work. Thank you for putting out this content!
Awesome! Thank you!
Your videos are a tremendous help.
Congratz for your channel and all provided information! I love your tips and the clear way you explain everything! About this video, you had a base table and referenced it from a spill formula (UNIQUE). How can we transform the spilled result into another table to further manipulation? Thank you!
The best part of the video I liked was the subscribe part .. Intelligently done. And I am subscribing
Glad to have you here! Thank you.
Great video on the hash function.
I was trying to find a way to get an auto expanding two-variable table where the 'x' and 'y' values of my data table were created by SEQUENCE function but didn't know the HASH operator existed until now.
Hi thank you for your videos. I learn a lot from you. More please.
Definitely useful...I am just picking up on a lot of Excel functionalities lately...wanna step up my Excel game for the future (taken up your Udemy course on VBA, too😊)....BUT...that bonus tip...Leila....that's simply awesome...I am still in awe of how there are so many Excel tricks and tools that APPEAR so simple...but the time and effort they can save us...really really great! Thanks for this.👍🏻🙂
The INDEX trick was for me very useful doing a XMATCH of my two spilled ranges
Glad it was helpful!
This is so explicit. Now I understand better. Thanks for sharing
Then where was the parental advisory label? /s
Big fan. This is powerful. I'm a long time excel user and have a great grasp of macros etc. A bit hesitant to implement tables BUT is there a way to make dynamic charts extend when new data is added to a table? It's tedious to change chart series every time new data is added to a row in a table.....I tried with macros for a long time but never found a good solution.
Nice info.
Now a days, I am revising my excel works with 2016 version and I found your videos much helpful.
Thanks a lot.
You are most welcome!
@@LeilaGharani
Is possible that I can share my project book with you for help?
Really You Made it Simple and Easy
The pound sign (or number sign or octothorpe) is used to trigger the calculation of a hash to index a string of characters, forming a "hash tag". There is no "hash sign" (and there is no "forward slash"). ;-)
😂 Ok. North America = Pound sign, Rest of the World = Number sign, Leila Land = Hash sign.
Depends how old you are. # to me is always *hash* and has been since I programmed the Apple ][ and CBM PET.
Apple ][ - PR#6 is read as P-R-hash-six
PET - PRINT#5,A is read as print-hash-five-a.
You're obviously of the generation that calls an answerphone "voice-mail" despite the fact that you can't CC (carbon copy) the message to someone else that you can with ordinary snail-mail and email (amongst other features of mail).
@@cigmorfil4101, well fwiw, my original character set was 6-bit fieldata. ;-) en.wikipedia.org/wiki/Number_sign
I tried some hash once .. have to say i excelled in everything afterwards.
Another very useful video! Thanks for sharing!
Thanks a lot for the video. As you presented Unique and # with the same results, finally I an not sure to understand in which situation I should need # (instead of any function)
Thank you for this excel-lent presentation.
Wow! Very lucid explanation.
Always useful tips, thanks Leila!
My pleasure!
Very informative and useful information ... Thank You
Hi Leila. Thanks for making this explanation easy and understandable but I'm stuck in the mud using the # in a formula referencing a spilled range. In my formula I'm getting an error. Let me explain below
In cell A3 the formula is:
=SORT(FILTER(VSTACK(January:December!B9:T1009),VSTACK(January:December!A9:A1009)""),{1,2},{1,1}) - (Learned this from your video)
Formula in cell T3 to get the worksheet name from where the VSTACK function above got the data:
=MID(CELL(“filename”, INDIRECT(“A3#”&ROW()-2)), FIND(“]”, CELL(“filename”, INDIRECT(“A3#”&ROW()-2)))+1, 256)
After entering the formula in cell T3 I got an error highlighting “A3#” in the formula bar.
I was told this could be a dynamic array or spill range bug or I cannot use the formula on a VSTACK result/formula.
Excel 365 version:
Microsoft® Excel® for Microsoft 365 MSO (Version 2311 Build 16.0.17029.20028) 64-bit
Since I found you here in TH-cam I love your videos!!!!
...just three words - i - love - you.
gimme more of that input - please!
Nice 👍 very informative video
Good morning
I'm press on the like button before watch this video because all of your videos are amazing.
Thank you very much.
So nice of you, Ahmad!
Each of your video is an interesting learning curve. Thanks Leila :)
I hope it's an upward trajectory 😂
@@LeilaGharani absolutely yes :)
Super Cool like All your Tip videos!!!
Thanks, Michael!
i have to watch 4 to 5 times because something new topics dosent enter my mind easily 😄😄. great video i like your teaching.
That's great!
Excellent Leila, Each time you come with very unique lesson, keep it up
You are great! One my favorite channels on TH-cam. So useful!
2:50 awesome =) I like your videos, thank you!
Incredible amount of useful information!
Glad you think so, Michael!
Great teacher.
Very Informative Video!
9:10 That would be extremely powerful, it would make storing 65,536 bytes in a single cell far easier, and if added I wouldn't be surprised to see it everywhere, big bulky blocks of data are often inefficient for the sheet memory, this is because if you have two of these table things where one is 5*300 long and the other is 5*100 long then you'd optimally need only 2000 cells, however this isn't really feasible instead you are more likely to have 10*300 cells or in other words 3000 which is 1.5 times more than 2000 and thus quite memory inefficient.
Wow, Everything is Good content and the way to present.
Great explanation. Thanks. I subscribed.
Fan of Excel.! ❤️❤️
From Delhi 🙏😁
Greetings to Delhi!
@@LeilaGharani thanks a lot 😁🙏🙏 Your welcome in India 😁👍
How would you then sum sales by F&G in day column H? And have that run down if new things are added to the table?