Microsoft Excel

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:
[Page Field]
............
............[data series 1]
............[data series 2]
[row series 1]....xxx
[row series 1]....xxx

This is what I would like:
[Page Field]
............
............[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:
 Distinct Count
 Distinct Count
 Distinct Count

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.

Thanks!

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)
to:
Column A | Column B | Difference (A-B or B-A)

without jumping through so many hoops.

To Air_Cooled_Nut:

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?

1. Yes.
2. n/a

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

eg :
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:
[Page Field]
______________[Field 1]
______________[Field 2]
[row series 1]___xxx__
[row series 1]___xxx__
[row series 2]___xxx__
[row series 2]___xxx__

What I would like to do:
[Page Field]
______________
______________[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...

Thanks!!!
axel

1. All the time and I would like to use it more
2. I am frequently asked to orient others (primarily executives) to the use of pivot tables for creating crosstabulations of data.  I'd love to have % of column available at the top menu level, and I'd love even more to have the "group" option available easily.
3. I use pivot tables to crosstabulate survey data and database extracts.  Excel is my preferred tool; I use SPSS when I need to, but I find pivot tables much easier and quicker to use and they format for presentation more quickly than SPSS tables.  Someone called pivot tables a poor man's OLAP, but I find it more flexible and useful than any other OLAP product I've found.
4. In addition to adding a "group" and "ungroup" option more accessible in the menu system (I create custom buttons on my toolbar for them), I'd like the option to show counts in the totals only--in other words to show % of column throughout the table, then show counts on the "stubs" of the "banner" to use old stats reporting language.  If I could have a suite of statistical tests that I could choose on each table, well, wow.

There's a fair amount of interest in using excel for reporting in higher ed administration.  I almost went to a conference last week *just* to see a paper on how to report on survey results in excel only (as opposed to moving it into SPSS).  

Thanks!

On the occasions that I have checked out whether this feature can do what I want, the answer is allways no. The reason for this is that what I have wanted was a function of a parent or group. So the suggestion to include this would be welcomed.

Encountered two issues when creating a Pivot Table, firstly the table did not automatically show a row or column field caption, I had to recheck the "display field captions and filter drop downs" buttons in the Display page of the PivotTable pop up.

Secondly, when using Classic PivotTable Layout the "Number Format" in "Field Settings" will not display red font for a negative number in currency format although it will display a negative sign. It can show a negative number without a negative sign and in black font therefore indicating a positive number.

Just some further clarification on the second point made above, this problem only occurs in totals or grand totals thus values in a column field can be a mix of red and black fonts but the total will only be black.

Thanks Excel team for taking the user input on summarization options. I use all existing pivottable functions heavily and echo a previous comment about the powerful tool.

What I'd like to see in addition (off the top my head now more will come):

1. Running total percent. You can do running total now but not in percent format

2. Custom "Index" calculation. There is one Index option but apparently based on a very unique industry defition which rendered the option of little use.

3. Calculated fields: more summary option than the only default sum available

4. Calculated fields: it will be great to have two levels of the calculated fields: raw data level (currently in place) and summarized level. A raw data level always calculate based on the most granular level data but a summarized level calculated fields always perform based on the data elements already summarized in pivottable. For example, you can create a sales commission based on sales and a percent constant as a raw level calculated field; but often you need to caculate a profitability on a group (say Southern region on the row header field) based on multiple aggregated fields (sales, margin, total expense, capital cost, etc) by the row header.

5. Item grouping: would like to see more options such as by 1-100% increments of a base field count. I often need to group the data in 5% or 10% groups based on number of records. Today I can only do it mannually. Also, make multiple groupings co-exist in one pivottable would be extremly helpful. For example, for scores less than 100, I need to group them by 5-pt interval; 100-500 by 20-pt; then 500+ by 10-pt; etc.

Thanks in advance.

Functions would be useful - if you need the percentage difference between the cell in columns B and C you cannot calculate in in the query.

Also, how about the following ideas:

When the user double clicks on a cell it opens another worksheet. It would be *very* useful to have a way of doing this so that they disappear automatically when the workbook is closed, or the sheet looses focus.

Lock design. Once you have a design layout that works, a checkbox in the properties which locked it from accidental design changes.

Ian Murphy

I would love the ability to show ranking within pivot tables so...

         2005   2005Rank  2004  2004Rank
apples     20       1       15      2
oranges    10       2       20      1
grapes      5       3       10      3

My wish-list items are:

(A) The full worksheet function set for use in the calculated fields.  Ideally even custom user defined functions if it's remotely feasible.

(B) Show data as: Rank
I presume it would have to include a few options like ascending or descending.  Also, rank among all, or within a given subset.

(C) Count distinct

Thanks for listening!
Fred Barwell

In addition to some useful functionality already mentioned, I'd like to see the following 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. For instance, after you specify the multiple ranges (or Tables) for consolidation, the PivotTable field list should 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 (something 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 might be from an external database. The information can be brought into Excel in multiple tables. In the above example, to get to the lowest part level requires a query involving two self-joins on a table. Each resultset (i.e. individual level data) has the same field names and includes a field with the associated parent part number. Perhaps the above can be a multiple range consolidation layout option. 4) Last n Months/Years date filter (Same for ranges/Tables) – Useful for filtering or pre-filtering out all but the last n months or n years from the imported data. 5) Formula based filter (Same for ranges/Tables) 6) Ability to apply number formatting to multiple data fields simultaneously 7) A table layout option to allow for the generation of treemaps, such as that provided by the Excel Treemapper add-in supplied by Microsoft.