The ultimate Excel tips and tricks guide

20 years of Excel Tips and Tricks jam packed into 1 hour.
Created for the Microsoft Excel Tech Community Webinar Series
Learning Power BI?
Check out my book
"Power BI for the Excel Analyst"
pbi.guide/book/
Amazon: www.amazon.com/Power-BI-Excel...
0:00 Introduction
00:53 Merging and Hiding
3:39 Fills and Formats
13:10 Copy Paste
15:10 Shapes
18:04 Tables
23:50 Power Query
31:00 HYPERLINK Function
34:54 Hyperlink with XLOOKUP
37:15 Traffic Lights (Icons)
39:16 AGGREGATE
42:00 SWITCH
44:45 TEXTJOIN
46:30 Grand Totals on a Stacked Chart plus the 5 m trick
48:45 Dependent Drop Downs
56:24 XLOOKUP and Data Validation
58:21 Quick PIVOT tricks
The Excel file is available for you to download here accessanalytic.com.au/excel-t...
The other webinars in the series are here
techcommunity.microsoft.com/t...
The team and I at Access Analytic develop Power BI and Excel solutions for clients in Australia and deliver training around the World. accessanalytic.com.au/
Did you know I've written a book "Power BI for the Excel Analyst"?
pbi.guide/book/
Connect with me
wyn.bio.link/

[@ne1711]

This is the most informative video I have ever watched, and also most useful for those who works extensively with office apps. Like You have improved the efficiency of thousands of people. Unbelieveable work.

[@AccessAnalytic]

Thanks for the kind words

[@bernardheymans5955]

You definitively made my day.
second video seen... LOST 3 hours looking. WON many hours working.

[@AccessAnalytic]

Excellent, thanks for taking the time to leave a kind comment

[@joedi]

I have to admit I smugly thought ‘Been here and done it all’. Ha! Little did I know, the old cat learned some new tricks.
Count: 8 new ones that I did not know!

[@AccessAnalytic]

I never stop learning new ones, including the ones I’ve forgotten 😀

[@joedi]

@@AccessAnalytic one of my favourite one is Ctrl [ for auditing. It’s one I use often when I inherit files.
Another one is creating a list of links (Bob Umlas - Excel Magic excel4 macros. Trick 23 or 24 by memory. Another that has helped while inheriting files.

[@AccessAnalytic]

Good ones Dee. Bob Umlas is a master of these tips

[@wmfexcel]

Power Query - The best washing machine for dirty data!
I love it. :)

[@AccessAnalytic]

Thanks 💚

[@Serraomomma]

I’ve been using excel for years and these tips are priceless! Some I knew, but many were new! Bravo!!!!

[@AccessAnalytic]

Thanks Roxanne, glad there were a few new ones in there for you 😀

[@txreal2]

12:06 Thanks for reminding me to remove the "idiot" like from my Autocorrect 🤣
I also saved a list of your Autocorrect options

[@AccessAnalytic]

Yes I've been caught by that one !! 😁

[@breadplastic]

I want to visit Perth once in my life. Bucket list.

[@AccessAnalytic]

It’s a great place, especially outside of the winter months

[@bimanroy8865]

Pivot learning was great

[@AccessAnalytic]

Glad you liked it. I’ve a short Pivot Table video coming out this weekend / stay tuned!

[@PhuongNguyen-wm8wi]

You are so kind to share all the tricks like that. I have been frustrating with doing things without shortcuts, but I didn't even know many of them were possible until I watched your video. Thank you sooooooo much!!!

[@AccessAnalytic]

You’re welcome Phuong

[@chartphred1]

Fancy meeting you here! :o)

[@PonyUWA]

Thanks for the GETPIVOTDATA tip

[@AccessAnalytic]

You’re welcome 😃

[@rahulmahajan5932]

To be Honest... Ur Presentation Skills r Pretty Impressive

[@AccessAnalytic]

Thank you

[@TheJoshtheboss]

6:14 what kind of wizardry is this :). Excel is like an infinite well of suprises

[@AccessAnalytic]

😀. A never ending box of tricks !!

[@gwenkreager4208]

Excellent! I will be watching this every day until I have memorized every trick!!!!!! Amazing new stuff.

[@AccessAnalytic]

Hi Gwen, thanks for taking the time to leave a kind comment, glad you find it useful

[@txreal2]

21:50 Insert Slicer - I use this quite a bit so I put this in the QAT. Thanks.

[@abdullahalnaiem3501]

Your video is very helpful and you are the best. Love from Bangladesh

[@AccessAnalytic]

Very kind, thank you

[@jcc5018]

Only 10 mins in, but that double click formatting thing will save me so much time! It was such a pain trying to copy formats to different parts. And it works on WORD too

[@AccessAnalytic]

Glad it's helping! Double Click formatter is a great trick

[@jcc5018]

@@AccessAnalytic it just goes to show, if I were to take the time to scan those help prompts or tips on some programs, instead of always assuming I know how they work,I could have known that trick long ago

[@AccessAnalytic]

I still learn new things 25 years on

[@flance4011]

You are the best MVP.

[@AccessAnalytic]

Hah, don't tell the others 🤣. Thanks F Lance

[@tahsincetinkaya7970]

One of the best tutorials. Hands down

[@AccessAnalytic]

Thankyou so much Tahsin

[@manel1185]

I've seen many unusual Excel tips that can be helpful in the future! Thanks

[@AccessAnalytic]

You’re welcome Manel

[@awakeshotgun1237]

I love your accent! And thank you very much

[@AccessAnalytic]

Hah , thank you

[@jsmnrzth]

This is so very awesome. I learnt so much. Thanks!

[@AccessAnalytic]

You’re welcome. I appreciate you taking the time to let me know you found it useful

[@roberth.9558]

Outstanding, Wyn. Thank you for the instruction.

[@AccessAnalytic]

You’re welcome Robert

[@Computergaga]

Nice one Wyn. I was waiting for your infamous Slicer tab technique 😜

[@AccessAnalytic]

Hah, you caught me, and I stole that from my colleague 🤣🤣

[@adamlasry5225]

Thanks a lot.

[@AccessAnalytic]

You’re welcome

[@tripuramalluyashwanth8701]

Out of words. Just solved many of my problems.

[@AccessAnalytic]

Great, glad to help 😁

[@benaycock1646]

Every video of yours I’ve seen makes me holler in excitement lol. Glad I found your channel! Thanks for sharing!

[@AccessAnalytic]

Cheers Ben, thanks for sharing the kind feedback.

[@adiloufkir317]

I've learned so much through this fantastic tutorial! Huge thanks!

[@AccessAnalytic]

You’re very welcome, thanks for taking the time to leave a kind comment

[@minecraftmike5193]

This is so awesome, love your content!

[@AccessAnalytic]

Thanks so much MinecraftMike!

[@BenDieselBasicAutomotive]

Thanks for sharing your knowledge.... More power to you, Sir!... May GOD bless you more and more......

[@AccessAnalytic]

Thank you

[@txreal2]

Also it seems Australia has more Microsoft MVP per capita than other countries. :)
Thanks for sharing your lifetime Excel(lent) experience!

[@AccessAnalytic]

Indeed, we're punching above our weight there

[@KingsoTV]

Right-click and drag a cell / range border...
Also, for the dependant lists, I just use separate tables with the DV of =INDIRECT(%primary%). I have a macro that sets the table name to the header value.

[@kennethjohansson840]

Bro you are beast

[@AccessAnalytic]

Thanks - I think 😆

[@billgillaspey9036]

Really good, quick, overview. I LIKE it; watched it a second time. [But only 190 likes? deserves MANY more than that.]

[@AccessAnalytic]

Thanks Bill - let people know! 😊 I appreciate you taking time to leave your kind feedback

[@aureliohess9349]

PERFECT WORK!!! CONGRATULATIONS.

[@AccessAnalytic]

Thanks Aurelio

[@tahiryfanambinanarafidiman9020]

The greatest Excel tips and tricks I've ever seen!!! Thank you big Wyn 😎

[@AccessAnalytic]

You’re welcome Tahiry. Thanks for taking the time to leave a kind comment

[@JuanIArana]

Great stuff out there Wyn !!!

[@AccessAnalytic]

Thank you

[@danjarupath]

Awesome 👍👍👍👍

[@AccessAnalytic]

Cheers

[@stefaanlouette]

Some of these tips are bananas!! Thanks

[@AccessAnalytic]

Cheers Stefaan

[@rahulshewale9206]

Thank you sir

[@michellethaovu2842]

Super informative. Sometimes I thought "oh I already know this trick" then you take it to next level and my jaw dropped =))

[@AccessAnalytic]

Hah, thanks for the wonderful feedback Michelle

[@Serraomomma]

He brought it up two levels!

[@AccessAnalytic]

😀

[@zulfiqarhussain5276]

God bless you. Very informative.

[@AccessAnalytic]

You’re welcome Zulfiqar

[@andregilhespy7504]

Thank you so much for this! So many helpful tips and lots of crucial knowledge for a beginner 😎

[@AccessAnalytic]

You’re welcome Andre

[@pm1336]

Great job!!!

[@AccessAnalytic]

Thanks

[@oztellingtales5879]

Lots of juicy stuff!

[@AccessAnalytic]

Cheers Oz

[@miguelcalejo1770]

absolutely marvelous...excellent 365

[@AccessAnalytic]

Thank you Miguel

[@eakonzhan4957]

this is fantastic！！！

[@AccessAnalytic]

Thanks very much eakon

[@jatinnandwani6678]

Thanks so much
This is golden...

[@AccessAnalytic]

You’re welcome Jatin

[@corporatelearnings__]

Sir, Very Impressive! Keep up the good work🙂

[@AccessAnalytic]

Thank you Naveen

[@sanjanasridhar6119]

So good!

[@AccessAnalytic]

Thanks for taking the time to leave a kind comment

[@s.triyambakpatro7341]

Awesome Tips

[@AccessAnalytic]

Thanks for watching

[@aimalalkozai5906]

perfect mate!

[@AccessAnalytic]

Glad to help

[@RachelDraaijer]

So much wow!!!

[@AccessAnalytic]

Cheers Rachel 😊

[@jerzykepinski]

Good stuff.

[@AccessAnalytic]

Thanks

[@elaf70]

Nice & very useful job - Wyn
- 1 of the most complete 1 I've ever seen on TH-cam. Everyone I think could grab some new tips to increase his own efficiency in EXCEL.
=> Will for sure keep your related demo file (thanks for it) + video in my FAVORITES.
So I can review it once more : When for sure I will not be able to remind details of all new tips for me, that I've just learned with it ... :=)
Thanks a lot

[@AccessAnalytic]

Thanks Eric, I appreciate you taking the time to leave a comment.

[@alexwaterton9769]

An excellent presentation and demonstration. Many thanks. One surprise though is using the arrows at the left hand side to view a long list of worksheets?? Have you tried right mouse clicking on the arrows to display a list of Worksheet names and then double clicking the one you wish to go to?

[@AccessAnalytic]

Thanks Alex, we sure do use that Right Click sheet navigation option. Thanks for calling that one out

[@inuyatsha2018]

Very helpful

[@AccessAnalytic]

Thank you

[@kahfibonboy]

this is gold, never seen such a vast excel trick in 1 video like this. wish i know this earlier. and why the views is so little?

[@AccessAnalytic]

Thank you for the kind words Achmad... share with others! 😁

[@romeocostillas]

This is great tutorial Wyn.. you're still one of the best Excel MVP's I've known.. keep sharing.. God bless always

[@AccessAnalytic]

Thanks Romeo

[@surnameh7227]

Massive fan of Excel and still found some nuggets in there. Wondering how someone with that accent ends up in Perth?! Thanks Wyn!

[@AccessAnalytic]

Glad you liked it 😀 🏴󠁧󠁢󠁷󠁬󠁳󠁿🇦🇺

[@Junckook24]

It is useful

[@Acheiropoietos]

Double clicked paint format works across entire 365 suite. Nobody has ever told you this; until today.

[@prawtism]

Was taught this in school in 2008 :D

[@martinlaw8298]

49:08 Actually you can refer to a table in data validation list by using =indirect("Table_name")

[@AccessAnalytic]

You can but you shouldn’t ( 😀 ), it’s then something else that can break if tables are renamed.

[@RanjithPankajakshan]

Hi Mr Wyn.. Great tutorial. Learned a lot of shortcuts.. I have a query.. its regarding fills and format section. When you try to fill a date with right click how does the series option show?

[@AccessAnalytic]

There's a subtle drag down drag up. The easier option is to go to the Home ribbon in the top right corner there is Fill -> Fill Series

[@prashantvijay9466]

instead use ctrl+Alt+v for paste value

[@AccessAnalytic]

Yep Ctrl Alt v v enter

[@brianxyz]

The "5 M" tip is something I've never seen used before. Is that documented?

[@AccessAnalytic]

Don’t know - one of those things I saw someone do somewhere sometime ago 😃

[@dandeman1148]

Hi Bit Off Topic. But for Sharepoint Files, How did you stop users from Opening the File in Desktop App, did you edit the link in some way

[@AccessAnalytic]

Which minute of the video was that at Dan?

[@mehranbarahouei2346]

which version of Excel do you use?

[@AccessAnalytic]

365 ( these days I’m on the current channel ) I was on semi annual for that video

[@pgplanas]

Win: Double click locks the paintbrush formatter
Me: I have wasted my whole life

[@AccessAnalytic]

😆😆

[@kahfibonboy]

12:01 you even put a joke in a serious video

[@AccessAnalytic]

😀

[@mazharhussain8871]

I knew 95% of it and that I knew in a year. Hence your caption of 20 years is misleading.

[@AccessAnalytic]

You’re special. I’m not sure what your point is. Anyone who’s watched this video has learned this in one hour ? The title is referring to how long it took me to learn ALL these tips.