Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Suggested new functionalities for Filters, Pivots and other issues
Although I like a lot the way that Excel handles data analysis (and
especially the Pivot Tables) I think that there is still significant "room" for improvement. Here is what I suggest: Regarding FILTERS we need the following improvements on functionality and ergonomy: 1) to be able to formulate more than 2 conditions - probably 10 would be ok; I know that this could be accomplished by using Advanced Filter but this is very time-consuming 2) possibility to create conditions that relate current cell with the coresponding cell from other column 3) to filter after more than one value and on multiple columns when we click on "AutoFilter" icon - according to the cells we have selected while keeping CTRL pressed 4) to obtain a "Show excluding" filter when we click on "AutoFilter" icon while pressing Shift 5) a corresponding button to "Show All" which enables us to remove only the filter of the current column (or selected columns) - a shortcut key like F6 would also be very useful - so if we have 5 active filters we could easily cancel one and keep the other 4 6) to be able to see (and activate) each one of the last 10 different filter conditions used when we click on the filter arrow attached to the header cell of a filtered column - this could be shown either on the left or the right side 7) to be able to save and load certain set of filters - this is very important when you work with more than 3 custom filters and occasionally press "Show All" or when you need to switch between different sets of filters 8) to view the current filter condition when we float the mouse pointer over the column's header cell 9) enhanced visibility for the active filters - not only the small blue arrow on the grey square Regarding PIVOT TABLES: 1) to save the pivot table "settings" in order to be able to switch quickly from one "look" to another - I often have to make at least 4 different copies of the same pivot in order to quickly show the desired aspects of different analysis (on the same data set) to my superiors 2) when we make a copy of one pivot table in the same workbook Excel should base the new pivot, by default, on the "database" used by the original pivot; 3) when we double-click on one of the detail fields the new sheet that appears should keep the same formatting as the "database" sheet; by formatting I also refer to the settings like Group/Ungroup and Freeze Panes 4) it is extremely necessary to have a function that allows us to copy the data from the TOTAL field in a pivot table to a special field in the pivot's "database"; this would help us when we need to group the data we analyse in different categories according to the sum in that speciffic field - one usage is when we need to separate the products that were sold above a minimum value (or quantity); at this time we can only run a very slow SUMIF on all data or a VLOOKUP over the pivot 5) there are many situations when our pivot table is based on an enormous database but we only need to study just a part of it at a time - it would help us a lot to be able to automatically generate a pivot table with the same design as the main pivot but based on our specific selection from it 6) each pivot table should be able to auto-freeze its header according to the changes we brought to its page, column, row and data fields Other issues: 1) the status bar should be able to display simultaneously at least 3 informations chosen by user: SUM, COUNT and NO DUPLICATES (something like MODE function) would be a good default option 2) we need an enhanced VLOOKUP function that would be able to search in a database by more that one single criteria (example: NAME + SURNAME) and to return more than one field (example: AGE, COUNTRY, SALARY, JOB); I know that multi-criterial search could be accomplished through DGET but this works only for one record at a time 3) we need to have an option that would automatically re-order the columns on a second table based on the columns in the master table. This is extremely useful when you have to append periodically some data to an existing table or to merge two tables in order to run an agregated pivot. Please note that this can't be achieved through horizontal sort since the header of a table isn't usually sorted alphabetically. Above is what I consider that would help me, my colleagues and a lot of power users that I know to use Excel more efficiently. Please let me know what do you think about my suggestions. Thank you very much for your attention. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...lic.excel.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|