Windows 7 sale PivotTables VI – Sorting and filt

As I mentioned a few articles back, PivotTables enable users to summarize data in an easy-to-read report. Once a user has a summary of something they are interested in,Microsoft Office 2010 Home And Business, they often want to further explore their data by seeing it ordered differently,Windows 7 sale, by excluding certain records, by seeing the top 10 of something, etc. PivotTable’s sorting and filtering capabilities are great for this sort of work,Microsoft Office Ultimate 2007, and we have done a lot of work in this area in Excel 12,Purchase Windows 7, so I wanted to review the highlights. Today I’ll start by describing the improvements we’ve made to sorting in PivotTables and then I’ll move on the cover the new filtering capabilities that we’ve added.
Sorting in PivotTables
Probably the best way to explain some of the sort-related improvements we made in Excel 12 is to explain how sorting worked in Excel 2003 and earlier versions. Assume you had a PivotTable that looked like this – something that summarized sales of certain models of bikes (i.e. “Road-150 Red 48”) by year and product category …
(Click to enlarge)
… and you wanted to see sales in the Northwest region sorted in descending order. To do this, you could select a cell containing the sales value for a bike in 2001 in the Northwest region and press Sort Descending (Z->A) in the toolbar. Your PivotTable would now look something like this.
(Click to enlarge)
When you take a close look at the PivotTable, you will notice that it sorted properly for 2001, but then for other years in the data set (i.e. 2002), the PivotTable ordered the Product Names (i.e. “Road-150 Red 48”) in the same order as they ended up in 2001, which means those “chunks” of the PivotTable aren’t sorted in descending order. While this could be argued to be correct, it was not what users generally expect.
Another behaviour in Excel 2003 and previous versions that we heard about from customers was what happened to their sort when new data was available. For example, if there was a new product in the data set next time the PivotTable was refreshed (i.e. “Road-150 Red 20”),Microsoft Office Enterprise 2007, it would show up at the bottom of the products listed, regardless of what the sales for that product were. In practice, this meant users needed to re-apply sorts whenever they refreshed their data.
In Excel 12, we changed both of these behaviours to be in line with users’ expectations. Here is what our unsorted PivotTable looks like in Excel 12.
(Click to enlarge)
To do the same sort - individual bikes listed in descending order by the sales amount of each – you could simply right-click a sales amount for one of the bikes in the Northwest region and pick “Descending” in the sort menu.
(Click to enlarge)
Here is a screenshot of the resulting sorted PivotTable.
(Click to enlarge)
Notice that the bikes are now ordered differently for 2001 and 2002, since it wasn’t the same bike that sold the most in both years. Additionally, this sort will be reapplied every time the PivotTable is updated – the PivotTable will automatically take additional source data into account.
Those of you that were familiar with PivotTable AutoSort functionality in current versions of Excel have probably guessed that we have used AutoSort to provide this functionality. We have also made two improvements to AutoSort. The first one is that AutoSort can now be applied based on the values in any row or column and not just based on the values in the grand total row/column. In the above example this is illustrated by sorting based on the values in the Northwest column. The second improvement is that AutoSort is now the default, independent of whether you apply the sort from the ribbon, the context menu or the new filter UI covered under filtering below.
One final sort-related change I want to highlight is some work we have done on the Sort dialog. We have actually split the old dialog into two in order to improve its usability. When users launch the Sort dialog for and have a cell in the Row Area selected, they will see a new dialog that better lays out the full options for sorting a field.
Row Area Sort Dialog
The “More Options…” button on the sort dialog to allows users to control more detailed settings like whether to sort automatically every time data is refreshed.
More Options ...
When users launch the Sort dialog and have a cell in the Values Area selected, we detect that you want to sort based on values and give you a different dialog which makes it very easy to define whether you want to sort by values in the selected row or values in the selected column. Our tests have shown this to be a very helpful improvement to users.
Values Area Sort Dialog
Filtering in PivotTables
I want to cover two classes of work we did in this area in Excel 12. First, we addressed a number of customer requests – being able to filter fields before adding them to a PivotTable, enabling multi-select for Report Filters (nee Page Fields) – and second, we added some completely new filtering capabilities. Let’s start with the latter.
PivotTables are great at summarizing large amounts of data quickly and easily. But oftentimes even summarized data contains thousands of rows which raises the need for ways to limit the amount of data to just the relevant parts for answering a certain business question. Excel 12 offers three new types of filters in PivotTables to make this easily possible.
Label Filters (e.g. show only product names containing “bike”) Value Filters (e.g. show only products that sold for more than $10,000) Date Filters (e.g. show only dates within the current year)
Let’s take a look at an example of each.
Label filters
To illustrate how Label filters can be used, we’ll use the following PivotTable as an example. It is pretty much the same as the one we were just looking at, except that there are more bikes listed making it a longer PivotTable.
(Click to enlarge)
Pretend, for example, you would like to see only the bikes that have “silver” in the name. To so so, you would simply select the button in the PivotTable that says “Filter rows” (the equivalent of the filter field button in current-version PivotTables), and then navigate to “Label Filters” and select “Contains”.
(Click to enlarge)
After you select “Contains”, a Label Filter dialog appears where you would enter “silver” and select “OK”.
And the PivotTable updates to only include bikes that have “silver” in the name.
(Click to enlarge)
Folks that have read my post on AutoFilter improvements may have noticed that AutoFilters and PivotTable filters bear a strong resemblance. We did a lot of work in Excel 12 to try and make the two experiences consistent in both how they look and how they work, the goal being users find it easy to move back and forth between the two features with no difficultly or confusion.
Value filters
Another thing that users commonly want to do is filter based on values. For example, again looking at the PivotTable above, I might want to just see the bikes that sold more than $10,000 across all categories. This is a perfect candidate for a Value filter. You would set the filter pretty much the same way as a label filter …
(Click to enlarge)
… type "10000" and press OK …
… and end up with a PivotTable showing just the bikes that sold more than $10,000.
(Click to enlarge)
Date Filters
When you have date fields on rows and columns, the PivotTable offers Date filters instead of Label filters. The UI entry point is the same …
… but in this case, the date filter menu offers a set of one-click dynamic filters that are automatically reevaluated over time to only include the relevant items based on the current date. (Again, this is similar to what is available in AutoFilter in Excel 12.) You can also set a date filter based on specific dates in the data filter dialog.
Filtering in the field list
Another request we hear commonly from customers is for the ability to filter a field before adding it to the PivotTable (this is more efficient, because if you filter it after adding it to the PivotTable, you end up running two queries against the data instead of just one, which can be an important consideration against big data sets or external databases). To allow you to filter a field before adding it to the PivotTable, the PivotTable Field List provides access to the filter UI. In the screenshot of the field list below, the filter drop down menu is open for the Customer Name field allowing me to apply filtering before I add the field to the PivotTable.
(Click to enlarge)
You can apply, change and remove filtering from the field list once the field is added to the PivotTable as well. Also, once the field is added, the sorting options become available (you may have noticed they were disabled in the screenshot above).
Multi-select in the Report Filter area
Users of current versions of Excel generally assume that Report Filters (at least in PivotTables not attached to SQL Server Analysis Services cubes) are single-select, because that’s what the UI communicates. (There is a way to multi-select items, but it is not terribly clear). In Excel 12, the Report Area filter UI will look just like all other filters, making it easy to multi-select whatever a user wants quickly and easily.
(Click to enlarge)
That’s the overview of new sorting and filtering capabilities in PivotTables in Excel 12. Next up = conditional formatting applied to data in PivotTables.