PivotTables – overview of improvements in Excel 12

re: PivotTables – overview of improvements in Excel 12

Wesner Moise — Wed, 07 Dec 2005 05:02:52 GMT

Preservation of formatting was a feature of PivotTables since Excel 97. What exactly changed?

re: PivotTables – overview of improvements in Excel 12

Wesner Moise — Wed, 07 Dec 2005 05:04:34 GMT

I also am wondering how the existing PivotTable "banded report" autoformats interact with the new PivotTable styles that you have described.

re: PivotTables – overview of improvements in Excel 12

Sam — Wed, 07 Dec 2005 15:23:19 GMT

"Preservation of formatting was a feature of PivotTables since Excel 97. What exactly changed? "

I hope they included "preserving" column widths

I aslo hope the have included features
like

a) clear ghost items(old items)
b) More chart types in Pivot Chart
c) Add Auto Filter capability - Right now we need to use a Hack (select extra clolumn outside the pivot and apply filter)
d) Improve the formulas we can use in Calculated items and Calculated fields. The ones we can use are pretty basic.

Sam

re: PivotTables – overview of improvements in Excel 12

David Gainer — Wed, 07 Dec 2005 23:43:58 GMT

Hi folks

Wesner - In PivotTables specifically connected to OLAP data, if you format an item, then hide it by collapsing its parent and expand it again, the formatting will be gone. In Excel 12, the formatting will still be applied. We have also fixed some smaller issues around things like Custom Captions for all PivotTables.

Also, I will cover PivotTable styles in a future post, but the short answer is that the new PivotTable styles are a replacement for existing autoformats.

Sam, the "preserving" column widths is actually a property you can turn on and off in PivotTable options in current verions. We have made this clearer in Excel 12. With respect to ghost items, there is an object-model only way to turn ghost items on and off. That will be in the UI for Excel 12. No change to chart types in PivotCharts (though we have made improvements to PivotCharts I will discuss later). I do not understand your third point - could you perhaps provide an example. No change in Calulated items or fields.

re: PivotTables – overview of improvements in Excel 12

Helen — Thu, 08 Dec 2005 13:45:40 GMT

Do pivot charts also get format persistence?

re: PivotTables – overview of improvements in Excel 12

Johan Nordberg — Thu, 08 Dec 2005 13:48:07 GMT

Will it be possible to get row field labels on every row when you have two or more row fields?

Today the output is something like this:

Fruit Apple 100
Banana 200
Candy Chocolate 300
Lollipop 400

I would like to get the output like this:

Fruit Apple 100
Fruit Banana 200
Candy Chocolate 300
Candy Lollipop

re: PivotTables – overview of improvements in Excel 12

David Everard — Thu, 08 Dec 2005 14:23:59 GMT

PivotTables do not seem to be able to handle data with two header rows (a header row and a units row). My data is often formatted this way.

When the PivotTable is created, the units (second row in each column of the source data) appear as an item in the row and column fields of the PivotTable with no associated data in the datafield. I've had to write some VBA to uncheck units and stop them being included in the row and column fields of the finished PivotTable. Would be nice to see a checkbox in the PivotTable wizard to specify whether your data has units or not, and if so just have the PivotTable creation process ignore the second row of data automatically so it doesn't get included in the PivotTable.

re: PivotTables – overview of improvements in Excel 12

Stephen McLaren — Thu, 08 Dec 2005 15:35:19 GMT

Hallelujah!

Thank you David for switching your server for your pictures.

I can now view them without them being filtered by Web(non)sense...

I look forward to reading more of your blog and seeing the pictures of the nice new office 12

re: PivotTables – overview of improvements in Excel 12

Joe Dowski — Thu, 08 Dec 2005 20:53:14 GMT

I second Johan's question:

"...Thursday, December 08, 2005 5:48 AM by Johan Nordberg
Will it be possible to get row field labels on every row when you have two or more row fields?...."

Using Johan's example - On large reports end users want to be able to see Row labels for each line of data, not just once at the beginning of the group and/or once at the top of a page, (for groups that span multiple pages).

re: PivotTables – overview of improvements in Excel 12

XL-Dennis — Fri, 09 Dec 2005 01:06:55 GMT

David,

I'm really looking forward to see the news with the P/T, especially in view of what we can do with the P/T-control in the OWC-package.

Kind regards,
Dennis

re: PivotTables – overview of improvements in Excel 12

Jean Martineau — Fri, 09 Dec 2005 03:34:57 GMT

Great looking stuff. I can’t wait to read more on your future pivot table posts. Here is a list of items/suggestions I have accumulated for pivot table:

1) Will MultiRangePivot be possible? MultiRangePivot ’Enables Pivot Tables to collect Data from Multiple Sheets, but with full functionality of the 'standard' Pivot without the restrictions of using Multiple Consolidation Ranges.’
2) Will pivot table parameters be working when we use external reference?
3) Will we be able to have Calculated Item based on the previous column field item? See my previous post at http://blogs.msdn.com/excel/archive/2005/10/04/477226.aspx
4) Will we be able to use other functions, like median, than the regular summary functions?
5) Could it be possible to enable a real time window to view underlying data when we select cells inside a pivot table? Without replacing the double-click approach witch popup in a separate sheet that I typically delete afterwards, this new approach would be more efficient to view the underlying data.
6) Finally, as I mentioned in this previous post http://blogs.gotdotnet.com/excel/archive/2005/10/13/480599.aspx would it be possible to have Conditional Formatting for Pivot Table based on underlying data?

Jean

re: PivotTables – overview of improvements in Excel 12

David Gainer — Fri, 09 Dec 2005 10:03:38 GMT

Howdy folks,

Helen – yes, we hope to improve PivotChart format persistence.

Johan – we did not get to this feature this time out, although we do display the information in tooltips.

David, Joe – no new capabilities to handle your sceanario.
Stephen – have you talked to your IT folks? The other site isn’t a gaming site …

Jean – No change to multi range PivotTables. Could you clarify your question about parameters? No new capabilities on calculated items, however, you can have calculated items defined by previous items of the same field … for example, =IF(Year[-1]=0,Year[-2],2*Year[-1]-Year[-2]). No new summary functions, and drill-through is still a new sheet. Assuming I understand the question, no to conditional formatting on underlying data - the conditional formatting is evaluating the summarized values in the PivotTable itself. We have done some neat work on conditional formatting in PivotTables which I will cover later.

re: PivotTables – overview of improvements in Excel 12

Jean Martineau — Sat, 10 Dec 2005 03:46:34 GMT

Thank's David for your feedback.

|2) Will pivot table parameters be working when
|we use external reference?
When Pivot tables are based on external data, we can't use parameters inside Microsoft Query. For more information see http://www.dicks-blog.com/archives/2005/04/20/pivot-table-parameters/

The Year[-1] is a great improvement and will be usefull.

Jean

re: PivotTables – overview of improvements in Excel 12

Jean Martineau — Sat, 10 Dec 2005 06:33:33 GMT

To be sure I understand YEAR[-1] correctly, here is a complete example. Let say I have this data (I added . to align the data in this post):

PRODUCT YEAR TYPE....... QTY.
produ_A 2005 consumption 1000
produ_A 2005 reception.. 4000
produ_A 2007 consumption 1000
produ_B 2006 reception.. 6000
produ_B 2007 consumption 1000

If I define this calculated item:
EndInventory = YEAR[-1]-consumption+reception
could I expect to have this PivotTable result?

Filter rows..... 2005 2006 2007
produ_1
... reception... 4000 .... ....
... consumption. 1000 .... 1000
... EndInventory 3000 3000 2000
produ_2
... reception... .... 6000 ....
... consumption. .... .... 1000
... EndInventory ...0 6000 5000

Jean

re: PivotTables – overview of improvements in Excel 12

David Gainer — Mon, 12 Dec 2005 21:37:24 GMT

Hi Jean - no change in parameters unfortunately. With respect to your other question, the formula for a calculated items can only refer to items of the same field as the calculated item itself so you cannot do “EndInventory = YEAR[-1]-consumption+reception” since the Year items and the Type items are not in the same field.

However, you can add the following three calculated items to the Year field (in this order):
End Inventory 2005 = Year[-3]
End Inventory 2006 = Year[-4]+Year[-3]
End Inventory 2007 = Year[-5]+Year[-4] +Year[-3]

And this calculated item to the Product field:
Reception - Consumption = Reception-Consumption

If you want an example, use the link to send me an email, and I can send you a workbook.

Excel 2007 charting UX design

Alex Barnett blog — Fri, 14 Jul 2006 13:31:58 GMT

Sander Viegers is a user experience (UX) designer in the Office Design Group who contributed to Excel...

Excel 2007 charting UX design

Alex Barnett blog — Fri, 14 Jul 2006 13:36:24 GMT

Sander Viegers is a user experience (UX) designer in the Office Design Group who contributed to Excel...

Excel 2007 charting UX design

Alex Barnett blog — Fri, 14 Jul 2006 13:36:59 GMT

Sander Viegers is a user experience (UX) designer in the Office Design Group who contributed to Excel...

PivotTables: Calculated Items

Microsoft Excel — Fri, 09 May 2008 08:17:37 GMT

Today's author: David Gainer, a Program Manager on the Excel team. PivotTables are designed to help users

PivotTables: Calculated Items, Microsoft Office developer blog

PivotTables: Calculated Items, Microsoft Office developer blog — Mon, 14 Jul 2008 22:23:27 GMT

PingBack from http://ms-office.yourbloggingpro.com/2008/07/pivottables-calculated-items/