34 thoughts on “New Features”

1. Probably not at the top of my list, but it just came up again. I’d like to be able to use PasteSpecial after Cut. I go to do it every so often – usually to choose “All except borders” I think – and am surprised that it’s not available.

2. A more logical Object Model, I waste hours looking for things.

3. Be able to unhide more than one sheet when using Format – Sheet – Unhide …

4. Is it too broad to say “better charts”?

5. I have two requests. For me, it’s more rows. I know 65K is a lot, but one of the apps that I use inside of Excel could surely make use of more. Second, a better “clipboard”. I want copy and paste to work differently!

6. One more (picky little) thing. I’d like to be able to wrap numbers that are custom formatted to include text. I really like the ability to have a variable and it’s label in the same cell, e.g., “Fee – ” 0.0%. But the custom format won’t word wrap or slop over into an empty cell, which makes it less useful for long ones like “Number of years to amortize – ” 0.0.

7. Those commandbars that Dick mentioned is right up there. I find it such a tease to have gauge bars, dropdown gfx splitcombos etc. as types but unavailable. Just look at them all in Enum MsoControlType! drool.

   A worksheet function for determining lastrow or even usedrange.

   additionally, ability to mark a column as “formula autoextending” depending on used range of constants.

   As Jon mentioned, better charts – but more specifically, antialiasing.

   Conditional number format.
   It be great to set the numberformat to ;;; if the cell =NA() ?

   Microsoft has a Wish service: KB114491
   http://go.microsoft.com/fwlink/?LinkId=32748

8. An easier error handling to worksheet functions. I mean that you don’t need to type it allways like this:

   =IF(ISERROR(LOOKUP(….)),””,LOOKUP(….))

9. Two words: .NET

10. Hi Dick,

    Funny coincidence, I was contemplating on posting exactly the same subject just yesterday.
    I lacked the time to do so.

    Since Jon already Hi-jacked my #1, I’ll post another:
    Fix all bugs that still remain in Excel and in VBA.

11. How about the ability to set / unset Freeze Panes when in Group mode. Nice and simple (you’d hope). There’s a few other things that don’t work in group mode – would be nice to get them all working.

12. Hi,

    I would like to disable the error message that the VBA Editor pups up the moment your cursor leaves the command your are just writing. It says “Compile Error. Expected expression”. Your incomplete command is colored red. That would just do for me. I hate that useless window!

    Gerrit

    P.S.
    Rob, hasn’t John Walkenbach published a worksheet function for determining lastrow?

    Function COUNTROWS() As Long
    Application.Volatile True
    COUNTROWS = ActiveSheet.UsedRange.Rows.Count
    End Function

13. Aj: above funtion counts only the rows in any used range. That should teach me never to edit published functions any more. Please use:

    COUNTROWS = range(Cells(1, 1), ActiveSheet.UsedRange).Rows.Count

14. Hi Gerrit
    re: error message that the VBA Editor

    In the VBA Editor goto ‘Tools – Options – Editor’ and uncheck the first option (something like ‘Automatic syntac check’)

    So no need for a new feature
    Frank

15. Frank, Thanks! I missed it. At one point I even had a program running in the background that would recognise this pup-up and kill it imediately.

16. Agree with lot’s of the stuff mentioned here. In addition I wish MS could implement a a calculation tree that actually works.

17. 1) The ability to compile UDF’s to native code so they could actually run at a reasonable speed.

    2) The option to switch the formula bar into an IDE-type mode and view long formulas as code-style lines, e.g.

    =IF(
    Condition,
    val1,
    val2
    )

    I’ve tried building this functionality myself but it’s fairly rudimentary thus far.

    I think it would make auditing far easier.

18. If MS were to implement every one of the suggestions in this discussion, it would still leave unanswered the question “where’s the innovation?”! So, how about going beyond the baby steps that MS can take and seek the true power that would make a worthy successor to VisiCalc, 123, and Excel itself?

    Here’s the crux that I would explore: what new features are critical to (a) making XL a *serious* executive support system, (b) a *serious* data analyzer, (c) a *serious* XL-native database system, (d) a *serious* number cruncher, and (e) a *serious* visual representation tool?

    Will any of what I have to suggest show up in the next N versions of XL? I don’t know. We will just have to wait and see. However, they would spark a revolution in how XL is used by individuals and corporations.

    – Better database analysis: simplified and secure connectivity with external data sources, improved automation (a super version of MS Query, a product that hasn’t changed in eons), improved analysis tools (for a *start* make the entire range of XL functions capability, including UDFs, available in PivotTables), improved formatting capability including respecting user customization.

    – Universal drill down & back-out capability (PTs, charts and ??)

    – Worksheets as user-defined ‘sub processes’ (akin to VBA subroutines or functions)

    – Parameterised named formulas; other improvements to facilitate maintenance of XL ‘systems.’

    – Improved management of rudimentary databases (how many times have we seen a request for “how do I generate a unique invoice number?”). Basically, radical improvements in the areas of data validation, data management (compared to the current elementary Data | Form…) and a way to map a user interface ‘template’ (order form, invoice, etc.) to the underlying data

    – Improved reliability of functions. Continue the fixes made in 2002 and 2003 in the various stats functions, and extend them to other math/scientific functions. Basically, improve the numerical stability of all functions.

    – Improve the management of links for workbooks that use functions in external files.

    – Fix the damn bugs, especially in the functions and preferably in the UI!

    – While the above three should improve the attractiveness of XL to the academic/scientific/engineering community, let’s not forget those who want XL to be at least as reliable as a $10 calculator. Provide a mechanism that doesn’t hide behind “you don’t understand how digital computers work.” Damn right. People don’t. And, don’t want to. They only want number minus number to be zero, not some MS/XL/hardware/software/IEEE litany of we-are-so-much-smarter-than-you-that-trust-us-when-we-say-you-don’t-have-a zero.

    – Improved visual representation of data — but think beyond the mantra of “more charts.” How about a “police sketch artist” (PSA) approach to charting? Start with a blank slate. Add this series. Now, that. How about this as the title…Well, can’t I just stick in a equation in here? Ummmm…no, that’s not quite right. How about changing this to… And these bars should be…oh, can we add this range as labels? Now, how about some shadow for these columns? No, that’s too much make it a little less…Great…now, can we have this line completely above the tallest bar…

    – Maybe, all of this in XL will be overkill. How about a modularized XL? Start with the core XL. Add the database analyzer module; add the PSA charts module; add the ‘baby Access’ database module; add the scientific processing module…

    – Or, open up XL to developers in a manner unprecedented to-date. No, not access to the source. But, the ability for VBA/VSTO to interact with core XL capability. What level of (structured) access would a developer need to create the PSA charting module? What level of access would one need to create a worksheet-as-a-function?

19. Since I use both Mac and Windows, I would like to see the Mac VBA developed to at least the same level as on the Windows VBA, so that “cross-platform” would truly be cross-platform.

20. I would like to see the 3D-functions extended to all XL functions, and alow them to work with arrays.

21. Here is some ideas:
    1. Better 3D capabilities.
    – Like wireframes with lighting, shading etc
    – Mathematical formula to chart
    2. Map linkings to countries & cities. Geo coordinates
    3. Pivottable: “filtering capability” and possibility to filter by code.
    4. Case (
    if a=123 then 1000
    if a>123 then 2000
    else 9999)
    5. Iserror: If(a=1;TRUE;FALSE;(ErrorRule))
    If ErrorRule is set to 1 then if error, FALSE
    6. Paste Formula button
    7. Tool:Find Broken links to other XL-books.
    8. Formula: Formula editor with Indent possibility for complex formulas
    9. Chartwizard:Easier to make 2nd axis, Broken axis, Gant, Flowchart, …
    10. …

    and there are many more things.
    I think the lack new functionalities is due to lack of competition.

22. In Excel VBA:

    allow setting a control’s Controlsource to a VBA variable or another control; it’s only allowed to pointto a worksheet at the moment.
    (Might actually already work in Access)

    Would make creating forms with synchronized data/fields a breeze.

23. I already like some of the things mentioned above, but still have a few more

    – Generally I’d like to have more flexibility for functions that refer to a range of cells that might grow/shrink over time.

    – I’d like to be able to use references to whole columns/rows in array formulas (for example “=sum((A:A)*(B:B))” returns an error).

    – Formulas in Pivot tables are somewhat limited. If I could include formulas that in which I can reference regular cells and not only the pivot fields, I would much more often use Pivot tables at all. Now I usualy need something that’s not possible with a Pivot table (at least not without lots of additional columns in the data source) and end up doing it without a Pivot table.

24. 1. Make in cost less than 10 million bucks:
    2. Fix the bloody bugs!

    Jake Nash

    I have an addin that hides sheets and stuff, it’s not finshed yet but if you would like you can give it a go – It’s one of thoese, hey this gets on my nerves, i’ll write something to fix it, then you use it, and nevr get round to finishing it!

    ross at thestructure.co.uk

25. Array formulae into which you could insert rows and columns without generating an error!!!!

    Typing in a single array formula in a column, referencing other columns, is fine. But decide you want to insert some rows you forgot about – at present I have to:
    – copy the top cell out of the range,
    – delete the whole array formula,

    – insert the rows

    – copy the cell back,
    – delete the copy,
    – highlight the new range for the array formula,
    – Hit F2
    – Press ctrl-shift-return.

    Sigh: unless I’m missing something ;o)

    Mpemba
    PS: 64K columns would be nice too

26. Let a workbook compact on exit instead of getting bigger and bigger and bigger and bigger, etc. etc. etc.

27. “- Parameterised named formulas; other improvements to facilitate maintenance of XL ‘systems.'”

    This would be a _huge_ step forward. I’d also like to see:

    – Pivot Tables that produce results that easily work with vlookup.

    – Faster performance of VBA functions that access Excel data cells.

    – A few more cell value types: generalized list and function values would both make Excel far more powerful than it is.

    – More sophisticated control over cell formatting. (At the very least, I’d like to be able provide a VBA function to draw the contents of a cell myself.)

    – More rows/cells.

    – Continue cleaning up the behavior of the existing functions. More numerical reliability is always a good thing.

    Generally, if Excel is roughly equivalent to the old HP41 calculator (powerful, extendable, but hitting architectural limits), I’d like to see the Excel analogue to the HP48 series: Take the core ideas of Excel and work to make them fit togther better (ie: vlookup/PivotTables) as well as generalize them to other domains (lists, first class functions, etc.)

    Of course, this is expensive to develop, and I doubt there’s enough demand to warrant that kind of investment from Microsoft.

28. Just thought of one more: something analagous to Lisp’s _let_ form to work in Excel’s formulas. Basically, what let does is let you establish variable bindings in the middle of an expression. It might let you convert this:

    =MY_FN(SOME_BIG_NASTY_EXPRESSION(A1, A2, A3), SOME_BIG_NASTY_EXPRESSION(A1, A2, A3) + 3)

    into this

    =LET(i = SOME_BIG_NASTY_EXPRESSION(A1, A2, A3)) { MY_FN(i, i + 3) }

    This has the potential for efficienty gains as well as readability gains…

29. Hi
    can be solved with VBA:
    ––
    Public Function V(Optional vrnt As Variant) As Variant
    ‘
    ‘ Stephen Dunn
    ‘ 2002-09-12
    ‘
    Static vrntV As Variant
    If Not IsMissing(vrnt) Then vrntV = vrnt
    V = vrntV
    End Function
    —-

    And now use in your worksheet:
    =MY_FN(V(SOME_BIG_NASTY_EXPRESSION(A1, A2, A3)), V() + 3)

    Frank

30. A couple comments/questions:

    – Does Excel make enough guarantees about execution order to make this work?

    This expression:

    =MY_FN(V(SOME_BIG_NASTY_EXPRESSION(A1, A2, A3)), V() + 3)

    Depends on the second instance of V being called after the first. If Excel runs it the other way around, the evaluation fails.

    – When does Excel/VBA clear the static? Is it cleared at tbe beginning of a recalculation? At the beginning of cell evaluation? Ever? Is this predictable behavior?

    – Related to the last question: can I use V in multiple cells? What happens if I do? Does a V() in one cell pull from a V(…) in another cell? That would be a good way to introduce hidden dependancies.

    – How does this interact with array formulas?

    I like the idea, but it seems pretty limited in the end, and the loosely defined semantics concern me.

31. Simple one: be able to pass a User Defined Types ByVal

32. WHERE’S the Innovation?

    OK, numerical accuracy would be a good start.
    64-bit integers
    signed and unsigned types
    double-double-floats …

    Excel is LESS accurate than computers I was working on 25-y ago. Something to do with cross-platform stability.

33. One more: I’d like the ability to apply an arbitrary calculation (perhaps specified via a formula) to a range of cells inplace. For example, if I wanted to double a range, I’d like to be able to:

    – Select a Range
    – Invoke a command “Apply over range”
    – Enter a formula =a1*2

    And have my range doubled.

    “OK, numerical accuracy would be a good start.
    64-bit integers
    signed and unsigned types
    double-double-floats …”

    Why not arbitrary length integers and arbitrary precision floats? Sure, they’re slower than IEEE754, but we’ve been using IEEE754 (or a close approximation) since the days of the 8088/8087.

    BTW, here are a couple links with other good ideas for Excel:

    http://research.microsoft.com/%7Esimonpj/Papers/excel/index.htm

    http://citeseer.ist.psu.edu/nunez02extended.html

    http://citeseer.ist.psu.edu/dehoon95implementing.html

    http://citeseer.ist.psu.edu/dehoon93designing.html

34. The application of an operation to an entire range is already possible, although it is limited to multiplication, division, addition and subtraction.

    Copy a cell that contains the factor, select the range to operate on and hit Edit, paste special. Check the features in the “Operation” frame.

---

Posting code? Use <pre> tags for VBA and <code> tags for inline.