Annoying file format warning when exporting CRM records to Excel

Pointless error message dialog box

When you export from CRM to Excel the data is derived as XML, saved with an XLS file extension and Excel is invoked to open the temporary file. Unfortunately Excel checks to see if the file being opened is actually of a type which matches the file extension and tries to be helpful. Normally this is to help overcome problems such as a comma-separated variable (CSV) file being saved as an XLS file extension, which ought to mean Excel tries to read the XLS file, fails because the contents are nothing like a real Excel binary file and gives up. Instead, Excel actually looks at the content, spots that it looks very much like a CSV and allows you to open it just as if the file extension was correct in the first place. However, this cleverness is tempered somewhat by the fact that the default setting for this is to ask the user every single time what they want to do.

As always, this is probably intended to be a helpful warning and prevent people opening files which might have insecure content, but it fails to do so because most users do not understand the implications and the longwinded message is probably not even read properly anyway. Certainly the 50th time someone sees a dialog like the one below, they just click “yes” without reading and it no longer provides any benefit whatsoever (by the way, I have done nothing to this, it displays in this ridiculously wide, un-resizable window on my machine).

Click to see larger version - CRM Excel export error message

Whenever I have managed people in IT support roles I try to eliminate fixes which involve things like “ignore that error message, just hit OK and it will work fine”. This not only numbs people to the meaning of that particular error message but to these sorts of warnings in general. Too often I have heard users explain why they did not report a problem until it was too late, saying “well, I got an error every day saying something about faulty disk or something but I just clicked OK, like John said we should with that other one…”. Find the root cause, eliminate the error, or suppress the error somehow, don’t teach people that errors don’t matter or they just ignore them. If you went to your doctor and said “it hurts my neck when I lift my arm up” you would not be impressed if she replied “then don’t lift your arms up!”, would you?
Read on to find out how to stop Excel asking unhelpful questions when you export records from CRM»

Awesome quick formatting of chart elements in Excel 2010

I just discovered a minor enhancement to chart controls in Excel 2010 that makes a really big difference in terms of how long it takes to quickly add the formatting I need.

When you right click something in your chart you see the usual right click menu as with Excel 2007 but now you also get the floating mini-bar above it as shown below (click for larger version)

Excel 2010 chart formatting mini-bar

On the right hand end of this you can see the selected element (Series 3 in this case) which you can then directly format, but even more usefully, you can use the drop down here to select another element, format that, then another and so on, and the mini-bar stays on top throughout.

Excel2010 chart formatting2

Now of course you can achieve the same thing by selecting elements on the Chart Tools|Layout or Chart Tools|Format ribbons, but for some items like labels and axes you want to change font properties and that means going from one Ribbon to another. If you want to rapidly change the colours or borders of various things, make the gridlines and plot area less obvious, alter the fonts of data labels or axes and other quick changes, you can do all of this without ever leaving this mini-bar area

Read on to find out more about using the mini-toolbar to change chart formatting»

Draft whitepaper about improvements to functions in Excel 2010

I don’t often write posts that simply say “hey, did you see this post over here?”. These echoes in the blogosphere don’t really add much value, and are sometimes symptomatic of people being measured by how many blog posts they write to meet some arbitrary marketing activity metric, rather than adding quality.

(Aside: the same applies to a series of posts about 6 related features of some software or comparing 5 alternative products which would have made much more sense written as a single cohesive article, but failed to tick the box for 10 blog posts per month. You get what you measure, or WYMIWYG)

But today, I though this was important enough to just say – have you seen this post on the Excel Team blog about improvements to functions in Excel 2010? Now that one’s a week or so old now, but today there was an even more important post with a link to a draft whitepaper with more information.

I have linked to the post , not the pdf file directly as it is only a draft, and hopefully the post will get an edit or at least a comment when a newer or final version is available. This document is for those who like to understand the details, and for any sceptics who might say “well, they said it was accurate last time, how do we know it’s any better now?” – it does sound a bit like washing powder ads who always tell us that this time round it will get things even whiter and brighter and cleaner than ever (just like they said before).

There are a couple of typos in the draft (the floor.precise function for example has an obvious chunk of copy and paste from the ceiling.precise function for example), and there are some things not made very clear (for example in most cases it only describes the new behaviour, not the old for comparison or for explanation of the difference and why the new way is more accurate).

It lists the MOD function in the section on functions whose accuracy has been improved. Did you know that MOD gave inaccurate results on older versions of Excel? I didn’t. In fact, it doesn’t – it gives a completely accurate result or fails with a #NUM error if the divisor goes into the number more than 2^27 times as described here. I think this is distinct from being inaccurate in the way the statistical functions have often been criticised.

Similarly the RAND function is listed but its problems not described in any detail – I can only assume that the function is flawed in that it does not give a perfectly even distribution of results and is therefore “weighted” to some extent. Since I only ever use it to produce dummy data for examples used in my Excel training courses, it does not really matter to me if it not truly statistically pure, but I am sure to others it is vitally important what algorithm is used to generate the results (it’s now the Mersenne Twister, for those who care, but this fact is not from the whitepaper, it’s in a comment to the original post made by Jessica Liu).

Anyway, the bugs and inaccuracies that are discussed in the whitepaper are all now fixed (but it does not say this in the whitepaper, merely leaves it implied), and already works in the Technical Preview. Some of the other changes came too late for TP but should be in the public Beta when that gets released.

I expect the changes to naming conventions will also help people who use the statistical functions a lot (I’m not one of those) or have to make sense of others’ work. The convention of .precise added to a function name seems to mean “according to a precise definition” rather than “inherently more accurate”. I would have though .strict might be less ambiguous and similar to the use of the term in other fields (eg web design using XHTML versus XHTML strict).

Has anybody had real issues with these inaccuracies in the past? (and had you even noticed?)

Are you using other software tools to avoid the problem in Excel, and will these changes allow or encourage you to switch back?

A couple of quick Excel 2010 discoveries

At the moment I’m revisiting pretty much all my course materials for my Microsoft Office training courses, partly to restructure them into different chunks, and partly to start work editing where necessary to include coverage of Office 2010 so that I will be ahead of the game when that gets released next year.

Along the way I’ve been finding out loads of cool things about major new features such as sparklines and slicers (more on that in a future post, as promised), and the ability to customise the Ribbon easily without writing code. There are also lots of tiny changes as well, which are easy to miss and may get drowned out in the sea of other news about the next version, so I thought I would mention a couple of them here – the status bar summaries and filters in Tables.

Read more of this post

Excel 2010 new features – Sparklines

As discussed previously, we have some marketing information about what we will be seeing in the next release of the Office system, but not really a great deal of technical information. The Excel team are starting to blog a bit more now that the Technical Preview is underway; their 10,000 foot view is a good starting point to find out what’s coming, or you can read the press release. On the official Office 2010 site there is a video by Albert Chew, Product Manager for Office, which shows off some of the new features of Excel 2010 (sorry, no direct link to the video available, it’s linked in the menu on the left of that page). On the Microsoft PressPass videos page there is an Excel 2010 demo video which you can also download (16MB wmv file). This covers two new features – sparklines (from the start) and slicers (from about 1min 55 into the video)

As more information emerges, I’ll write in more detail about some of the new features. Today let’s have a look at probably the most eagerly awaited extension to Excel’s data visualisation capabilities – sparklines.

Sparklines in Excel 2010

Sparklines are very dense microcharts used to display simple information, usually showing historical values to give context to the current data. The term was coined by information visualisation guru Edward Tufte and discussed in a whole chapter in his book Beautiful Evidence, which describes sparklines as “intense, simple, word-sized graphics”.

Examples might include past share values, commodity prices, exchange rates, or internal business key performance indicators. Lines may have key points highlighted (high value, low value, last value), show a trendline or normal band, but otherwise will be deliberately uncluttered to aid easy interpretation.

Typical sparkline sales versus targetThe example to the right shows the previous 12 months sales and target, with the highest and lowest sales figures highlighted. Some people might prefer to show the figures to the right of the sparkline as they relates to the final data point and therefore the right hand end of the plotted line, but this is a matter of personal preference. Although this example is show here in quite a large screenshot, the trend is very clear at much smaller sizes too.

While lines are by far the most common choice for sparklines, especially to show changes over time, other formats may be found – columns to show breakdown of a total by category for comparison, for example. Another popular use is to track success and failure, such as wins and losses for a sports team, a technique described well by this article on sparklines at Bissantz, the creators of SparkMaker, an add-in for Excel.

Read more of this post

Is the Office button a menu or a dialog box?

Another of Simon’s excellent posts about the Ribbon and other parts of the Fluent UI in Excel 2007 has prompted me to respond. Read the ribbon file blunderfest, where Simon says (I snipped a few bits out here for brevity, and the bold is mine):

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

So, does the Office button bring up a (poor) dialog, or is it just a menu?

Sorry Simon but I have to disagree with you on this one (I seem to recall being told I was the voice of balance on smurfonspreadsheets by someone…).

Just because you think it’s a dialogue and call it a dialogue does not mean it is a dialog or should behave like one. Shredding a straw man / ribbon does not make a valid argument. To me it looks and behaves pretty much like I would expect a menu to behave:

Read more of this post

Excel cell styles – useful feature or waste of ribbon space?

I agree with Simon in his article about the usefulness of cell styles in Excel, where he says:

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…then you get a right royal style mess.

Cell styles as a concept seem pretty weak to me. The built-in ones are hopeless; I know hardly anyone (actually no-one that I could name right now) that uses them.

I have recently done some extensive work for a client on a set of templates, themes, etc for the whole Office suite. For the Excel templates I included some cell styles to make it quick to format things in “corporate” colours for headings and so on (as well as default table styles for the same reason). This provides user convenience and helps them create more consistent documents with more of a “branded” feel to them.

As to imposing a regime of “pink means bad” and “orange double underline means linked” (linked to what?), no chance.

Why styles don’t address the real need for good formatting

I teach students on my Excel training courses that formatting of spreadsheets should be used for three purposes:

  • to highlight (data outliers; estimates as opposed to actuals)
  • to group or associate data together (months in the same quarter or year having a light shaded background say, next group no background; using matching colour for axes and lines in a two-series chart with two different scales)
  • to separate data by category or type (line above the first month of a new year; making the title row bold)

These principles of using formats to help interpret the data, rather than help it look pretty tend to get people focussed on the task rather than the appearance. The built-in cell styles only seem to address the concept of highlighting, rather than being useful for grouping or separating. The highlighting they provide seems arbitrary at best, and quite likely to cause headaches with some of the colours involved.

Read more of this post

Excel 2007 calculation bug fix released after two weeks

A fix for the Excel 2007 calculation bug affecting results around 65535 and 65536 has been released in the last few hours. The Excel team blog post says:

As of today, fixes for this issue in Excel 2007 and Excel Services 2007 are available for download…We are in the process of adding this fix to Microsoft Update so that it will get automatically pushed to users running Excel 2007 or Excel Services 2007.  Additionally, the fix will also be contained in the first service pack of Office 2007 when it is released (the release date for SP1 of Office 2007 has not been finalized).

Microsoft knowledgebase article KB943075 discusses the fix and gives the usual details for what versions and sizes the updated files should have after the fix. The version number of Excel.exe is altered from 12.0.6024.5000 to 12.0.6042.5000. Now read that again – yes, easy to miss the difference from ’24’ to ’42’ if you look too quickly. (NB: you may have a different version, mine is at 12.0.6024 after installing the security update as per KB936509, as far as I can tell.)

The download for the fix for Excel 2007 (33Mb exe file) is linked from the Excel team blog as well as from the KB article. The blog post also has links for Excel Services 2007, both 32 bit and 64 bit.

Excel 2007 bug shows wrong answers to simple multiplications

This is a follow-up post to my earlier one about a bug in the way Excel 2007 displays the results of certain calculations. Read that one first of you have not already done so.

A few people in the comments thread in the Excel team blog post about the bug seem to have some misconceptions about the seriousness of the problem. Some have asked how often it is likely to come up, implying that they think it is vanishingly unlikely. This seems to be particularly those who have misunderstood that the example of 850*77.1 is only one simple example which is easy to remember and to type, but there are several more simple ones as well as thousands of other combinations which lead to the buggy result (due to floating point rounding errors in the calculation hitting a result which is sufficiently close to 65,535 to cause the false display of 100,000). Nine examples are shown in the screenshot below, and in a table you can easily copy and paste to try this for yourself.

» See some simple examples and find out more about functions which reproduce the error, and which ignore it safely»