Category Archives: Excel

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

Excel Hints and Tips

Ten things I hate about your spreadsheet

It’s been a long day and I feel like having a whine.

Here are ten things I hate about your spreadsheet:-

  1. The fact that you’ve created a new workbook but not saved it with a meaningful name before emailing it to me.  No file should be named “Book1.xls”.  Ideally you should save it to your computer first and then email it to me.  It makes things better for all of us.
  2. Flashy Wordart to make it look ‘prettier’.  Don’t. do. it.
  3. Quickly thrown together charts with default settings, especially in Excel 2003…yuck.
  4. I hate that pie chart.  I really hate it.
  5. Please don’t use garish background colours to highlight cells/rows/columns – I’ve got a migraine and it’s not helping.. Also red text with a blue or green background (and vice versa) is bad!
  6. Broken links to your external workbooks.
  7. Spelling mistakes are unforgivable.  A kitten will die for every one I find, and it’s your fault.
  8. Every time I see a formula error (#VALUE!, #N/A, etc) I die a little inside.  Do something so they don’t appear, if you can, especially if your workbook is being seen by a few people.
  9. In-cell comments are great, but if they don’t add value to the work you’ve done (or just serve as a reminder to yourself to do something), get rid of them before you send me your file.
  10. Is that Comic Sans?  There goes another kitten..
Excel Hints and Tips

Custom lists in Excel

If you type ‘Monday’ into any cell in Excel and then click and drag the chunky square fill handle on it’s bottom right down, you’ll notice that Excel automatically recognises that you’re using weekdays and, when you let go, et voila, the days of the week are filled into the cells you highlighted:-

 

Handy stuff!  Excel has some default lists built in for days of the week and months of the year, which in most cases would be all you need, but what if you’ve got lists of your own that you use regularly?  You’re in luck!  Excel allows you to roll your own.

In this example I want a custom list of seasons which I will use in a gardening spreadsheet.  So, when I type in ‘Spring’ and use the fill handle I want ‘Summer’, ‘Autumn’ and ‘Winter’ (if in the UK) to follow.

Go to Tools>Options and select the ‘Custom Lists’ tab from the dialog:-

As you can see, the existing lists appear in the right hand list box.  At the top of the box is an entry called ‘NEW LIST’.  Select this and click in the ‘List entries’ box to its right, and enter your new list, pressing enter in between each entry until you get something looking like this:-

And now, if you click the ‘Add’ button on the right, your new list will be created and will appear in the ‘Custom lists’ box:-

Easy, isn’t it?  Now click OK and you can start using your new custom list as you did with the weekdays earlier:-

And that’s your new custom list created!

This is really useful if there are lists you regularly use in your daily business, and can really save you time.  I’m not sure if you noticed, but at the bottom of the Custom Lists dialog, there is also a control you can use to refer to a range of cells which contain a list, which you can then Import by clicking the button.  Give that a try as well -it makes things a lot quicker, especially if you already have a long list available and don’t want to type it in again.

PS – apologies for the lack of posts recently.  Things have been picking up at work lately and time hasn’t been readily available.  More stuff to come soon.

Charts Excel

Editing Excel charts with Illustrator/Inkscape

I only discovered this little gem a couple of days ago, but it’s such a useful little hint that I thought I’d share it with you guys.

Excel charts allow you to customise to a pretty decent degree, but you don’t get full control of how everything looks, which can make it a little restrictive.  If you have Illustator or Inkscape however, you can get some control back and edit everything down to the most minute detail.

If you select your chart object in Excel and CTRL+C to copy it, you can then paste the chart into the graphic design studio of your choice.  I use Inkscape as it’s free and open source, but I expect other software such as Illustrator will do the same.

Once the image is pasted in, you can un-group the individual chart elements and from that point forward you can edit every part of your chart so that it looks exactly as you want it to.

That doesn’t mean however that you should then use your new tip to make your charts look horrible.  Just saying.