Like all of you, I am dependent upon day to day work in Excel. Excel is a software which is like ocean, unlimited in bounds. It has almost all features but it doesn't have few which everybody cherishes. All of us want something or the other. Below is a list of features which I would like Excel to have.
1. Making Volatile Functions Non-volatile on the basis of a Parameter
We all like TODAY(), NOW(), RAND() and RANDBETWEEN() (there are other volatile functions also). They are very useful but suffer a fatal flaw, if something gets recalculated, they always get recalculated also. Hence, if I am using TODAY() to derive today's day and if I open the sheet tomorrow, TODAY() will change tomorrow's date. I wish that all volatile functions should carry a parameter so that they should not recalculated when the sheet is recalculated. For example, we should have something like TODAY(N), NOW(N),RAND(N) and RANDBETWEEN(N) where N
stands for non-volatile. Hence, if N is existing these functions should behave as non-volatile. If N is not existing, they should behave as volatile.
2. Option to Refresh Pivots when Recalculation Happens
Currently, whenever recalculation happens, pivot tables don't get refreshed. We have to manually update them. When pivots are many in numbers, then the problem gets compounded. (If a number of pivots are based on same data range, then refreshing one pivot would refresh all of the pivots based on same data range). Many times, users forget to refresh pivots and it carries wrong information to stakeholders. I wish that there should be an option in pivot tables checking which would made them refreshable whenever recalculation happens.
3. Concatenation of a Range
Currently, you can not concatenate a range. You can concatenate them separately though. Hence, you can concatenate A1&A2&A3&A4&A5&A6&A6 but I can't write Concatenate(A1:A6). The problem becomes bigger if number of cells are more. I want Excel to supply a function through which I can concatenate a range. Also I want it to supply parameter for a concatenation separator. Hence, if I specify comma as concatenation separator and I want to concatenate "a" and "b", the answer should be "a,b". (My wish is also that Excel should supply a CONCATENATEIF function which should do concatenation conditionally)
This wish has been fulfilled in Excel 2016 where TEXTJOIN function has been provided.
4. Excel giving Out of Memory Error when Deleting Columns / Rows
This problem has become rampant with newer versions of Excel. There are occasions when we want to delete rows / columns in a data set. Excel gives "Out of Memory Error" very often and it makes working with data very difficult. I wish Excel doesn't give this error.
5. Inquire Add-in should be part of all Excel Versions
Inquire add-in is available only in Pro versions of MS Office and this is a very good tool to compare Excel sheets. I wish that this should be part of native Excel installation on all versions of MS Office.
6. Easy Calendar Controls (Date Picker)
Calendar controls, currently, can be provided only though VBA and implementation through VBA is also not easy. Providing calendar control through a simple Excel feature would be very handy. A general user should be able to insert this Date Picker very easily and should be able to work with dates.
7. Generating csv Files
Generating csv through Excel is still not very user friendly. It doesn't offer the option to specify separator, text separator, encoding standard and many options which are needed. For example, if I generate a csv file through OpenOffice, I get following options -
I wish Excel offers the above capability when generating csv files.
8. Color Options in Excel
Currently, Excel offers a very dull color for its skin. I want to have custom color for its skins and buttons. After all, I use Excel throughout the day.
9. Data Validation should also be Protected in Sheet Protection Mode
Currently, we can't lock / protect Data Validation even if sheet is protected. If a cell can be overwritten, Data Validation can also be overwritten / removed. I want Excel to provide the feature of protecting Data Validation when sheet is protected and cell is unprotected.
10. Auto change of Calculation Mode should not be possible
Currently, many times Calculation Mode gets changed to Manual and it causes problems when we don't realize that this has happened. (https://support.microsoft.com/en-us/kb/214395). I just want Excel to give a pop-up message saying that calculation mode is being changed to Manual.