This blog’s opening series focuses on worksheet design components and principles. While the most fundamental part of a spreadsheet is its core functionality, it’s almost equally important to provide a proper user experience.
The idea of spreadsheet design refers to the overall experience a user has interacting with a spreadsheet. Design may conjure up thoughts of visuals and aesthetics, but that’s only a minor component. It often dictates the way users interact with an application. In the early stages of spreadsheet application development specific questions should be asked to drive design decisions. Who will be the primary user? How will the user enter and manage data? How will errors be handled? How will the user navigate to various components of the application? These are just a few questions that can act as a basis for effective spreadsheet design.
Generally, the longer the planned lifespan of a spreadsheet application, the more thought should be placed on its design. A long lifespan means that it will be used for many cycles and easily transferable with staff turnover. By utilizing proper design approaches, you can ensure that the spreadsheet application will not “break” or fail to meet the demands of a changing processes. Failure to follow proper design principles may mean that your elaborate project may only be used once.
This series provides 16 exercises which walk through four core design concepts that influence a user’s relationship with a spreadsheet: Navigation, Data Management, Layout, and Security. Some of the exercises duplicate end results, developing solutions with different approaches and varying levels of difficulty. By the end of this series, you should have a strong foundation for creating powerful spreadsheet applications.
Navigation is one of the most neglected components of large Excel projects. While dashboards work to consolidate key data summaries on a single screen, users are often left navigating a web of linked formulas and tabs to make updates. An effective navigation system can make even the most complex Excel applications user friendly.
- Navigation Overview
- Navigation in Excel with Text Links
- Navigation in Excel with Shapes
- Navigation in Excel with VBA
Data Management is another important component of an Excel application that can greatly improve the user experience if done right. When using a desktop application or a web-based program, users never need to access a database to add, modify, or delete data. The same should hold true for Excel applications. By separating data entry from data storage, developers can ensure information entered by users has been validated and posted correctly.
- Data Management Overview
- Adding New Data to Datasets in Excel
- Data Validation with Excel’s Built-In Data Validation Tool
- Data Validation with Excel’s Built-in Formulas
- Data Validation with VBA
- Building a Custom Listbox in Excel without VBA
- Building a Custom Listbox in Excel with VBA
- Modifying Data in Excel with VBA
Layout is closely related to navigation, but there are certain layout principles that should be followed to improve the user experience.
- Layout Overview
- 10 Tips for Great Spreadsheet Aesthetics and Design
- Conditional Formatting in Excel
- Protecting Layout and Formulas