I recently presented at ModelOff’s Global Training Camp in Toronto. One of the many great things about the training camp was that I had the pleasure of meeting Excel guru Liam Bastick.
Liam Bastick
Liam is a Fellow Chartered Accountant and Fellow Chartered Management Accountant, specialising in financial modelling and strategic advice.
Liam has worked in the UK, the rest of Europe and Australia and writes monthly articles for the Chartered Institute of Management Accountants (CIMA). He is also an experienced facilitator with the Institute of Chartered Accountants in Australia (ICAA) and a regular presenter with the Certified Practising Accountants (CPA) accounting body.
This description from Liam’s Linkedin profile gives you a good idea of his accounting and finance knowledge/experience. What it doesn’t tell you is that Liam is an Excel guru and has a great sense of humor! Liam jumped around the room tossing out Excel tricks and jokes keeping everyone on their toes for 90 minutes! It reminded me of Bill Jelen’s session from almost exactly one year earlier. Liam’s session was 2016/09/25 and Bill’s was 2015/09/24.
Liam’s Modeloff Session
Here are my highlights from Liam’s Excel Tips & Tricks session.
Quick Trick: If you want to extract every 3rd item from a data-set I would immediately consider using a offset or index. Liam’s quick trick is:
- Type an apostrophe ‘, equal sign =, and 1st cell reference. It should look like this–> ‘=B5
- Repeat above but skipping down 3 cells. ‘=B8
- Select these two cells and drag down as far as required
- Highlight this selection, use ‘Text to Columns’ feature (click Next & Finish)
This creates formula references to each 3rd value (or adjust to 4th, 9th, whatever!)
Defending OFFSET’s honor! Because the OFFSET function is volatile some say we should never use it. WHAT?!?! I love OFFSET because it has so many incredible uses! Liam mentioned that adhering to a strict rule and never using OFFSET is going to far. Liam said, “It’s a balancing act” adding that it takes practice to know when to use it and when not to. Liam showed the class several great examples of how incredible OFFSET function can be.
SUMPRODUCT Function Liam also showed us several awesome examples of what Excel’s Sumproduct function can do. You can tell that this is his favorite function due to the name of his website/company: www.sumproduct.com
MOD Function Liam showed us how handy MOD can be when dragging sets of formulas to the right in a financial model. MOD function is always good to keep in your back pocket because you never know when it will come in handy!
Liam’s Website
The Modeloff training camp ended Friday night and I took Saturday as a data recovery day. However, on Sunday I explored Liam’s website especially several sumproduct formula related posts. This post is a gem especially if you’re unaware of the awesome potential of SUMPRODUCT.
Explore Liam’s other Excel files here ! Your career will thank you!
Training & Consulting
Liam and his team offer various training sessions back in Australia along with consulting services.
Thanks for coming to Toronto Liam!
About Me
My name is Kevin Lehrbass. This is my personal blog about Microsoft Excel. I live in Markham, Ontario, Canada. I’ve been studying, supporting, building, troubleshooting, teaching and dreaming in Excel since 2001. I’m a Data Analyst.
Thousands of hours can be saved and costly errors can be avoided or corrected if you study this powerful software. Check out my videos and blog posts.
Away from Excel I enjoy playing with my dog Cali, learning Spanish, playing Chess and drawing nerds.