You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
A few months back I wrote several posts about the improvements that we made to PivotTables in Excel 2007. I even showed how summarizing data in different ways is now just a right click away on the “Summarize Data By...” menu. What I didn’t show was that in addition to these standard ways of summarizing data (sum, count, max, min, average, etc.), Excel already supports some more complex summary views for items contained in a PivotTable. For instance, by selecting “More Options,” you can choose to show the data as a “Running total” or “% of Total.”
So, given the data below in a PivotTable ...
(Click to enlarge)
...you can choose to “Summarize Data By... > More Options” and then show the values as the “% of total”....
.... and Excel will automatically perform the calculation for you in the PivotTable.
We’ve gotten a few pieces of feedback about this functionality, usually that (a) it’s useful but hard to find, or (b) it would be more useful if there were additional options for how the data could be shown in addition to the ones listed in the dialog above.
We have someone on the team starting to look at this as part of our planning for future releases (the version after 2007), so we’d like to hear what you think. Here are some specific questions we would love to hear your answers to, but feel free to add additional commentary too.
Please post your answers using comments, as it is easier for everyone on the Excel team to read comments (as opposed to the email link which sends email just to me).
Thanks for your help.
As a reporting specialist I use the pivot functionality a lot. With a little personal training I've shown Excel users the power of pivot tables and they are amazed at what they can do. Something we've 'discovered' is that Excel is a very good reporting tool, it's just that people are poorly -- if ever -- trained to exploit this powerful tool! Business people grow up with Excel ;-)
"% of Parent" would be a nice-to-have option. I would like to have the option to place two or more data items together in a row or column instead of the additional items becoming subgroups. Example using columns -
This is what's normal:
............[data series 1]
............[data series 2]
[row series 1]....xxx
This is what I would like:
............[data series 1][data series 2]
[row series 1]...xxx.............xxx
Funky, I know, but I've run into instances where it'd just make my life easier if I could "join" two or more fields into one data field. Such an option would be best if it was NOT a default but required the user to specify the action.
ACN: You can drag the 'Data' button to the column area to show the data items side-by-side.
The three things I would like to see are:
And the ability to have totals and grand totals on only some data fields. E.g. If I have a table with price and revenue by product, I want to show totals for the revenue but not the price. Or if I have Local-currency-revenue and Reporting-currency-revenue by currency and product, I want to be able to total them both for the currency sub-totals but only the Reporting-currency-revenue for the grand totals.
In the past I've added another column to show "% of total". I like to see the totals as well as the %.
Our users have not been able to find this functionality in general when accessing SQL 2K5 OLAP cubes. The only way to get at these options AFAIK is through the "Field Settings" dialog box. Even at that, you have to switch tabs to see "Show Values As" options. This is one area that is a step back from OWC, IMO.
In a cube context I think the OWC options & interface was quite good - context menu for "Show As", options for:
Percent of Row Total
Percent of Column Total
Percent of Parent Row Item
Percent of Parent Column Item
Percent of Grand Total
When pivoting on non-OLAP data I also think Distinct Count is high-priority / glaring omission.
Yes, I have used this functionality from time to time and it's so well hidden, every time I use it I keeping expecting that bells will go off and I'll be awarded a prize from Microsoft for actually finding and using it. Even if you find it, it's not at all obvious how to use it. For example, the user shouldn't have to drag multiple duplicate data fields into the table.
I often need to compare 2 columns of data (usually 2 years) with a difference column. I use:
Show data as: Difference from
Base field: Year
Base item: (previous)
This works but annoyingly always includes a blank column (because there is no prior year 2 to compare to) and unnecessary Total columns. I hide all these manually.
I would LOVE it if there were a built-in easy way to simply change from the usual:
Column A | Column B | Grand Total (A+B)
Column A | Column B | Difference (A-B or B-A)
without jumping through so many hoops.
I may be misunderstanding what you want, but I think the solution to putting your 2 data series in columns instead of rows is to simply drag the Data column header over to the right into the column area.
would be nice to have the option to summarize using a VBA / XLL udf function. Or is that already possible and I missed that feature?
3. I do not "commonly" use pivot table because I am spoiled by a 3rd party "Excel friendly OLAP" product. That said, it offers great value as a poor man's OLAP. Of course the most common application is: quick and simple cross tabulation.
4. Jeff's enumeration of options for Percent-of-WHAT is right on point, although the average user might find the term "subtotal" or "group" more intuitive than "parent." I also vote for Count Distinct in the summary options. How about Median?
On a related, broader topic: The team needs to think hard about discoverability and intuitiveness. It drives me crazy that so many intelligent people will not undertake to learn to use pivot table at all, but it is not entirely their fault. In that regard, here is one small point for the features under discussion: "show values as" really does not convey much without further exploration to discover this is about relationships between data.
I would like to see enhancements to thepivot cache tools currently in place.
-Ability perform *accurate* weighted averages (aka SumProduct) within the GrandTotal and Subtotals.
-The ability to group items without having to include them in the pivot table first.
-The ability to return text values from a calculated field.
(i.e. If('Revenue'>1000,"Over", "Under").
- The abilility to create Calculated Items in a pivot table where averages and grouped items are being used.
I'm sure someone will tell me this is possible...but here goes
I'd like to be able to do more totalling in columns:
eg at the moment it is:
Group1 Group2 Grand Total
Data1 Data2 Data1 Data2
What I'd like is
Group1 Group2 Total Grand
Data1 Data2 Data1 Data2 Data1 Data2
Does that make sense?
I would love to see
a) % of Actual total
i.e If I chose to filter data for a field
The % of total readjust according to the filtered result.
What I would like is a % of the Total without the filter
A : 20%
B : 30%
C : 40%
D : 10%
Tota : 100%
Lets say I remove c and d
I would like A and B to continue showing 20% and 30% w.r.t Actual total
In addition to some useful functionality already mentioned, I'd like to see the following PivotTable enhancements:
1) A calculated field feature that works reliably under all conditions without calculation errors. Why bother having the feature otherwise? In most instances, the calculated field could be added to the source data but there are circumstances where it'd be useful to add a calculated field to the PivotTable e.g. some instances involving complex subqueries or where the end user would not have access to the source data.
2) A more useful multiple range consolidation feature. After you specify the multiple ranges (or Tables) for consolidation, the PivotTable field list would simply contain the column headings from the ranges or Tables. The actual PivotTable will be summarizing the data across the multiple ranges or Tables based on the fields included in the PivotTable.
3) An option to have one set of common headings when elements in a hierarchy have similar titles (easily done in Crystal Reports) e.g.
Part Number Description Quantity
1000-3900 xxxxxxxxx 1
2001-3700 xxxxxxxxx 2
3001-4200 xxxxxxxxx 4
3001-4300 xxxxxxxxx 4
2001-3800 xxxxxxxxx 2
2001-4700 xxxxxxxxx 4
3001-4500 xxxxxxxxx 8
The data for the above report comes from an external database. The information can be brought into Excel in multiple tables or can come from multiple database table views. To get to the lowest part level requires two self-joins on a table. Each resultset has the same field names and includes a field with the associated parent part number.
4) Last n Months/Years date filter (also for ranges/Tables)
5) Formula based filter (also for ranges/Tables)
6) Ability to apply number formatting to multiple data fields simultaneously
My mistake with terminology :-( Instead of using "[data series x]" I should've written "[column x]" or "[field x]" -- please substitute accordingly. The three little x's in my example would be considered the aggregate Data values and they are of no concern. Let's try this again. Current way:
[row series 1]___xxx__
[row series 2]___xxx__
What I would like to do:
______________[Field 1][Field 2]
[row series 1]___xxx______xxx___
[row series 2]___xxx______xxx___ ...
Thanks to Excel MVP Stephen Bullen for helping me see my error :-)
First, not to get too far off track, but I need to express my thanks.
Thanks to the Excel team past and present for their work on pivot tables. It's largely because of their work on the mysterious and, to some, intimidating nature of the tool that I have my current Reporting Analyst position. I, thankfully, took the time to learn and understand what other Excel users ran from.
I've been using pivots since '97. I've led training sessions at our office with dozens of fellow staff so that they can make good use of the tool too. Skill with pivots has earned me a fair bit of $$, so, sincerely, "Thanks!".
In response questions:
1 - Yes.
2 - N/A
3 - Almost alway one of % of row, % of column, or % of total. Usually used for budget or sales analysis.
4 - % of parent would be exceedingly useful. Would've loved to have had that at times before instead of jumping through hoops. But making it easy takes all the fun out of it, right?
Maybe this feature exists, and I can not find it. What my users want is a similar column grouping/composite function that Excel OLAP cubes provide, e.g. A is B + C + D, and E is F + G. In essence it lets you do structured drill down on the columns.
This should be possible n-levels down, e.g. need to nest this functionality.
Should be able to assign different colors to A and E than to the other columns.
We would like to expand and collapse A and E, and when A is expanded, A, B, C, D should show.
When all columns show and you select across one row to see a sum,totals should not show double (e.g. select A, B, C, D, E -> sum is A + E.
It would be fine with me if this could only be achived programmatically, since we generate the pivot tables programmatically anyways...