Tag Archives: standards

Excel

Excel standards

Just recently I’ve been getting into web development stuff a lot.  Part of my job has changed and as a result I’m now not only developing for Office, but making tools that can be used via the web.  It’s a lot of fun learning this stuff, and kind of reminds me of my early days of picking up the basics of Office and VBA.

The one thing I’m noticing a lot with the web development stuff however is the emphasis placed on building web-based applications to web standards, incorporating accessibility and a separation of concerns.  That is, that semantic content, style and logic (ie the code that drives the application) shouldn’t overlap, but should be stored as separate files to make them not only easier to manage but to ensure they meet web development standards.

In fact, if you are developing for the web there is a validation tool at validator.w3.org which reads your html markup and tests it against current web development guidelines to ensure that your code is ‘valid’.  Things like checking that html tags are closed properly and that images have an ‘alt’ attribute are two such ‘rules’ you need to follow to ensure your markup is ‘valid’.  Valid markup and CSS have many advantages – your SEO is improved and you can guarantee that your pages are accessible to screen-readers, for example.

Whilst learning all this new stuff and trying my best to stick to the rules I got to thinking about development for Office products, mainly Excel.  Is there a ‘standard’ to stick to when developing Excel workbooks/applications?  I can’t find any kind of software which can check your work against any kind of predefined and acceptable ‘standard’.  I did a tongue in cheek post about the things I hate about your spreadsheet a couple of weeks back which kind of hints at some of the no-no’s, but what about general best practises?

Some things I would suggest are basic common sense:-

Variables should always be declared in code
Option Explicit should be mandatory 


Where possible, names should be used for ranges rather than absolute or relative references.
This makes your work easier to read, manage and maintain, both for you and others who might inherit your work.  As an example, this:-

=VLOOKUP(A1, Salaries, 2, FALSE)

Is much easier to read than this:-

=VLOOKUP(A1, Sheet2!$B$12:$F$13, 2 FALSE)

Isn’t it?

Worksheets should be named based on their contents.
No default worksheet names should appear in your work.

Unnecessary calculations or ‘helper’ cells should be hidden from reports if they don’t help in displaying your data.

Color should be used sparingly and to useful effect.
People with version of Excel prior to 2007 take note – the standard color pallette provided by Excel is pretty horrible and limited to a selection of about 56 colors.  One of the first things you should do is go into the options and change those bright, garish colors to something more pleasing on the eye.  Personally I like pastel shades rather than heavily saturated choices.  It’s a personal thing, but I think it makes a real difference.

Your code should make sense and be commented.
Comments are king in programming, no questions asked.  If others are to inherit your code, it’s so much easier to grasp the role of a sub or function if you define what it does.  You should especially comment particularly complex or tricky sections in your code so that they know what was going on in your mind when you gave birth to it.  It helps you, too.  I often come across code I’ve created in the past and been thankful that I left instructions for my future self on what I was doing all those years ago.

Charts
Charts would require a whole section just to themselves, and are a controversial matter for some.  The key with the visual display of information as far as I am concerned is similar in some ways to the semantic web – the display should be clear, uncluttered and get the message across in as plain and simple a way as possible.  Due to the objective nature of this subject it would be nigh on impossible to write a full specification of standards, but leaders in the field such as Stephen Few and Edward Tufte have made great inroads to doing this.   Reading any of their work or any of these blogs:-

Bella Consults
Excel Charts
Junk Charts
Peltier Tech Blog
Storytelling with Data
Visual Business Intelligence
Visualising Data

..will see you on the right kind of path.  There’s loads more to read out there, but adding these to your regular reading list will help you understand what is perceived as right and wrong in terms of building visual displays for data.

There are a heapload of further suggestions I could throw into the mix, but I could be here forever.  I find it interesting that there aren’t really and solid, defined and followed guidelines into correct spreadsheet creation, or at least none that I know of.  I did come across this, and it seems like a reasonably good start, but it would seem that there are no working groups to speak of to ensure that Good Practise is followed.

I know comparing spreadsheets to the web is like comparing chocolate to lettuce, but I do feel that there should be a set of common standards/guidelines that professionals should stick to and beginners should be made aware of from, well, the beginning.

What are your thoughts?  Have you come across any kind of global development guideline that I’m not aware of?  What other ‘rules’ would you have in place for spreadsheet application development?  Answer in the comments..