Excel in Excel presents #EiEFreshTalk on “MS Excel Tips & Tricks – a Pro must know” by Mr. Bob Umlas
Download the Training Material: http://bit.ly/3aBkZoy
In this video:
00:00 – Getting Started
03:42 – Introduction to the Speaker
05:00 – Foreword by the Speaker
05:53 – Zooming tricks
07:13 – Page Setup demo
08:48 – Calculating Grand Total using SUMIF & SUMPRODUCT
11:47 – Task – Remove everything before the colon
15:20 – Amazing Formula
20:19 – FILTER Function
21:46 – Filter required columns using FILTER Function
23:02 – Nesting SORTBY and FILTER Function
23:50 – Reverse the Unreversed Texts
25:35 – How to Auto Sequence
28:40 – 3*3 grid for Non Repeating Integers
30:12 – Fill Series
32:22 – Get all Files in required directory
35:16 – Mirroring Concept
37:10 – Entering Totals for alternative columns
38:51 – Change Row height using GO TO Special
40:56 – Double Spacing
42:55 – Other Essential Shortcuts
47:43 – Merge Across
48:15 – Hide Row Numbers
49:44 – Insert message using Macro
53:20 – Customize Address Format
1:01:01 – Question and Answer
Key Takeaways:
1. If you want to reference cells A1, A2, A3, etc. but as a horizontal fill (that is, i in cell B1 you put =A1, then drag the fill handle to the right, you would get =B1, =C1, etc. but you want =A2, =A3, etc.) you can enter A1 in cell B1 (without the “=”). Then when you drag the fill handle to the right you will see A2, A3, etc. When done, replace A with =A, and you have it!
2. I’ll show how to copy a page setup from one sheet to another (or to all!)
3. If you have 5 workbooks open, and then do an Arrange All, I will explain the order that Excel puts the 5 windows in — it’s not random!
4. If you have 10000 rows of data, how can you insert a row between every row so that they’re all double-spaced— in under 10 seconds!
5. Tricks with the format painter you probably didn’t know!
About EiEFreshTalk:
#EiEFreshTalk is a Talk Show presented by Excel In Excel on its YouTube Channel (https://www.youtube.com/theexcelinexcel), where it will be hosting great leaders and subject matter experts (SMEs) from various fields to speak regularly on a variety of topics ranging from Excel to Analytics and Finance to Financial Modeling and many more.
These sessions are intended to offer the viewers the opportunity to listen to the esteemed speakers and interact with them to have their queries addressed.
Watch our past talks at https://www.youtube.com/playlist?list…
______________________
Register for Upcoming #EiEFreshTalk at https://excelinexcel.in/eieFreshTalk
Keep Learning on a go.
Join our Facebook Group @ https://www.facebook.com/groups/eiefreshtalk/
Join our WhatsApp @ https://www.excelinexcel.in/whatsappupdate
Join our Telegram @ https://www.t.me/ExcelinExcel
Visit: www.ExcelinExcel.in, an online resource center that seeks to equip users with knowledge about MS Excel with excellent content supported by examples, case studies, and short videos.
Please give this video a Like and subscribe to our channel to Learn more on MS Excel. In the comments section, please share your thoughts and let us know about things that you wish us to cover in our forthcoming videos.
Connect with us on:
Facebook at https://www.facebook.com/TheExcelInExcel
Twitter at https://twitter.com/theexcelinexcel
Instagram at https://www.instagram.com/theexcelinexcel
LinkedIn at https://www.linkedin.com/company/theexcelinexcel
#exceltips #excel #proinexcel
