E
ed05h
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.
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.