• Excel - PivotTables

    by Published on 2013-10-31 01:58 AM     Number of Views: 40620 
    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: 19798 
    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: 27703 
    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: 8306 
    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: 56008 
    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: 33713 
    Article Preview

    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: 14017 
    Article Preview

    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: 12808 
    Article Preview

    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: 32182 
    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: 5362 
    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: 7414 
    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: 12616 

    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

    needexcelhelp

    Convert large dataset into 2 columns into multiple columns

    Hi. I have a large dataset from multiple experiments that are all in two columns, when in reality they should be split into 60 or so columns from 30 separate...

    needexcelhelp Yesterday, 11:20 PM Go to last post
    Hercules1946

    week num

    Excels weeknum function can result in a week 54 in some years! This is because:
    1. 1st January is always Week 1.
    2. But .... there is also...

    Hercules1946 Yesterday, 11:16 PM Go to last post
    myk2thestar23

    Highlighting Cells based on Date

    I need help trying to accomplish this to save me some manual work.

    I have an excel spreadsheet where I need to Highlight cells in Column...

    myk2thestar23 Yesterday, 11:10 PM Go to last post
    jonespandrew

    WEEKnUM AND PIVOT TABLES

    Hi Thanks

    However the forumula used in previous post is showing 31/12/17 as week 53 when it should be 52 how do i alter the formula to correct...

    jonespandrew Yesterday, 07:00 PM Go to last post
    jonespandrew

    week num

    Hi thanks for this, the only issue now is that it is shows 31/12/17 as week 53 when infact it is week 52, what do i need to do to alter this....

    jonespandrew Yesterday, 06:59 PM Go to last post