There are several ways to approach custom software development. You can develop custom applications from the ground up using traditional development tools such as HTML and C#. You can also build custom applications using “low-code” tools such as Microsoft Power Apps. Custom applications are usually composed of a user interface (webpages, forms, menus) and a database that contains all the data.
Over the last few years, we have launched a new approach to custom software development which utilizes Microsoft Excel as the user interface and a SQL Azure database in the cloud to manage all the data. Many of our clients love this approach because they are already familiar with Excel. Adding a database helps them solve a lot of problems such as having a large collection of individual spreadsheets in a massive directory of folders. It is almost impossible to utilize data efficiently that is stored in this manner. The database is a game changer when used in combination with Excel.
The Workbook
When developing business applications in Excel, the workbook is what the user sees and interacts with. Instead of asking the user to navigate through the workbook by working through many sheets, we hide all that complexity and add a custom menu to the workbook. A typical custom menu looks like this:
The standard Excel menu options are still available to the user, but a new custom menu is added to allow . The sheet tabs are typically hidden, and everything is controlled using this menu.
This type of application also makes good use of Excel “user forms” which are input forms that can be customized for any purpose. Excel user forms allow us to create a form driven process in the workbook which makes it very clear to the user where they enter data and how they launch various processes within the application. Typical Excel user forms look like this:
The Database
The technological breakthrough that makes this approach so economical is the ability to provision a SQL database in Azure for as low as $5 per month. SQL Server is Microsoft’s premium database system used by customers across the globe. Before the cloud, having a SQL Server database to utilize in this manner would require a very expensive server and very expensive software. Today, this is amazingly simple and cheap to accomplish on Azure.
By using a database connected to Excel, we can create a multi-user environment for our clients and manage a very large amount of complex data. The data is brought into the workbook when requested by the user through SQL calls made with Excel VBA (Visual Basic for Applications). When data is organized and stored in a central location instead of stored in dozens or hundreds of workbooks in folders all over the company, it’s a whole new situation with a lot of benefits.
Business Benefits
- Lower cost approach to custom software development.
- Faster development and shorter time to process improvement.
- Well organized data which provides new insights and new efficiencies.
User Benefits
- Provides a familiar user interface. Everyone already works with Excel.
- Creates a multi-user environment (each person just needs a copy of the workbook).
- Menu and Form driven process for efficient and fool-proof navigation and use.
- Powerful Excel functions are still available just like your typical spreadsheet.
Data Benefits
- Move from many disconnected workbooks to a single workbook.
- The database can handle very large amounts of very complex data.
- Your data is secure and backed-up on a regular basis.
- Your data maintains its integrity in this controlled environment.
- Your data can also be accessed with or other modern tools.
Summary
Excel Database Applications are a new way to create powerful business applications for a fraction of the cost of a typical custom software application.
About the author: Brian Busscher is CEO and Founder of Thesis Technologies which pursues a mission to “help you get your systems right.” If you would like to learn more about Excel Business Applications, please contact the Thesis Team at (888) 705-7253.
Find other posts here.