Excel 2021 Advanced Tutorial
ฝัง
- เผยแพร่เมื่อ 11 มิ.ย. 2024
- Excel 2021 Advanced Tutorial
Get Ad-Free Training by becoming a member today!
/ @learnittraining
Exercise Files: bit.ly/3xZKDiw
Who it's for: Beginners - experienced, everyday users looking to sharpen their skills and increase efficiency in Microsoft Excel.
What it is: Microsoft’s spreadsheet application used to clean and organize data.
What you'll learn: We start with the basics, showing you the user interface and quick ways to navigate. Once you’re comfortable with the software’s layout, we quickly move on to calculating data with basic formulas and functions. In the Intermediate section, we show you the various tools and ways to sort and filter data in a timely manner. Then, we dive into one of Excel’s most popular features; the Pivot Table. Our Advanced training starts with basic functions like the IF function. After that, we explore complex Database functions, provide an introduction to Macros, and show you how to save time by automating common Excel tasks.
Join Learnit Anytime for ad-free training, exams, certificates, and exclusive content:
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
Introduction 0:03
Formatting Lists as Tables 2:03
Flash Fill 10:59
Custom Sorting 13:46
Subtotals 17:59
QA Tool 22:38
Data Charts 25:45
Sparklines 33:13
Introduction to Pivot Tables 38:10
Advanced Pivot Tables Options 50:16
Data Validation 57:45
Conditional Formats 1:04:18
Comments and Notes 1:10:49
Linking Data 1:13:24
Privacy and Protection 1:16:25
Conclusion 1:20:00
#excel #excel2021 #exceltutorial
(C) 2022 Learnit, Inc.
Any illegal reproduction of this content will result in immediate legal action.
This guy is a FANSTASTIC teacher
Thank you so much!
just aced my exam today because of you, GOD bless you man. One love:)
Great job!
Best excel explanation !Your voice is so good to focus on the subject and clear instruction make me go forward.
Very well spoken/narrated and illustrated. I really appreciate how easy to follow the speaker made these examples and illustrations. The right method of teaching is to speak slowly and effectively instead of rushing through the examples fast and the presenter here did just that. Thank you!!!!
You are very welcome!
This tutorial is top notch! Very focussed and practical aligned to scenarios that could potentially crop up in day today work!!😀
These courses are fantastic and very useful in helping me get better at my Excel skills. These videos are very entertaining and engaging. Thank you for your help!! (P.s. I love Excel and will be doing it for fun now just like you🤣🤣)
Great Course! My brain is absolutely fried since I did this and the Beginner tutorial all in basically one sitting, but I definitely fell 100x more confident using Excel now than I did at the beginning. I am probably going to try to do the Expert tutorial Monday when I come into the office again, and honestly, I am looking forward to it. Never thought that an Excel tutorial would be actually fun to just sit down and follow but here we are.
Great to hear!
Very fine tutorial. The presenter demonstrates the tools expertly. His narration is in a clear pleasant voice. Save this for your professional development courses.
Thank you very much!
This was a great one-hour and half hands-on practice course. Thanks, Faz.
Glad you enjoyed it!
Thank you so much for the course. The presentation was off the charts.
You're very welcome!
Learnit Training you are doing a great job by proving such valuable lectures in free. May God give you rewards for this. Thank you Faz!
It's our pleasure!
Preparing for data analytics , never worked much on excel before and now got a lot from this course.
Thank you for watching!
Presentation style is really nice. Language is clear, awesome tutorial.
Glad you liked it
Great job! I thought the grouping sheets were so important for me to learn. I had created spreadsheets for work in the past and would have so many tabs with so much duplicated information. The grouping method would have saved me so much time.
We learn something every day with Excel!
You are the best instructor. Great job and verry helpful. Thank you!
You're very welcome!
Thank you for this course. I must say you are a great teacher.
Wow, thank you!
I have learned new things! Thanks Boy!
Glad to hear it!
This was very useful, thank you! Very good at teaching.
Thanks!
This excellent tutorial provides a comprehensive overview of Excel. With its step-by-step instructions, it's easy to follow and understand even for those less familiar with the software. The clear language and illustrative examples make it even easier to grasp the concepts. This tutorial is a valuable resource for anyone looking to enhance their Excel skills.
Thank you so much for the kind words!
Great training! Thank you!
You're so welcome!
You are a very gifted instructor. God bless.
Thank you! 😃
fantastic i loved the grouping technique
Thanks for watching!
Thank you so much for you great, clear tutorial. I am learning a lot from you❤
You are so welcome!
Faz is the best instructor who has interesting and dramatic voice to make learning boring stuff much fun and engaging. 👨🏫
Thank you for the kind words and thank you so much for watching!
@@LearnitTraining Can we put these tutorials on our resume for a job?
Great explanation! Thank you!
You're welcome!
cam on ban nhieu lam luon a, ne va thuc quy nhung nguoi gioi ma con sẵn sang chia se kien thuc va kinh nghiem cua ban than cho nguoi khac, chuc ban luon thanh cong nhe
Cảm ơn sự hỗ trợ của bạn!
I think you are the best teacher I have ever seen
Thank you!
Great job! Very useful information, it helps me a lot. Thank you very much!
Glad it was helpful!
I can't thank you enough, I learned a lot.
Glad we could help!
Nice and clear, thanks so much
You are welcome!
As someone dedicated to continuous learning, I've explored numerous tutorials across various subjects throughout the years. Without a doubt, Faz Karim's Excel 2021 Advanced tutorial stands out as an exceptional resource in my learning journey.
Karim's mastery of the subject matter and his engaging teaching style combine to create an educational experience that is nothing short of delightful. His explanations are not just informative but meticulously crafted, ensuring that even the most intricate topics are presented in a way that's easy to grasp. No matter your prior Excel knowledge, Karim's talent for breaking down complex concepts into manageable pieces makes this tutorial accessible to learners of all backgrounds.
Without reservation, I wholeheartedly recommend Karim's tutorial to anyone seeking to elevate their Excel proficiency. It's a learning experience that promises growth and mastery in a user-friendly and enjoyable format. I can't wait to watch his next course, Excel 2021 Expert.
#excelmastery
#learnexcel
#exceltutorial
Thank you so much for the kind words!
Great training! Faz is a great facilitator!
Thanks!
i have learned a lot and It may help me pass my ICT test for my secondary school so thank you
We are very glad we helped you pass your exam!
Faz, you are my valuable go to teacher.....
Thank you for the kind words!
Great course by the way!
Thank so much for the kind words!
Thanks
You are very welcome!
Great tutorial, I can't wait to watch to the end.
Thank you! Cheers!
For some reason, they table option at 4:47 is greyed out for me and the shortcut option also does not seem to work. Any idea why this is occurring? Thank you.
NVM, i got it. For some reason when you download the exercise file, the first 4 sheets are grouped, you need to ungroup them.
Thanks for the tip!
This should be pinned!
@@Aerospacekitten This ABSOLUTELY should be pinned!
Thankssssss
Thanks for posting, I had the same issue and could not figure out why!
utilised and enjoyed wNice tutoriallst increasing in value.
Glad it was helpful!
excellent tutorial
Thank you! Cheers!
Hi Karim you did Explained very good because you point out some the important and deep basic key points. Really VERY GOOD Karim.
Glad it was helpful!!
You've done a great job but please can explain the flipping to custom sorting
part again ?
When I am trying to access the exercise file and trying to do the same task along with the video it shows insert option fully grayed out. any solution for this?
Please use the file Excel Adv Clean File.
Every file is like thia
You guys are legend for this 🤘🤘
🤘🤘
This video show will make me want to use Excel 2021 more. It is the best tutorial ever developed.
Thank you for the kind words and continued support Captain Keyboard!
@@LearnitTraining You are welcome. Thank you for typing to me. Incidentally, I am subscribed to your platform. I began using Microsoft Windows XP Professional and Microsoft Office XP Professional with Microsoft Publisher 2002 by myself after I bought my first microcomputer, all by myself. Again, I thank you for typing to me.
Sure
@@LearnitTraining You are welcome. Thank you for typing to me.
Thank you so much!.
You're welcome!
Thanks 👍
You are welcome!
i like it
Thanks!
Thank you😊
You are welcome!
Hey Faz, awesome Video.
Can you dynamically display / hide columns by creating a slicer?
To hide/show columns with slicer, you can create two bookmarks, one with all column values showing in the matrix and in the other, select the column field in the matrix, and in the Filter Pane, Select only the values that you need to show and create a bookmark. You can assign these bookmarks to a button to show and hide the views. Alternatively, you can unpivot your data in the Power Query Editor and use a matrix to show the static column in ROW of matrix and Attribute column in Columns of matrix. You can also use a slicer to quickly show and hide groups of values in a pivot table.
I just had the same trouble with the greyed out table menu options. At the very bottom of the page, it said Accessibility: Investigate. So I clicked on that and it added an Accessibility tab. I clicked Accessibility Checker under the tab and then it brought up an error box to the right. Once I fixed the (3 minor) errors, everything was fine.
Thank you for the great tip!
Thanks penny it helped
Thank you really
Thanks a lot!
This is why I came to the comment section. Thanks.
Your videos has been really helpful. Thank you but I do have a question. I did save my chart as a template but when I used it for my second d chart there was a slight difference so the salesperson name became the legend while the average became the average data became the horizontal axis is there a solution to this. Thank you. I hope to get a swift response from you. God bless
The video is just awesome
Thanks!
With working on a mac you lose out on a lot of fun stuff, anyways this was a great video Faz.
Glad you enjoyed it!
Please can I get a sample data to practice with? You have done a very nice job. Well done
Exercise Files: bit.ly/3xZKDiw
Thanks so much for the data
I am confused. The files are .html and not .zip and there are no instructions to actually get the files.
Next thing you need to teach is how to unlock worksheets so I can actually use the provided files =)
Hi John! We just tested the files and none of them are locked. We suggest downloading them again and that should fix the issue.
HI ! this is very useful, but i find that everything under the toolbar under "insert" is all grey, i can't copy paste the data as well, can you please help me with this
Great help and excellent presentation!! I don't have the new "threaded" comments in my version 2021 excel, just the old notes type, how do I install the new comments? I have the Office Professional Plus 2021 loaded on my PC.....THANKS
Hello Ian! You can get help directly from our instructors at Offsite, our online community - www.offsitebylearnit.com/
On the data charts portion of the video - when I try to add the average column, it gives me an average per week but not per person across the weeks. How do I fix this?
Hi Faz, I have watched your first video for begineer. very helpful. For this advanced excel video, I could not find exercise files. Could you please guide me to find the file. would be much appreciated.
Its all right. I got it. It was not showing before. all good
Tha ks Fazzz
You are welcome!
How come when you sort a column E the data in the other columns keeps attached to that column
Does Excel connect the data in the same row for all columns?
hi Faz I'm not able to access the buttons on insert option
Hi, i can't save the file because it's read-only, and also can't use insert and data tab. Their command groups are all grayed. Please help me
You needed to do a ‘save as’ to make the document editable.
After I watch Beginner, Advanced and Expert Tutorials for Excel 2021 , Do I need to watch Excel 2019 pivot tables , power user and intro to data analysis tutorials ? or there will be equivalent videos for them in 2021 Excel Tutorials ? and Thanks for Advance
Hi Seif! The equivalent videos are coming this summer! But you can still watch the others as 80 - 85% of the content is relatively the same.
Look at the prices of those homes in the Conditional Format sheet. 😍😍😍
Right?!
I mean I downloaded the provided file, but everything under "insert' is grey, is the file protected?
Very nice
Thanks!
Favorite part: Pivot Chart - Change Data Source 🤗
Thank you for watching!
Hi, thank you for your lessons - they are helping me! I have a question - the version of Excel I'm working on is apparently 2023, so not sure why I'm having this problem, but I can't find the design tab. It doesn't pop up when I create a table, and when I go to file - option - custom ribbon etc. to look for it it's not there. Any ideas?
This article may help
@@LearnitTraining Hi thanks! Not seeing an article though.
At 30:05, you talk about the chart formating where there is a pop up next to the chart, and you can remove the chart title or move the labels up and down. I can't find that in mac. Is there another way to do it in mac
Hello Khadija! You can get help directly from our instructors at Offsite, our online community - www.offsitebylearnit.com
For the Data Chart section doing a combo chart to make average a line? U don't mention to click the secondary axis in order to show average for line.
Thanks for the tip!
The exercise files have accessibility issues that prevent them being used to follow along in the training- greys out table menu
Hello stevemitchell7839! If you download a copy of the files you can edit the files however you want.
@@LearnitTraining I'm finding the same. I have downloaded and tried to even Save As but I'm unable to edit anything as it is all greyed out
Great tutorial, but I can't open exercise files
I just notice that on Mac, I cannot find the section on Change chart type, meaning that I can only use the graphics for default. There is a certain way to solve this?
Hi Nuno! You can get help directly from our instructors at Offsite, our online community - www.offsitebylearnit.com
also one more thing that i have noticed is that sparklines aren't visually accurate. the bars are of the same height for very contrasting values. so what is the point of even having them as a feature.
I downloaded the file but I can't create the table (not event with ctrl+T) it's not clickable(?)
I can't find a link to the the list data we're working with. please can i get help on how to get the data list?
Exercise Files: bit.ly/3xZKDiw
may i know how to enable the insert button for clicking the table as i open the file it not allowed me to click on the table button.
Ungroup the worksheet first
For some reason the Clean file does not allow me to insert table or use other functions. They are all greyed out...
please how can i get a list so i practise. these tables do i have to create it on my own
Exercise Files: bit.ly/3xZKDiw
little too late but can I get the exercise file for this video?
Exercise Files: bit.ly/3xZKDiw
I cannot seem to use any options like tables, flashfill etc on the files. It's all greyed out :/
For questions like these, head over to our learning community at offsitebylearnit.com and ask away!
The file is not responding whenever i try to convert it to a table. Everything under the insert ribbon is just blur. pls help!.
Search for the comment by Penny Worsfold under this video and follow his steps, then it should work
Can't download the exercise file from the description. Please check into the matter.
Hello Hasan! We tested the files and links. They are currently all working.
Where can I get the template of data??
is it possible??
Exercise Files: bit.ly/3xZKDiw
One BIG problem ! the practice excel sheet is passwrod protected-- I cant practice the INSERT tab because when all the options to create a tabkle are 'grey' NA
ungroup the sheets
Yep, ungroup the sheets
when i choose specific day and bunch of people such as anne jan laura , excel always gives me one of their sum of revenue what i am doing wrong
Hello,
I downloaded the files but when I open them in Excel most of the menus are greyed out. How can I edit the sheet is I can't access the Menu?
James :)
Hi James! We just downloaded all the files and were not able to replicate the same error. We recommend restarting your computer to see if that will fix the issue
@@LearnitTraining I forgot to reply back the same day or next day cause apparently I did something and now it all works just fine. I have access to all the menus.
Thanks for a wonderful course, enjoyed it very much.
What did you end up doing? I'm having the same problem. Read-only file.
ungroup the sheets @@tbird6175
When I add the data chart, instead of having down the weeks, I have the names, and then under the graph I have the weeks, How can I solve that?
same
how do i remove the subtotal column afterwards?
Hello Lord Yeezus! You can get help directly our instructors at Offsite, our online community - www.offsitebylearnit.com
Thank you. I finally learned how to protect sheets the right way from watching this video.
Thank you for watching Courage!
How to remove just the table, not the data inside?? I wonder..
This article may help: excelgraduate.com/delete-table-in-excel-but-not-data/
👏
👍
Can't edit the files even though I downloaded them and saved them to my computer, I can't make a table with the first exercise. The entire insert tab is greyed out and is not allowing me to make any changes so I can't use this file for these exercises.
Thanks for letting us know Jordan! We'll look into this shortly.
Ungroup the sheets.
Please can we have your dataset?
Exercise Files: bit.ly/3xZKDiw
I'm using Libre open office and they need to catch up their functionality of the excel equivalent program. it sucks pretty bad. just watching the video anyways.
Excel is a very complex program. Even the Mac versions do not have full parity yet.
@@LearnitTraining ty
Why provide data and make it read only....
You cut entire thing on a sheet. Then paste it on a new sheet. Then you can use that spreadsheet.
Do not copy and paste. Cut and paste.
Hello, the exercise files can't be edited. Is there any solution?
Download a local copy and you will be able to edit the files
I can't do anything with this exercise cause everything in the insert and data tabs is blanked out. I can't insert tables or anything. So frustrating
Hello Chris! We recommend using the file "Excel Adv Clean" that should solve your issue.
He just needs to ungroup the sheets