All language subtitles for 1. The Golden Rules of Spreadsheet Design

When most of us are asked to put together a spreadsheet, we tend to dive into this task without giving

too much thought to the design or the structure.

And whilst the results of simply just entering in data into a spreadsheet without thinking too much

about it might be sufficient for the task at hand and your manager might be completely happy with the

spreadsheet that you've produced.

It can cause problems further down the line if you haven't really thought about how you're designing

it.

For example, if you've spent a long time working on a spreadsheet and you haven't protected it in any

way when you share that with other people, when it comes back around to you, you might find that you

have lots of errors in that spreadsheet.

Or maybe formulas that you didn't intend to be changed have been changed.

Or maybe you're getting lots of questions from your colleagues about where they should enter their data,

what type of calculations they should be using, which can become very time consuming?

Or maybe when you originally set up this spreadsheet, you didn't really think about the future.

So in six months time, when you come back to that spreadsheet and you need to make changes to it,

you find that you're really having to spend a lot of time modifying different formulas in different

cells, as opposed to just being able to update one figure and have everything else in the spreadsheet

automatically update.

So it's things like this that we really do need to think about at the design stage.

So what I've done here is I've put together some golden rules of spreadsheet design that would be very

helpful to think about before you get too far into the creation process.

So let's take a look at them.

Number one, adopt a standard and implement it.

So this relates to adopting a consistent convention when it comes to things like cell formatting, fonts,

colors and file names.

It might be that you work at an organization that has branded colors, and so the colors that you're

using in your spreadsheet need to be from a specific palette.

Whatever colors you choose to use, make sure that they are appropriate for the type of spreadsheet

that you're putting together.

They add to the data and don't distract and you use color consistently across your spreadsheets.

And it doesn't end up looking like a rainbow.

Also, when it comes to fonts, make sure that you're using a font that's easy to read.

Ariel Font Caleb Reform Those are always great examples of font that works well in Excel.

Stay away from some of the fancy Afon, which again, is really going to distract from your data and

make it difficult for other people to rate.

Put things like large data sets into tables so that they look organized and use borders and background

fails to highlight certain pieces of data.

Even when you're thinking about naming your spreadsheets, trying to implement a consistent file naming

convention.

If the spreadsheet relates to an invoice, maybe think about using a prefix in the file name like I

envy.

And then maybe the date.

If it's a report, maybe use a prefix like r p t.

So immediately.

If you're looking at your files in File Explorer, you have a rough idea as to what's contained within

that spreadsheet.

All of these things really help you adopt a consistent standard number to try and ensure there is an

appropriate level of knowledge and competence within your team.

If you have top notch excel skills and it's your job to put together the majority of the spreadsheets,

but you're sharing those spreadsheets with colleagues who have very basic excel skills that can cause

problems.

So it's a really good idea to make sure that the people within your team have not only had the sufficient

training, but they have the knowledge and skills to work on your spreadsheets without having to ask

many questions or maybe cause problems with that spreadsheet.

Number three, identify your audience, ensure that the design is appropriate for your audience, and

it's as clean and simple as possible to understand.

It's really important when you're putting together a spreadsheet to think about who's going to be looking

at this spreadsheet.

Is it just going to be internal?

Is it just your team colleagues?

Is it your manager or is it going to a client or up to stakeholders?

Maybe it's a personal project that you're working on.

Maybe you're putting together something for your after school club, and so therefore have a little

bit more leeway on how professional that spreadsheet needs to look.

So think about whose eyes are going to be on this spreadsheet and design accordingly.

For example, if this is a professional business spreadsheet that's going to go to your clients, you're

probably not going to want to do something like comic sans font.

You also might not want to add any images that appear too cartoony or colors that are very lurid.

So think about your audience prior to starting out with the design.

Number five include a welcome sheet with instruction.

It's always good to provide a welcome sheet with your Excel spreadsheet, which has instructions on

how to use the spreadsheet.

And this is really something that I don't tend to see a lot of.

People do, but it can be so helpful, particularly if you're sharing this workbook with lots of people,

simply having a worksheet at the beginning called How to Use can offer valuable instructions that people

aren't confused as to how to use the worksheet and where they need to input their information.

It might provide some guidance as to where that user needs to enter their data or what they need to

do.

And you can add things onto this sheet, such as keys or legends.

So if you have lots of formatting in your spreadsheet, let other people know what that formatting means.

You might want to give some guidance about where this worksheet needs to be saved once they've made

changes.

Or you might even want to go a stage further and have a versions worksheet that people update once they've

made changes.

All of this type of information is so useful when you're sharing your workbooks with other people and

really cuts down the amount of questions that you're being asked.

Number six, Separate your data.

Make sure that you have your source data, your calculations and your analysis on separate worksheets

to avoid confusion.

Excel is one of the best tools out there when it comes to analyzing data.

So if you have a whole bunch of sales data like I have just here, you want to ensure that you have

your raw data.

Source your sales data on one worksheet, but any calculations you perform based off of this data,

you want to keep those separate on a different worksheet.

If I'd put these on the same worksheet as the source data, things can start to get really confusing,

and it really doesn't assist with readability for other people.

I would also then put my analysis of that data on another worksheet entirely.

So my analysis might be something like taking that source data and creating a pivot table or creating

some kind of charts.

So don't try and cram all of these different things your source data, your calculations and your analysis

on to one worksheet separate everything out.

Design for longevity make sure that you future proof your spreadsheet and allow for future changes.

Now this is a really important one.

As I said at the beginning, we tend to just put our data into a worksheet, and sometimes we don't

really think about how much to use that worksheet in six months or a year's time, particularly when

it's been circulated around numerous people.

So make sure you think about using things like Excel tables, which is going to make updating your data

a lot quicker and easier.

And the golden rule here never hard code values into your formulas.

For example, if we take a look at this tax column, I'm currently clicked on Cell I six.

I have a formula here which is just multiplying the sales value in so six, with the sales tax now instead

of typing in or hard coding the number 15 percent into this formula.

I've used a cell reference.

So if these sales tax changes in six months time to 20 percent, it means that I'm not having to go

into the formula and change 15 to 20 in everything in this tax column.

I can simply change it in one cell, and all of the formulas will automatically update.

So don't hard code numbers into your cells and think about using things like tables.

Use a consistent, clear structure.

So think about color coding your worksheets and use cell styles to identify inputs, outputs and calculation

cells.

It's good to get into the habit, particularly when you're working on larger workbooks that have lots

and lots of different worksheets of color coding those worksheets so worksheets that contain related

data or similar data make the tabs all one color.

For example, if you take a look at my tabs in this workbook, you can see that the three that are in

green are all kind of related to each other.

So it's a really good visual indicator of which worksheets are related to other worksheets.

Another thing that can be really helpful is to use something called cell styles.

I'm really going to talk more about this in the first lesson, but won't sell styles.

Do I basically let the user know which cells are input cells, i.e. which ones they can change, which

ones contain calculations, which ones contain a heading, which ones contain warnings, so on and so

forth?

And once you have cell styles in your workbook, you can then add those into the legend on that welcome

sheet so that people understand exactly what each of these formatted cells mean.

Another thing that really assists with keeping your spreadsheets looking clean and clear is by removing

the grid lines you can see on all of my worksheets.

I don't have the Excel grid lines showing I have a really nice, clean white background, which really

allows the photo.

Has to be entirely on the data.

And finally, a really important gold and roll control data input, so keep worksheets error free using

data validation and protection if you're sharing your workbook with lots of other people, as I mentioned

at the beginning.

The more people who have access to your workbook, the more likely it is that somebody is going to change

something that's going to cause an error.

And you really don't want to be spending a lot of time putting together a really nice worksheet with

lots of whizzy formulas in it, only to have someone break the formula the first time they use it.

So we can help with that by controlling access to certain parts of our worksheet.

For example, you could think about using things like data validation dropdown list to control exactly

what users are entering into the cells.

With this dropdown this just here, nobody can free type into that cell.

They are forced to select one of the options that I've set up in this dropdown list.

It also might be that you want to add protection to the worksheet or even the entire workbook so that

people can edit specific cells.

So if you have a cell that contains a formula like this one just here, if I don't want anybody changing

what this formula says, I can choose to protect this cell but keep everything else unprotected so they

can do things like select manager names.

They simply can't edit the formula.

Notice if I try and edit anything, I'm getting a message pop up because I've applied protection.

So there's lots of things we can do in Excel to control the data that's going into our spreadsheet to

keep them as error free as possible.

So those are my golden rules when it comes to designing spreadsheets.

Have a little think about your work and how you might adopt some of these approaches.