Excel Power User Beginner Tutorial
Excel Power User Beginner Tutorial
Get Ad-Free Training by becoming a member today!
/ @learnittraining
Exercise Files: bit.ly/3if2Aye
Who it's For: This course was built from the ground up for the advanced users and those in search of more than typical Excel tools and processes. Attendees of this course are recommended to have completed our Excel 2016 training, or at a minimum already be proficient in the skills referenced in those courses.
What it is: The Excel Power User course expands on the idea that the tools by themselves are only part of the equation. To say that you’re capable of solving a problem is enough for most users, but participants in this course aren’t most users. The desire to learn and invest time in building resources that solve problems faster and with less effort is a key quality of the power user and something the course maintains a heavy focus on.
What You'll Learn: In this series, students will learn how to intermix tools in Excel to accomplish time-intensive tasks. Students will learn to develop a deeper understanding of data mining tools like VLOOKUP, as well as more advanced solutions like the INDEX, MATCH, and OFFSET functions. Throughout the course, users will learn how to expedite data analysis through dynamic ranges and how to push Excel to the edges of what’s possible out of the box with new perspectives on how to think their way through challenging problems.
For ad free training and certificates please join Learnit Anytime:
www.learnitanytime.com
For Private Group Trainings please visit:
www.learnit.com/private-group...
Manuals: bit.ly/30xZb59
Username: manuals
Password: password
Start 0:00
VLOOKUP 0:03
Range Lookup 7:45
Autofilling Lookup 10:41
Defining Names 14:06
Using Named Ranges to Retrieve Data 17:18
INDEX 22:11
MATCH 27:41
INDEX & MATCH (Part 1) 30:28
INDEX & MATCH (Part 2) 34:27
INDEX & MATCH (Part 3) 40:23
Introduction to Array Formula 44:00
Array Formula 46:20
Array Function 51:49
Dynamic Named Ranges (Offset) 1:01:03
OFFSET Function 1:05:30
Name Manager 1:12:52
Basic Conditional Formatting 1:18:37
Building New Rules and Troubleshooting 1:22:06
Expanding Conditional Formatting Fields 1:30:39
Text Search Conditional Formatting 1:33:14
Data Validating Conditional Formatting 1:40:16
#Excel #VLOOKUP
(C) 2021 Learnit, Inc.
Any illegal reproduction of this content will result in immediate legal action.
Пікірлер: 59
ONE OF THE BEST TUTORIAL YOU EVER GET ONLINE
@LearnitTraining
2 жыл бұрын
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.
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
really appreciate your effort that you have put into this amazing video. i wish i can support u in anyway
Clearly instruction for the functions related each other, awesome.
Excellent course, functions clearly explained, by a gifted instructor. Thank you.
@LearnitTraining
2 жыл бұрын
You're very welcome!
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?
MINE OF INFORMATION THANKS FOR THE QUALITY VIDEO
Your presentation of excel power was great. Thank-you LearnIT team
@LearnitTraining
3 жыл бұрын
Our pleasure!
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.
thanks!
Wow, that was really teaching. Thank you.
@LearnitTraining
3 жыл бұрын
You are so welcome!
superb 👍👍👍👍👍👍👍👍👍👍👍👍
• 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
33:15 what if the list of employees grows? Isn't it easier to just select the 3 entire columns?
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
@kensimba7868
2 жыл бұрын
I think u need to autofill again after changing the prod # to refresh
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?
@hezekiahabdiel6868
3 жыл бұрын
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 =)
@brunoduncan2381
3 жыл бұрын
@Hezekiah Abdiel Yea, have been using InstaFlixxer for since november myself =)
Wonderful tutorial! Loved the detail explanation but Couldn't find a link to this data to follow along :(
@LearnitTraining
2 жыл бұрын
Hi Jyothi! The link to the exercise files is in the video description!
@jyothikaniti1662
2 жыл бұрын
@@LearnitTraining Got it! Thanks.
Great
@LearnitTraining
Жыл бұрын
Thank you!
Hi, which video talks about the absolute referencing? Thanks
@LearnitTraining
Жыл бұрын
Using Named Ranges to Retrieve Data 17:18
Sir I tried using this formula =VLOOKUP(H1&H2,CHOOSE({1,2},First_Name&Last_Name,Salary),2,FALSE) At 58:28
In 'Data Validating Conditional Formatting" if I want to find only D, R or H exclusively, how can I do it?
@snehakembhavi9541
Жыл бұрын
This should do the task - =IF($M$70, $H4=$M$7)
May I use Name Range in formular of Conditional Formatting?
@LearnitTraining
2 жыл бұрын
Hi check in ho! You can get your question answered at Offsite, our online community - www.offsitebylearnit.com
Good
@LearnitTraining
2 жыл бұрын
Thanks!
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?
@SebaDuron
3 жыл бұрын
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.
The last formula isnt working properly, its not able to pick up cells if you type two letter like DR or RH etc
@kensimba7868
3 жыл бұрын
Hi John. U can just add 'DR', and 'RH' on the data validation list. It should work that way
In Excel 2021, no need to hold ctrl+shift and hit enter anymore :D just hit enter its fine~
@LearnitTraining
2 жыл бұрын
Thanks for the tip!
13:44
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)
@TY-zl1vw
3 жыл бұрын
Posted your formula in the Excel file I'm playing with, it turns out COUNT used instead of COUNTA is causing the issue.
We need the excel files please
@LearnitTraining
4 жыл бұрын
Hi Chinwe Esther, we have added link to the exercise files!
@CHINWE_ONUCHI
4 жыл бұрын
@@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.
@milliem4178
3 жыл бұрын
Not confusing. It shows unwanted surprises you can stumble on when forgetting vital components on the beginner level.
@kensimba7868
3 жыл бұрын
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