Good Stuff in Excel 2007

We all had great fun the last couple of weeks giving the ribbon and the User interface disaster a richly deserved kicking.

So to regain a bit a of balance I thought this post should focus on the good stuff in Excel 2007.

To summarise: the Excel product team added some useful features, the ribbon team made them hard to use.

I’m not going to do a bunch of user guide style stuff here, its 2009 FFS use your search engine (or decisions support system) of choice to find the many good ones already written.

My favourite new features are:

1. Remove duplicates – simple and effective, and a mild PITA before
2. Pivot tables – they made them easier to use for nooBs and maintained the old skool for those that fear change. I didn’t like the way the task pain (sic) encroached on my screen – then I discovered you can tear it off and float it like a dialog – best of both worlds – neat.
3. The new conditional formats are handy enough – I know some are underwhelmed, but the 2007 ones do make the 2003 ones look, well, so 2003.
4. IFERROR and SUMIFS, not too many new functions, but these 2 are massively useful. The CUBE functions are okay too, if you have access to a suitable data source. They are so much neater than GETPIVOTs. Making the ATP stuff native is v useful actually – now you can be sure the user will not get #NAME? errors if you use EOMONTH etc (they may get #N/A data loss in .xls files but we already covered that way back.)
5. The black colour scheme is easy on the eye.
6. The status bar has loads of useful info now, including the zoom which is handy, if someone nicked your scroll mouse for example.
7. COM add-ins now shows HKCU AND HKLM – no more sneaky COM add-ins messing things up. NOTE: if your Excel 2007, or other 2007 app, is acting flaky COM add-ins have proven to be the number one suspect.
8. Sharepoint integration, I’ll cover this some more but the Office 2007 client + Sharepoint (even just sharepoint services) story is pretty damn good.
9. Tables – these are actually worth using – unlike lists in 2003. Great as a pivot source – the table expands as new rows get added, and that is reflected in the pivots without needing to change the data source, just refresh.
10. Structured reference in tables are neat too, especially once you have worked out the relative/absolute effect of dragging v copy pasting
11. The sort and filter stuff seems a bit more obvious now, and is easier to use, and pretty smart (sort A-Z – Excel sees a columns of text, Sort largest to smallest, it sees numbers, earlierst to latest it’s dates – handy early warning when you mess up your data types).
12. I think Find is a bit more obvious too, especially what used to be tucked away behind select special.
13. The name manager is much better than the previous name editing dialog.
14. Function autocomplete, I think will lead to less silly errors with things like using the 3 argument lookup when the table is not sorted.
15. Trusted locations give the best of both worlds – I don’t get nagged about my own VBA but anything not in my safe directories gets a warning.
16. being able to make the name box wide enough to see useful names is good.
17. being able to extend the formula bar down to see other peoples dreadful long formulas without hiding column letters (or names if you’re in a table) is handy.
18. Multithreaded calc, and larger memory addressing sound good, I havent really seen the impact yet, but I expect I’ll be working with those sorts of monsters soon enough. Shame MTC will never include VBA UDFs, better VBA UDF performance would be brilliant. 2010??
19. What others have I missed?

Having read this far, I’m sure you are wondering if I can complete a post about Excel 2007 without a dig at the UI.

Yes

Cheers

Simon

(3rd sentence doesn’t count surely?)

One more for the road

Just before we finish the ribbon kicking fest:

If you think it was a bit harsh or a bit undeserved – dream on! Here is Charley Kyd’s on-line survey showing 80%, yep that’s eight zero, eighty, almost all, Excel 2007 users don’t like it or hate it because it damages productivity.

And his related survey that shows around 30% of respondents using 2007, which means that majority of the spreadsheet market prefer to use a 6 or more years old spreadsheet than the current one.

(I have linked to those surveys before but they are updated regularly so its good to see the changes, or non changes)

In my opinion/experience working in 2007 is worse than 2003, but its also more variable. If you can find the commands quickly then its not that bad. If you can’t find what you are looking for, the keyboard shortcut doesn’t work (most do – well done the Excel team for encouraging the ribboneers to respect that at least), or you work in one of the ‘here be monsters’ areas (charting, drawing etc) then something that is quick and easy in 2003 can be stunningly painful in 2007. So painful many drop into VBA and type the command into the immediate window. Or switch to 2003 and record a macro (the monsters areas don’t have proper macro recording).

Thats it for whinging about the ribbon from me for now, now I want to move on and look at some of the good stuff in 2007, suggestions for migration approaches, and recommended mitigations.

cheers

simon

Biggus Dickus blogging

My mate Dick is back blogging about all things Excel and Access.

nice one Dick – just when I was meant to be working!

cheers

simon

Googles new operating system

The beeb are reporting that Google is due to release a new operating system targeting netbooks.

Are you bovvered?

Thre seems to be a lot of hyperbole in the tech world, but I’m undecided as yet.

Yes a new OS based on the cloud (the very same one MS is boring us to tears with) could be ‘exciting’.(I red-lined my R6 in second the other day (on a private road officer), I doubt a cloudy os will be quite that exciting).

But after a great start in netbooks, Linux adoption seems to have tailed off a little? At first they only had some home made Linux, then they got XP too. Now many of the new ones coming out only have XP. Is compatibility with existing (mainly Windows based) pcs that important?

We have 4 netbooks here, all running Linux, the kids have bagpuss Linux I have Ubuntu NBR (9.04 – with sound finally – yahoo!). Everyone seems happy. I really thought Ubuntu was the way things were going, but it doesn’t look like that right now.

Phone operating systems seem more diverse, and indeed Android is doing well there.

I dunno, Google are one of the few IT orgs that have a chance to push this sort of stuff through.

What do you think? Are you saving up for a Chrome OS netbook?

Cheers

Simon

Blog Stats

JW asked for Excel related blogs to publish their June stats.

I never did bother much with them so I don’t have google analytics or any clue about who reads the feeds etc.

But in June there were 10,000 visitors, dunno how many new and returning etc there tends to be about 400 a day weekdays. I didn’t do that many posts in June so if they were all returners they would have been disappointed.

July is looking better for posts.

cheers

Simon

Right click obliteration

The right click used to be the most useful commands in any particular context. But in 2007 those fail UI engineers just couldn’t help themselves, they had to put another bunch of pointless formatting front and centre. I think it is/was called a floatie, but of course its more of a floater!

Now within the grid it just hides key parts of your work.

And there is no way to turn it off. [Edit sm Ken Puls provides some VBA to disappear this bag of hammers in the comments]

They killed right click in 2007 – its now well worse than useless.

Now I can’t see what the sales figures are for months 2-6, how crap is that?

How hard would it be for them to put in a checkbox in options:

show/hide pointless space wasting right click format toolbar?     Δ

(triangle because I couldn’t find a checkbox symbol)

In Word and Outlook there is the option to not have it on hover, which you have to set unless you are a gynaecologist or postman and can work through that sliver of a gap between the proper right click menu and the pointless format nonsense.

Of course the ribbon team don’t expect us to be actually looking at the content, just polishing it. Thats all theyv’e  done right?

This post marks the end of Ribbon Lovers week here on SOS. Obviously not the end of moaning about the ribbon completely – its too rich a vein of fail for that.

Codematic is on the cusp of releasing a Ribbon enhancement, that will reduce the pain of moving to 2007 by providing 2003 style menus. If its not available by the end of this week I’ll be getting several kickings froma a range of people.

Cheers

Simon

Acer Aspire one Ubuntu 9.04 sound hell

I had my AA0 150 working like a charm with 8.10 but something must have been wrong, otherwise I wouldn’t have moved to 9.04. I think it might have been networking.

Anyway ever since that fateful day when I moved to 9.04 sound has not worked at all. I kind of assumed there was some blunder somewhere and a fix would drop in one day in one of frequent Ubuntu updates. Not so, or at least not yet.

Basically all my sounds stuff has just had some pulse audio null output bollocks, and done nothing at all. All tests were silent. Skype has long since given up.

Today I resolved to find out the problem, having come to terms with the fact those nice Ubuntu people weren’t going to fix it as it was my blunder.

it was a –

Yep a -, or with quotes for clarity ‘-‘. That’s it, (obviously I had to get it in the right place!).

In a previous incarnation I had compiled my own ALSA drivers and at some point added

options snd-hda-intel model=acer aspire

at the bottom of

/etc/modprobe.d/alsa-base.conf

Turns out in 9.04 that needs to be ‘acer-aspire’ (and a quick reboot)

who da thunk it?

In celebration of the joyous, momentus occasion I went straight to youtube for a dose of class:

yerha

have a good weekend (I will, listening to stuff on my pc, I might even skype the Mrs in the lounge (would have been more dramatic and relevant had I still been in sunny Geneva of course))

(I have been buggering around for a couple of hours and I am sure that is the only change I have made – just checked, this is deffo the fix. Dunno if the missing dash was a prior typo of mine? maybe)

cheers

simon

btw my acer aspire with ubuntu post is the 2nd most popular post on this blog – not bad for a mainly Excel on windows related blog.

The ribbon file blunderfest

I already mentioned the lack of file open icon, and previously I have talked about the ridiculous blob. And the initial flashing they had to incorporate to tell us its a button.

But when you actually get closer it just gets sillier – I really wouldn’t have thought that was possible!

When you click and look, if you decide to cancel and move to the traditional cancel location (lower right) and click that button, does it close the file open dialog/ribbon?

Or does it close Excel?

Everyone I have asked (and me) has accidentally closed Excel numerous times before eventually learning that this particular piece of the interface is not ‘normal’.

In fact to cancel that thing you click anywhere else in Excel – and Excel ignores the click but closes the dialog! How ridiculous is that?

They have created a thing that is not as powerful or controllable as a dialog, but is too big and intrusive to be a menu or toolbar so they butchered an existing UI concept – the click away to cancel menu concept to work with this quasi dialog. But dialogs never worked like that before or in other applications. So now Office is the most friction-full application in the widows world (excluding perhaps Ulead products).

I won’t talk about the pain of the new file formats and the shock inexperienced users go through on seeing all the spreadsheet types you can save as.

So the ribbon file/blob shambles:

1. Its massive
2. its not really ‘in’ the command part of the UI
3. No one knew it was a button
4. the flashing just worries new users
5. The cancel button actually closes Excel!
6. Its full of those inconsistent dual controls pretending to be a single
7. combines the worst of menus with the worst of dialogs
8. Why is Excel options there? its not related to files its related to the application

From start to end I think the blob epitomises everything that is wrong with the Effluent UI, and it really shows just how polished the proper Windows style guidelines UI had become. The ribbon confuses and scares beginners and regularly wrong-foots experiences users. A stunning piece of work!

(have a good weekend)

cheers

simon

Ribbon Style Princess

No one ever accused me of being TOO stylish, in any walk of my life. Perhaps thats why I gravitated to Microsoft rather than Apple or Macromedia/Adobe?

Microsofts main stated aim with 2007 was to pull style and presentation front and centre. (some of us might argue that was at the expense of content – but that is a separate rant).

Now Styles in Excel are one of those things that sound good in theory, but are significantly worse than useless in reality. In an isolated world they may work but as soon as you start copying a pasting between workbooks, and who doesn’t (apart from the styles feature team)? then you get a right royal style mess. I was looking at one workbook the other day that had aver 50,000 styles defined in it. It was very unstable.

It was in working with that file though that I noted just how crap the Styles interface is in the ribbon.

Sure, it has the live preview and the styles gallery (well whoop de-chuffing-doo). I spent 20 minutes scouring t’intarwebs trying to find how on earth you might delete a style. Pretty standard procedure right? part of the normal CRUD lifecyle, well the end of it in fairness.

Anyway to delete an existing style you right click on it and choose delete from the right click menu. Hmm, pretty ‘discoverable’ right?

Since when did right clicking a menu item give you commands related to the underlying object rather than options about the menu itself? I’m guessing since the effluent UI?

And what’s with right clicking?

Right click was always a collection of the most contextually useful commands repeated from their usual location. If you wanted to do something unusual then normally the subset on right click would not cover it and you went to the home of those context sensitive commnds to trigger the unusual one.

I still haven’t found the base location of the style-delete functionality. I don’t think there is one. I think the right click concept has changed in the Effluent UI

from:

Collection of most useful commands in the current context

to:

Dumping ground for anything that doesn’t fit our magnificent design

So in summary:

1. Styles are still rubbish
2. Styles are even harder to delete
3. Right clicking the UI is now totally unpredictable, you may get no effect, options about the UI or options about the object to which the UI relates.
4. Right click is now the bin for things that don’t fit the woeful UI design

Note on tense:

My use of the past tense for some parts of this should in no way be taken to indicate I have moved on from Excel classic, I havent.

Style misery can be reduced by avoiding copying whole sheets and just copying cell values and formulas, or by just using minimal formatting and let your analysis speak for itself (if it can!).

cheers

simon

Ribbon screen stealer

We all know the ribbon is just a fat clumsy toolbar. And if you followed the big marketing push to try and convince us it is not big you’ll know that it is apparently 140 pixels deep.

Heres the odd thing though – the smaller your screen, the bigger the ribbon gets. Not just bigger relatively – bigger really.

As the screen gets narrower the ribbon extends itself down over another few lines of your work to make sure you can still see all those critical formatting options. (at the expense of a few rows of your irrelevant spreadsheet)

In the real world people run their screens at different resolutions. I guess a 19 or 20″ inch screen is pretty much standard. But that doesn’t mean everyone is running 1280 x800 or better. oh nonny no.

And the ribbon being the ribbon the controls adjust in completely random ways to ‘accommodate’ different screen resolutions.

I had a support call;

“Please can you tell me where on earth they have put the bloody fill options?”

Well Derrr!  – “Certainly – Home tab, right hand side, where it says Fill”

“I don’t see it”

…

Eventually I went to see, sure enough, he was running a lower resolution than me and his UI just showed the buttons not the text (as above).

I seem to remember reduced support costs due to consistent UI control layout as being one of the fantasy benefits claimed for the fail UI.

From personal, enterprise support experience the ribbon will increase support costs (dramatically), and it is not consistent and stable, and it will present differently on different peoples machines, and there is no way to ‘reset to default’ unlike in Excel classique. You have to physically go to the users machine, or remote to it to work out what they are seeing.

So two big problems with the big fat clumsy toolbar

1. It gets bigger vertically as screen resolution decreases
2. It shuffles commands even more between different screen resolutions
3. Its HUUUGGE

cheers

Simon