Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2021
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Data Analytics in Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2021
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • Power BI
        • Power BI
        • Power BI Intermediate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2021
        • Project for the Web
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2021
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Desktop Pro 2022
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Risk Management
        • Financial Forecasting and Modeling
      • Alteryx
        • Alteryx Advanced
        • Introduction to Alteryx
      • Power BI
        • Power BI Intermediate
        • Power BI
      • Qlik Sense
        • Qlik Sense Advanced
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Asana for Employees and Managers
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Get Started
Home > Microsoft Excel > The 7 Golden Rules of Excel Spreadsheet Design

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?
  • Excel Spreadsheet Design – 7 Golden Rules
    • Rule 1 – Adopt a Standard And Stick To It 
    • Rule 2 – Know Your Audience
    • Rule 3 – Include a Welcome Sheet
    • Rule 4 – Separate Your Data
    • Rule 5 – Design For Longevity
    • Rule 6 – Use Consistent, Clear Structure
    • Rule 7 – Control Data Input

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
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
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
Use a legend

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

Remove Gridlines if required
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
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
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
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
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.

Most Popular Posts

  • Kanban vs Scrum: Project Management Methodologies and Their Differences [2022]
  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15

Similar Posts

Using the Windows 8 Mobility Center

How to Use ANOVA in Excel: 4 Simple Steps

How to Navigate a PowerPoint Presentation in PowerPoint 2016

Formatting Text in PowerPoint 2016

How to Create Pivot Charts in Excel 2013

Linking Tasks in Microsoft Project

Course Categories

  • Adobe
  • Data Analysis
  • QuickBooks
  • Microsoft
  • Web Development
  • Work Productivity

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebookLinkedIn
© 2023 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
  • Sitemap
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)