Our first phone call from a new Excel consulting client often goes like this: “I have all these spreadsheets that don’t connect very well, what can I do?” Sometimes our clients are looking for new formulas that can tie things together better. Other times, they are looking for automation that can make their processes run smoother. Some clients also realize that their spreadsheet “house of cards” cannot continue as it is for very much longer. Fortunately, our business consultants can handle all of these requests.
The extent to which each client uses varies greatly. Some of the calls we get relate to a workbook that is used for a specific purpose which is just a small part of the overall business. Others are from clients where Excel serves a mission critical purpose in running their business.
Since we are Excel consultants, we have no problem with mission critical spreadsheets of course. In fact, we do believe you can run your business with just Excel (not that you should – more on that later). One of our key responsibilities in consulting with clients that operate with mission critical spreadsheets is to give them the full range of options for improving their systems and processes.
Here are 5 approaches we take with clients to address their mission critical spreadsheet situations.
1. Spreadsheet Design
One common issue that can keep a spreadsheet from accomplishing its purpose is a bad design. If Excel spreadsheets are a mission critical part of your business, they need to be built properly. We often see situations where a workbook started out just fine at the beginning but after years of additions and changes the workbook is now an unstable mess. I admit, I have done this plenty of times myself! It is very easy to do given the flexibility of (which is why we all love it).
Sometimes the best approach to improving a mission critical spreadsheet is to do a little redesign of the layout. Maybe a single sheet needs to become several sheets to clarify the data. Maybe several sheets can be brought together into a single, well organized sheet. In many situations, we add a few tables into the mix to simplify formulas and improve the ability to maintain the spreadsheet.
The possibilities are endless and there is often more than one approach that can be taken. The key is to get the design right. This helps guarantee accurate data and efficient use.
2. Advanced Formulas
Sometimes the right set of formulas are the only difference between an efficient and accurate spreadsheet and a spreadsheet that cannot be trusted in a mission critical setting. Excel has a vast number of functions for almost any purpose.
The functions that are the most misunderstood or underutilized come in 3 flavors:
- Lookup functions (VLOOKUP, HLOOKUP, XLOOKUP, INDEX, MATCH)
- Conditional functions (IF/THEN, AND, OR)
- Summary functions (SUMIF, COUNTIF, SUMPRODUCT, MAXIF, AVERAGEIF)
When the data in one or several spreadsheets can be improved by using formulas, the answer is usually found in one of those function categories. Mission critical spreadsheets need to be built properly to deliver accurate and consistent results. Using the right can make a big difference.
It is also important to keep your Excel version up to date! Most of us are using Microsoft 365 for our Office products these days which is what you should be doing. We still get calls, however, from clients that are running an Excel version that is 10 years old. If you use Excel for mission critical purposes, make sure you invest a little bit of money in the latest version. Many useful functions did not exist 10 years ago, so you are handicapping yourself before you start. Use Microsoft 365!
3. VBA Macro Automation
VBA stands for Visual Basic for Applications. VBA is a powerful programming language that is embedded inside all Microsoft Office products. You may have used VBA without knowing it if you ever recorded a macro. When you record a macro in Excel, VBA code is written for you and it is what allows that macro to run each time you push a button or use a keyboard shortcut. When we help clients with VBA automation, we do not record macros but instead write the code as you would with any other programming language.
The possibilities with VBA are endless. My answer to clients when they ask, “can this be done?” is always a confident “yes!” If something cannot be accomplished with formulas, it can be accomplished with VBA. And yes, there are plenty of things that require the use of VBA.
Here are some of the more common ways we use VBA in Excel:
- Automate the inserting or deleting of rows (you cannot do that with formulas).
- Bring data together from multiple sheets or multiple files into a single output.
- Write a custom function when Excel does not have exactly what is needed.
- “Loop” through rows or columns to apply complex logic to large datasets.
- Create a form-driven process flow in a workbook (maintain data in forms instead of in cells).
- Create complex data validation rules.
- Extract portions of a large dataset based on complex logic.
- Add a custom menu ribbon to simplify user navigation throughout a complex workbook.
- Convert a csv file from one format to another.
- Send an email (through Outlook or Gmail).
- Push data to a Word document or a PowerPoint presentation.
- Bring data into the workbook from a website or other business application.
- Geo-code addresses and plot the results on Google Earth.
You get the idea. The possibilities are limited only by our imagination in most cases. The VBA language inside of Excel is what really has the power to turn an Excel workbook into a mission critical application. So, can it be done? Yes, it can!
4. Database Integration
One of the most exciting things we do with Excel in this era of “cloud” is to connect workbooks with databases on Microsoft Azure. This connection is usually established by using VBA. Under this scenario, Excel becomes the user interface for the application and all the data is stored in the database.
Here is why we do this:
- When the data is stored in a database, we have created a multi-user application where many people can have a copy of the workbook and access the same data.
- This approach allows us to create a very well organized and comprehensive data model which is easy to do with a database but difficult to do with flat Excel sheets.
- This type of application allows our clients to still benefit from the native functionality of Excel while also adding additional functionality specific to their business process.
- This approach is a cost savings over a more traditional custom software approach. We can build a custom application in Excel for a fraction of the cost usually required.
- When the data is in a cloud database, we can now access it with other tools as well such as Power BI or Power Apps. The Excel part of the application does the hard work, but we can also make the data available on your mobile device and through the web if needed.
The best way to appreciate this approach to custom applications is to see it in action. Contact us if you would like to see a few examples. VBA together with a SQL database on Azure provides a very powerful platform to build mission critical applications for your business using Excel.
5. New Technology Platform
Many of our clients started their business by using Excel for many different purposes. As their business grew, the Excel approach started to strain under the growing requirements that needed to be addressed. Sometimes this can be fixed with the Excel approaches I have outlined above. But in other cases, Excel is no longer the answer. Excel is an amazing tool, but sometimes it is not a good solution for what needs to be accomplished.
A very common conversation with our clients who use Excel for mission critical purposes is a discussion of alternative solutions. Thesis Technologies offers several software solutions to our clients including Excel, Microsoft Access, Dynamics 365, Power Platform, and more. Each of these technologies has benefits and shortcomings that are important to consider when decisions need to be made. We help our clients evaluate these options and pick the right solution for them.
As I mentioned earlier in this post, you can run your entire business with Excel but that doesn’t mean you should!
Summary
As companies grow over time, they sometimes become reliant on mission critical Excel spreadsheets to run their business. As these spreadsheets begin to strain under the weight of a growing business, there are several options to consider. These options include spreadsheet design, advanced formulas, database integration, VBA automation, and choosing a new technology platform for the future.
About the Author
Brian Busscher is Founder and CEO of Thesis Technologies which pursues a mission to “help you get your systems right.” If you would like to learn more about how Excel spreadsheets can be taken to a new level, please contact the Thesis team at 888-705-7253.
Find other Excel blog posts here.
This blog post has been updated.