- 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:
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.
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.
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.
Also Read:
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 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 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.
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.
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.
If you use cell styles in a worksheet, add a legend to the welcome sheet to explain the formatting.
Spreadsheets can look cleaner and more professional by removing the gridlines.
- From the View tab, in the Show group, remove the tick from Gridlines.
Suggested Reads:
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.
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.
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).
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.
- 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:
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.
