Excelguru.ca - Powered by vBulletin
  • Register
  • Help

  • Home
  • Products & Services
    • Live Training
    • Video Courses
    • Private Courses
    • Consulting & Mentoring
    • Training Calendar
    • Books
    • Affiliate Sales Program
    • About Us
    • Today's Posts
    • FAQ
    • Calendar
    • Community
      • Member List
    • Forum Actions
      • Mark Forums Read
    • Quick Links
      • View Site Leaders
  • Blog
  • Forum
    • Excelguru Forums
    • What's New?
    • Today's Posts
    • Forum Guide
    • Forum FAQ
    • Supported BB Code Tags
  • Resources
    • Online Training
    • Recommended Books
    • Add-ins & Tools
    • Excel Blogs
    • Useful Links
  • Knowledge Base
  • Contact Us
  • Advanced Search
  • Home
  • Home
  • Knowledge Base Articles

  1. If this is your first visit, then welcome! Be sure to check out the FAQ by clicking the link above.
    Register here so that you can post in the forums or comment on the articles.

  • Knowledge Base Articles By Category

    Access - ADO (4)
    Access - SQL (4)
    Access - VBA Programming (10)
    Downloads - Games (1)
    Downloads - Learning Aids (11)
    Downloads - Utilities (9)
    Excel - ADO (4)
    Excel - Charts and Data Visualization (1)
    Excel - Formulas (8)
    Excel - General Tips (36)
    Excel - Interactive Pages (9)
    Excel - PivotTables (12)
    Excel - Power Query (3)
    Excel - PowerPivot (7)
    Excel - SQL (4)
    Excel - UDF (7)
    Excel - User Interface Customization (2)
    Excel - VBA Programming (49)
    Excel - WebApp (4)
    Internet Explorer - VBA Programming (1)
    Novell Groupwise - VBA Programming (2)
    Other - General (1)
    Outlook - General Tips (5)
    Outlook - VBA Programming (7)
    PDF Creator - VBA Programming (11)
    Power BI (3)
    PowerPoint - VBA Programming (7)
    Publisher - VBA Programming (7)
    Syndicated Articles (7)
    Training Products (10)
    Word - VBA Programming (8)
  • Excel - Formulas

    VLOOKUP for Pictures 

    by
    Ken Puls
    • View Profile
    • View Forum Posts
    • View Blog Entries
    • Visit Homepage
    • View Articles
    Published on 2013-03-14 06:47 AM     Number of Views: 147799 
    Article Preview

    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 icon for a weather forecast; something we use on our dashboards from our golf course. We update the weather data daily via a weather feed, and really don’t want to have to manually update ...
    Read More Read More 18 Comments

    Understanding How Conditional Formatting Rules Are Applied 

    by
    Ken Puls
    • View Profile
    • View Forum Posts
    • View Blog Entries
    • Visit Homepage
    • View Articles
    Published on 2013-01-22 07:30 AM     Number of Views: 35538 
    Article Preview

    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 Read More 4 Comments

    Highlight Subtotals for Easy Reading 

    by
    Ken Puls
    • View Profile
    • View Forum Posts
    • View Blog Entries
    • Visit Homepage
    • View Articles
    Published on 2012-05-17 06:48 AM     Number of Views: 19194 
    Article Preview

    One of the things that always struck me as odd about using subtotals is that only the words in the subtotals turn bold, and not the actual subtotals themselves. With a long list of data this can make it hard to see which numbers are the subtotals amongst the data. Fortunately this is very easy to fix using conditional formatting.
    ...
    Read More Read More

    Approximate Matches With VLOOKUP 

    by
    Ken Puls
    • View Profile
    • View Forum Posts
    • View Blog Entries
    • Visit Homepage
    • View Articles
    Published on 2012-03-22 07:00 AM     Number of Views: 27868 
    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.
    ...
    Read More Read More

    Always Refer to the Cell Above 

    by
    Ken Puls
    • View Profile
    • View Forum Posts
    • View Blog Entries
    • Visit Homepage
    • View Articles
    Published on 2008-05-05 10:10 AM     Number of Views: 27234 

    Introduction
    How many times have you built a table of data, and put totals on it. You ship it out the door, and another user comes along and inserts a new row right before the totals row? If you have seen this in play, you'll know that the new row is actually inserted between the end of the data range and the totals row, meaning that your sum formula no longer picks up the entire range of data!

    Fortunately, the issue with missing rows in totals can easily be fixed so that a user can insert a row immediately above your totals without missing the last line(s). ...
    Read More Read More 3 Comments

    Understanding Dates in Excel 

    by
    Ken Puls
    • View Profile
    • View Forum Posts
    • View Blog Entries
    • Visit Homepage
    • View Articles
    Published on 2008-03-30 08:44 AM     Number of Views: 18618 

    What are dates?
    This may seem like a strange thing to ask but, as far as Excel is concerned, dates are numbers. By storing them as such, it gives us the ability to add or subtract days to/from a date, as well as get the difference between two dates. If dates were stored as text, this would not be possible. Storing dates as numbers also allows us to construct far more complicated formulas, based on results that we may want to know.
    ...
    Read More Read More 1 Comment

    Formula To Average Letters 

    by
    Ken Puls
    • View Profile
    • View Forum Posts
    • View Blog Entries
    • Visit Homepage
    • View Articles
    Published on 2006-01-17 10:53 AM     Number of Views: 25857 

    Formula Purpose:
    • This function takes a series of letters and returns the average of them, i.e., the average of A, B, C is B.
    ...
    Read More Read More 1 Comment

    Five Very Useful Functions For Working With Text 

    by
    Ken Puls
    • View Profile
    • View Forum Posts
    • View Blog Entries
    • Visit Homepage
    • View Articles
    Published on 2006-01-10 09:08 AM     Number of Views: 32108 

    This page is dedicated to explaining how to use what I believe are the five most valuable formulas for working with text in Excel. They are useful on their own many times, but can become immensely powerful when nested (combined) with other formulas later as well. Mastering these five formulas will open up the door to many things that you may have never thought possible. All of these formulas can be used by putting actual text in the "text" area, but their true power is unlocked when using them on cell references as the data can then be dynamic.
    ...
    Read More Read More 2 Comments
  • MVP Logo
  • Recent Forum Posts

    majid

    how to add additional date and other column PIVOT table?

    Hi all,
    i need your help to understand this how the attached file add to extra column in a pivot table. i received from some one but don't know...

    majid Today, 07:13 AM Go to last post
    alansidman

    Power Query to move 24 values

    Attach a sample workbook
    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need....

    alansidman Today, 03:15 AM Go to last post
    p45cal

    Macro for filtering/copying/and pasting to new tabs

    try:

    Code:
    Public Sub sing_off_formatting()
    'Creates tabs for sing off analysis macroeach of the desired reports
    Set PS = Sheets.Add(After:=Sheets(Sheets.Count))
    ...

    p45cal Today, 12:37 AM Go to last post
    p45cal

    VBA Auto Add Hyperlink based on Cell Values when Data is updated

    On each of the sheets concerned, unlock all the cells on the sheet, then lock the cells in the columns containing the formulae you want to protect....

    p45cal Yesterday, 11:53 PM Go to last post
    mielkew

    VBA Auto Add Hyperlink based on Cell Values when Data is updated

    Thank you so much, that sound perfect if we can protect it. However does the query will still be able to refresh? That's what I did earlier were I tried...

    mielkew Yesterday, 10:52 PM Go to last post
  • Contact Us
  • Excelguru Forums
  • Archive
  • Top
All times are GMT +1. The time now is 07:58 AM.
Powered by vBulletin® Version 4.2.5
Copyright © 2019 vBulletin Solutions Inc. All rights reserved.
Pre-Defined Posts provided by Post Templates v1.6.1pl1 (Free) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
Thread / Post Bookmarks provided by Thread / Post Bookmarking v1.2.0 (Free) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
Copyright © 2004 - by Excelguru Consulting Inc.
Gravatar by 1e2.it