Save time in complex spreadsheets - Evaluate Formula

You've probably been there.  You have a spreadsheet formula that is complex, and it is not giving you the answer you expect.  You have looked at this spreadsheet for longer than you want to, and you don't know where to look next to find the problem.

1. First, congratulations.  You realized it was the wrong number before you sent the results out to the rest of your team.

2. Second, you still have a problem.  You can't find the error in the middle of all the parentheses, functions, and other clutter.

In comes "Evaluate Formula" to help solve the problem.  Evaluate Formula is used to "step through" the portions of your formula.  "Step through" is a common programming term that means to do something one step at a time, so you can find the problem in the middle of a multi-step process.  

Usage:

Each time you push Evaluate, one single portion of the Excel formula in your current cell is executed.  

Press Evaluate, then look to see if that portion of the formula pulled in the numbers in Excel that you expected.  Excel highlights in blue in the upper box which portion of the formula it just ran, then shows the result in the lower box.  If that intermediate result was correct, press evaluate again to see the next portion.

Repeat until you find the number Excel is using that you didn't expect.  Correct that portion of the formula, and then Evaluate again to make sure that new cell formula gives the correct result.  This process can take time, but it also gives you a step-by-step task list of items to check.

How to find it:

Evaluate Formula is found under Tools->Formula Auditing in Excel 2003.  Search for "Evaluate Formula" in Excel help to find it in newer versions of Excel.

More information:

Additional helpful details can be found found at Mr Excel (a straightforward, reliable source of help) and on Microsoft's website.

Under-appreciated REPT() - Quick Lightweight Excel Data Analysis

One of the most frequent tasks I face is to start forming a point of view on 50-1000 rows of information.  Common data sets are a large list of projects, a list of sales figures, an interesting bit of log file data, my own list of credit card transactions, or pricing patterns in a large corporation.  

I almost always use a variation of REPT() to get started.  I haven't seen anyone else use this method in the corporate world, oddly.  The common solution I see many people use is to sort the data, scan it visually, attempt to coalesce the data, then form an Excel chart around it.  Excel charts are difficult to place inline with other data, though, and Excel charts only show about 200 different bars before they become unwieldy.  

REPT("|",100) will draw a bar 100 units tall very simply.  For me, this solves 80% of my charting needs when I'm looking at new data.

Below is a bar chart of the 700 major IT projects the Federal Government has pursued in 2010, from data dot gov.

It becomes straightforward to see the relative size of 700 projects.  I simply added the portions in green to the raw data set in about 5 minutes, then sorted.  The full descriptions are available in the xls version of this data below, and they contain some interesting descriptions of government initiatives.

I am an enthusiastic reader of Edward Tufte's work.  He formed the idea of a sparkline -- intense, simple, word-sized graphics.  A Frenchman who was writing macros around the sparkline first turned me on to the REPT() function in Excel as a way to visualize data in a bar chart fashion.  I've lost that link, but now Juice Analytics has done a very complete piece on the idea.

Excel 2007, 2010 now have a sparklines like concept, called "Data Bars" in Excel 2007.  For me, I've found that plain old REPT() is still the quickest, most flexible way to move my analysis forward.

MVPS

Microsoft has a long list of existing MVPS.  For inspiration, it's worthwhile to take an hour or two and read through what others are doing to be creative in Excel:

http://www.mvps.org/links.html#Excel

Excel HowTo

J-Walk is considered one the of the best.  After doing a lot of googling, I found he was constantly referenced by others.  I'd recommend reading through his blog for a while.  Some Excel sites you find are riddled with ads, and occasionally incorrect, so I'd start here.

Here's is John Walkenback's "for dummies" book.  Several of these are probably at Barnes and Noble if you wanted to go read up one evening to get an idea which one you like.

Many offices will still have Excel 2003, which is just fine for 90% of tasks. 

http://spreadsheetpage.com/index.php/book/C5/

The later versions of Excel make things easier and better looking.  If you have Excel 2007 or Excel 2010, John has written a book about that, too.

http://spreadsheetpage.com/index.php/book/C45/

Why Real World Excel?

A relative of mine has become the go-to guy at his work for Excel questions recently.  I was asking about his work, and we started talking about Excel.  He has made a name for himself by showing useful patterns in warehouse data at his work.  He did such a nice job that the company created a new position for him to do data analysis full time.  He is being bombarded by Excel books and "Is this possible?" questions at work, and he was asking me how I learned Excel.

I'm a software engineer by training.  As I have worked, I have found that no matter the problem, Excel usually becomes a tool in some part of delivering a piece of work.  I've seen that virtually everyone around me uses Excel extensively as well -- project managers, marketing analysts, salespeople, investors, engineers, administrators.  I expect many other professionals will also go through the advanced Excel learning process, and so I'm putting my work online with the expectation that it will save someone else time one day.