Efficient Navigation in Excel Workbooks

When I saw the signs in this shop window, I laughed, and snapped this picture.

door

How confused are your customers, if you need a sign that says, "This is the door", and another sign that points to the door handle?

Design Flaws

Later, I realized that it’s not a customer problem – it’s a design problem. A substantial number of people had trouble finding the handle, which looks more like a box. And I don’t remember exactly what the front of the store looked like, but the door must have been hard to identify too.

How about your Excel workbooks – are they easy to understand and navigate? I’ll admit that some of the spreadsheets I’ve built might have similar navigation problems. I’ve added arrows that point to data entry cells, and text boxes with user instructions. Not much different from that door!

Navigating a Workbook

What can you do to make Excel navigation easier?

In a large workbook, a menu sheet, with links to other sheets, is helpful. And all the other sheets should have a link back to that menu sheet.

hyperlinkclick 

On a large worksheet, you can add a menu at the top of the sheet, linking to the sections below.

tableofcontentssheet05

If users can enter data on some sheets, group all the data entry cells in one area, and use colour coding, to make the cells easy to identify.

dataentry15

Worksheet Design Resources

Where do you get your worksheet design ideas? From your own experiments? From sample workbooks that you’ve found online? Somewhere else?

Even though it’s focused on web site design, rather than spreadsheets, I found plenty to think about in Don’t Make Me Think, by Steve Krug.

There are good tips in Professional Excel Development, by Rob Bovey, et al.

What books or other resources would you recommend?

________

You may also like...

10 Responses

  1. The picture of the door reminded me of a great book on the subject: “The design of everyday things”. After reading that, your life will never be the same again.

  2. Sorry for the silly typo :-)

  3. Doug Glancy says:

    I’ve got the Steve Krug book and the one mentioned by Jan Karel on hold now. I’ve always wanted to see more written about the topic of Excel user interfaces/spreadsheet design. The beginning of PED is great, but left me wanting more.

  4. I’ll be interested in your review of ‘Design of Everyday Things’, Doug. Everyone loves that book except for me. I absolutely hated it. I couldn’t even finish it.

  5. Also, when I tab out of this comment box, it doesn’t go to the ‘Post Comment’ button, it goes to the sign up for email part in the sidebar. It reminds of every userform I’ve ever made – before I went back and fixed the tab order.

  6. I just turned off JS and it still does it. There are two un-hidden input tags between this comment box and the email subscribe input tag; Post Comment and the Notify me checkbox. But it’s skipping over them in the tab order. Strange.

Leave a Reply

Your email address will not be published. Required fields are marked *