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.
PivotTables are designed to help users make sense of large amounts of data by providing an easy way to build a summarized report. In addition, PivotTables can be rearranged easily, so that once you have some summary data in a PivotTable, you can look at the same information in many different ways with only a few mouse clicks (the name “PivotTable” is derived from the fact that the process of rearranging your data is known as “pivoting” your data).
To illustrate the core capability of PivotTables, let’s imagine you have sales records listed in a worksheet something like this.
(Click to enlarge)
To see a sales summary, all you have to do is to click anywhere inside this range of data, create a PivotTable, and specify how you would like your data summarized. For example, if you wanted to see Sales Amount organized by Product Category and SubCategory, it would look like this (UI is our Beta 1 build, in which the PivotTable UI is not final and in some areas not yet complete).
Without a PivotTable, summaries like these are typically built using formulas like SUBTOTAL, VLOOKUP etc. However, in cases where you want to build more complicated reports, or where you want to look at the data in numerous different ways, or where the data itself changes quite frequently (i.e. Categories and Subcategories show up or disappear frequently), PivotTables are a great tool.
I deliberately chose a small data set for the example above (and I demonstrated a fraction of what PivotTables can do), but as soon as you have even a few dozen rows of data that you want to summarize, PivotTables can deliver magical results (I love showing PivotTables to customers for the first time and watching their eyes light up). For this reason, the PivotTable feature has been a very popular tool for quite some time, at least with those people who have learned to use it. Unfortunately, there are a large number of users who are not yet aware of this feature, or have not figured out how to use it. While planning for Excel 12, we did a lot of customer research and we found a couple of things. First, we found that many users need the summary capabilities that PivotTables offer, but some currently consider PivotTables “too advanced” and don’t use them regularly. Second, we found that users that did use PivotTables regularly had lots of requests to make them more powerful and more capable. Finally, we found that customers that had adopted SQL Server Analysis Services as their business intelligence platform wanted great support for Analysis Services in Excel.
Essentially, it became clear that there was still a lot of additional capability that we could add in this area that would benefit all sorts of users, so we set out to improve the feature in a number of ways. Specifically, we set out to:
These goals translated in to the following work:
This is a pretty big, exciting area of work for our team. Over the next two weeks or so, I am going to review all this in detail.
Comments: (16) Collapse
Preservation of formatting was a feature of PivotTables since Excel 97. What exactly changed?
I also am wondering how the existing PivotTable "banded report" autoformats interact with the new PivotTable styles that you have described.
"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
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.
Do pivot charts also get format persistence?
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 Banana 200
Candy Lollipop
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.
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
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).
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
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 blogs.msdn.com/.../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 blogs.gotdotnet.com/.../480599.aspx would it be possible to have Conditional Formatting for Pivot Table based on underlying data?
Jean
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.
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 www.dicks-blog.com/.../pivot-table-parameters
The Year[-1] is a great improvement and will be usefull.
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
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.
Comments: (loading) Collapse