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.

  2. Unregistered, Nice to see you again, please help us to help you by clicking this link Edit My Profile and add your current version of Excel.

    There's a quick explanation here How-to-show-us-what-Excel-version-you-are-using

    Kind regards,
    Admin
  • Knowledge Base Articles By Category

    Access - ADO (4)
    Access - SQL (4)
    Access - VBA Programming (10)
    Downloads - Games (1)
    Downloads - Learning Aids (11)
    Downloads - Utilities (7)
    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 (2)
    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 (2)
    PowerPoint - VBA Programming (7)
    Publisher - VBA Programming (7)
    Syndicated Articles (7)
    Training Products (10)
    Word - VBA Programming (8)
  • Excel - PowerPivot

    Making Pivot Table Hyperlinks Clickable 

    by
    Ken Puls
    • View Profile
    • View Forum Posts
    • View Blog Entries
    • Visit Homepage
    • View Articles
    Published on 2013-10-31 01:58 AM     Number of Views: 49239 
    Article Preview

    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 Read More 1 Comment

    Using HASONEVALUE in a DAX IF statement 

    by
    Ken Puls
    • View Profile
    • View Forum Posts
    • View Blog Entries
    • Visit Homepage
    • View Articles
    Published on 2012-12-17 04:50 AM     Number of Views: 24608 
    Article Preview

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

    Hide Calculated Items With Zero Totals In PowerPivot PivotTables 

    by
    Ken Puls
    • View Profile
    • View Forum Posts
    • View Blog Entries
    • Visit Homepage
    • View Articles
    Published on 2012-12-13 06:02 AM     Number of Views: 32820 
    Article Preview

    The method for hiding items with zero totals in a PivotTable is different if you're working with a regular PivotTable or a PowerPivot PivotTable. This article focusses on how to accomplish this goal in the PowerPivot version. (If you're working with a regular and you want to hide calculated items that have zero balances, you'll want to check out Debra Dalgleish's blog post on the subject.)

    To start, assume that we’ve got a fairly simple PowerPivot pivot table that looks like this: ...
    Read More Read More

    Creating a Spacer Column in a PowerPivot PivotTable 

    by
    Ken Puls
    • View Profile
    • View Forum Posts
    • View Blog Entries
    • Visit Homepage
    • View Articles
    Published on 2012-12-13 05:50 AM     Number of Views: 9590 
    Article Preview

    Mike Alexander has a great blog post on how to Add Column Spacing In A PivotTable. We can also accomplish the same thing through a PowerPivot solution, but using DAX. And in this case, DAX is even easier to use that the old method, taking one less step!
    ...
    Read More Read More

    Sorting A Column Of PowerPivot Data By Another Column 

    by
    Ken Puls
    • View Profile
    • View Forum Posts
    • View Blog Entries
    • Visit Homepage
    • View Articles
    Published on 2012-03-28 06:16 AM     Number of Views: 37629 
    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.
    ...
    Read More Read More 1 Comment

    Displaying “Last Updated” Date And Time In PowerPivot 

    by
    Ken Puls
    • View Profile
    • View Forum Posts
    • View Blog Entries
    • Visit Homepage
    • View Articles
    Published on 2012-03-28 05:49 AM     Number of Views: 25823 
    Article Preview

    We use PowerPivot to display key information in dashboards, some of which can be refreshed right up to the current second. Naturally, one of the first questions asked when looking at the reports (particularly if they get printed) is “When was the data last updated?”
    ...
    Read More Read More

    Write-back Using PowerPivot 

    by
    Ken Puls
    • View Profile
    • View Forum Posts
    • View Blog Entries
    • Visit Homepage
    • View Articles
    Published on 2011-03-10 08:43 PM     Number of Views: 8538 
    Article Preview

    In a discussion about PowerPivot yesterday, one of my friends stated that it wasn’t really useful since you couldn’t perform write-back using PowerPivot. To him this is a very important piece in the Excel budgeting process. Now, I agree that PowerPivot doesn’t give you write-back to a database, but this got me thinking; we have linked tables, so why couldn’t we create a write-back loop for a model that was built entirely in Excel? Well, we can!
    ...
    Read More Read More
  • MVP Logo
  • Recent Forum Posts

    alex20850

    Is there way to report or manage the number of .Add lines in a Scripting.Dictionary?

    In this function, I was able to have 3,000 .Add lines, but I don't know
    how to determine the limit beforehand or if there is a way to increase...

    alex20850 Today, 03:05 AM Go to last post
    NoS

    Copy & Paste based on multiple criteria

    for the dates, just use like this without the formatting stuff.
    Criteria1:=">=" & OKSheet.Range("C2"), Operator:=xlAnd,...

    NoS Today, 12:32 AM Go to last post
    Hercules1946

    First day of each week in a year excluding holidays

    Let me see if I understand...
    If we are talking 2018, then the first working day is Tuesday, 2nd January (assuming that New Years Day is a holiday)....

    Hercules1946 Yesterday, 10:43 PM Go to last post
    mossdrive

    First day of each week in a year excluding holidays

    I am looking for some help on creating a formula that will return the first day of each week in a quarter based on the current year and eliminating holidays....

    mossdrive Yesterday, 09:06 PM Go to last post
    Rahul Malhotra

    Copy & Paste based on multiple criteria

    Hi I am trying to copy and paste the value in the Analysis tab based on Analysis tab multiple criteria at Cell C2, D2, C3, D3, C4, D4, C5 & D5. I...

    Rahul Malhotra Yesterday, 05:42 PM Go to last post
  • Contact Us
  • Excelguru Forums
  • Archive
  • Top
All times are GMT +1. The time now is 07:50 AM.
Powered by vBulletin® Version 4.2.5
Copyright © 2018 vBulletin Solutions Inc. All rights reserved.
Thread / Post Bookmarking by Thread / Post Bookmarking v1.1.0 - vBulletin Mods & Addons Copyright © 2018 DragonByte Technologies Ltd. Runs best on HiVelocity Hosting.
Copyright © 2004-current by Excelguru Consulting Inc.
Gravatar by 1e2.it