Afrikaans
Akan
Albanian
Amharic
Arabic
Armenian
Azerbaijani
Basque
Belarusian
Bemba
Bengali
Bihari
Bosnian
Breton
Bulgarian
Cambodian
Catalan
Cebuano
Cherokee
Chichewa
Chinese (Simplified)
Chinese (Traditional)
Corsican
Croatian
Czech
Danish
Dutch
English
Esperanto
Estonian
Ewe
Faroese
Filipino
Finnish
Frisian
Ga
Galician
Georgian
German
Greek
Guarani
Gujarati
Haitian Creole
Hausa
Hawaiian
Hebrew
Hindi
Hmong
Hungarian
Icelandic
Igbo
Indonesian
Interlingua
Irish
Italian
Japanese
Javanese
Kannada
Kazakh
Kinyarwanda
Kirundi
Kongo
Korean
Krio (Sierra Leone)
Kurdish
Kurdish (Soranรฎ)
Kyrgyz
Laothian
Latin
Latvian
Lingala
Lithuanian
Lozi
Luganda
Luo
Luxembourgish
Macedonian
Malagasy
Malay
Malayalam
Maltese
Maori
Marathi
Mauritian Creole
Moldavian
Mongolian
Myanmar (Burmese)
Montenegrin
Nepali
Nigerian Pidgin
Northern Sotho
Norwegian
Norwegian (Nynorsk)
Occitan
Oriya
Oromo
Pashto
Persian
Polish
Portuguese (Brazil)
Portuguese (Portugal)
Punjabi
Quechua
Romanian
Romansh
Runyakitara
Russian
Samoan
Scots Gaelic
Serbian
Serbo-Croatian
Sesotho
Setswana
Seychellois Creole
Shona
Sindhi
Sinhalese
Slovak
Slovenian
Somali
Spanish
Spanish (Latin American)
Sundanese
Swahili
Swedish
Tajik
Tamil
Tatar
Telugu
Thai
Tigrinya
Tonga
Tshiluba
Tumbuka
Turkish
Turkmen
Twi
Uighur
Ukrainian
Urdu
Uzbek
Vietnamese
Welsh
Wolof
Xhosa
Yiddish
Yoruba
Zulu
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.
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.