Excel 2021 Advanced Tutorial

Excel 2021 Advanced Tutorial
Get Ad-Free Training by becoming a member today!
kzbin.info/door/qyBfm_H9ugGirk1ufYA2YAjoin
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-classes-workshops
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.

[@scguy681]

This guy is a FANSTASTIC teacher

[@LearnitTraining]

Thank you so much!

[@PearlBasnet]

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!!!!

[@LearnitTraining]

You are very welcome!

[@Urcinamongurl]

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🤣🤣)

[@paasha2233]

Preparing for data analytics , never worked much on excel before and now got a lot from this course.

[@LearnitTraining]

Thank you for watching!

[@ashhyd]

This tutorial is top notch! Very focussed and practical aligned to scenarios that could potentially crop up in day today work!!😀

[@soepaing5177]

Best excel explanation !Your voice is so good to focus on the subject and clear instruction make me go forward.

[@maryharvey724]

Very fine tutorial. The presenter demonstrates the tools expertly. His narration is in a clear pleasant voice. Save this for your professional development courses.

[@LearnitTraining]

Thank you very much!

[@truthfulpenguin]

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.

[@LearnitTraining]

Great to hear!

[@vivek6555]

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.

[@LearnitTraining]

Thanks for the tip!

[@Aerospacekitten]

This should be pinned!

[@BordiFX]

@@Aerospacekitten This ABSOLUTELY should be pinned!

[@arifzone6444]

Thankssssss

[@andyfernandez9599]

Thanks for posting, I had the same issue and could not figure out why!

[@user-pm8px6fi8t]

Thank you so much for the course. The presentation was off the charts.

[@LearnitTraining]

You're very welcome!

[@Spidervsvenom]

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.

[@LearnitTraining]

We learn something every day with Excel!

[@AhmedGamal-mi1ns]

This was a great one-hour and half hands-on practice course. Thanks, Faz.

[@LearnitTraining]

Glad you enjoyed it!

[@maomama5980]

Faz is the best instructor who has interesting and dramatic voice to make learning boring stuff much fun and engaging. 👨‍🏫

[@LearnitTraining]

Thank you for the kind words and thank you so much for watching!

[@karennguyen3223]

@@LearnitTraining Can we put these tutorials on our resume for a job?

[@Sidra_Shahbaz]

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!

[@LearnitTraining]

It's our pleasure!

[@RogerClubv]

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

[@LearnitTraining]

Thank you so much for the kind words!

[@robertochigbo4076]

Thank you for this course. I must say you are a great teacher.

[@LearnitTraining]

Wow, thank you!

[@agnieszkasummerlin8506]

Great training! Thank you!

[@LearnitTraining]

You're so welcome!

[@kiinahmed3397]

I can't thank you enough, I learned a lot.

[@LearnitTraining]

Glad we could help!

[@techtricksgh]

Great tutorial, I can't wait to watch to the end.

[@LearnitTraining]

Thank you! Cheers!

[@KrishnaPatel-cf3uz]

i have learned a lot and It may help me pass my ICT test for my secondary school so thank you

[@LearnitTraining]

We are very glad we helped you pass your exam!

[@Epictubeh]

Great explanation! Thank you!

[@LearnitTraining]

You're welcome!

[@habesha7777]

You are a very gifted instructor. God bless.

[@LearnitTraining]

Thank you! 😃

[@lailabenosmane5586]

You are the best instructor. Great job and verry helpful. Thank you!

[@LearnitTraining]

You're very welcome!

[@milinkabarnak4995]

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.

[@LearnitTraining]

Thank you so much for the kind words!

[@pharmedic1863]

I think you are the best teacher I have ever seen

[@LearnitTraining]

Thank you!

[@galmeidayou]

This was very useful, thank you! Very good at teaching.

[@LearnitTraining]

Thanks!

[@user-bz6xr3kl5s]

Thank you so much for you great, clear tutorial. I am learning a lot from you❤

[@LearnitTraining]

You are so welcome!

[@mariagambuti8787]

Great training! Faz is a great facilitator!

[@LearnitTraining]

Thanks!

[@edgars.smelovs]

Great job! Very useful information, it helps me a lot. Thank you very much!

[@LearnitTraining]

Glad it was helpful!

[@jiwonkim3180]

Faz, you are my valuable go to teacher.....

[@LearnitTraining]

Thank you for the kind words!

[@salmashahamiri2335]

Nice and clear, thanks so much

[@LearnitTraining]

You are welcome!

[@captainkeyboard1007]

This video show will make me want to use Excel 2021 more. It is the best tutorial ever developed.

[@LearnitTraining]

Thank you for the kind words and continued support Captain Keyboard!

[@captainkeyboard1007]

@@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.

[@user-uc2tr6bd8n]

Sure

[@captainkeyboard1007]

@@LearnitTraining You are welcome. Thank you for typing to me.

[@pennyworsfold3897]

Great course by the way!

[@LearnitTraining]

Thank so much for the kind words!

[@sivareddy3745]

Hi Karim you did Explained very good because you point out some the important and deep basic key points. Really VERY GOOD Karim.

[@LearnitTraining]

Glad it was helpful!!

[@roover321]

You guys are legend for this 🤘🤘

[@LearnitTraining]

🤘🤘

[@brendamartinezgomez2297]

excellent tutorial

[@LearnitTraining]

Thank you! Cheers!

[@thaonghiquan5524]

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

[@LearnitTraining]

Cảm ơn sự hỗ trợ của bạn!

[@kshitijverma4804]

utilised and enjoyed wNice tutoriallst increasing in value.

[@LearnitTraining]

Glad it was helpful!

[@pennyworsfold3897]

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.

[@LearnitTraining]

Thank you for the great tip!

[@queenrahma6373]

Thanks penny it helped

[@rashadganiyev]

Thank you really

[@hikihii7382]

Thanks a lot!

[@spacerayg.7382]

This is why I came to the comment section. Thanks.

[@folorunshorosemary4857]

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

[@shashankbose3698]

With working on a mac you lose out on a lot of fun stuff, anyways this was a great video Faz.

[@LearnitTraining]

Glad you enjoyed it!

[@AdesijiDareYomi]

You've done a great job but please can explain the flipping to custom sorting part again ?

[@johncarroll9803]

Next thing you need to teach is how to unlock worksheets so I can actually use the provided files =)

[@LearnitTraining]

Hi John! We just tested the files and none of them are locked. We suggest downloading them again and that should fix the issue.

[@tourekatinanhpatrick-samso6668]

Thanks

[@LearnitTraining]

You are very welcome!

[@tom19_06]

Hey Faz, awesome Video. Can you dynamically display / hide columns by creating a slicer?

[@LearnitTraining]

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.

[@PampostReturning]

Thank you so much!.

[@LearnitTraining]

You're welcome!

[@yusufgbengaayodele5576]

Please can I get a sample data to practice with? You have done a very nice job. Well done

[@LearnitTraining]

Exercise Files: bit.ly/3xZKDiw

[@yusufgbengaayodele5576]

Thanks so much for the data

[@vanscoyoc]

I am confused. The files are .html and not .zip and there are no instructions to actually get the files.

[@aboibrahim4069]

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?

[@rashminimasha197]

Favorite part: Pivot Chart - Change Data Source 🤗

[@LearnitTraining]

Thank you for watching!

[@ianjones6849]

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

[@LearnitTraining]

Hello Ian! You can get help directly from our instructors at Offsite, our online community - www.offsitebylearnit.com/

[@spacerayg.7382]

Look at the prices of those homes in the Conditional Format sheet. 😍😍😍

[@LearnitTraining]

Right?!

[@Ok_vard]

Thank you😊

[@LearnitTraining]

You are welcome!

[@mandeepsaini23]

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.

[@mandeepsaini23]

Its all right. I got it. It was not showing before. all good

[@anshu2006gupta]

Thanks 👍

[@LearnitTraining]

You are welcome!

[@khadijaraffat3161]

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

[@LearnitTraining]

Hello Khadija! You can get help directly from our instructors at Offsite, our online community - www.offsitebylearnit.com

[@kyeremehsylvester5646]

The video is just awesome

[@LearnitTraining]

Thanks!

[@michaeldonkor6283]

please how can i get a list so i practise. these tables do i have to create it on my own

[@LearnitTraining]

Exercise Files: bit.ly/3xZKDiw

[@xiaochangshen1658]

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

[@ndifonvincent8907]

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?

[@LearnitTraining]

Exercise Files: bit.ly/3xZKDiw

[@AljIsHere128]

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.

[@LearnitTraining]

Thanks for the tip!

[@lordyeezus]

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.

[@user-ec1zk5dt3l]

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?

[@LearnitTraining]

Please use the file Excel Adv Clean File.

[@stevemitchell7839]

The exercise files have accessibility issues that prevent them being used to follow along in the training- greys out table menu

[@LearnitTraining]

Hello stevemitchell7839! If you download a copy of the files you can edit the files however you want.

[@ginabarrell2155]

@@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

[@seifreda5911]

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

[@LearnitTraining]

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.

[@harmanjeetsingh7189]

hi Faz I'm not able to access the buttons on insert option

[@kakukaki4388]

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

[@empresshunt7280]

You needed to do a ‘save as’ to make the document editable.

[@chonghong7649]

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.

[@natural_maryjane]

Ungroup the worksheet first

[@nunosousa4013]

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?

[@LearnitTraining]

Hi Nuno! You can get help directly from our instructors at Offsite, our online community - www.offsitebylearnit.com

[@xiaochangshen1658]

I mean I downloaded the provided file, but everything under "insert' is grey, is the file protected?

[@eibnakaesh325]

i like it

[@LearnitTraining]

Thanks!

[@scguy681]

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

[@Thatotherschmuck]

ungroup the sheets

[@LearnitTraining]

Yep, ungroup the sheets

[@user-jf6je1pn8b]

Tha ks Fazzz

[@LearnitTraining]

You are welcome!

[@user-he5jp5ip7h]

Great tutorial, but I can't open exercise files

[@user-gt2hw8kk2e]

Please can we have your dataset?

[@LearnitTraining]

Exercise Files: bit.ly/3xZKDiw

[@khalilsantarellicoach]

I downloaded the file but I can't create the table (not event with ctrl+T) it's not clickable(?)

[@couragetamakloe4621]

Thank you. I finally learned how to protect sheets the right way from watching this video.

[@LearnitTraining]

Thank you for watching Courage!

[@Shruti-or8bn]

Where can I get the template of data?? is it possible??

[@LearnitTraining]

Exercise Files: bit.ly/3xZKDiw

[@nixsoftofficial]

Very nice

[@LearnitTraining]

Thanks!

[@gadohimself]

I cannot seem to use any options like tables, flashfill etc on the files. It's all greyed out :/

[@LearnitTraining]

For questions like these, head over to our learning community at offsitebylearnit.com and ask away!

[@oyetundesaheed8976]

The file is not responding whenever i try to convert it to a table. Everything under the insert ribbon is just blur. pls help!.

[@dirtysouth3291]

Search for the comment by Penny Worsfold under this video and follow his steps, then it should work

[@lordyeezus]

how do i remove the subtotal column afterwards?

[@LearnitTraining]

Hello Lord Yeezus! You can get help directly our instructors at Offsite, our online community - www.offsitebylearnit.com

[@hasanrafsanjani9641]

Can't download the exercise file from the description. Please check into the matter.

[@LearnitTraining]

Hello Hasan! We tested the files and links. They are currently all working.

[@mreighties2860]

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 :)

[@LearnitTraining]

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

[@mreighties2860]

@@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.

[@tbird6175]

What did you end up doing? I'm having the same problem. Read-only file.

[@Thatotherschmuck]

ungroup the sheets @@tbird6175

[@OnyejiEvans-fm7pm]

little too late but can I get the exercise file for this video?

[@LearnitTraining]

Exercise Files: bit.ly/3xZKDiw

[@ManoelaDaher]

For some reason the Clean file does not allow me to insert table or use other functions. They are all greyed out...

[@khalilsantarellicoach]

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?

[@chekurusnaaida2193]

same

[@papatomahawk]

Why provide data and make it read only....

[@NeedyChris]

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

[@LearnitTraining]

Hello Chris! We recommend using the file "Excel Adv Clean" that should solve your issue.

[@gold_friday]

How to remove just the table, not the data inside?? I wonder..

[@LearnitTraining]

This article may help: excelgraduate.com/delete-table-in-excel-but-not-data/

[@lyrastar999]

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.

[@LearnitTraining]

Excel is a very complex program. Even the Mac versions do not have full parity yet.

[@lyrastar999]

@@LearnitTraining ty

[@JordanEliasShanbrom]

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.

[@LearnitTraining]

Thanks for letting us know Jordan! We'll look into this shortly.

[@Prasannamahadagde]

Ungroup the sheets.

[@Ddau143]

Hello, the exercise files can't be edited. Is there any solution?

[@LearnitTraining]

Download a local copy and you will be able to edit the files

[@fifofuko1864]

i dont have table design on my excel :( fun to use tho

[@fifofuko1864]

i actually have it you need to select a box from the table haha sometimes you get stuck on silliest of things

[@LearnitTraining]

Glad you found it!

[@fifofuko1864]

@@LearnitTraining omg ty sir for the videos

[@malinmalindic8582]

1:02:51 it would be better to use function =today()

[@LearnitTraining]

Thanks for the great tip!

[@rahmiddin_dehqonov]

I downloaded exercise files which are in the video description but I can not insert table because it is protected workbook. Can you tell password, please?

[@LearnitTraining]

Hello Rahmiddin! None of the files are protected. You may want to go to the file options and select save as to get a local copy of the workbook.

[@Thatotherschmuck]

ungroup the sheets

[@elsieatieno4421]

👏

[@LearnitTraining]

👍

[@ramink.r9581]

guys make sure you ungroup the first few sheets. Otherwise the creating a table wont work.

[@LearnitTraining]

Thanks for the great tip!

[@rouencorpuz9502]

[@khalilsantarellicoach]

Sad beginner video 600k here 100k, people really don't want to learn useful things to change their life.

[@misterwise1742]

i have excel 2021 and for some reason i only have notes, it says add comment which shows as note

[@LearnitTraining]

Noted!