• Excel - PivotTables

    by Published on 2013-10-31 01:58 AM     Number of Views: 41418 
    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: 20184 
    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: 28210 
    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: 8423 
    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: 57246 
    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: 34464 

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

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

    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: 32616 
    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: 5446 
    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: 7503 
    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: 12712 

    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.
    ...
  • MVP Logo
  • Recent Forum Posts

    ohhaykfrankk

    2010 index match

    My table is as such:
    Column B: Names
    Column E: Remainder
    Column K: Criteria is True or False.

    I need a formula that searches...

    ohhaykfrankk Today, 12:58 AM Go to last post
    Hercules1946

    Countif visible cells only

    Hello
    I don't think that COUNTIF itself will work, but it can be done using SUMPRODUCT, if that helps.
    See my attachment....

    Hercules1946 Yesterday, 11:48 PM Go to last post
    coachclint

    If statements

    thank you - the two formulas you provided are outside of my scope unless they are explained teacher to student
    as a caveat to this - how do you...

    coachclint Yesterday, 11:00 PM Go to last post
    NBVC

    If statements

    I was also going to suggest:

    =IF(G14="ns",-10,IF(ISBLANK(G14),0,LOOKUP(G15-$B14,{-9.99999E+307,4,6,8},{5,2,1,-5})))

    ...

    NBVC Yesterday, 09:33 PM Go to last post
    p45cal

    If statements

    What are all those AND(...)s doing in there?; they seem at first sight to be superfluous.
    You could also try the likes of:
    =IF(G14="ns",-10,IF(ISBLANK(G14),0,INDEX({5;2;1;-5},MATCH(G14-$B14-0.00001,{-9999;3;5;7}))))...

    p45cal Yesterday, 09:19 PM Go to last post