Features that should be built in to Excel - ATTN MICROSOFT

[xlsx]

So everyone here appreciates(to some extent) the degree to which Excel allows for customization via VBA etc.. While this is an awesome capability that not many(if any?) applications can match, there are certainly some features that Excel developers should simply make part of the official release. My thoughts are that maybe we can come up with a sticky that lists these user requests and invite a few members of the Excel developer team to take a look. Now, I can only hope that someone knows somebody on that team or maybe there are already some that browse the boards. I would like to start with a couple that have semi-viable VBA solutions, but could be much better if implemented into the core application.

1. Ability to work with a fiscal calendar.

The way I envision it would allow you to specify a fiscal calender to a specific spreadsheet. You could have a list of user-defined "fiscal profiles" if you will. Then you could select the one you want to apply to your specific worksheet. This would then allow the use of "FMONTH(" or "FDATE(" for example. I am sure this type of "profile-dependent" formula could have other uses as well, but this use would be a godsend to many people. Plus, because this would not change the normal date functions, it would allow both to coexist in the same worksheet.

2. Highlight the row/column that the cursor is over.

This could be a preference so that you could turn it on and off. The current coded options for this can mess with cell selections and formatting(not ideal, obviously). This feature would be invaluable, especially with growth in popularity of large/high-res LCD's. This would also be great for worksheets that are very large, and thus require you to zoom out further to see the whole sheet. This is something that should be handled on the visual layer of the application and not left to coding that can manipulate the data.


These are my two greatest desires with regards to Excel. Please follow with yours. And if anyone knows someone on the dev team, please forward this to them. And if you are one, please read and consider these as suggestions from your most loyal and advanced user-base.

 

[jbeaucaire]

3. String Concatenation - I see this all the time! Excel should add this concept to its builtin text tools.

a) Simple Concatenation of a range:
=CONCAT(A1:A5, "-")

b) Concatenation with an IF test, works like SUMIF()
=CONCATIF(A1:A5, "dog", B1:B5, "-", TRUE)

Based on an implementation by Mike Rickson here on the forum... the last parameter is a "suppress duplicate values" flag.

c) #b above also capable of working with a more complex IF() test in an array.


4. Random selections from a range - an extension of the RAND() tools.

=RAND(A1:A100)
...would return an item at random from that range.

 

[jim.knipe]

Another one is the ability to toggle highlighting all locked (or unlocked) cells. This way you can quickly glance over the spreadsheet and be sure you have unlocked only what you want

 

[T. Valko]

I'm a number cruncher so my area of "expertise" is formulas.

I agree with jbeaucaire on the need for both range and conditional range concatenation functions.

If we all thought about it I'm sure that collectively we could come up with dozens of useful worksheet functions that Excel is sorely missing.

Some of my top choices would be:

Every function should be able to handle 3D references and arrays.

An efficient function that counts uniques and conditional uniques.

A more robust set of functions to work with filtered ranges:

MINIF_FILTER
MINIFS_FILTER
MAXIF_FILTER
MAXIFS_FILTER
SUMIF_FILTER
SUMIFS_FILTER
COUNTIF_FILTER
COUNTIFS_FILTER
AVERAGEIF_FILTER
AVERAGEIFS_FILTER
V/H/LOOKUP_FILTER
COUNTUNIQUE_FILTER

An efficient function that returns multiple results when there are duplicate lookup_values:

VLOOKUP(lookup_value,table_array,col_index_num,[instance_num])

There is a CELL("width") function so why the hell isn't there a CELL("height") function?

I could go on ad nauseum...

Seems to me that development of worksheet functions would be the easiest thing to do as far as developing for the Excel application as a whole, yet, version after version, new worksheet functions are few and far between.

I have more thoughts on this subject but I'll keep those to myself since they get me all fired up and I might say something that'll get me into trouble!