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