Over the years, I’ve consulted with dozens of companies on many different software tools: manufacturing systems, financial tools, CRM systems, order entry, reporting, etc., etc. The variety of software in use today is dizzying, and it’s rare for me to have more than one client using the same tools. But there’s one tool that I know I’ll encounter at every one of them: Excel.
When Excel first emerged on the scene in the late 80s, unseating Lotus 1-2-3 as the go-to spreadsheet tool, it was perceived as an accountant’s tool, mimicking the format of paper ledgers. Its strength was in its ability to process complex calculations at faster-than-the-human-brain speeds, and it was very good at that.
Over the years, though, it’s become clear that Excel is much more than a financial tool. Sure, it’s great at crunching numbers. But it also shines at presentations, with its array of charts and graphs that make data visualization easy. It’s an effective reporting tool, connecting seamlessly to big databases stored in less user-friendly tools like Oracle and SQL Server. It’s a great platform for data analysis, with hundreds of build-in functions, like VLOOKUP, to facilitate your work.
These are just a few of the reasons why just about every successful business uses Excel. A few years ago, I made a career out of building databases, and the associated tools for data entry and reporting. Now, I spend much of my time connecting Excel to database tools and using Excel programming to build dashboards and reports that can be directly manipulated by users. The days of static reports that are printed on paper are gone. Now we get the data into the hands of the end user, so that they can twist it and turn it to answer whatever questions the business asks of it. Which is very cool stuff.
As an experienced software developer and database designer, I’ve had little trouble transitioning into Excel programming, but without that background, it can be tough to make Excel jump through your particular hoops. So in today’s blog, I’m going to share some hard-earned wisdom with you that will make your Excel work a little easier.
Today’s topic is Scaffolding.
When we talk about scaffolding in the realm of construction, we’re talking about a physical structure that gives us a frame to work from while building a structure or working on one. It’s a support system that gives us relatively easy access to the various parts of the building that would otherwise be out of reach.
In education, scaffolding means that we build knowledge support structures, and then we hang additional knowledge on those structures, gradually building a complete knowledge product with a sturdy base.
In Excel, I use a scaffolding technique when building complex formulas. I start by creating a simple structure to support the rest of my work, and then I build on to that structure, step by step, until I have a finished product. Much like in construction, the scaffolding provides a sturdy frame to work from. And as in education, the scaffolding also acts as a base to build bigger and better things.
Let’s look at an example. I am often asked to create complex formulas in Excel which nest multiple functions, like this:
=IF(AND($K9>$B$2, $K9<=$C$2), $A$2, IF(AND($K9>$B$3, $K9<=$C$3), $A$3, IF(AND($K9>$B$4, $K9<=$C$4), $A$4)))
This formula uses just two functions: IF and AND, but it uses each repeatedly, which makes the formula a little hard to decipher at first glance. We have an “If” statement with an “AND” statement embedded in it; and then another “IF” statement embedded in the first with its own embedded “AND” statement; and one more embedded “IF” statement with an embedded “AND”. In this scenario, the hard part is getting all of the punctuation in the right places. Misplace a comma or a parenthesis and the formula will throw an error, or worse, will generate the wrong value.
Trying to build this entire formula is one pass is tough, and I almost never get it right the first time. That’s where the scaffolding comes in. I start by creating a formula with just one of these functions, and then add the others one at a time, testing the results after each one. Let me break it down step by step:
1. =IF($K9>$B$2, “Yes”, “No”)
That’s all I put in on the first pass. Then I check to make sure I’m getting a “Yes” in this cell when K9 is greater than B2, or a “No” if its not. The “Yes” and “No” are just placeholders, so that I can make sure the formula is working correctly. As I progress, I’ll replace them with additional formulas. This is the basis of my structure – the beginning of my scaffold, if you will.
2. Now I start adding on to my scaffold :
=IF(AND($K9>$B$2, $K9<=$C$2), “Yes”, “No”).
Here, I’ve added the first “And” condition. Now I check to make sure I only have “Yes” in this cell if K9 is greater than B2 AND K9 is less than or equal to C2.
3. Now I replace the “Yes” with my next “IF” statement. Once again, I keep it simple at first, adding only one condition. In a later step I’ll add the associated “AND” statement.
=IF(AND($K9>$B$2, $K9<=$C$2), IF($K9>$B$3, “Second Yes”, “Second No”), “No”)
Notice that I used different placeholder text here, so that it’s easy for me to see which formula is giving me the resulting value.
4. Next I replace the “No” with a simple IF statement, which, again, will be expanded on in later steps:
=IF(AND($K9>$B$2, $K9<=$C$2), IF($K9>$B$3, “Second Yes”, “Second No”), IF($K9>$B$4, “Third Yes”, “Third No”))
5. Next I’ll add in the second “And”, wrapping the “$K9>$B$3” into an “AND” function:
=IF(AND($K9>$B$2, $K9<=$C$2), IF(AND($K9>$B$3, $K9<=$C$3), “Second Yes”, “Second No”), IF($K9>$B$4, “Third Yes”, “Third No”))
6. I’m sure you see where I’m going with this. I’m going to keep adding on to my scaffolding, adding the final “AND” and replacing all of the placeholder text with the actual values.
This method may seem like it’ll take longer than entering the entire formula in one pass, and that’s true, but ONLY if you can type the whole formula in without making any mistakes. When I try to enter it all at once, I inevitably miss a comma or add an extra parenthesis, and then the process of debugging and correcting often ends up taking even more time. In addition, this scaffolding method also allows me to test as I go along, giving me confidence that I’ll end up with a formula that delivers the correct result in all scenarios.
I hope you find this technique helpful in building your own Excel formulas. As always, don’t hesitate to reach out to me if I can help with your Excel project: https://www.northportsolutionsllc.com/excelhelp
About the Author
Kimberlee Martin is the owner of North Port Solutions and has 30 years of business experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at kmartin@northportsoftware.com.
© 2020 North Port Solutions, LLC. All right reserved.