• Who is the "Excel Guru"?

    ExcelGuru.ca is run by Ken Puls, CMA. I'm a Certified Management Accountant, who never remembers life without spreadsheets and computers.

    Since mid 1999, my career has been spent in the hospitality and tourism industry, in the finance and IT field. Starting as the Accounting Supervisor and Systems Administrator at Fairwinds Community & Resort on Vancouver Island, BC, I was promoted to Controller/Director of IT in September 2007. To this day, I dedicate a significant portion of my time to developing automated solutions and internal controls for the golf course, 400 berth marina, and two food and beverage outlets that make up the resort, as well as focusing time and energy on improving the Business Intelligence systems that run our real estate development. During this time, I've found that my true passion is designing and developing new systems, using such technology as Excel spreadsheets, databases built with Microsoft Access, and hand-held scanner technologies for taking inventory.

    When I first started at the resort in 1999, the Microsoft Office systems in the resort were all upgraded to Office 97, and my users panicked as the "macros" that the resort used all stopped working. I passed the problem to the head office's IT department head in Toronto, and had to explain what they did, step by step. As it turned out, the resort's users weren't using macros at all, but rather had memorized the lotus keystrokes to copy and paste instead of using a mouse! In July of 1999, the IT department sent me a file that actually did use a macro, built by a summer student.

    As happens with summer students, this one went back to school, and then it became necessary to change something. Being the only systems oriented individual in the company who knew Excel at all, (even the head office didn't have anyone at that point,) I got nominated to fix the macro. After a bit of experimentation, I managed to fix it, and then basically just updated the file whenever it needed it.

    In early 2001, I began recording simple macros of my own, mainly just to copy closing balances to opening balance columns and clear out data entry fields. This continued in until late 2002.

    In the fall of 2002, the resort's management company decided to cut labour costs in the administration department. Overall, 2.5 full time equivalent positions were cut out of a 7.5 person office (yes 33% of the labour used) within one month, with the department expected to produce the same amount and quality of work. (As an added complication, due to the management company being absorbed into another in early 2002, the Resort's IT department had become a one man show -- me!) During a desperate shuffle of tasks to other departments, and dropping of tasks that were low priority, I realized that automation was going to be the only answer.

    From there, I launched myself into VBA (Excel's programming language) full force. Automating as many tasks as possible, the administration office has been able to perform virtually the same task load as before the cuts, and the annual labour savings can be estimated somewhere between $20,000 and $40,000 per year. Suffice it to say, I love VBA, and am still amazed by the things it can do.

    I live my business life automating as many of my, my staff's, and my co-workers tasks, so that they can focus on what is truly important: Running the business. I am a firm believer that properly set up, a computer can process numbers and data far more efficiently and accurately than any human, and that human brainpower should be devoted to solving problems, not data input.

    I've been an active participant in many Web forums since 2002, and built this website, which provides code samples for working with Excel, other Microsoft Office applications, as well as some free utilities. (In fact, NASA has even made use of this site, using some of my PDFCreator examples to automate the production of PDF reports which monitor the power supply levels on the International Space Station!)

    This site also hosts my technology blog where I share much of my experimentation and observations of different technologies.

    In recognition of my contributions to the online community, I was awarded the prestigious Microsoft Most Valuable Professional – Excel award in October 2006; a distinction I holds to this day. The Microsoft MVP Award recognizes exceptional technical community leaders from around the world who voluntarily share their high quality, real world expertise with others. In Microsoft's words, "MVPs are a highly select group of experts representing technology's best and brightest who share a deep commitment to community and a willingness to help others." Worldwide, there are over 100 million participants in technical communities; of these participants, there are fewer than 4,000 active Microsoft MVPs. Of this pool of MVP's, less than 200 hold the distinction of MVP-Excel globally, and I am one of less than a dozen to do so in Canada.

    In addition to my other technical works, I am also co-author of RibbonX – Customizing the Office 2007 Ribbon, published by Wiley Publishing, Inc. This book is the definitive guide for all users who wish to put their own touch on Office 2007's new user interface, providing clear step by step instructions and examples of the code required to manipulate the RibbonX API.

    I've also worked as a freelance Microsoft Office developer, and completed many consulting projects, mainly in Excel, but also with Access, Word, and Outlook. While consulting offered many great learning opportunities and challenges, I have changed my focus to concentrate on my true passion – sharing my knowledge about Excel, and teaching people how to get the most from it.

    To this end, I opened up my own web forum and also lead Excel training sessions for those companies or groups interested in having a live instructor with courseware customized to their attendees.

    I take a lot of pride in filling this site with accurate and helpful content, and hope you find it useful for your needs. And if you can't find the exact answer to the question you have, don't hesitate to drop by the forum for help. It's completley free!

  • Recent Knowledge Base Articles

    Ken Puls

    Making Pivot Table Hyperlinks Clickable

    If you’ve ever built a PivotTable that contains hyperlinks, you’ll notice that clicking the hyperlinks doesn’t do anything. This can be a bit frustrating as the reason you put that field on the Pivot in the first place is that it’s valuable information you want to use. When you click the hyperlink,... read more
    Ken Puls 2013-10-31, 01:58 AM
    Ken Puls

    Temperature Forecast Chart

    Some of the really cool charts that we can build in Excel involve the trick of combining multiple chart types together to make them happen. In this article, we’ll build one of those; a temperature chart that not only shows the forecasted high and low temperatures, but also the season highs and lows. The beauty of this chart is that it provides a lot of information, some of which essentially fades into the background until you really need it.
    ... read more
    Ken Puls 2013-03-21, 05:04 AM
    Ken Puls

    VLOOKUP for Pictures

    Something that can be very handy when you’re building a dashboard is to return a certain picture depending on a condition. We can use VLOOKUP to look up data in a table and return the corresponding value from a different column, but unfortunately we can’t do that with pictures... or can we?

    This example shows how to accomplish the equivlanet of a picture VLOOKUP, and is based on looking up a picture to display the appropriate... read more
    Ken Puls 2013-03-14, 05:47 AM
    Ken Puls

    Understanding How Conditional Formatting Rules Are Applied

    Conditional formatting in Excel is a powerful tool that allows you to dynamically format cells depending on the values of that or other cells’ data. In Excel 2007 the conditional formatting engine was re-written, opening things up to allow more than 3 conditional formats on any cell, as well as conditional formats that could overlap ranges. All in all, these were fantastic improvements that can lead to some very versatile and useful worksheets.

    Unfortunately, the user interface to control conditional formatting is not the most intuitive. The purpose of this article is to help you understand the way Excel applies rule precedence so that you can build powerful formatting rules of your own, without getting frustrated along the way.
    ... read more
    Ken Puls 2013-01-22, 06:30 AM
  • MVP Logo
  • Recent Forum Posts


    Bid Spreadsheet

    A big thank you to all whom took the time out to work on this and assist me.

    Thanks again, I'm a very happy man.


    skydivermel Today, 02:29 PM Go to last post

    Count problem / logic

    Well done! ...

    AliGW Today, 02:09 PM Go to last post

    Count problem / logic

    Function solved


    stefan8871 Today, 01:58 PM Go to last post

    Count problem / logic

    Could I please ask for some more help?

    I'm trying to get this result on a different sheet. I've updated the document to reflect the change....

    stefan8871 Today, 01:52 PM Go to last post

    Count problem / logic

    Glad we have it sorted! It would have helped if you had broken your requirement down and told us your expected outcomes for each bit of it. ...

    AliGW Today, 01:16 PM Go to last post