MS Excel Tips & Tricks – a Pro must know by Mr. Bob Umlas
25*Microsoft MVP, Excel Trainer, and Consultant
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!
Mr. Umlas was voted an “MVP” (Most Valuable Professional) by Microsoft each year from 1993-2018 (25 years!) for his contributions to the various online Forums about Excel and is known worldwide for his contributions in Excel. He has been a beta tester for new versions of Excel since version 1.5 and was asked by Microsoft for his input for newer versions of Excel.
Bob Umlas worked for a major tax and accounting firm, using Microsoft Excel® 8 hours a day, writing custom applications for staff and clients from 1998 to 2018.
He has been using Excel since version 0.99 (on the Macintosh)! He was a contributing editor to Inside Microsoft Excel for many years, a magazine devoted exclusively to Microsoft Excel and published by The Cobb Group and later Ziff-Davis. He has had more than 300 articles published on subjects ranging from beginner to advanced macros, and on tips, shortcuts, and general techniques using virtually all aspects of Excel.
He is also the author of “This isn’t Excel, it’s Magic!”, “Excel Outside the Box”, “Excel Preschool: Finally Understanding what Excel is all About”.
Mr. Umlas used to co-lead the New York PC User’s group on Excel every month for about 10 years. He started leading this group again in 2018. He has been teaching Excel to individuals and corporations for several years.
Currently, Mr. Umlas leads a 12-hour class in Excel called Excel in Depth and a 6-hour class on VBA.