• Excel - PivotTables

    by Published on 2013-10-31 01:58 AM     Number of Views: 42053 
    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, ...
    by Published on 2012-12-17 04:50 AM     Number of Views: 20531 
    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.
    ...
    by Published on 2012-12-13 06:02 AM     Number of Views: 28653 
    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: ...
    by Published on 2012-12-13 05:50 AM     Number of Views: 8530 
    Article Preview

    Mike Alexander has a great blog post on how to Add Column Spacing In A PivotTable. We can also accomplis 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!
    ...
    by Published on 2012-05-02 07:45 AM     Number of Views: 58248 
    Article Preview

    So you've built a really cool PivotTable, and you hooked up a slicer to allow exploration of the data. And now you want to do something really cool, but you need to make your formula react to the slicer value. Can you do it? Of course you can, but how?

    This article will focus on the technique to do exactly that: return the value of a slicer to a formula. Note that, in order to follow along you will need Excel 2010 or higher, as Slicers didn't exist prior to this version.
    ...
    by Published on 2012-04-24 08:10 AM     Number of Views: 35082 

    Add to Cart

    Excel PivotTables: Itís a polarizing term. People who use PivotTables absolutely love them. For those who donít, the term is mysterious and encourages the fear of powerful features that are the domain of geeks and Excel junkies, and out of reach to the common man. But nothing could be further from the truth.

    Why You Should Take This Course:

    If youíve never created, or donít regularly use PivotTables in your work, let me show you that you are missing out on one of the most useful, impressive and easy-to-use tools in Microsoft Excel.

    Excel PivotTables are an amazingly powerful feature that can be used to very quickly summarize and slice and dice data with ease. And contrary to many usersí fears, they are actually VERY easy to use once youíve been shown how.

    So why donít you let me do just that? In this one hour video training course, I will teach you how to build your first PivotTables. Youíll see first hand just how easy they are to create, how fast they work, and how easy it is to change them to display your data the way you want to see it.
    ...
    by Published on 2012-04-24 08:07 AM     Number of Views: 14412 

    Add to Cart

    Excel PivotTables: Itís a polarizing term. People who use PivotTables absolutely love them. For those who donít, the term is mysterious and encourages the fear of powerful features that are the domain of geeks and Excel junkies, and out of reach to the common man. But nothing could be further from the truth.

    Why You Should Take This Course:

    If youíve never created, or donít regularly use PivotTables in your work, let me show you that you are missing out on one of the most useful, impressive and easy-to-use tools in Microsoft Excel.

    Excel PivotTables are an amazingly powerful feature that can be used to very quickly summarize and slice and dice data with ease. And contrary to many usersí fears, they are actually VERY easy to use once youíve been shown how.

    So why donít you let me do just that? In this one hour video training course, I will teach you how to build your first PivotTables. Youíll see first hand just how easy they are to create, how fast they work, and how easy it is to change them to display your data the way you want to see it.
    ...
    by Published on 2012-04-24 08:03 AM     Number of Views: 13185 

    Add to Cart

    Excel PivotTables: Itís a polarizing term. People who use PivotTables absolutely love them. For those who donít, the term is mysterious and encourages the fear of powerful features that are the domain of geeks and Excel junkies, and out of reach to the common man. But nothing could be further from the truth.

    Why You Should Take This Course:

    If youíve never created, or donít regularly use PivotTables in your work, let me show you that you are missing out on one of the most useful, impressive and easy-to-use tools in Microsoft Excel.

    Excel PivotTables are an amazingly powerful feature that can be used to very quickly summarize and slice and dice data with ease. And contrary to many usersí fears, they are actually VERY easy to use once youíve been shown how.

    So why donít you let me do just that? In this one hour video training course, I will teach you how to build your first PivotTables. Youíll see first hand just how easy they are to create, how fast they work, and how easy it is to change them to display your data the way you want to see it.
    ...
    by Published on 2012-03-28 06:16 AM     Number of Views: 32995 
    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 2011-04-06 07:11 PM     Number of Views: 5515 
    Article Preview

    Over the past while weíve been building a Dashboard report for our golf course. Itís got some historical information in it, but weíve also pulled in things like weather forecasts. The intention is that our managers will be able to see where weíve been over the past week, as well as look at the key measures that will allow us to staff appropriately for the next week.

    We put out a prototype of the Dashboard report, and our Director of Golf said ďThis is cool. Whatís the chance we could also have the events coming up over the next week listed?Ē
    ...
    by Published on 2011-03-10 08:43 PM     Number of Views: 7591 
    Article Preview

    In a discussion about PowerPivot yesterday, one of my friends stated that it wasnít really useful since you couldnít perform writeback 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!
    ...
    by Published on 2011-03-08 09:09 PM     Number of Views: 12851 

    Iím a huge consumer of Data Validation in Excel. At this point, however, Excel services (the Excel webApp) is still so new that there are very few of the techniques that we normally use in Excel which are web compliant. This article looks at ways that we can implement data validation into an Excel services solution.
    ...