I came across a horribly convoluted Excel spreadsheet this week. This wasn’t hard to do. The thing with Excel is that once you have a simple, working model that gets the job done, there is always the temptation to add just one more input, one more variable, one more tweak, to the point that your model is now so complex that only you can understand it. This only leaves your boss scratching his head and wondering why he hired you to make things that he can’t use.

Which leads to the point of this post: no one cares how fancy your spreadsheet is, they only care if they can understand it.

It took me maybe 3 or 4 years to get this, because for a long time, I was that guy. The one who would find a way to combine multiple obscure functions into a calculation that ran off the edge of the screen. Try explaining how this works to someone else:

=IF(SUM(OFFSET(google!$E$1,MATCH($B$3,google!$A:$A,1)-1,0):OFFSET(google!$E$1,MATCH($B$2,google!$A:$A,1)-1,0))=0,””,SUM(OFFSET(google!$E$1,MATCH($B$3,google!$A:$A,1)-1,0):OFFSET(google!$E$1,MATCH($B$2,google!$A:$A,1)-1,0))/SUM(OFFSET(google!$B$1,MATCH($B$3,google!$A:$A,1)-1,0):OFFSET(google!$B$1,MATCH($B$2,google!$A:$A,1)-1,0)))

What? I made that and even I can’t figure out what it does without some serious effort. I wouldn’t stop there, though; I would find a way to extend this madness over multiple tabs, making even my computer wince just at the thought of opening Excel.

In the beginning I thought this approach was fine. Surely, everyone must be SO impressed by how complex and precise my models are! And how useful it will be to our company now that we can model out revenue scenarios using relevant data such as the weather in Japan next Tuesday and the average wind speed velocity of an unladen swallow. 

This was my attitude for years. I had earned my Merit Badge in Excel and was proud of it.

But when I found myself on the other end of the equation, as the guy trying to wrap his head around the monstrosity someone else had made, I found myself saying “Oh….”. As in, “Oh, dear God, how am I supposed to use this thing?!?”. Which led to the even more horrifying realization that I had been inflicting this type of pain on others for years, unintentionally torturing co-workers and investors by asking them to understand a maze of formulas, columns, and rows that looked like it had been created in a drug-induced fever dream of mine. Oh, indeed.

Many consider navigating this to be a more favorable option than dealing with a Byzantine spreadsheet.
Many consider navigating this to be a more favorable option than dealing with a Byzantine spreadsheet.

Now that I know better, I’m a recovering member of Excel Freaks Anonymous. And I’m trying to make my spreadsheets much more user friendly. To help me get there, I created these 3 Excel Pro Tips for Helping Others Not Hate You, which I will share here:

1. Don’t confuse precision for accuracy. Just because your spreadsheet can handle forty different inputs doesn’t mean that your forecast is any more accurate. There’s probably already a huge margin of error involved in your model, because so much of what you’re doing is guessing anyway. Adding more variables than necessary will only make it more difficult for someone else to figure out how your model works.

2. If you have to be complex, give directions. OK, sometimes you do need to include those forty different inputs. Maybe you’re launching a space shuttle. Or perhaps you’re just trying to visit that chocolate factory you’ve always wanted to see and are attempting to predict the location of the next golden ticket. In those cases, pretend you’re going to walk your grandma through your spreadsheet. Write comments. Highlight sections where inputs are taken. Break complex formulas out into simpler ones so readers can walk their way through a calculation. Most importantly, take the time to lay out your work in an intuitive manner. Your grandma will thank you.

3. Colors are your friend. And you have so many to choose from! Using color to signify related data and to call out special attention will have you wining friends in no time. Here’s an example: all hard coded data is blue, formulas are black, and inputs are green. Just don’t go crazy and make your spreadsheet look like an Easter party. A dab will do.

So if you’re an Excel-meister, be kind next time you craft a spreadsheet and make something others can actually understand. And if you have any more Pro Tips and want to further humanity by sharing them here, please do!