imilar to how web design principles are applied, the design of a spreadsheet needs to be optimized for the user experience. The more complicated or messy the spreadsheet is, the higher chance that the user leaves without obtaining what they're looking for. As a result, it's important create a system, such as a design pattern, that keeps focus on the ultimate answer(s) and spares the passive user from the gory details.
Create a Design Pattern for your Spreadsheet
Too often throughout my old accounting days, I saw messy spreadsheets where the backend work was mixed up with answers that the end-user was looking for. This frequently led to a negative user experience and made sifting through others’ spreadsheets for information more difficult than it needed to be. The experience would be similar to taking a test where you had to show your work, but instead of putting your relevant answers on the answer sheet once you solved the problem, you just left the answer (oftentimes un-circled) somewhere within all the other work. That other work is ultimately noisy, unnecessary, and leaves the door open for the end-user to make some unintended mistakes.
For example, what if we received a raw data set consisting of some type of case data by country, along with a request to filter that data by country?
The move people normally do is simply throw a filter on the first row of the data set and send it off without a second thought.
However, this approach is lazy and expects too much out of the end-user. This approach expects the end user to properly navigate through the filters and gives them too much access to the business-critical raw data. All they need is the end result of the raw data, so why would we open ourselves up to unnecessary risk by putting them in a position to inadvertently edit the data?
Instead, the default approach to spreadsheet construction should be thought of with the CEO in mind as the end-user. Would the average CEO want to bother with eyeballing through unnecessary data to get to what they’re really looking for? Would they prefer to click through formulas or filters within a spreadsheet that they’re unfamiliar with, or would they prefer to have the information easily accessible and digestible the moment they open the document? Spreadsheet focus should always stay on the intended information to ease the burden on the end-user, and the work that led up to the answers should always be background information that can be found on separate supporting sheets. This is just good practice for a positive user experience, which is most important for all parties involved.
With this in mind, we can try re-thinking the approach to the aforementioned example from the end-user’s perspective and implement the following design pattern:
- Rename the sheet containing the raw data to “Data Source”.
- Create a new sheet called “Cases by Country” and copy the first row headers from the “Data Source” sheet into Column A’s rows.
- Click into the cell immediately right of “Country” (Cell B1), click Data within the menu bar, and select Data Validation.
- Within the Data Validation popup, make sure “Cell Range:” is still pointing to the cell immediately right of “Country”, select the “Reject input” option under “On invalid data:”, and click the grid icon under “Criteria:” while “List from a range” is selected.
- Once you click on the grid icon, select the data range that the countries are located in within the “Data Source” sheet containing the spreadsheet’s raw data. Click OK.
- Now the Data Validation popup is ready. Click Save. (Note: If you want to include all potential additions to the column that the data validation is using as its data source, it’s best to set the selected data range to the infinite bottom of the column, as opposed to a specific ending row of the column. This is why I used “A2:A” as the input as opposed to “A2:A19”. Often times, people make the mistake of setting a specific ending row. This gets them into trouble in the event that new data is added to that data source column because the newly added data then gets excluded from the data range. By adding an infinite bottom, you’re assured to always include new additions to the data source’s column without having to manually adjust your criteria range.)
- There will now be a dropdown in Cell B1 within the “Cases by Country” sheet.
Now that we have a dropdown list containing all of the countries, we can use the INDEX MATCH formula to dynamically lookup the case data within the “Data Source” sheet, which will then output the case data into the “Cases by Country” sheet based on our country selection from the dropdown.
However, it’s worth noting that the lookup depends on matching header/label criteria to do a proper lookup/output. Because we now care about the user experience just as much as we care about getting the right answer, we can tailor our labels to be user-friendly in one column while doing a lookup of the “Data Source” sheet’s headers in another column. In order to execute this properly, we could do the following:
- Add a new column to the left of Column A within the “Cases by Country” sheet.
- Copy the headers from the “Data Source” sheet and paste them into Column A of the “Cases by Country” sheet.
- Apply the INDEX MATCH formula to Cell C2 within the “Cases by Country” sheet, looking up Column A’s header data pasted from the “Data Source” sheet as opposed to trying to use Column B’s user-created labels. (Note: There are some Absolute Reference techniques being applied to specific parts of the formula in order to make copying the formula down the column a dynamic process.)
- Copy the Cell C2 formula within the “Cases by Country” sheet down to the remaining rows.
- We can now hide the Column A information within the “Cases by Country” sheet, since its only purpose was to properly implement the lookup formula. We’ll now be left with a sheet that dynamically outputs the case information by country, depending on which country we select within our dropdown list.
Since we have successfully implemented a dynamic data pull for the case data by country, the only thing left to do is some minor formatting of our spreadsheet to further enhance the end-user experience.
Set Distinct Formatting for the End-User
It’s not enough to just leave our sheet as is because we leave too much open for assumption. For example, while we may intuitively know to go straight to the drop-down within the “Cases by Country” sheet in order to get our desired information, it’s not apparent for a completely new user of the spreadsheet. It’s up to us to make our spreadsheet easily accessible and leave as little open to interpretation as possible. One way to do that is through deliberate formatting, such as the following:
- Add a new first row within the “Cases by Country” sheet that will serve as space for a header.
- Merge Cell B1 & Cell C1.
- Add a header that succinctly describes the purpose of the sheet and center the header.
- Highlight the data in Columns B & C, click “Format” within the menu bar, and click “Alternating Colors”.
- Pick a color scheme that fits your specific tastes. (Note: Personally, I prefer either the gray and white color scheme or the dark blue and white color scheme. These are relatively conservative and still accomplish the goal of making our data easier to digest.)
- Bold the header and change the font color to white.
- Add a Grid Border to the full data set.
- Highlight the row with the country selection dropdown, bold the highlighted cells, and add a notification immediately to the right of the dropdown, citing the action needed in order to pull the desired case information.
- Lastly, color-code the sheet tabs in a way that can imply a delineation between viewing tabs most important to the user (ex: green) and supporting tabs (ex: red).
- The spreadsheet is now ready to be viewed and used as an easily digestible data source by virtually any user, regardless of their skill level.
Conclusion: User Experience (UX) Design Matters
I know from experience that within highest levels of business, designing and formatting spreadsheets is taken just as seriously as getting the correct answer. Seemingly trivial things such as “0” showing up as opposed to “-”, or spreadsheet data lacking order and a logical flow, don’t go unnoticed. In the grand scheme of things, it’s less about the presentation of the “0” and more about an appreciation for the most minute of details. It is that level of attention to detail that prevents the potential multi-million dollar mistake from happening just as much as experience does. I believe that if more spreadsheet creators were properly trained on how to make spreadsheets with a focus on user-experience and preventative care, the businesses that depend heavily on spreadsheets would be better off for it.
