My Philosophy On The Use Of Spreadsheet Tools

Spreadsheets are great tools. As much as computers have transformed the way we work (and play to some extent) the spreadsheet has been as important to transform the way the technical professional works. Many professionals use spreadsheets to do things that were once extremely time consuming. When I went to University in the early 1980s, we did plotting, data analysis, statistics and regression by hand or with the use of calculators. It took a long time and was easy to make mistakes, which then would require the calculation or plot to be re-done. By the time I started working in the mid 1980s, personal computers were making their way into the office and spreadsheets arrived shortly thereafter. First was Lotus 1-2-3, which was ground breaking, but eventually Excel became the standard. 

Good old Lotus 1-2-3

When I first started out using Excel, I used to build a new custom spreadsheet every time I wanted to do some plotting or some analysis. This process was typically to load in some data to a sheet, make some plots from this data, do some stats with built in Excel functions or do some regression. I have done this process many times, and had no problem with it. For the Engineer or other professional, this skill is very important to have. Being able to build a new spreadsheet and do some sort of analysis you've never done before is the power behind the flexibility in Excel. Engineers love Excel because they can ALWAYS find a way to do whatever they want to do.

I did find myself repeating the same type of analysis with different data sets. This was fine, but I realized that I could re-use some of the spreadsheets if I made them more flexible and easier to use. 

So, over the years my philosophy changed somewhat. Later on in my career, instead of creating a new spreadsheet for any analysis, I started creating spreadsheet tools that I could re-use over and over with different data sets. This I felt made me even more efficient and effective.  

For my own benefit, so I could remember how each spreadsheet worked, I adopted a consistent format for the spreadsheets. Input cells were always shaded in yellow. The cells were easy to spot and when you printed the sheet the yellow background did not drown out the text colour. I borrowed this idea from a famous reservoir engineer, Ed Woods, who I had the pleasure of working with for a short period of time. Most of the sheets were fairly plain formatting, but columns and rows were clearly annotated. I tried to stay away from macros as much as possible, as sometimes it is hard to follow how they work. A sequence of formulas in cells is easier to trace and debug, than doing calculations within macros. I adopted a version numbering system, so I could remember which spreadsheet had certain capabilities, otherwise I would get lost trying to remember which sheet had been updated or not. 

When I provide these tools to others, I never locked the formula cells, so that others could customize them also. I had so many spreadsheets, that it seemed that if anyone asked how to do something technical at work, my answer was often "Hey, I have a spreadsheet for that". Thus the inspiriation for a gift from some of my co-workers.

But even with some fairly useful pre-built tools, if I still wanted to do something custom, I would build a new spreadsheet, or customize one of these already existing tools. That's the beauty of a spreadsheet - the ease to be able to modify or customize. 

On this blog I will be posting some of my most useful spreadsheet tools. Almost all of them run in Excel, but a few I have ported to Google Sheets. Some are quite technical in nature, but some should be useful to many people. I hope you find them useful and the use of them saves you time and makes you more productive.