Excel spreadsheet colour coding/design/layout principles?

Question

With programming you have "software design patterns", with websites you have design standardised (and then some templates, e.g. "bootstrap"), and people know where the input etc. is just because it was designed well, but are there any design patterns with Excel, so that input cell are of one colour, function cells are of "that colour", hyperlinks another, and then constant cells are of another colour? UK government standardised the look of its info pages, perhaps some government standardised the look of its excel sheets?

I want it to be intuitive, not just for me (some people are colour blind (I'm not), so I guess it would be great if the design would account for them), but for users of my spreadsheets.

Just as when for example a Finnish person (I don't speak Finnish) gives me their phone, I can set up their Wi-Fi, or search something on maps/internet, because the design/layout/colours/shading is so intuitive I can do it without even being literate in that language on an smartphone OS I barely ever used before.

I'm an advanced Excel user (custom coded VBA code, form controls (e.g. buttons, sliders), dashboards). For VBA code I use coding patterns and comments just fine, so it's not about that.

I'm aware of:

• page layout view, which helps to see how it will look like after printing.
• cell styles (I don't think the default ones in the newest Excel are intuitive enough, or if someone with UI background actually worked on it)
• conditional formatting in excel
• data validation and drop down lists
• formatting stuff as a table for easier sorting, filtering etc.
• "name manager" to rename cells and then using those intuitive cell names in references and formulas
• how to "lock" cells, to prevent edits

They did not teach us design principles at school/Uni, so I wouldn't mind at all if you answer with just a book link instead.

Answer 1

There is some sort of standardization suggested by Excel itself, which you can see when you open the Cell Styles menu from the Home ribbon (see the screenshot below, on macOS):

However, I don't think many people are using this.

For things like hyperlinks, you can fall back on general Internet standards: blue and underlined.

Answer 2

I recently watched a tutorial on discounted cash flow modeling and heard the author say that "people at Wall Street color hardcoded values in blue."

With this at hand, I was able to find some guidelines like here.

From the article above:

Recommended formatting: text color The first and easiest method of formatting a financial model is to use a consistent color scheme to annotate different types of cells and data. Here is a recommended color scheme that is quite commonly known amongst financial analysts and other users of financial models:

Blue: Inputs, or any hardcoded data, such as historical values, assumptions, and drivers
Black: Calculations and references to the same sheet
Green: Calculations and references to other sheets (note that some models skip this step and use black for these cells)
Red: References to separate files or external links

There is also an Excel Formatting: Best Practices in Financial Models guide on the web, which matches most aspects from the article above but goes way beyond that.

I think that following the above style guide is a good idea. I haven't heard of any other industry/sector using such a foundation, but I might have missed it.

Answer 3

I think standards are what a majority of users are used to and agree based on familiarity. As such, we could consider common table design patterns as standards, as well as those for visualizations and graphs.

Maybe you can discover these by testing non-excel tools such as Airtable with people who have never used them before. Whatever they understand right away based on excel familiarity could be called a ‘standard’.

Also, there could be different excel standards per industry, use case or profession. Excel Is such a versatile and universal tool that everyone adopts it for their solutions.

Example for accounting - https://medium.com/lets-excel/microsoft-excel-rules-for-designing-excel-workbooks-part-1-49fd4c9fd07