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.

They also apply too aggressively for my liking. For example, if I apply a format to a cell to show that it is an estimate, and someone else wants to style it to say it should be checked (or updated or whatever), my original formatting is irrecoverably lost. Resetting the cell style to “normal” simply applies this instead. By using precise formatting I could control this better – for example my estimates might be indicated by use of italics and a different font colour. I could then highlight this cell to be checked by adding a border or background fill, which can be separately removed later once done, leaving my original font format in place. Or I can change the format to represent an actual rather than estimate figure, but still leave the border to make sure it gets checked.

Cell styles are all-or-nothing, and that does not work for me.

Surely styles help people apply formatting more quickly?

Don’t get me wrong, I’m not for people formatting everything by hand, one property at a time. Paragraph styles are a great way to format Word documents (the only sensible way for a consistent approach to layout and formatting). How a document is structured and how it looks are closely tied together, and providing users with an easy way to show heading hierarchies, properly legal-numbered lists and so on is really valuable, and saves a ton of time versus bold, italic, indent-a-bit, font size 24 etc. Re-using the hierarchical structure this gives for things like table of contents or an outline make good sense too. Marketing departments typically like templates as a convenient way to give end users some built-in branding and good design, making it easier for everyone to create documents which look similar, and meet the guidelines they have chosen for colours, layouts, even choice of bullet points. I just can’t see how this sort of model translates to Excel.

Colour schemes do make sense to help “brand” Excel documents with corporate colours for headings and the like, and to make sure that visual elements such as charts or diagrams have a sensible and consistent palette (not necessarily in corporate colours, which may be too saturated or distinctive in many cases).

Cell styles don’t seem to meet any actual need that is not just as well addressed for the majority of users by the format painter tool, and learning to use it properly – I have lost count of the people I see on intermediate or advanced courses who have never double clicked this to lock it on and apply a format to multiple targets, whether in Excel, Word or PowerPoint

Workbooks that have lived for a while in 2003 and been reformatted over time seem to generate loads of horrid pointless cell styles when converted to 2007 (equally PowerPoint files create large numbers of colour schemes so that existing slides can retain their colours while still pretending to be theme-aware). Copying sheets between workbooks seems to compound this (although because I hardly even look at the styles gallery it is hard to know exactly how bad this gets). The style gallery quickly becomes useless, and as Simon highlights, it is tedious beyond belief to sort out. Some kind of proper style manager interface might redeem this. I tend to crack open the zip file and reach for Notepad++ for some XML-hacking – not what I would expect most users to do, or even many power users.

Styles gallery is a waste of space – literally

My biggest gripe with cell styles is that for a feature which is very rarely used it gets given a lot of space. Right now my ribbon is maxed out on my 1920×1200 widescreen. The Home ribbon is 47cm wide, and the styles gallery is showing 10 items in 5 columns taking up 15 cm – nearly a third of the ribbon. The same space could show various groups which many people might find more useful, any of these pairs would fit in the same space or less:

  • names and formula auditing
  • page setup and scale to fit
  • changes (protection) and workbook views

I know some things have been made more prominent on the ribbon to get people to consider features they did not even know were there – conditional formatting and defined names being two of the most underused in my opinion, and I’m very glad to see them given a useful amount of space. But getting people to use cell styles simply won’t set the world on fire, or make spreadsheets more efficient, or easier to maintain, or less prone to user errors. If anything, they distract from good practice and promote eye-candy – will “death by PowerPoint” spread to “death by spreadsheet”?

About ukcrmguru
I'm an MVP for Dynamics CRM, consultant, Microsoft Certified Trainer and self-confessed geek. I also lead the UK CRM User group when I'm not too busy with all that.

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

  1. Bob Phillips says:

    … conditional formatting and defined names being two of the most underused in my opinion …

    Yeah, I agree, but as I said on Simon’s blog, they have really creamed CF! And their NameManager is a joke compared to the real NameManager (as written by Jan Karel Pieterse).

  2. jonpeltier says:

    The Office 2007 motto: Style over Substance.

    They could extract the style gallery (put it onto a new “Styles” tab!), and in its place put a few useful controls, like Print, or File Open.

  3. Kyle says:

    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)
    _________________
    Sorry, but I have to disagree with you here.

    Spreadsheet do not have to be “boring” to be effective. The utilitarian use of formatting has it’s place, however…

    Creative formatting can draw users in and create a more organic feel to something as harsh as “raw data”. The acquisition of completely new data can be facilitated if it’s displayed properly. I also find that people who use my spreadsheets for the first time tend to be faster at figuring out how to use them, where to enter data, what kind of results to expect, etc. Much faster than they are with our other co-worker’s utilitarian style spreadsheets.

    …just my two cents.

    • Adam Vero says:

      Kyle, thanks for the feedback. I agree that spreadsheets don’t have to be boring, they should be attractive and easy on the eye. I guess I find that with the right guidance people can create spreadsheets which are easy to understand and to find the data they are looking for, as well as looking polished, professional and pleasant to use.

      For example, using a background colour (maybe a pastel or a very light grey) to group together the months of each quarter so that there are stripes across the page for three rows at a time makes it really easy for the eye to follow across (much easier than alternating rows or loads of gridlines), and you get the bonus of being able to easily see and relate figures within a quarter.

      What I really find frustrating is some users who seem to want to use every formatting feature available to them just to add decoration for its own sake, which makes me wonder how they had so much spare time after analysing the data to waste on this (or maybe they spent all day colouring things in and never actually analysed the meaning of the information at all).

      They seem to need to colour the background of every cell that has any kind of data, label or formula in it, without any obvious reasoning for the garish bright colours they have chosen. Maybe all the sales figures have a yellow fill, the budget figures have magenta, the salary information is in cyan, the salespeople’s names in a lovely lime green and the customer names in fetching fuschia. Most of the sheet in in Arial 10 point, but some labels are in an unreadable 7 point font (to make the text fit in the cells, of course). In the column for variance (this month vs last month’s sales, for example) all the negative figures have a dark red background to show they need to be looked into. This is of course added by hand every month rather than using a conditional format.

      Some of the figures, totals in particular, are in red, to remind people that they are important – though ironically this makes them less legible rather than more, especially when printed on the black and white office laser printer. The accountants always have to re-tune their brains and ignore their natural instinct to assume these figures are negative, which slows them down.
      All cells are centred, including numeric data of varying length, making it really hard to actually compare anything, or to spot the cells which have numbers stored as text. And every single cell has a black border line round it for no obvious reason at all.

      So that’s the kind of thing I come up against when working with customers to help them build models to make more effective decisions and I ask to see what they are working with at the moment. Not all the time of course, but often enough to find it really frustrating. The first step to fixing this is always one of realisation – understanding that there is a better way to approach this.
      From the sound of things, Kyle, you are streets ahead of this and actually making good use of relevant, helpful formatting to make your spreadsheets easier to use. Now you just need to spread the word to your colleagues and get them to aim for usability, legibility, comprehension, and efficiency rather than a Technicolour nightmare.

  4. Ryan says:

    I actually like the cell styles.

    We use them in our office (accounting/auditing) when reviewing survey responses to denote items we feel are either good, bad, or require follow up. Conditional formatting would be too cumbersome for us to use so we have begun using styles to make things easier on the partners, etc. doing review.

    • Adam Vero says:

      Thanks for your input, Ryan.
      I must say I would probably go for conditional formatting for the sort of thing you describe. Use Data Validation on the column in question to give you a picklist of choices, make sure they all begin with a different letter to make it even easier to do data entry by “type and tab” approach, and conditional format the cells according to the value selected. You might have values for good, bad, requires follow-up and completed follow-up for example.
      This also means these values can easily be used elsewhere such as in filters or to summarise in PivotTables (how many responses require follow up, per month, for example). Using styles to apply a visual setting is all very well but can’t easily be used to get any insight into the data you are gathering.

%d bloggers like this: