The 7 Golden Rules of Excel Spreadsheet Design

Mục Lục

The 7 Golden Rules of Excel Spreadsheet Design

This guide on spreadsheet design is suitable for Excel 2016, 2019, 2021, and Microsoft 365 versions

Design better spreadsheets by implementing the ‘golden rules’ of good spreadsheet design.

You’ll learn:

What is a Good Spreadsheet Design?

When creating a new Excel workbook, most of us simply dive in and start inputting data without giving too much thought to longevity, ease of use, and future-proofing. Neglecting to think about these things can result in a spreadsheet that is inconsistent, full of errors, and hard to update.

This is particularly important when sharing spreadsheets with others. Spreadsheets that make sense to you, might be confusing to your colleagues.

Imagine the scenario. You’ve spent two weeks putting together an amazing spreadsheet with lots of complex formulas and formatting. You share it with your wider team and within a day, you’re fielding lots of questions about the spreadsheet or even worse trying to fix accidental errors from less-skilled staff members.

It might not be clear to your colleagues where to input data, which cells can be edited, and which ones should be left alone. People might have varying levels of skill in Excel and accidentally edit a formula, effectively breaking it.

Or, maybe you put together a spreadsheet 6 months ago and are now revisiting it. Can you remember what the formatting in each cell means? Or why did you put that formula in a cell?

Employing these basic principles when creating a spreadsheet is not only going to give others guidance when working on your spreadsheet but also minimize the amount of time you spend fixing issues or updating the data.

Related:

Easily Make a Bullet Chart in Excel—2 Examples

Creating a Dynamic Pivot Chart Title Based on Slicer(6 Easy Steps)

How to Make a Line Graph in Excel? 4 Best Line Graph Examples

Excel Spreadsheet Design – 7 Golden Rules

Rule 1 – Adopt a Standard And Stick To It

It’s important to adopt a consistent standard when working on a spreadsheet. It might be that you work at an organization that has its own standard with all colours and fonts used adhering to company branding guidelines.

Regardless, it’s important to keep fonts, colours, cell styles, tab colours, and file names consistent.

Fonts and Colors

Spreadsheets that contain multiple font styles can be difficult to read. It’s better to stick to one or two and ensure that the fonts you pick are professional. In general, fonts like Arial, Calibri, and Cambria work best. Stay away from anything cursive or quirky like Comic Sans.

Colors should also be consistent but you don’t necessarily only have to use one color. If you have a theme applied, choosing colors from within the theme palette works well. Ensure it’s easy to read the data and you don’t have a light font on a light background and vice versa.

Spreadsheet Design Rule 1 - Adopt a Uniform Standard Spreadsheet Design Rule 1 – Adopt a Uniform Standard

Tab Colors

Changing the color of spreadsheet tabs can be an effective way of grouping worksheets. For example, you might have your calculations on one worksheet, analysis on three other worksheets, and then a dashboard on another.

Color coding the tabs so that worksheets of the same type are grouped by color is a good, visual way of organizing data.

Right-click on the tab.
Choose Tab Color from the menu.
Select a color from the palette.

Use tab colours to organize data

File Names

Implement a consistent file naming convention so that it’s easy to identify files. For example, if you have a folder of ‘January Invoices’, consider naming them ‘INV-JAN-03.xls’, ‘INV-JAN-04.xls’. Or maybe name them by client, ‘INV_Microsoft_01032022’ etc.

This makes your files easy to search for and find in File Explorer and gives you an idea of the file contents without opening it first.

Rule 2 – Know Your Audience

Whilst your Excel skills might be top-notch, other people might not be. If you are sharing the spreadsheets you create with others it’s important to ensure that everyone who will be working on that spreadsheet has the appropriate level of knowledge and competence in Excel.

If you design a spreadsheet that contains complex formulas and you expect others to update those formulas or perform calculations, you need to ensure they have had the appropriate level of training or you will spend a lot of time fielding questions or fixing errors.

It’s important to design a spreadsheet that is appropriate for your audience. Who will be looking at it? Maybe it’s for internal use. Maybe it’s for your manager. It could be sent out to a client or key stakeholders. Or, maybe it’s for a personal project like an after-school club. This will greatly affect how you design your spreadsheet.

Professional spreadsheets for business use should be designed as such. Keep colors and fonts clean and simple. Ensure the data is easy to read and interpret. Try to keep any images or icons professional and definitely avoid cartoon images (clip-art style) and lurid colors. Selecting images from Excel’s stock image library or a professional image website like The Noun Project (icons) or Pexels (Images) works best.

Personal projects are more forgiving and it might be appropriate for your audience to inject a little fun with images, cartoons, bright colors, and quirky font.

Consider your audience. Design appropriately.

Spreadsheet Design Rule 2 - Know Your Audience Spreadsheet Design Rule 2 – Know Your Audience

Also Read:

Bar Graph in Excel — All 4 Types Explained Easily (Excel Sheet Included)

How to Make a Scatter Plot in Excel? 4 Easy Steps

How to Add Error Bars in Excel? 7 Best Methods

Rule 3 – Include a Welcome Sheet

Where possible, include a ‘Welcome Sheet’ with instructions on how to use the spreadsheet. This can be extremely helpful to colleagues reviewing the spreadsheet for the first time.

Add valuable instructions. Let them know which cells they should be inputting data into and which cells should be left alone. Consider adding a legend or a key to explain the formatting used in the spreadsheet. Provide guidance on how the spreadsheet should be used, where it should be saved, and any version control instructions.

This will minimize the number of questions you are asked about the spreadsheet leaving you time to work on other projects without interruption.

Rule 3 - Include a Welcome Sheet Rule 3 – Include a Welcome Sheet

Rule 4 – Separate Your Data

Keep different types of data on separate worksheets. For example, if you want to analyze sales data with PivotTables and Pivot Charts, ensure that you have the source data on one worksheet, calculations on another and then any PivotTables, charts, or visuals on another worksheet.

If we put everything on one worksheet, it’s not organized, it’s hard to interpret, and can be confusing for the person reading the spreadsheet. Ensure spreadsheet tabs are clearly named.

Rule 4 - Separate Your Data Rule 4 – Separate Your Data

Rule 5 – Design For Longevity

Future-proof your spreadsheets and allow for changes. Sometimes, when we create a spreadsheet, we don’t think about how easy it will be to update next month or in 6 months’ time. Think about using Excel tables for large datasets. Excel tables auto-expand to accommodate any new data added so formulas and calculations that use the table data can be updated with the click of one button.

Avoid hard-coding values into cells. For example, if we have a spreadsheet of sales information and we need to add 15% sales tax to each product, don’t hard-code ‘15%’ into the SUM calculation. If the sales tax rate changes, we would need to find each formula that contains the hard-coded value and change it.

Rule 5 - Design For Longevity Rule 5 – Design For Longevity

Instead, use cell references. If the sales tax rate changes, the value only needs to be updated in cell M4 and all formulas will update automatically.

Avoid hard-coding values

Rule 6 – Use Consistent, Clear Structure

Spreadsheets should be as simple, clear, and consistent as possible. Remove unnecessary data and formatting to reduce clutter. Think about using cell styles to make it clear which cells are input cells, which are calculation cells, and which contain headings and warning messages.

Select the cell/cells.
From the Home tab, in the Styles group, click Cell Styles.
Choose an appropriate cell style from the gallery.

Rule 6 - Use Consistent, Clear Structure Rule 6 – Use Consistent, Clear Structure

If you use cell styles in a worksheet, add a legend to the welcome sheet to explain the formatting.

Use a legend

Spreadsheets can look cleaner and more professional by removing the gridlines.

Remove gridlines if required

From the View tab, in the Show group, remove the tick from Gridlines.

Uncheck the Gridlines checkbox in the View tab

Suggested Reads:

How to Add a Watermark in Excel? 2 Easy Methods

How to Remove Hyperlinks in Excel? 3 Easy Methods

How to Use the Format Painter Excel Feature? — 3 Bonus Tips

Rule 7 – Control Data Input

Keep worksheets as error-free as possible by using data validation and protection. The more people that have access to a workbook, the more likely it is that something will be changed in error or formulas will get broken. As the spreadsheet owner, you should think about implementing controls to stop this from happening.

Data validation is a great way of controlling what can be input into a cell or cells. Create data validation drop-down lists to prevent users from inputting anything you haven’t specified. Add warning messages and helpful instructional text to guide users towards the correct inputs.

From the Data tab, in the Data Tools group, select Data Validation.

Rule 7 - Control Data Input Rule 7 – Control Data Input

Another way of controlling user input is to protect the workbook, the worksheet or specific cells. When you protect a worksheet, you can choose to add a password. Only users that have the password will be able to make changes. You can also choose to keep certain parts of the worksheet unlocked if required. Protection will only be applied to the worksheet.

From the Review tab, in the Protect group, click Protect Sheet.
Enter a password (if required).
Choose which parts of the worksheet you would like users to be able to change.

Protect the Worksheet

Protecting the workbook will protect all worksheets within that workbook. It protects the structure of the workbook so users cannot add new worksheets, delete worksheets, move or copy worksheets, rename or delete.

From the Review tab, in the Protect group, click Protect Workbook.
Enter a password (if required).

Protect the Workbook

We can also lock specific cells on a worksheet. This is useful if you have complex formulas that you don’t want to be changed.

Select the cells you want users to be able to edit.
Press CTRL+1 to open the Format Cells dialog box.
Go to the Protection tab.
Remove the tick from Locked.

Lock specific cells

From the Review tab, in the Protection group, click Protect Sheet.

These are just some of the ‘golden rules’ you might think about implementing when designing spreadsheets. For further reading, please check out the following links:

PerfectXL – Create and Maintain Good Spreadsheets

GeekGirls – Spreadsheeting II: Good Spreadsheet Design

Let’s Wrap Up

That’s all folks. In this guide, I have given you the seven golden rules of Excel spreadsheet design. Do keep them in mind and apply them in your upcoming projects. Working with your spreadsheets will be a delightful experience.

For more high-quality guides on Excel do check out our free Excel resources centre.

Do you want to master your Excel Skills? Click here for in-depth courses that explain everything you need.

Deborah Ashby

Deborah Ashby is a TAP Accredited IT Trainer, specializing in the design, delivery, and facilitation of Microsoft courses both online and in the classroom.She has over 11 years of IT Training Experience and 24 years in the IT Industry. To date, she’s trained over 10,000 people in the UK and overseas at companies such as HMRC, the Metropolitan Police, Parliament, SKY, Microsoft, Kew Gardens, Norton Rose Fulbright LLP.She’s a qualified MOS Master for 2010, 2013, and 2016 editions of Microsoft Office and is COLF and TAP Accredited and a member of The British Learning Institute.