H
Harlan Grove
I read several Excel blogs, though hardly ever post in them (with one
big exception). This is purely an opinion piece on my reaction to
other people's reactions to Excel 2007 and the upcoming Excel 2010.
-- Grudging recognition of what Microsoft has done right on its own
initiative --
Excel 97 and VBA 5.x - Microsoft killed off Lotus 123 with this
version 12 years ago, deservedly.
Excel 2000's support for OLAP cubes and pivot table improvements.
Excel 2003 lists which improved into Excel 2007 tables.
-- Improvements Microsoft was dragged kicking & screaming to make --
Excel 2002 colored worksheet tabs (Quattro Pro 5 had them 8 years
earlier), full pathnames in print headers/footers (123 Release 3 had
them 12 years earlier), improved stats functions (123's VAR and
related functions have used better implementations since Release 1
back in 1981; continuous distributions finally came close to the
quality Gnumeric had provided (since, as free software, it could take
advantage of the superlative implementations in R and StatLib).
Excel 2003 finally used algorithms for regression functions as good as
they had used for XY chart trend lines for years. The improved RAND
function after SP1 fixed the really stupid bug in the original release
that allowed for negative results.
Excel 2007 expanded grid (various Unix spreadsheets had 512 columns or
more than 100K rows since the late 1980s; WingZ provided a default 32K
by 32K grid and allowed users to customize the grid as long as there
were no more than 2^30 cells, so, e.g., 1,024 columns by 1,048,576
rows would have been OK), more sort keys (123 Release 3 allowed
sorting on all 256 columns, A to IV), more levels of nested function
calls and function arguments (both 123 and Quattro Pro provided more
for as far back as I can recall, even VisiCalc (available as freeware
on the web) provided more - Excel was in a class of its own in a
decidedly incapable way).
-- Things lost along the way --
Excel 2003 dropping the XLODBC add-in which provided the SQL.REQUEST
function.
Excel 2007 dropping support for toolbars except as groups in the
ribbon's Add-In tab.
Now I get to the good stuff - addressing deficient legacy
functionality. These are things that are still WRONG or MISSING in
Excel over many supposed major version releases.
-- What I'd like to see in Excel 2010 (but know better than to expect)
--
MOD function providing IEEE 754 capability by using hardware FPUs -
something that's been possible since Excel 97 at least.
MODE function supporting 3D references as arguments. If AVERAGE and
MEDIAN can, it's a mystery why MODE can't.
SUMIF etc supporting multiple area ranges and 3D references. If NPV
support ordered iteration through arbitrary collection arguments,
there's no obvious reason SUMIF etc can't.
Ability to load multiple workbooks with the same base filename (e.g.,
summary.xlsx) from multiple drives or directories in the same Excel
instance at the same time. That is, Excel finally getting a capability
had by EVERY other Windows program capable of loading multiple files
at the same time. Too darn bad if this would require a very thorough
overhaul of the Excel/VBA interface or Excel object model. If Word can
have C:\A\foobar.docx and C:\B\foobar.docx open at the same time (and
it can!), is there any GOOD reason Excel can't have C:\A\foobar.xlsx
and C:\B\foobar.xlsx open at the same time?
Something from Lotus 123, whose DataLens patents have expired by now:
the ability to name queries and use them like defined names in
formulas and function calls WITHOUT having to load those queries'
results into worksheet ranges. Nice if Microsoft included a File-Save
option that allowed users to choose to cache current query results
with the workbook or force refresh on re-open, kinda like external
references in formulas to other workbooks.
A rewrite of the XLODBC add-in (so no longer written in XLM, nice to
fix the memory leaks too) and resurrection of the SQL.REQUEST add-in
function.
Provide at least extended wildcards and patterns like Word already
provides at least in Excel's Find and Replace dialogs. Better still
would be WSH- or .Net-level regular expression support in these
dialogs AND functions such as MATCH, VLOOKUP, HLOOKUP, and SEARCH.
Either extend the CELL function to accept 1st arguments for all range
properties or add a new function like CELL to do so. This would
address the perenial newsgroup questions about conditional summing/
counting/etc by format.
Finally, finally, finally make Excel a 3D spreadsheet, by which I mean
1. allow advanced filters to extract (copy to another location)
records to worksheets other than the active worksheet,
2. allow users to protect all selected/grouped worksheets with a
single, common password in a single operation (it's not as though
worksheet protection passwords are all that secure anyway that this
would be a security hole),
3. allow references to ranges in other worksheets for data validation
lists and conditional formatting,
4. provide 3D what-if tables - see how 123 or Quattro Pro handle this
if the implementation isn't obvious,
5. provide a syntactic mechanism or a new function for RELATIVE
worksheet references in formulas (hint for a syntactic approach:
#'Sheetname'!$X$99 to refer to worksheet Sheetname relatively - this
shouldn't cause parsing problems with error value constants since #
would need to be followed by a single quote - besides, # is already
overloaded in table structured references),
6. add 3D INDEX and OFFSET functions which accept 3D references as
first arguments and additional arguments for sheet index/offset and
extent for OFFSET,
7. add SHEET and SHEETNAME functions both taking a single range (not
3D reference) argument and returning that range's worksheet's index
number and name, respectively,
8. add a SHEETS function that takes a single 3D reference argument and
returns the number of worksheets spanned by the 3D reference,
9. add a generalized 3D TRANSPOSE function that could rotate 3D
references around various 'axes' - see Lotus 123 if the concept isn't
obvious,
10. (why not?!) provide a DECENT surface charting facility and allow
3D references as data source.
There are a number of other things I'd like to see, such as a
variation on the named query pipe dream above where a named query's
results would appear as effectively a read-only worksheet containing a
single table with no columns or rows beyond the table's extent but
would automatically resize upon query refresh. I'd also like VBA udfs
to accept 3D references, but since Microsoft seems to be deprecating
VBA by indifference and inattention in favor of VSTA/VSTO, I realize
that ain't ever gonna happen.
As I've written before, I expect Microsoft to provide flashing,
dancing, shimmering text before any of these actual spreadsheet
functionality improvements. I believe I can wait to see how little
Microsoft improves Excel 2010.
big exception). This is purely an opinion piece on my reaction to
other people's reactions to Excel 2007 and the upcoming Excel 2010.
-- Grudging recognition of what Microsoft has done right on its own
initiative --
Excel 97 and VBA 5.x - Microsoft killed off Lotus 123 with this
version 12 years ago, deservedly.
Excel 2000's support for OLAP cubes and pivot table improvements.
Excel 2003 lists which improved into Excel 2007 tables.
-- Improvements Microsoft was dragged kicking & screaming to make --
Excel 2002 colored worksheet tabs (Quattro Pro 5 had them 8 years
earlier), full pathnames in print headers/footers (123 Release 3 had
them 12 years earlier), improved stats functions (123's VAR and
related functions have used better implementations since Release 1
back in 1981; continuous distributions finally came close to the
quality Gnumeric had provided (since, as free software, it could take
advantage of the superlative implementations in R and StatLib).
Excel 2003 finally used algorithms for regression functions as good as
they had used for XY chart trend lines for years. The improved RAND
function after SP1 fixed the really stupid bug in the original release
that allowed for negative results.
Excel 2007 expanded grid (various Unix spreadsheets had 512 columns or
more than 100K rows since the late 1980s; WingZ provided a default 32K
by 32K grid and allowed users to customize the grid as long as there
were no more than 2^30 cells, so, e.g., 1,024 columns by 1,048,576
rows would have been OK), more sort keys (123 Release 3 allowed
sorting on all 256 columns, A to IV), more levels of nested function
calls and function arguments (both 123 and Quattro Pro provided more
for as far back as I can recall, even VisiCalc (available as freeware
on the web) provided more - Excel was in a class of its own in a
decidedly incapable way).
-- Things lost along the way --
Excel 2003 dropping the XLODBC add-in which provided the SQL.REQUEST
function.
Excel 2007 dropping support for toolbars except as groups in the
ribbon's Add-In tab.
Now I get to the good stuff - addressing deficient legacy
functionality. These are things that are still WRONG or MISSING in
Excel over many supposed major version releases.
-- What I'd like to see in Excel 2010 (but know better than to expect)
--
MOD function providing IEEE 754 capability by using hardware FPUs -
something that's been possible since Excel 97 at least.
MODE function supporting 3D references as arguments. If AVERAGE and
MEDIAN can, it's a mystery why MODE can't.
SUMIF etc supporting multiple area ranges and 3D references. If NPV
support ordered iteration through arbitrary collection arguments,
there's no obvious reason SUMIF etc can't.
Ability to load multiple workbooks with the same base filename (e.g.,
summary.xlsx) from multiple drives or directories in the same Excel
instance at the same time. That is, Excel finally getting a capability
had by EVERY other Windows program capable of loading multiple files
at the same time. Too darn bad if this would require a very thorough
overhaul of the Excel/VBA interface or Excel object model. If Word can
have C:\A\foobar.docx and C:\B\foobar.docx open at the same time (and
it can!), is there any GOOD reason Excel can't have C:\A\foobar.xlsx
and C:\B\foobar.xlsx open at the same time?
Something from Lotus 123, whose DataLens patents have expired by now:
the ability to name queries and use them like defined names in
formulas and function calls WITHOUT having to load those queries'
results into worksheet ranges. Nice if Microsoft included a File-Save
option that allowed users to choose to cache current query results
with the workbook or force refresh on re-open, kinda like external
references in formulas to other workbooks.
A rewrite of the XLODBC add-in (so no longer written in XLM, nice to
fix the memory leaks too) and resurrection of the SQL.REQUEST add-in
function.
Provide at least extended wildcards and patterns like Word already
provides at least in Excel's Find and Replace dialogs. Better still
would be WSH- or .Net-level regular expression support in these
dialogs AND functions such as MATCH, VLOOKUP, HLOOKUP, and SEARCH.
Either extend the CELL function to accept 1st arguments for all range
properties or add a new function like CELL to do so. This would
address the perenial newsgroup questions about conditional summing/
counting/etc by format.
Finally, finally, finally make Excel a 3D spreadsheet, by which I mean
1. allow advanced filters to extract (copy to another location)
records to worksheets other than the active worksheet,
2. allow users to protect all selected/grouped worksheets with a
single, common password in a single operation (it's not as though
worksheet protection passwords are all that secure anyway that this
would be a security hole),
3. allow references to ranges in other worksheets for data validation
lists and conditional formatting,
4. provide 3D what-if tables - see how 123 or Quattro Pro handle this
if the implementation isn't obvious,
5. provide a syntactic mechanism or a new function for RELATIVE
worksheet references in formulas (hint for a syntactic approach:
#'Sheetname'!$X$99 to refer to worksheet Sheetname relatively - this
shouldn't cause parsing problems with error value constants since #
would need to be followed by a single quote - besides, # is already
overloaded in table structured references),
6. add 3D INDEX and OFFSET functions which accept 3D references as
first arguments and additional arguments for sheet index/offset and
extent for OFFSET,
7. add SHEET and SHEETNAME functions both taking a single range (not
3D reference) argument and returning that range's worksheet's index
number and name, respectively,
8. add a SHEETS function that takes a single 3D reference argument and
returns the number of worksheets spanned by the 3D reference,
9. add a generalized 3D TRANSPOSE function that could rotate 3D
references around various 'axes' - see Lotus 123 if the concept isn't
obvious,
10. (why not?!) provide a DECENT surface charting facility and allow
3D references as data source.
There are a number of other things I'd like to see, such as a
variation on the named query pipe dream above where a named query's
results would appear as effectively a read-only worksheet containing a
single table with no columns or rows beyond the table's extent but
would automatically resize upon query refresh. I'd also like VBA udfs
to accept 3D references, but since Microsoft seems to be deprecating
VBA by indifference and inattention in favor of VSTA/VSTO, I realize
that ain't ever gonna happen.
As I've written before, I expect Microsoft to provide flashing,
dancing, shimmering text before any of these actual spreadsheet
functionality improvements. I believe I can wait to see how little
Microsoft improves Excel 2010.