Doug Schiller Services - Repair and Build Excel
I'm the author of ExcelPatterns.com. I've used spreadsheets for decades. I've never shared my secrets till now (Oct 2018). I'm an Interim CFO. I've had experience in many industries. I also started successful CrushErrors.com, which finds needles in haystacks (meaning errors in tons of data). I have an MBA from Columbia University.
Excel Patterns - Secrets from Decades of Spreadsheet Use, Not Available Anywhere
I noticed Excel users struggle largely because Excel is essentially free form. (See WSJ story.) This site is dedicated to adding some structure to Excel without any extras. No add-ins. No VBA. Just standard Excel.
Patterns are strategies for handling specific problems.
The guiding principles are :
What is presented here is really not available anywhere else on the Internet. It represents my secrets that I've adopted over almost 40 years of using spreadsheets. I started with VisiCalc. Then Lotus 1-2-3. Then Javelin. Then Symphony. Then Excel. I've built 100 MB spreadsheets. I've read tons of books and always look forward to the latest and greatest improvements.
The solutions here will likely shock you because they are really unique. Sophisticated. Simple. Fast.
Summary of the Three Patterns
- Introduction to Relative Named Ranges - Dramatically Reduce the Number of Formulas. 5 separate videos. 1 Embedded Excel File.
- Summarize months by Years and Quarters. Fewer formulas. Less chance for error. 1 Video. 1 File for downloading.
- Create a Scenarios tab that allows you to play with the assumptions in a very coherent manner. 1 Video. 1 File for downloading.
You can expand the videos and watch full screen or watch on YouTube directly.
Pattern # 1 : Forecast Expenses, 3 years
That's it. Now wherever you go on the Sheet 1 tab, you can use "=prior" to retrieve the value in the column immediately to the left. That's easy. but it's just the beginning. There are so many advantages of this pattern.
You have to watch the videos to really understand.
See the general layout of the demo file You'll see how there are only 10 formulas.
You'll see the first two components of the primary formula. You'll see how overrides are calculated. This technique avoids using hard coded values.
The pattern (or technique) of using Relative Defined Names makes it very easy to create Conditional Formats for cells. This video shows how the cells are automatically highlighted when the values are sourced from the OverRide tab.