Structuring Excel

The real article is being written and will appear in due course.

Part of a series:

  1. Introduction. About the series.
  2. Structure. The key to success.
  3. Navigation. Sheets and more.

What is structure? Why is it key?

Excel guru David Hawley (RIP) puts it well:

Without a doubt, the number one mistake most Excel users make when creating their spreadsheets is that they do not set up and lay out the data in the manner in which Excel and its features expect... always try to keep related data in one continuous table.

Source: Excel Hacks: Tips & Tools for Streamlining Your Spreadsheets

When I look at spreadsheets I often groan: bloated, no use of tables or pivot tables, with data analysis from the 1990s. The VBA macro recorder used without subsequent editing and code which is 30-1000 times too slow. Formulae that go from sheet 1 to 2 to 7 to 3 to … oblivion.

But much worse: there is little hope of finding stuff.

The Word comparison

Did you ever hear of someone say of a book or, more pointedly, a Word document: “it was OK, but I just couldn’t find things”? But that’s a common complaint with Excel - or should be.

This is a pointer to a solution:

  1. Have a robust and automated table of contents
  2. Use sheets (c.f. chapters) appropriately
  3. Don’t start work off screen “to the right”

Good news!

You can achieve all this, without moving all your calculations into Word! There are two obvious possibilities and I’ve used both.

You can be more self-disciplined. Just start adopting better practices. You can grab table of contents VBA code from the web. These vary from shambolic to more than reasonable. You can start using tables as a matter of course and use a new sheet when appropriate. There’s surprisingly little guidance on this, but think coding and procedures for a single purpose.

You can call in Big Brother. The problem is that Excel is too easy; we can build and prototype things rather quickly. It’s all-too-easy to create a mess; in another realm we’d call this technical debt. A Big Brother approach is to have an addin which enforces good practice and, more positively, simply helps you be more productive, vastly reducing manual work in Excel. That’s why I built the PXL addin.