• Welcome to the Excelguru Knowledge Base

    by
    Ken Puls
    Published on 2011-08-26 07:22 AM      Number of Views: 2041319  

    Welcome to the new and improved Excelguru Knowledgebase!

    My favourite definition of the word Guru is "Remover of darkness"; A guide. A teacher. It is in this spirit that the ExcelGuru website was created: to help remove the mysteries of Excel, help you find solutions, help you to be more proficient or accomplish certain tasks.

    I am a firm believers 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. Did you know that the only thing that can't be automated in Excel is the input of raw data... and that even that can sometimes be done? If you perform repetitive tasks in your Excel spreadsheets, you may be wasting both your time and business resources performing unnecessary work. Let us show you how to save both your time and money by helping you automate your tasks today!

    The Excelguru Knowledgebase is packed with numerous articles on how to accomplish specific tasks in Microsoft Excel, Microsoft Access and other programs included in the Microsoft Office suite. It also includes examples on a select few non-Microsoft Office programs, and many general tips as well.

    If you notice errors in any of the articles, or have suggestions for improvements, please feel free to leave a comment on the article's page. And if you need any help modifying the articles to suit your needs, then please, post in my free help forums!

  • Recent Knowledge Base Articles

    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

    Categories:

    Excel - General Tips  Excel - Charts and Data Visualization 
    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

    Categories:

    Excel - Formulas  Excel - General Tips 
    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

    Categories:

    Excel - Formulas  Excel - General Tips 
    Ken Puls

    Using HASONEVALUE in a DAX IF statement

    As an accountant, I build financial reports, and one of the issues that we have to deal with is getting the numbers to display in a friendly format. Because of the way that debits and credits are stored in databases though, this can be a little challenging.

    In this article, I’m going to walk through the process of building a simple profit and loss statement with PowerPivot, showing how to make all values show correctly. There are some certain key issues that we’ve got to work through though, and we’ll do that using a conditional DAX measure.
    ... read more
    Ken Puls 2012-12-17, 03:50 AM

    Categories:

    Excel - PowerPivot  Excel - PivotTables 
  • MVP Logo

  • Recent Forum Posts

    emartz

    Additional Questions

    This looks awesome, however I realized I need to be more specific with how exactly the data is being pulled.

    The container # is pulled into...

    emartz Yesterday, 09:45 PM Go to last post
    Hercules1946

    IF AND Statement with ISBLANK and conditional

    Hi
    Sorry - I messed up trying to change the sheet from .xlsm to .xlsx. This new attachment should be OK....

    Hercules1946 Yesterday, 05:56 PM Go to last post
    emartz

    Attachment does not open

    Hi There-


    Can you re-send the attachment in a different format by any chance?
    When I tried opening the file it gave me the...

    emartz Yesterday, 05:17 PM Go to last post
    excelrookie

    Error Message?

    I get a message telling me 'Type Mismatch'. What am i doing or not doing right? As my user name would suggest i am a rookie at VBA and the more complex...

    excelrookie Yesterday, 02:48 PM Go to last post
    Hercules1946

    Cross Sheet References within cells

    Hello
    How is the data in Sheet 2 formatted? If the district codes are a string of characters in one cell adjacent to the County, then a VLOOKUP...

    Hercules1946 2013-05-18, 09:31 PM Go to last post
Gravatar by 1e2.it