• Ken Puls

    by Published on 2012-03-27 06:16 AM     Number of Views: 46574 
    Article Preview

    One of the things that used to drive me crazy about working with PivotTables in PowerPivot’s initial (2008) release was summarizing dates by month. With a standard PivotTable, we can use the built in Group functionality to group dates by Years, Quarters and Months. But in PowerPivot, that functionality wasn't implemented. To deal with this, we have to provide our own date table, but the months never really sorted well, and we had to resort to tricks to coerce them into the right order.
    ...
    by Published on 2012-03-22 06:00 AM     Number of Views: 31407 
    Article Preview

    The purpose of the VLOOKUP function is simple: it looks up data in tables and returns results from a different column. So if you have a table of products, for example, you could ask VLOOKUP to return the price for an item given the ID of the product.

    But VLOOKUP is more than just that; it is the gateway to real Excel knowledge. The VLOOKUP function contains everything that a function can throw at you: multiple required parameters, optional parameters with defaults, and needs both ranges and numeric data in its input strings. If you can master this function, you can master ANY other function in Excel.
    ...
    by Published on 2012-01-22 04:52 AM     Number of Views: 30165 

    Over several years of participating in forums, and working on my own projects, I always felt it was a bit awkward to create and send new emails through Excel. Invariably, every time I found that I needed email code, I ended up heading off to a site to copy an example (usually from my colleague Ron de Bruin's excellent site), then customizing to make it work.

    The goal of this article is to provide an even easier way to add email functionality to your Excel (or any other Office) project... something easy enough for beginner coders to use as effectively as master coders. I wanted a re-usuable chunk that I could just drop into my project with ease, and I believe I've accomplished that here.
    ...
    by Published on 2011-12-06 04:36 AM     Number of Views: 10744 

    If you'd like to prevent anyone from printing your workbook, this code will do the trick (subject to the caveat below).
    ...
    by Published on 2011-11-11 06:56 AM     Number of Views: 15245 

    My staff and spreadsheet users will tell you that any time I build a spreadsheet, there are always shaded cells on the grid. I preach that "Green means go", and make sure that any cell they enter data in has a green background. I also use blue backgrounds for "update these sometimes" cells, like tax rates. If cells are left with no colouring, though: everyone here knows that they should be left alone.

    Often, I have a few data entry cells or blocks on multiple worksheets. While this follows good spreadsheet design principles, it does have a bit of a side effect: When you print the worksheets, the green backgrounds print as well. While they're great for telling the user where to input data manually, it's distracting when you are looking at a printed (presentation version) of your data or report. So the question you may be asking is what can we do about it?

    We could create a full new report that essentially duplicates what we've got, but then it's both a maintenance headache and performance hit. Instead, we can use a conditional format ...
    by Published on 2011-11-04 04:04 AM     Number of Views: 46589 

    Reference Sites
    • Anne Troy's Office Articles
      • Anne has over 230 articles and tutorials available for the MS Office suite! Well worth the read!

    • Andy Pope's "AJP Excel Information"
      • Andy's site is very heavy on Charts, with lots of great pictures and examples. He's also got a collection of Excel based games as well.

    • Bob Phillips xlDynamic
      • Bob's site contains very comprehensive papers on Excel formulas and VBA. My personal favourite is his explanation of Multiple Condition tests using Sumproduct.

    • Chip Pearson's Excel Information
      • Chip has an incredible amount of information on his site, including using the Windows API to get at things not accessible through VBA alone.

    • Colo's Junk Room
      • Colo has all kinds of neat things at his site, including a Class Module tutorial, and tips for teaching VBA programmers how to program in javascript. (This site went offline back in 2011, but the link above accesses the most recent copy via the Wayback Machine.)

    • Deskbright
    ...
    by Published on 2011-11-03 07:37 AM     Number of Views: 10310 

    The following are some of the forums and usergroups that I've been know to frequent:
    ...
    by Published on 2011-11-02 06:31 AM     Number of Views: 22930 

    I am quite grateful for all the great feedback I've received from my many years in the community working on projects and training. Below are a few excerpts/quotes from some of my particular favourites.

    NASA - International Space Station Power Systems

    • The team has created a tool that takes their analysis info and puts it all into excel. This has allowed them to chart their information, making
    ...
    by Published on 2011-11-01 07:40 AM     Number of Views: 74311 

    Excel Blogs
    • The Ken Puls Blog
      • Okay, it's a shameless plug, but this is my own blog, which serves as my testing ground for Excel related material, among other things.




    • Bacon Bits
      • Mike Alexander's Excel Blog. Excel during the week and bacon recipes on the weekend




    • Contextures
      • Debra Dalgleish's Blog focussing on Office and productivity






    ...
    by Published on 2011-10-24 06:17 AM     Number of Views: 40417 
    The only thing that can't be automated in Excel is the input of raw data... and even that can sometimes be done. If you are looking to avoid repetitive tasks in your Excel spreadsheet (clearing out data to start a new month, manually incrementing dates, copy your data to historical tables, etc...) you should know that it's more than just possible!
    by Published on 2011-10-24 03:21 AM     Number of Views: 80711 

    This page lists many of the books I recommend.

    In the interests of full disclosure here, each of these images contains an affiliate link, so I do make a (small) amount of money if you click through and purchase. If you find that offensive, you can always go directly to the site in question and purchase direct.

    RibbonX - Customizing the Office 2007 Ribbon

    Naturally, I get to plug this one first, since it's mine! This is the definitive guide for customizing the Ribbon in Office 2007 (and higher):
    by Published on 2011-10-17 04:12 AM     Number of Views: 62582 

    I try all of the add-ins I recommend, and won't recommend them unless I believe they are worthwhile (particularly if there is a charge!) Each of those listed below are helpful tools that I have no problem recommending.

    In the interests of full disclosure here, any images below contains an affiliate link, so I do make a (small) amount of money if you click through and purchase. If you find that offensive, you can always go directly to the site in question and purchase direct.

    Chandoo's Excel Templates
    Chandoo is a fellow Excel MVP with a passion for dashboarding and Excel. He has Excel Templates for sale, as well as runs an online Excel School which includes Dashboard Training.



    Peltier Tech Charting Utilities for Excel
    Jon Peltier ...
    by Published on 2011-08-26 05:14 AM     Number of Views: 121055 

    ExcelGuru.ca is run by Ken Puls, FCPA, FCMA. 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 (I was awarded in the Data Platform category in 2017). 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!

    by Published on 2011-08-22 06:44 AM  Number of Views: 153822 
    The tools within Microsoft Office are extremely powerful, and already exist on your systems... but do your staff know how to use them?

    The biggest issue facing corporate efficiency today is not the inability to afford powerful systems, but rather the lack of recognition of what you already have and how to leverage it. Does a knowledge gap exist in your staff's Office skill-set? Would you like to fix it?
    by Published on 2011-07-29 08:03 PM     Number of Views: 6315 
    Article Preview

    I was working with some budget stuff today and wanted to forecast that a range of accounts would be written off. I had a forecast that showed the projected transactions monthly, (all zeros as the projects have been discontinued,) and the projected year-end balance. So I basically wanted to take the projected year-end balances, flip them from positive to negative and stuff them in the July transaction column. Here’s how:
    ...
    by Published on 2011-07-15 08:35 PM     Number of Views: 5713 
    Article Preview

    If you’re running Outlook 2010 and Exchange 2010, you can set up your out of office replies any time during the day, and schedule them to take effect for a certain period. I wish I could do this with my phone… that way I could set it up when I think about it, and then forget about it. Currently I have to set a reminder to change my greeting just before I leave for the day… something that I’ve forgotten in the past due to the inevitable last minute chaos that always erupts just before you’re planning to go away for a week!
    ...
    by Published on 2011-07-11 07:22 AM     Number of Views: 9319903 

    What is Excelguru?

    Our favourite definition of the word Guru is, "Remover of darkness." A guide. A teacher. It is in this spirit that the Excelguru site was created by Ken Puls (also known as the Excel Guru). Our goal is to help remove the mysteries of Excel and Power BI, help you find solutions, and help you to be more proficient or accomplish certain tasks. Our passion lies in exploring tools to turn data into information, and teaching others how to benefit from them.

    The team at Excelguru is dedicated to helping you get the most from your data by leveraging tools you already own. We specialize in both live and video training, as well as mentoring and consulting services, in:

    • Excel
    • VBA
    • Charting & Dashboarding
    • Power Pivot
    • PivotTables
    • Power Query
    • Power BI


    EXCELGURU NEWSLETTER

    Keep up-to-date with Excelguru by signing up to receive our monthly(ish) Excelguru email newsletter. It features the latest updates for Excel and Power BI, upcoming training sessions, new products and other information.

    As a "thank you" for subscribing, we are excited to share our FREE eBook series, 'DIY BI' Tips, Tricks & Techniques. Don't miss out - sign up today!

    Subscribe to the Excelguru Newsletter

    * indicates required

    View previous campaigns.

    by Published on 2011-05-05 05:13 AM     Number of Views: 27090 

    Please Note: This text is the complete reproduction of Chapter 17 - Security if Microsoft Office from my book RibbonX: Customizing the Office 2007 Ribbon, and is reproduced with permission of Wiley Publishing who retains the copyright of this work.



    This chapter was obviously written for Excel 2007, and still holds true for Office 2010.

    In addition, this chapter also discusses macro security applicable to Office 97 through Office 2003. Key portions to read for these versions are Security Prior to Office 2007 and Digital Certificates. While some of the screens may look a little different, they should be close enough to help you understand how to set up a SelfCert digital certificate and add it to your projects. These steps will allow you to avoid macro warnings in your programming environment, without having to sacrifice security by setting your security levels to low. ...
    by Published on 2011-04-14 10:30 PM     Number of Views: 9741 

    In Introduction to the Excel WebApp, we learned how to create, upload and view files in Microsoft's Web Version of Excel, hosted on SkyDrive (a free offering from Windows Live.)

    It should be noted that thee main reason for creating a file in the Excel WebApp is to share it with others. So now that we’ve learned how to create and upload documents into our Windows Live SkyDrive account, let’s look at how to share them with our friends, co-workers and potentially anyone else in the world.

    Before we can share any documents using the Microsoft Excel WebApp though, we need to create a folder in SkyDrive for that purpose.

    Creating a Public Folder
    Log in to your Windows Live account, and navigate to your SkyDrive. Once you get there, create a new folder by clicking “New-->Folder”:

    In the screen that pops ...
    by Published on 2011-04-11 10:21 PM     Number of Views: 11542 

    In June 2010, Microsoft released the Excel WebApp as part of their Windows Live offerings. This is an interesting product as it allows you to store, read, edit and share your Excel document in the cloud. This means that it is no longer necessary to email your file around to all of your friends; you can have it stored in one place so that everyone is always working with the latest version of the spreadsheet. ...
    Page 2 of 6 FirstFirst 1 2 3 4 ... LastLast