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.

​​If you need assistance with  your spreadsheets, then please contact me.  
I can help with modeling for all sorts of purposes,
from internal analysis to due diligence to raise capital.

Contact Doug Schiller

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 :

1. Less variety of formulas
2. More defined names
3. Separate formulas from constants
4. Fewer errors
5. Easier to understand and update

​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.   

I know VBA, but it has many drawbacks (speed, fear of viruses, etc.).   I built a system that creates a custom ribbon application.   I built a spreadsheet accessory in JavaScript that provides functionality not available at all in Excel (CrushErrors.com).  I have an MBA from Columbia University.  

The solutions here will likely shock you because they are really unique.   Sophisticated.  Simple.   Fast.   

Summary of the Three Patterns

1. Introduction to Relative Named Ranges - Dramatically Reduce the Number of Formulas. 5 separate videos.  1 Embedded Excel File.
2. Summarize months by Years and Quarters.  Fewer formulas.   Less chance for error.  1 Video.  1 File for downloading.
3. 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
ONLY TEN FORMULAS

After a long time using Excel, I discovered using defined names to create simple formulas.  It takes some a little time because it's different, but its really amazing.

Many people think defined names can only be used for constants.   That's NOT true.  They can be used for relative formulas.   The simplest example is to define a name called "prior".   Every time you enter the word prior, it will retrieve the value in the cell immediately to the left.   

Even that alone should make an Excel user's life easier.

​​Here's how to do it.  Go to cell C13 on tab Sheet 1.   Go to Formula->Define Name.   (Alt MMD).   Then put the word "prior" in the "Name" box.  Then select the "Sheet 1" as the scope.   Then next to "Refers to:" enter "=D13".    

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 image below shows all of the formulas needed to make the demo file.   The demo file can be viewed online with this link.

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.

Pattern # 1 Demo file is below.  You can see it, but you cannot really see the underlying formulas unless you download.

---

​Pattern #2 - Summing Quarters and Years

​This pattern utilizes the Defined Named Ranges in Pattern #1 above but it actually can be deployed without that technique.   But, you'll see that it is so useful that it can be used in many many cases.   In this new pattern, we use SUMIF in a very smart way to sum years and quarters.   It's much easier than using SUM, SUBTOTAL, or just +.

To download the file, use this link.

Pattern #3 - Use Scenarios to Change all Assumptions

In this pattern, all constants are moved to a Scenario tab.   Set up several scenarios and label them with High, Medium, Low.   Or whatever terminology you choose.  Then use a drop-down (data validation) to select the scenario.  Then all of the constants and assumptions associated with that scenario flow into the rest of the spreadsheet. 

​To download the file, use this link. 

Contact Doug Schiller