The Golden Rules of Spreadsheet Design

Locked lesson.

Upgrade

About this lesson

Explore the golden rules when it comes to designing and organizing spreadsheets effectively.

Quick reference

The Golden Rules of Spreadsheet Design

Understand the golden rules of good spreadsheet design so workbooks are organized, easy to interpret, and efficient to update. 

When to use

These golden rules should be abided by whenever we create a new workbook to ensure they are kept error-free and user-friendly. 

Instructions

We rarely create spreadsheets just for our own use. These days, collaboration is key. We share spreadsheets with colleagues, managers, stakeholders, and clients. To prevent unwanted changes, keep our spreadsheets organized, and make them efficient and user-friendly, we need to think about the design. 

To help with this, there is a set of golden rules we can follow to produce better spreadsheets. 

Organize Data Logically

Workbooks should always be organized logically. We need to ensure that we are separating different types of data onto different worksheets. For example, our source data, calculations, Pivot Tables and Charts should all be on separate worksheets not crammed into one.

It's also recommended to group worksheet tabs visually using color so that users can see which worksheets are related. 

Create a Summary Sheet

Think about creating a summary sheet at the beginning of the workbook. Summary sheets provide colleagues with useful information about the workbook and how it should be used.

For example, a summary sheet could contain some general information, links to jump quickly to a specific worksheet, save location information, and even a key to explain what different cell colors represent. 

Use Formatting to Highlight and Distinguish Cells

It's a good idea to use formatting to highlight cells of importance. We can use cell formatting to help others know which cells are calculation cells and which cells are input cells. 

Remember, formatting should be used to enhance the readability of worksheets not distract. 

Use Cell References instead of Hard-Coded Values

It's never a good idea to hard-code values into formulas. Hard-coded values make the spreadsheet more difficult to update if the value changes.

Instead, put values in their own cells and link to the cell in the formula. If the value changes, we only have to update it in one cell and everything else will update automatically. 

Control Inputs with Data Validation

Accidental or non-accidental mistakes in a spreadsheet are a big problem. Even something as simple as a spelling mistake can throw our data analysis results off. 

Controlling the data that is going into the spreadsheet with Data Validation is essential. Data Validations allow us to limit what information goes into a cell by only allowing users to select from a drop-down list. 

Protect Cells, Workbooks and Worksheets

We can completely lock down our workbooks, worksheets, or specific cells in a worksheet using protection. 

We can add a password to a workbook to prevent unwanted access, we can protect specific worksheets from editing or we can choose specific cells to lock to prevent anyone from tampering with the data. 

Track Changes with Versions

If a file undergoes many changes, it's a good idea to keep track of the different versions. This might just be a simple case of making a copy of the worksheet and indicating it's a new version in the file name. 

When we create versions of the same file, it's much easier to track the changes and roll back to a previous version if required. 

Consider Standardizing Naming Conventions

If we don't implement a standard for naming our files and folders, things can get messy quickly. If everyone's naming files differently it makes it much harder to find and search for files. 

Think about implementing a standard naming convention for files and folders that everyone in the team can adopt.

Login to download
  • 00:04 When we're using Excel, there is a tendency to simply dive straight into our
  • 00:08 spreadsheet, start adding numbers, start formatting things, without giving too
  • 00:13 much consideration to the overall design and usability of our spreadsheets.
  • 00:17 If the data is there and we understand it, that's all that matters, right?
  • 00:22 Wrong, these days, we rarely create workbooks just for our own use.
  • 00:27 We are collaborating with others, we are sharing our spreadsheets.
  • 00:30 We might have multiple people using the workbook and all updating data.
  • 00:36 And these are all things that we have to bear in mind when we are putting
  • 00:39 a spreadsheet together.
  • 00:41 I'm sure we've all had that experience, where we've spent a lot of time putting
  • 00:45 together this super flashy workbook that calculates beautifully, and then we've
  • 00:50 sent it to a colleague who maybe doesn't have as many Excel skills as we do.
  • 00:54 And before you know it, the formulas are broken, things have changed.
  • 00:58 So we then have to go back and start correcting all of these errors.
  • 01:02 Now, as I said, this could be because our colleague doesn't have as much Excel
  • 01:06 knowledge as us, or it could just be that they've changed it by accident.
  • 01:10 Sometimes it's super easy to hit the wrong button on the keyboard, or
  • 01:13 maybe we've put a book on top of our keyboard, pressed a button, and
  • 01:16 before we know it, our lovely formula is completely broken.
  • 01:19 So a big part of designing better spreadsheets is to make sure that
  • 01:23 everything is organized logically, it's easy to understand for everyone,
  • 01:28 and it has clear instructions as to which parts of the spreadsheet should be
  • 01:32 changed and which parts should be left alone.
  • 01:35 We can even go a stage further by making sure that we add protection and
  • 01:39 control into our spreadsheet to minimize the number of errors that go into it.
  • 01:44 So in this lesson, we're really just going to talk through some of the golden
  • 01:48 rules of better spreadsheet design.
  • 01:50 And these are things that I recommend that you implement when you're creating
  • 01:54 a workbook, particularly an important workbook that contains sensitive data that
  • 01:59 you want to share with other people.
  • 02:01 And whilst this is a little bit more work at the beginning,
  • 02:04 it's going to really help you out in the long term.
  • 02:07 So let's dive in and take a look at these golden rules.
  • 02:10 My first golden rule is to organize your data logically.
  • 02:14 Now, what exactly do we mean by that?
  • 02:16 You want to make sure that you're separating things like your source data
  • 02:20 from any tables or pivot tables you might create, and
  • 02:23 also separating those from things like charts, dashboards, and calculations.
  • 02:28 I've seen so many situations where someone's put all of their data on one
  • 02:32 worksheet, and they've also tried to cram on a pivot table and
  • 02:36 a pivot chart on the same worksheet.
  • 02:38 It makes the worksheet look really messy, unorganized, it's hard for
  • 02:42 people to read, and it's just not logical.
  • 02:45 We can even go a stage further and color code those tabs so
  • 02:49 that people can visually see groups of data or
  • 02:52 worksheets of data that are of similar type or are related to each other.
  • 02:57 So think about those things when you're putting together your spreadsheets.
  • 03:01 My second golden rule is to create a summary
  • 03:04 sheet at the beginning of your workbook.
  • 03:06 And when I say a summary sheet, I simply mean just add worksheet into the beginning
  • 03:11 of your presentation that gives some kind of instruction or
  • 03:14 detail about how the workbook is to be used.
  • 03:17 For example, you might include things on there, like where the file is to be saved.
  • 03:22 You might include a legend which shows different cell colors within the worksheet
  • 03:26 and what those cell colors actually mean.
  • 03:28 You might provide information like the version or maybe hyperlinks to different
  • 03:33 parts of your workbook to make it easy for people to jump to the relevant section.
  • 03:37 So summary sheets are really useful because they just help out whoever is
  • 03:42 looking at this workbook so they know what they can change, what they can, and
  • 03:47 how to use it.
  • 03:48 My next golden rule is to use formatting to highlight and distinguish cells.
  • 03:51 So I mentioned a bit earlier, using things like format cells or
  • 03:55 maybe even conditional formatting, really make your spreadsheets a lot easier
  • 04:00 to interpret and just a lot more interesting to look at.
  • 04:03 And we want to make sure that we're using formatting to make our worksheets more
  • 04:07 readable and not distract from the actual data.
  • 04:10 So some well-placed formatting to highlight relevant information,
  • 04:14 important information can be extremely useful.
  • 04:16 The next golden rule is a really important one.
  • 04:19 We want to make our spreadsheets as easy to update as possible,
  • 04:23 because in most spreadsheets and most of the data that we're using,
  • 04:28 data isn't static, data changes over time.
  • 04:31 For example, if you've got a spreadsheet full of sales data for
  • 04:35 last year's sales, that's going to be updated at the end of this year or
  • 04:40 maybe even on a month by month rolling basis.
  • 04:43 So people are going to be adding data into this spreadsheet, and
  • 04:46 we want to make sure we make this process as simple as possible and minimize errors.
  • 04:51 And one thing you always want to make sure that you do is that you use cell
  • 04:55 references as opposed to hard-coded values in your cells as often as possible.
  • 05:00 And I'm going to show you an example of what I mean by this in a couple of
  • 05:03 lessons time.
  • 05:04 But in general,
  • 05:04 we want to stay away from hard-coding numbers into our cells where possible.
  • 05:09 It's not always possible to link to a cell reference, sometimes you do need to
  • 05:13 hard-code, but a general rule is, always linked to the cell,
  • 05:16 because it makes your spreadsheet easier to update and much more dynamic.
  • 05:20 The next golden rule is a big one.
  • 05:23 One way to help minimize errors is to control inputs with data validation.
  • 05:29 We want to make sure that the data going into our spreadsheet is exactly as it
  • 05:33 should be.
  • 05:34 For example, if somebody has to enter, maybe, a list of different department
  • 05:38 names, people tend to make mistakes, somebody might make a spelling mistake.
  • 05:42 Now, that can have really big consequences if you then want to go on and
  • 05:46 analyze that data.
  • 05:47 It's going to throw your filters off, it's going to throw your charts off,
  • 05:52 it's going to throw your pivot tables off.
  • 05:54 So we can control what goes into the spreadsheet by adding data validation so
  • 05:59 that users don't have any leeway as to what goes into a cell.
  • 06:03 The next golden rule is to protect cells, worksheets, and workbooks.
  • 06:07 You can protect individual cells in your workbook if you don't want those to be
  • 06:11 changed, you can protect individual worksheets, and
  • 06:15 you can also protect entire workbooks and make them read-only.
  • 06:19 And there are numerous different protection options that you have,
  • 06:22 one of them is to add a password.
  • 06:23 But again,
  • 06:24 if you only want to protect formulas in certain cells you can do that as well.
  • 06:28 The next golden rule is to track changes in your worksheets by creating versions.
  • 06:33 Versions give us the ability to be able to backtrack out of our work.
  • 06:37 So if there is an error, we can easily go back to the previous version.
  • 06:42 And the final tip here is to consider standardizing your naming conventions.
  • 06:48 And what I'm talking about here is the name of your files.
  • 06:51 The last thing you want to do is go into a folder, in File Explorer,
  • 06:54 maybe one that you share with your colleagues.
  • 06:57 I have files all over the place in there that are all named different things,
  • 07:01 particularly if the files are of similar type.
  • 07:04 Maybe we have a folder that contains invoices, but because there's
  • 07:07 no standard naming convention, some people have named it invoice and
  • 07:11 then the number, other people have named it the number and then invoice.
  • 07:15 Some people have just put INV and the invoice number, and
  • 07:19 it's all over the place.
  • 07:20 So it makes it a lot harder for you to locate and
  • 07:23 find exactly the file that you're looking for.
  • 07:25 So implementing some kind of standardization when it comes to how
  • 07:29 you're naming your Excel files is also something
  • 07:31 that's going to help you out greatly.
  • 07:33 So those are my golden rules for better spreadsheet design.
  • 07:37 And throughout this section,
  • 07:39 we're going to explore some of these with real working examples.
  • 07:42 So I'm going to head over there now,
  • 07:47 I look forward to seeing you in the next lesson.

Lesson notes are only available for subscribers.

Gift this course