50 Ultimate Excel Tips and Tricks for 2020

[Uncle Gizmo]

50 Ultimate Excel Tips and Tricks for 2020

Contents
1. Move Data 0:35
2. XLOOKUP() 1:04
3. Filter List 2:24
4. Ideas 3:04
5. Remove Blanks 3:54
6. Quick Analysis Tool 4:28
7. AutoFit Column Width 5:27
8. Absolute Cell Reference 5:57
9. Paste Special Values 7:19
10. Drop-Down List 8:05
11. FILTER() 9:15
12. Remove Duplicates 10:43
13. Difference Between Lists 11:31
14. Flash Fill 12:15
15. AutoFill 13:06
16. Data Types 13:53
17. Transpose 15:11
18. Freeze Panes 15:37
19. Text to Columns 16:27
20. Recommended Pivot table 17:25
21. Slicers 18:04
22. Conditional Formatting 19:01
23. IF() 20:29
24. 3D References 21:12
25. Forecast Sheet 22:45
26. SUMIFS() 24:00
27. IFERROR() 25:15
28. Ctrl-Arrow Keys 26:03
29. Filled Maps 26:26
30. PMT() 27:13
31. Show Formulas 28:20
32. Advanced Select 29:10
33. Named Range Shortcut 29:56
34. Hide Cells 30:47
35. COUNTBLANK() 31:30
36. Natural Language Query 31:59
37. Goal Seek 33:08
38. Insert Screenshot 34:30
39. Power Pivot 35:14
40. 3D Maps 36:16
41. ISBLANK() 39:08
42. Analysis ToolPak 39:45
43. CONVERT() 40:48
44. Get Data from Web 41:24
45. People Graph 42:59
46. SORT() 44:18
47. Status Bar Info 45:36
48. Insert Multiple Rows 46:22
49. CHOOSE() 46:53
50. UNIQUE() 48:50

 

[strive4peace]

nicely done! And good he took extra time to put clickable links in the description. First one I saw to do that was ExcelisFun (Mike Girvin, who teaches Excel at a college and has numerous workbook files you can download to try yourself)

This video covers a lot of what I consider to be basics, which is nice because many don't know them! ... even though they might have used Excel a long time. I saw this in the day classes I taught, a lot! People would skip the beginner class but then go back and catch it!

All of this is very good! Although I only commented on some of what he mentioned ...

selecting blanks is also nice to copy values from above.
1. select column
2. select blanks
3. type =A2 in first cell (or whatever is the cell above from where you are) in the formula bar. Then press Ctrl-Enter to copy that formula to all selected cells
When I use Text to columns, the biggest issue for me isn't blank cells, but separating what should be in one cell at spaces

on toggling between relative, absolute, and mixed referencing ... the way I remember F4 is that $ is on the 4 key, and $ is what is used to fix references

on Paste Special, Values -- you can just paste them over the selected range with formulas instead of putting the results in another range. That way, you don't have an extra column. To document the formula, you can copy it and attach it to the first cell in a note

=Filter() was new for me

It would be nice if Remove Duplicates would let you give it some logic ... like keep the one with the highest amount -- but then maybe it can if you sort in descending order by that first -- something to try ~

I like Duplicate or Unique between 2 lists. That might not be new, but I didn't know it!

Ctrl-E for Flash fill is nice to know... how can I remember that? hmmm... Expression!

Autofill is another one nice to know -- and you can set up your own lists!

Data Types ... Microsoft is using Wolfram Alpha to get this information. I plan to explore this more ... but wish they'd picked a different name for it! Its really data object information. Problem though when something in the list isn't recognized. Nice feature though!

Freeze Panes -- almost always do this! (Access can do this on datasheets too!)

On Text to Columns ... once you do this, Excel remembers that you want stuff delimited! which is not always good. To stop this, choose Text to Columns and click the Cancel button

Only 19 minutes in (on slicers -- Excel trying to be like Access), but need to switch to other stuff now. I've marked this video to come back to later.
I've been using Excel since the late 80s and love it ... and love Access even more I like to watch other good teachers even though I may know a lot of what they show because I like to see how they teach it! And often, I pick up something I didn't know too

Great stuff, Uncle Gizmo, thanks for sharing!

 

[Uncle Gizmo]

And good he took extra time to put clickable links in the description.

I do that with some of my videos, it's quite easy because all you do is type in the time index in the YouTube Description and Google automatically creates a link for you! What you may not know is you can copy those time index comments from a YouTube Description, along with the link, and paste them straight into WordPress, and they work fine.. Example HERE:-

VBA Beginner – Nifty Access

www.niftyaccess.com

The YouTube created Clickable links also work within this Forum as you can see, where I copied the links from the YouTube comment section in to the above post....

 

[strive4peace]

I do that with some of my videos, it's quite easy because all you do is type in the time index in the YouTube Description and Google automatically creates a link for you! What you may not know is you can copy those time index comments from a YouTube Description, along with the link, and paste them straight into WordPress, and they work fine.. Example HERE:-

VBA Beginner – Nifty Access

www.niftyaccess.com

The YouTube created Clickable links also work within this Forum as you can see, where I copied the links from the YouTube comment section in to the above post....

thanks, Uncle Gizmo. I wrote a program to time-code videos for captions and added a feature to mark lines to generate time-code links for the description too, but I usually don't take the time to do it. I should though.

Nice video! Good to hear your voice too And I've marked your channel to see what else you have On YouTube, I'm LearnAccessByCrystal

 

[Uncle Gizmo]

I wrote a program to time-code videos for captions and added a feature to mark lines to generate time-code links for the description too,

I have a copy of it somewhere!

I've been a fan of yours for years... Your videos and style is impressive, something I aspire to.

 

[strive4peace]

I have a copy of it somewhere!
I've been a fan of yours for years... Your videos and style is impressive, something I aspire to.

oh thank you! that is so kind to say, Uncle Gizmo! My caption tool is better now. I use it every time I make a video, and occasionally add more stuff. One of these days I'll plan to weed it out and post an updated version!

Another thing I keep meaning to add to it, but haven't yet, is to read a caption file in (that was auto-generated) so it can be broken in better places instead of any ole place. I wouldn't need that since I write a script, but for those who use a tool to auto-generate captions might. One of my pet peeves is captions that don't start sentences with a new caption, or use natural breaks like commas to split lines.

 

[Uncle Gizmo]

. I wouldn't need that since I write a script

I use Screen-O-Matic to create my YouTube videos Screen-O-Matic is a fantastic system.

Home

ScreenPal’s intuitive tools help you capture, create, and share videos and images for authentic and effective visual communication.

screencast-o-matic.com

The free version is fully featured, although there is a 15min restriction on the length of the videos, but the paid for version is so cheap it's pointless having the free version.

I mention it because it has a caption feature where you can add the text and it automatically puts the text in the video for you and you just read it as you go.

I don't use the caption method as I prefer a "shoot from the hip" method of presentation, I know it lacks polish, but I enjoy it more, if you know what I mean.

 

[Uncle Gizmo]

it has a caption feature

Link to Caption feature:-

4 ways to create accessible video captions on ScreenPal

Accessible video content plays a huge role in allowing all students an equal opportunity to understand and engage with classroom topics.

screencast-o-matic.com

Edit:-

Actually I made a mistake, I think it's the:-

4. Scripted Recordings

feature that would be of interest to you.

 

[strive4peace]

thanks, Uncle Gizmo. I've made a note of Screen-O-Matic to pass onto others. I record audio and video separately then edit audio with Goldwave before synchronizing the video to it, using Camtasia, where I also add callouts. A short video usually takes me a couple days to make and produce!

Sometimes I do live presentations and even if I write a script (so I can know how long it will take), I rarely follow it well! Usually though, I just make an outline. For those times, it would be helpful to have a transcription so I can make a caption file for the recording. I also have Dragon Naturally Speaking (for transcribing) but haven't learned how to use it yet.

 

[Uncle Gizmo]

A short video usually takes me a couple days to make and produce!

Yes, they take an awful long time to do!