HEADS UP: DYNAMIC arrays formulas, and functions have been massively updates in OFFICE 365 so if you are on newer versions of excel, be mindful that tricks like ctrl+shift+ent or OFFSET may not properly work the way they are presented in this tutorial. Of course, this will not change the fact that Sean has done a great job and his teaching style is marvelous. Many thanks Sean 👍👍
Thank you very much for this superb course on Excel. Would you tell me please if I can format my data as a table to update new information instead of using the offset function I find it easier and I believe I get the same result. Could you confirm this please?
Firstly thank you for such a lovely video. Now my question is why do we needed to make an offset function to dynamically select data from our array when we could have just coverted the data into a Table and when we enter any data in the table would it not auto update the index match formula that we used earlier.? Kindly resolve my query.
• Where is your data housed • how do you import and clean your data • what types of methods do you use to clean your data • do you use anything to automate your daily functions • if no methods currently exists are you open to scripting repetitive tasks • what documentation recourse does your team use to solve issues e.g. official documentation, stack overflow, tutorial videos • what is the average time frame to complete a project and how much does that time increase with regards to the complexity of the project • what metrics do you use to measure performance and how does that relate to growth potential within the company
Good tutorial from you. It's by far the BEST i have found here. I have this problem. I was able to do the VLOOP, but when I change PROD# to say 11, the ProductName does not change. I don't know why. Please which version of excel are you using. Thanks
What's the difference between this excel power users and the first three excel 2016 lessons..... Is this a continuation from excel 2016 advanced users??
The Dynamic Name Ranges (Offset) didn't work out well when I experimented with the function on Excel Office 365. @Sean, could you please try the function on Excel Office 365 and let me know what to do differently?
You prolly dont care but if you guys are stoned like me during the covid times you can stream pretty much all of the new series on Instaflixxer. Been binge watching with my gf these days =)
Because the Array in which to look for the answer is constituted of only one column. So the fonction is going to scan various rows in this only one column. In the previous example where Sean Bugler explains how the Index fonction works the array is made of several rows and several columns, in which case he has to specify the number of rows and the number of columns. But here since there aren't any choice for the column, it can't be anything else than 1. That's my explanation anyway. Hope that helps.
In the array function tutorial I think I typed exactly the same formula in E8 but it results in "Karen" rather than "Richard". =OFFSET('Array Function'!$A$1,1,0,COUNT('Array Function'!$A:$A)-1,1)
![Excel to Power BI [Full Course] 📊](http://i.ytimg.com/vi/gjnnqsdvAc0/mqdefault.jpg)
ONE OF THE BEST TUTORIAL YOU EVER GET ONLINE
Thank you so much Mohamed!
HEADS UP: DYNAMIC arrays formulas, and functions have been massively updates in OFFICE 365 so if you are on newer versions of excel, be mindful that tricks like ctrl+shift+ent or OFFSET may not properly work the way they are presented in this tutorial. Of course, this will not change the fact that Sean has done a great job and his teaching style is marvelous. Many thanks Sean 👍👍
This is a great learning pack to advance the excel knowledge. Thank you LEARNIT team
Continuing to have real pleasure in learning. LEARNIT knows how to TEACHIT.
Excellent course, functions clearly explained, by a gifted instructor. Thank you.
You're very welcome!
Your presentation of excel power was great. Thank-you LearnIT team
Our pleasure!
U deserve a huge cheque for this! All these info for us ur a world hero bugler and I really appreciate man. Thaaaaankuuuu and am subscribing now
Thank you very much for this superb course on Excel.
Would you tell me please if I can format my data as a table to update new information instead of using the offset function I find it easier and I believe I get the same result. Could you confirm this please?
Firstly thank you for such a lovely video. Now my question is why do we needed to make an offset function to dynamically select data from our array when we could have just coverted the data into a Table and when we enter any data in the table would it not auto update the index match formula that we used earlier.? Kindly resolve my query.
really appreciate your effort that you have put into this amazing video. i wish i can support u in anyway
• Where is your data housed
• how do you import and clean your data
• what types of methods do you use to clean your data
• do you use anything to automate your daily functions
• if no methods currently exists are you open to scripting repetitive tasks
• what documentation recourse does your team use to solve issues e.g. official documentation, stack overflow, tutorial videos
• what is the average time frame to complete a project and how much does that time increase with regards to the complexity of the project
• what metrics do you use to measure performance and how does that relate to growth potential within the company
Wow, that was really teaching. Thank you.
You are so welcome!
Wonderful tutorial! Loved the detail explanation but Couldn't find a link to this data to follow along :(
Hi Jyothi! The link to the exercise files is in the video description!
@@LearnitTraining Got it! Thanks.
Hi, which video talks about the absolute referencing? Thanks
Using Named Ranges to Retrieve Data 17:18
Good tutorial from you. It's by far the BEST i have found here. I have this problem. I was able to do the VLOOP, but when I change PROD# to say 11, the ProductName does not change. I don't know why. Please which version of excel are you using. Thanks
I think u need to autofill again after changing the prod # to refresh
MINE OF INFORMATION
THANKS FOR THE QUALITY VIDEO
Sir I tried using this formula =VLOOKUP(H1&H2,CHOOSE({1,2},First_Name&Last_Name,Salary),2,FALSE) At 58:28
33:15 what if the list of employees grows? Isn't it easier to just select the 3 entire columns?
Clearly instruction for the functions related each other, awesome.
What's the difference between this excel power users and the first three excel 2016 lessons..... Is this a continuation from excel 2016 advanced users??
superb 👍👍👍👍👍👍👍👍👍👍👍👍
The Dynamic Name Ranges (Offset) didn't work out well when I experimented with the function on Excel Office 365. @Sean, could you please try the function on Excel Office 365 and let me know what to do differently?
You prolly dont care but if you guys are stoned like me during the covid times you can stream pretty much all of the new series on Instaflixxer. Been binge watching with my gf these days =)
@Hezekiah Abdiel Yea, have been using InstaFlixxer for since november myself =)
Time 39:43 why column no 1 =INDEX(LAST,MATCH(A5,EMP,0),1) ? l did not understand. Can someone please explain it to me?
Because the Array in which to look for the answer is constituted of only one column. So the fonction is going to scan various rows in this only one column.
In the previous example where Sean Bugler explains how the Index fonction works the array is made of several rows and several columns, in which case he has to specify the number of rows and the number of columns. But here since there aren't any choice for the column, it can't be anything else than 1.
That's my explanation anyway. Hope that helps.
thanks!
Great
Thank you!
May I use Name Range in formular of Conditional Formatting?
Hi check in ho! You can get your question answered at Offsite, our online community - www.offsitebylearnit.com
In Excel 2021, no need to hold ctrl+shift and hit enter anymore :D just hit enter its fine~
Thanks for the tip!
Good
Thanks!
In 'Data Validating Conditional Formatting" if I want to find only D, R or H exclusively, how can I do it?
This should do the task - =IF($M$70, $H4=$M$7)
The last formula isnt working properly, its not able to pick up cells if you type two letter like DR or RH etc
Hi John. U can just add 'DR', and 'RH' on the data validation list. It should work that way
In the array function tutorial I think I typed exactly the same formula in E8 but it results in "Karen" rather than "Richard". =OFFSET('Array Function'!$A$1,1,0,COUNT('Array Function'!$A:$A)-1,1)
Posted your formula in the Excel file I'm playing with, it turns out COUNT used instead of COUNTA is causing the issue.
13:44
We need the excel files please
Hi Chinwe Esther, we have added link to the exercise files!
@@LearnitTraining Thank you. Got the files now.
Pretty confusing. Giving wrong formulas then showing the right ones... I'll be tempted to retain the wrong solutions.
Not confusing. It shows unwanted surprises you can stumble on when forgetting vital components on the beginner level.
I prefer to see those common mistakes then their solutions so as to know how to deal with such a scenario. Good job by Sean Bugler