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

Excel Functions and Formulas Hints and Tips

10 (actually, 18) Excel functions you should learn in 2012

Happy new year 2torians!  With the Olympics and pending end of the world upon us this year, what better time to start learning more about Excel?  In this article I want to quickly breeze through a handful of the essential functions that I think you should take the time to learn about this year.  Not only will they take your mind off the inevitable demise of the human race, they’ll no doubt make you better at Excel for the short time you have left :)

Over the coming weeks I will go through each of these functions and explain more about their workings, but there’s absolutely no reason at all to wait until then.. Why not Google search for them in the meantime and learn how to use them for yourself?  You get such a warm and fuzzy feeling inside when you learn something for yourself.  In fact, self-teaching is the way most Excel experts earned their stripes!

1) VLOOKUP and HLOOKUP

I know, there are two functions here.  I’ve grouped them together because they operate in much the same way, only in a different direction. The lookup functions are more than invaluable in your wranglings with data.  The ability to combine multiple sources of data into one alone is worth the time spent figuring out how they work.  Whilst how they do work might confuse to start with, once you get to grips with them, they really are like magic (and will save you tons of time to boot).

2) LEFT, MID, and RIGHT

I’ve put these three together as one rather than individually as they all more or less operate in the same way with text data.  These three useful functions allow you to pull out useful stuff from otherwise useless or meaningless strings.

3) LEN

How long is a (piece of) string?  Used in conjunction with LEFT, MID and RIGHT, the LEN function can be an extremely powerful ally in your data manipulation efforts.

4) COUNT, COUNTA AND SUM

Again, I’ve lumped these together as they operate in very similar ways.  SUM does exactly what it says on the tin, and adds together provided values or a range of values.  COUNT and COUNTA do more or less the same thing (that is, counting the number of values provided), but in slightly different ways.  I won’t explain how at the moment but you’re more than free to explore for yourselves – just make sure you use the right one for the results you want.

5) IF

The ‘main man’ when it comes to adding real power to your spreadsheet work, the IF function allows you to add decision-making capabilities to your work, and used correctly can almost turn ordinary spreadsheets into applications in their own right.  Learn this and you’ll go far.

6) COUNTIF and SUMIF

More than one function again here, but these two again operate in similar ways.  Think of them as the love-children of COUNT, SUM and IF and you’ve got some idea of how they might work.  Learn these and you’ll be conditionally adding and counting values in your sheets in no time.

7) SUMPRODUCT

A mathematical function at heart, this function is also wonderful when you want to conditionally add or count data in tables when you have multiple conditions.  Want to find out how many blue Ferraris sold during February from a complete sales list?  SUMPRODUCT is the function for you, and can really add some valuable power to your analyses.

8)  OR and AND

Two logical functions here.  Using these in combination with the conditional functions (such as IF, etc) can really boost your productivity and general Excel wizardry smugness.

9) FIND

More text manipulation here, the FIND function can be really useful in trying to get meaningful data out of complex text strings, especially if used with the other text manipulation functions mentioned earlier.  Google it now!

10) CLEAN and TRIM

Dirty data (especially that copied and pasted from the web and other non-Excel sources) can be full of all kinds of non-printing, messy nasties.  The CLEAN and TRIM functions can tidy these nasties away for you and leave your data gleaming, like some kind of data disinfectant.

Ok, I know this has been a rather informal and brief list of essentials, and I know I’ve probably missed out a handful of other useful functions, but I would say that these are more often than not the most regularly used weapons in my Excel data arsenal, and without them my spreadsheets wouldn’t be half as good/useful as they are.

Keep coming back for more tips.  Comments, as always, are more than welcome (as long as they’re nice ones!)