I have recently been involved in a project where the end product spreadsheet has been designed to be used to consumers, i.e. a less technical audience than I would typically work with.
The expected lower level of familiarity with Excel of the users has forced us to be creative in how the user interface can be designed to allow users to instantly focus on the right areas and to design a strict ‘funnel’ of actions, resulting in a desired outcome.
The faded colour scheme guides the user through the Spreadsheet
Designing a user interface in Excel
One of the main drivers in the project was to allow a user to use a ‘Simple Budgeting System’ or a ‘Detailed Budgeting System’. We had to ensure that users, after flicking the switch (refer to the screenshots below), would instantly know where to focus their attention next.
If the spreadsheet would have been developed in a more traditional application environment (e.g. VB6) then this wouldn’t be a real issue and if we had full access to VBA then this also wouldn’t be a problem. Clearly we could have applied some fancy formatting using VBA but an unexpected problem arose early in the testing phase.
Many consumers don’t allow VBA in their Excel spreadsheets….
In a quick test run with the target audience of consumers we found that a vast majority clicked to not accept the VBA to run, or had Excel set to automatically decline VBA (the latter was true particularly for Excel 2007 users) for ‘security reasons’.
So, we didn’t have much choice but to work without any VBA which leaves us with Conditional Formatting as our only weapon in this battle.
Using tailored Conditional Formatting
To get the users to focus on the right areas we applied conditional formatting across the area which was controlled by the master switch (‘basic’/’detailed’). This takes a bit more work than to simply apply a blanket style conditional formatting as we had to apply different conditional formatting to different areas of the section.
The Basic budgeting system is activeThe Detailed budgeting system has been applied
The Detailed budgeting system has been applied
Generating a ‘faded’ colour scheme in Excel
The generate a shaded colour scheme in Excel we had to create a fainter version of each of the profile colours and add these to the Standard Colours in Excel.
The screenshots below show an example of how to create a shaded version of the main colour in this Example, the bold Green colour, RGB (0,51,0). First step is to select Tools -> Options -> Colours and to select the Green colour. Continue by selecting Modify which opens the Colours dialog as per below.
Create a faded version of your colours using the brightness slider in Excel
Using the slider on the right hand side of the Colors dialog allows you to change the brightness of the colour without changing the colour mix which is exactly what we wanted to do in this example.
The new, lighter version, of the bold green colour is now RGB 179,255,179 which can be saved in one of the spare spots.
Applying conditional formatting using the shaded colour scheme
Once the colours have been setup up the application of the conditional formatting is quite straight-forward, but unfortunately rather time consuming.
The section below shows how the green table headings are ‘faded out’ on the condition:
=Inc_Budget_Select <>1
‘Inc_Budget_Select’ is the named range identifying which budgeting system has been selected.
Conditional formatting is applied to the Excel workbook