28 Feb 2016

After we get comfortable with something we create habits and then we often go into auto pilot mode. The danger is that we can fail to see things that are right in front of us! 

Korean Pagoda Garden

PagodaGardenOne day last spring I exited the subway and I was about to walk to the office where I work. Something caught my eye and I looked sharply to the right.

It’s a Korean Pagoda Garden located on Victoria University (University of Toronto). 

At first I thought “Oh, this must be new.” I was surprised to learn that it had been there for several years. Why hadn’t I noticed it before?

 

 

 

My Excel Pagoda Garden

In about 2002 I was working with some messy data using string manipulation functions to extract text. After finishing I explained the solution to my manager. Someone else stopped by and said “Why didn’t you just use Text To Columns?”. Ouch. I wasted time and I was embarrassed.

Text To Columns is such a powerful feature and it’s so underutilized. Watch this video even if you’ve used it before. Or, consider using  Flash Fill feature.

 

What Have I Discovered Recently?

Lookup Function  Years ago, I had dismissed this ancient Lookup function. It can only do approximate match. Why use it? Thanks to Mike Girvin at ExcelIsFun I have learned how powerful it can be given that it can handle array functions without control shift enter.

VBA Watch Window I heard about it years ago but I’ve never spent the time to learn it. In this quick video Paul Kelly shows us how to use it! If you really want to learn VBA then check out Paul’s VBA Handbook course

Match Array Formula I’ve been writing array formulas for 15 years and I’m always discovering new techniques. Here is one from Mikkel Sciegienny at spreadsheeto.com  MATCH(1,(B:B=J2)*(C:C=J3)*(A:A=J1)) I like this approach as many other array solutions would be much longer.

Aggregate Function  It’s versatile and amazing! It’s the duct tape function! Watch this video playlist from Mike Girvin. It’s worth the time investment!

 

What’s Your Pagoda Garden?

Leave a comment below including something you’ve recently discovered in Excel or something that you’ve known about and should learn. If you can’t think of anything then spend a few minutes exploring Excel and you will discover something!

Save

Save

Post a comment