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 - 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 02:58 AM     Number of Views: 58034 
    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 2 Comments

    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: 29734 
    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: 37232 
    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: 11918 
    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

    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 06:49 AM     Number of Views: 31712 
    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

    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-27 07:16 AM     Number of Views: 44764 
    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

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

    thousand

    Private sub to refresh pivotfields

    Is there a way to combine private sub with refreshing pivot fields?

    Want to keep PivotFields("ABC") to always be selected as "True"....

    thousand Today, 05:29 AM Go to last post
    p45cal

    Keep formatted shape to use in other workbooks

    Record yourself a macro of you adding the shape and making modifications to it. Make sure you store it in the Personal Macro Workbook, you can alter the...

    p45cal Today, 01:01 AM Go to last post
    p45cal

    Timestamp data with missing time

    Try the button in the attached which runs this code:

    Code:
    Sub blah()
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    For rw = lr To 3 Step -1
    ...

    p45cal Today, 12:10 AM Go to last post
    AliGW

    The Web Query Table data in excel not the same as displayed online at the website.

    I cannot see any difference. You are the one asking for help: all I have asked is that you provide a version with a few specific anomalies highlighted...

    AliGW Yesterday, 04:15 PM Go to last post
    guanstan

    The Web Query Table data in excel not the same as displayed online at the website.

    Just pick any one row in my 50 entries/page and compare to your attached file (same row) and you see they are not the same. This only happens in my 50...

    guanstan Yesterday, 03:29 PM Go to last post
  • Contact Us
  • Excelguru Forums
  • Archive
  • Top
All times are GMT +1. The time now is 09:11 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