WARNING: Opinion

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.
 
T

T. Valko

I'd like to see *efficient* CountUniques() / CountUniquesIF() functions.

SUBTOTAL() should have a "countif" type argument.

There should be an option to reset Text to Columns. I wish I had a dollar
for every time I used TTC then later pasted something and TTC hosed it!

Refedits should be able to handle worksheet length formulas.

In Excel 2007 with the new table feature, if you use the structured syntax
for writing formulas you can't use absolute referencing without a kludge. I
guess MS figured no one would ever need to use absolute referencing!

I sure hope they do something with the conditional formatting user
interface!

The new expanding formula bar is a good thing but was poorly implemented.
 
B

Bob Phillips

Harlan,

As ever it is good to hear your opinion (and that is said without a touch of
irony, sarcasm, or tittering behind my hand). I only wish you had put it on
your blog exception, I think blogs are the perfect vehicle for this sort of
thing; you never know, MS people might even read them.

A quick passing comment before getting to the mean, I am surprised by your
initial comment on 123, I had not figured that from your previous comments.

I heartily agree with
- load workbooks with the same base name (the lack of this is a real joke)
- XLODBC addin
- the CELL function
- proper 3d formulae (that work)

and I agree with Biff that the CF dialog needs to be re-written, it is a
car-crash. I think the NameManager dialog also could be miles better, as
could DV.

I think the extra rows/columns was a missed opportunity (see
http://www.datapigtechnologies.com/blog/ for July 12) and I personally
wanted something like you describe for WingZ (something I have not come
across before), a user governed expansion.

I want ribbon customisation, including easy reload of the ribbon,
dynamically add to same, ability to re-locate ribbons, no hosing of the
ribbon when testing.

I want F4 back as it used to be.

I want the macro recorder back as it used to be.

I want a better implementation of colours.

However, like yourself I expect to get none of these. I don't expect to get
as many things broken as we did in 2007, indeed I expect some improvements
in these, but I do expect a lot more superficial gloss, more unnecessary
gizmos added to charts, more clutter. I am sure you have seen the leaks as
we have and no the big ticket items coming, but it is in the bits added
around the side that concern me.

I still think you should post this to the blog!

Bob
 
H

Harlan Grove

Bob Phillips said:
A quick passing comment before getting to the mean, I am surprised by your
initial comment on 123, I had not figured that from your previous comments.
....

Clarification needed. I still believe 123 Release 5, the last one
specifically for Windows 3.x, was the best spreadsheet for its own
time. As a spreadsheet it was better than Excel 5 even though it
lacked array formulas. When Excel 97 came out, it was a bit better
than 123 Release 5. Then Lotus soiled itself with SmartSuite 97, which
was such an incredibly bad 'upgrade' that Lotus lost and deserved to
lose. So Microsoft's wasn't entirely responsible for its win; Lotus
gets a lot of responsibility for its loss. Still, Microsoft did most
things right, Lotus most things wrong in their respective 97 versions.
I want the macro recorder back as it used to be.
....

I've seen blog comments that Excel 2007 was a thorough rewrite. If so,
then backfilling triggers for macro recorder would be a problem.
However, like yourself I expect to get none of these. I don't expect to get
as many things broken as we did in 2007, indeed I expect some improvements
in these, but I do expect a lot more superficial gloss, more unnecessary
gizmos added to charts, more clutter. I am sure you have seen the leaks as
we have and no the big ticket items coming, but it is in the bits added
around the side that concern me.

I've read about slicers and sparklines. whoopie!

The concentration on pivot tables is telling. From my perspective, it
seems Microsoft believes most 'analysis' done with Excel involves
pivot tables. They may be right. It also indicates that the age of
spreadsheets as platforms for serious analytical modeling is drawing
to a close. Spreadsheets will still be used for reporting, and that
seems to be where Microsoft is adding a little functionality.

Nothing would prove or disprove my pessimistic assessment of
Microsoft's target audience for Excel going forward better than their
continued inaction on the MOD and MODE functions. I know I repeat this
ad nauseum, but these would be obvious and simple fixes to bring Excel
up to the level of Gnumeric, which has become a better spreadsheet
than Excel even if it may never be as good a reporting platform as
Excel.
I still think you should post this to the blog!

You mean Simon's or get serious about one of my own? I actually prefer
newsreaders' ability to show multiple branch response trees. Besides,
USENET newsgroups weren't [originally] meant to be just Q&A forums.
 
B

Bob Phillips

Clarification needed.

Thanks, that makes sense now.

I still think you should post this to the blog!

You mean Simon's or get serious about one of my own? I actually prefer
newsreaders' ability to show multiple branch response trees. Besides,
USENET newsgroups weren't [originally] meant to be just Q&A forums.

Well if you get serious about one of your own, that would be a great
addition to the fold, but failing that I do mean Simon's. I am sure he could
relax it so that you could post an item.

The multiple branch response trees are a useful part of NGs I agree, but as
they are used today I think you will get a better discussion on a blog, with
many people who never visit the NGs.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top