• Ken Puls

    by Published on 2012-03-27 06:16 AM     Number of Views: 53349 
    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: 36133 
    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: 36640 

    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: 13413 

    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: 18204 

    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: 90428 

    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: 10657 

    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: 40069 

    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: 148667 

    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: 71784 
    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: 187434 

    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: 106518 

    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-22 06:44 AM  Number of Views: 334690 
    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: 8907 
    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: 8286 
    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: 11776955 

    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


    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: 32028 

    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: 13263 

    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: 16108 

    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