Some ideas for new features for Microsoft Excel 20xx
Part two of a two-part series of blogs

Excel is a great product, but there is always room for improvement. Here Andy Brown considers 12 possible new features for Excel 2020.

  1. New Version of MS Excel - Suggestions (Part 1)
  2. New Version of MS Excel - Suggestions (Part 2) (this blog)

Posted by Andy Brown on 24 September 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

New Version of MS Excel - Suggestions (Part 2)

I listed 6 suggestions for Excel 2020 in the previous part of this blog; here are 6 more!

7 - Revisit Excel Function Names

I don't think Microsoft can get rid of any Excel functions (imagine the outcry if the VLOOKUP function wasn't supported any more), but they could introduce new ones.  Here are some suggestions.

Old Functions Replacements
VLOOKUP The VLOOKUP function has two different forms, depending on whether you're doing an exact match or not.  Solution: replace this single function with two new ones, called VLOOKUPEXACT and VLOOKUPRANGE.
IF In Access, Reporting Services and other software applications this function is called IIF instead.  Time for a bit of consistency?
MATCH Another function suffering from overload - it's behaviour is determined by the value of a 3rd argument, which can take the value -1, 0 or 1.  The easiest solution would be to introduction 3 new functions called MATCHEXACT, MATCHASCENDING and MATCHDESCENDING.

I admit I've not thought about rewriting functions in much more detail than this because I think it's so unlikely to happen, but there's also scope to separate the INDEX and OFFSET functions into two separate ones, according to whether they're being used as single value functions or array functions.

8 - What-If Analysis

Excel contains two features to run sensitivity analyses: scenarios and data tables. 

The What-If Analysis dropdown

The scenario manager and data table menu in Excel.

 

Scenarios are a great idea spoilt by a ridiculous limitation:

Maximum 32 cells message

The message you'll see if you try to create a scenario affecting more than 32 cells.

What Excel needs is the ability to designate a cell as belonging to one or more scenarios, and then the ability to be able to save multiple data files of different sensitivity analyses containing all affected cells.  Just an idea!

9 - The Fourth Dimension

Is it time for a 4th dimension?  Or perhaps even for multiple dimensions?  In the same way that a pivot table can have one or more page fields (or report filters, as they're now called), so you could choose to enter or work with data for different products, groups, regions or branches.

To some extent PowerPivot for Excel provides this feature, but only as an add-in.

10 - Built-in Spreadsheet Auditor

There are a number of third-party add-ins which allow you to see where you've used formulae inconsistently (here's an example). 

A spreadsheet map

The F shows a unique formula, the arrow where it has been copied across.

 

Is it time to build this functionality into Excel?

11 - Special Dialog Boxes

There are a few dialog boxes in Excel which are - quite obviously - containers for all of the things Microsoft couldn't work out what to do with.  Here's the worst offender:

The Goto Special dialog box

I challenge anyone but an Excel guru to be familiar with all of these options!

 

The Paste Special dialog box has many options which are nothing to do with pasting:

The Paste Special dialog box

Another dialog box needing a rewrite.

 

If these dialog boxes were houses, they'd have been condemned and pulled down long ago.  Do the merciful thing!

12 - VBA Intellisense

This is a slightly unfair one, because Microsoft would love everyone to stop writing VBA and start writing code in Visual Studio using VSTO instead.  However, they could still improve the VBA coding interface in two simple ways.

Firstly, you should be able to define a variable and assign a value to it in one go:

Sub TestMacro()

 

'variable to hold active cell's value

dim ThisCell as String = activecell.Value

 

End Sub

The above code would work in VB.NET; why not VBA? 

Secondly, code should be indented automatically as you type it in, like this:

Sub TestCell()

 

'variable to hold active cell's value

Dim ThisCell As Integer

 

'test this value

ThisCell = ActiveCell.Value

If ThisCell > 5 Then

MsgBox "The cell is big"

Else

MsgBox "The cell is small"

End If

 

End Sub

Instead, VBA will let you get away with this:

Unidented code sample

Unindented code is almost impossible to read!

 

If you don't see what I mean about this change, try writing VB within Visual Studio for a few days, and then go back to VBA.

 

If you've got any suggestions for new features for Excel, please let me know via a comment on this page!

  1. New Version of MS Excel - Suggestions (Part 1)
  2. New Version of MS Excel - Suggestions (Part 2) (this blog)
This blog has 0 threads Add post