Excel Power User Advanced Tutorial

Excel Power User Advanced 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
Introduction to Dynamic Range Charts 0:03
Dynamic Range Chart Data 3:36
Defining Dynamic Named Ranges 9:11
Defining Dynamic Legend Entries 14:45
Defining Dynamic Category Labels 18:58
Dynamic Offset 21:30
Updating Dynamic Named Ranges 28:18
Enabling the Developer Tab 31:03
Inserting Spinner Buttons 32:35
Linking Spinner Buttons 34:36
Outside Formulas in Pivot Tables 38:40
Calculated Fields 44:44
Calculated Items 49:37
Removing Calculated Fields and Items 53:54
Grouping Pivot Items 55:32
Showing Values As 57:57
Importing Web Data 1:01:02
Recording Macros 1:06:08
Running Macros and Selecting Fields of Data 1:12:19
Editing VBA Code 1:19:05
#Excel
(C) 2021 Learnit, Inc.
Any illegal reproduction of this content will result in immediate legal action.

[@SolidSnake59]

You have made very interesting video. I knew most of it, but I wish someone showed me those things few years ago.

[@razvanungureanu8897]

Excellent explained, excellent examples and these are really cool, but also advanced things. Thank you very much

[@LearnitTraining]

You're very welcome!

[@longtll]

Thanks for your efforts, waiting for more videos in advanced excel , thanks again

[@wintermorillo6567]

As always, very useful content! Thank you!

[@LearnitTraining]

Glad it was helpful!

[@mdcs1992]

Excellent channel. Having been disabled by serious illness and losing my Analyst job, I'm trying to keep my current Excel skills up to date and learn new ones....so Thank You.

[@LearnitTraining]

You are welcome and we hope you get well soon!

[@cynthiac8387]

I needed to reverse the order of the Wine Sales Commission calculation to =$C$1*B4 in order to get a positive value. Otherwise for some reason it resulted in a negative value....

[@lisamccray6481]

Thanks, Sir. This is such a wonderful and appreciated contribution. But I see six thumbs down. Well, there should be six videos out there better than this one. I'm sure there are not. This is something that moves society along. Thanks for your hard work. You're like the scrubbing bubbles commercial. You figured it out. So we don't have tooooooooooo. Sparkling. Gleaming. Wow. Feels like a lottery win. Humbling. Now back to your Pythagorean computer theorems. See you next year. I should be understanding it by then. Ha ha. 🤔?

[@yashdawra8805]

Very useful, thank you so much Sir.

[@LearnitTraining]

You are very welcome!

[@Shadow-uv4ex]

1:03:12 Has some interesting info about stocks, but can't seem to do this as well with the formatting changes from Google

[@vanycdtd08]

thanks!

[@tahrin.choudhury]

How to turn on the calculated item? Mine is greyed out. Can someone please help?

[@tahrin.choudhury]

17:46 When I tried to select DynamicUnits it does not show anything under series values? Can someone please help me out? What am I doing wrong?

[@ifechukwuoguegbu6555]

Did you create your own named range or you tried using pre-made list already in the excel file?

[@mansideshpande5458]

I am not able to import data from web. When I try to import it shows me some html code and not the data as it is? What might be the reason for this?

[@LearnitTraining]

Hi Mansi, our instructors are available to answer questions at Offsite our online community - bit.ly/2RHRbzz

[@Dexter801]

When I use offset function it displays the full list ie Month, what have I done wrong??

[@ifechukwuoguegbu6555]

I circumvented this by ignoring the produced list, copying the offset formula from the formula bar & using that to rename the Defined name ranges in name manager So just ignore it imo. It's probably an updated excel function & acts differently now compared to before 👍

[@eldiel37]

any example file to work on?

[@alvaroninainca2501]

It's in the previous video of this series "Excel Power User Beginners"

[@calvinc851]

Hmmm assigning ctrl +t to that macro is maybe not the best practice.

[@usmankhalid9370]

Is there a video about "Excel Power User Expert" level?

[@LearnitTraining]

There is not. This is the last in this series. We do have a new update 2021 version coming soon however!

[@monkfoobar]

7:00 Is the “-1” really needed?

[@Vinickw]

Yes, because it is counting everything on the column, and you don't want to count "mouth"

[@kamalsonnylal8558]

I am unable to download the exercise files. Please advise

[@LearnitTraining]

Hi Kamal! We just tested the link and it is working fine currently

[@abbaskhalid5047]

You talk to much Sir, stick to skill to learn only, this will shorten your video. I have seen Liela Videos, she talks straight and to the point. Thanks

[@LearnitTraining]

Thanks for the tip