Does Excel do everything you want, easily properly and first time? If not, read on and bear with me.
A recent event held by the Institute of
Chartered Accountants in England and Wales, discussed the 20 principles of best
spreadsheet practice and something very interesting came to light.I raised a question about the difficulty that Excel sometimes placed in the way of adhering to these principles, particularly as regards spreadsheet design and security. James Akrigg of Microsoft who is Head of Technology for Partners at Microsoft came up with an answer which surprised us. Not because he answered the question (which he didn’t directly) but because he mentioned a website where Excel users could write in and vote on suggestions to be incorporated in forthcoming releases. He even implied that the turn-around on adding to Office 365 products could be weeks rather than years if it was practical and enough users wanted it. Almost nobody – maybe nobody – at our meeting had heard about it.
There were very few suggestions there
when I logged on, so I think I doubled the number.
The first ones I've made were to help with
keeping a consistent structure and documenting spreadsheets:
- Provide more worksheet functions for working with coloured cells: we all love to colour code cells to show different types of value, so for example being able to use SUMIF() to total a range of certain coloured cells would be boon
- Provide free-form notes 'pages' which could be added to sheets so that it is easy to record the purpose of sheets, calculations etc as you go
- Provide prompts to document and describe range names on the notes pages when the ranges are created; do something similar when styles and user defined functions are created.
- Provide, again possibly through the notes pages, a method of tracking which other spreadsheets are linked to data FROM your spreadsheet
- Provide a method of automatically listing of ranges, names etc (as the Access documentator does)
- It would help spreadsheet design if specific conditional formatting rules and data validation rules could be set within styles.
I then moved on to the spreadsheet
protection principle:
- Expand the range of protection options so add/edit/delete comments is a separate option for example to allow users to add comments to cells where they are not permitted to change values
- Allow addition/deletion of rows within a protected spreadsheet for only a certain range of rows (for instance allow rows to be added within a table, but in the rows above or below)
- And do the same thing for columns.
- Provide a better way of tracking circular errors possibly through a diagrammatic representation of cell linkages
- Certain functions such as COUNTIF and SUMIF give #VALUE errors if they are referring to ranges in closed workbooks, others such as MATCH don't. This often means complex formulae such as SUMPRODUCT need to be used to avoid the problem. This needs fixing: formulae should not given #VALUE errors just because the underlying workbook is not open.
- Array formulae should work consistently with all worksheet formulae. At present some can be used in arrays and others not.
- Ensure cells with spaces in them are treated consistently. For example if B4 contains a space and B5 contain 0 then =sum(B4:B5) gives 0 but gives a #VALUE error if the range referred to is on a linked workbook. This should not happen.
- Search and replace needs refining to use a more extensive RegEx : at present doing any complex search and replace within formulae is tricky or impossible.
- Expand the range of information to be returned by CELL() to include the interior colour of the cell, and the cell formula as text.
It seems like we have a chance to make Excel an even better product, so take James up on his challenge and go and do it.
And if you want to look at ICAEW's list of 20 principles of good spreadsheet practice you can read them here.
