Despite being staggeringly error prone, spreadsheets are a highly flexible programming environment.
Abreu, et al (2015)
The quality and reliability of spreadsheets is known to be poor.
Bishop & McDaid (2007)
Spreadsheets are commonly used and commonly flawed.
Caulkins, Morrison, & Weidemann (2008)
Every study, without exception, has found error rates much higher than organizations would wish to tolerate.
Panko (1999)
Never assume a spreadsheet is right, even your own.
Raffensperger (2001)
Every study that has looked for errors has found them... in considerable abundance.
Panko & Halverson (1996)
1% of all formulas in operational spreadsheets are in error.
Powell, Baker, & Lawson (2009)
Programmers exhibit unwarranted confidence in the correctness of their spreadsheets.
Krishna, et al (2001)
Spreadsheet errors... a great, often unrecognised, risk to corporate decision making & financial integrity.
Chadwick (2002)
Spreadsheets are extraordinarily and unacceptably prone to error.
Dunn (2010)
Spreadsheet errors are still the rule rather than the exception.
Nixon & O'Hara (2010)
The untested spreadsheet is as dangerous and untrustworthy as an untested program.
Price (2006)
The results given by spreadsheets are often just wrong.
Sajaniemi (1998)
Even obvious, elementary errors in very simple, clearly documented spreadsheets are... difficult to find.
Galletta, et al (1993)
Spreadsheets are easy to use and very hard to check.
Chen & Chan (2000)
Errors in spreadsheets are as ubiquitous as spreadsheets themselves.
Colbenz (2005)
Spreadsheets are the most popular live programming environments, but they are also notoriously fault-prone.
Hermans & van der Storm (2015)
Your spreadsheets may be disasters in the making.
Caulkins, Morrison, & Weidemann (2006)
It is now widely accepted that errors in spreadsheets are both common and potentially dangerous.
Nixon & O'Hara (2010)
Despite overwhelming and unanimous evidence... companies have continued to ignore spreadsheet error risks.
Panko (2014)
60% of large companies feel 'Spreadsheet Hell' describes their reliance on spreadsheets.
Murphy (2007)
Spreadsheets contain errors at an alarmingly high rate.
Abraham, et al (2005)
Spreadsheets... pose a greater threat to your business than almost anything you can imagine.
Howard (2005)
Overconfidence is one of the most substantial causes of spreadsheet errors.
Sakal, et al (2015)
Untested spreadsheets are riddled with errors.
Miller (2005)
Spreadsheet shortcomings can significantly hamper an organization's business operation.
Reschenhofer & Matthes (2015)
Spreadsheets are alarmingly error-prone to write.
Paine (2001)
...few incidents of spreadsheet errors are made public and these are usually not revealed by choice.
Kruck & Sheetz (2001)
Spreadsheets have a notoriously high number of faults.
Rust, et al (2006)
Spreadsheet errors have resulted in huge financial losses.
Abraham & Erwig (2007)
Spreadsheets are notoriously error-prone.
Cunha, et al (2011)
Spreadsheets are dangerous to their authors and others.
Durusau & Hunting (2015)
Most large spreadsheets have dozens or even hundreds of errors.
Panko & Ordway (2005)
Spreadsheet development must embrace extensive testing in order to be taken seriously as a profession.
Bock (2016)
People tend to believe their spreadsheets are more accurate than they really are.
Caulkins, Morrison, & Weidemann (2006)
Spreadsheets are often hard, if not impossible, to understand.
Mireault & Gresham (2015)
Studies have shown that there is a high incidence of errors in spreadsheets.
Csernoch & Biro (2013)
Most executives do not really check or verify the accuracy or validity of [their] spreadsheets...
Teo & Tan (1999)
Spreadsheet errors are pervasive, stubborn, ubiquitous and complex.
Irons (2003)
Errors in spreadsheets... result in incorrect decisions being made and significant losses incurred.
Beaman, et al (2005)
A significant proportion of spreadsheets have severe quality problems.
Ayalew (2007)
Developing an error-free spreadsheet has been a problem since the beginning of end-user computing.
Mireault (2015)
94% of the 88 spreadsheets audited in 7 studies have contained errors.
Panko (2008)
The issue is not whether there is an error but how many errors there are and how serious they are.
Panko (2007)
Spreadsheets can be viewed as a highly flexible programming environment for end users.
Abreu, et al (2015)
Spreadsheets are more fault-prone than other software.
Kulesz & Ostberg (2013)
It is irrational to expect large error-free spreadsheets.
Panko (2013)
The software that end users are creating... is riddled with errors.
Burnett & Myers (2014)
Research on spreadsheet errors is substantial, compelling, and unanimous.
Panko (2015)
A lot of decisions are being made on the basis of some bad numbers.
Ross (1996)
How to use Conditional Formatting in Excel

How to use Conditional Formatting in Excel

12 May 2019

This article shows over 20 different ways that you can use Conditional Formatting, starting with some basics and presenting some advanced techniques as well.

Many of the examples are templates that you can download and experiment with.

Average top 3 scores

Average top 3 scores

6 May 2019

This short article shows you how to average the top n values in a set of data.

The technique uses the LARGE function to select the required values, which are then evaluated by the AVERAGE function.

Excel trendline types, equations and formulas

Excel trendline types, equations and formulas

20 January 2019

In this tutorial, you will find a detailed description of all the trendline options available in Excel and when to use them.

You will also learn how to display a trendline equation in a chart and find the slope of a trendline.

Topics:

  • Excel trendline types.
  • Excel trendline equations and formulas.
  • Excel trendline equation is wrong - reasons and fixes.
The double negative in Excel formulas

The double negative in Excel formulas

10 January 2019

In more advanced Excel formulas, you might run into the double negative operation (--).

The double negative (sometimes called the even more nerdy "double unary") coerces TRUE or FALSE values to their numeric equivalents, 1 and 0. It's used in formulas where numbers are needed for a particular math operation.

A double negative is not the only way to get ones and zeros from logicals. You can also add or subtract zero, multiply by one, or use the inscrutably named N function.

Financial Modelling Code

Financial Modelling Code

16 November 2018

Financial models are often built ad hoc or without due planning, care and attention, and stories of faulty financial models are common.

ICAEW has created this financial modelling code to provide essential guidance to standard-setters, procurers and modellers on good practice. The guidance is principles-based, high-level and broadly accepted.

The code is divided into the following sections:

  • Model definition and purpose.
  • Layout and structure.
  • User interface and transparency.
  • Consistency.
  • Clarity.
  • Error reduction.
  • Calculation techniques.
Using an Excel Table within a Data Validation list

Using an Excel Table within a Data Validation list

8 November 2018

Excel Tables expand automatically whenever new data is added to the bottom. Data Validation lists would benefit from the auto-expand feature… but it just doesn't work.

This article shows you how to make a Data Validation list that refers to a Table, via three methods:

  • Normal cell references over a Table.
  • Named Range of the Table column.
  • INDIRECT function.
50 things you can do with Excel Power Query

50 things you can do with Excel Power Query

26 October 2018

Comprehensive list of things that you can do with Power Query.

Excel Power Query is one of the most powerful new features within Microsoft Excel and the easiest to learn.

You can use Power Query to clean & transform your data that you download from your ERP or accounting system and display it in a report for Excel to work with.

The best thing is that you can reapply the same transformation steps in just ONE single click, saving you HOURS! So next week when you get the same report with updated data, a simple REFRESH will transform your data once again!

As of October 2018, this interactive tutorial continues to be expanded, with 33 of the 50 topics completed. New topics are being added.

Preview of dynamic arrays in Excel

Preview of dynamic arrays in Excel

25 September 2018

New features coming to Excel soon.

Until now, you wrote a formula for each value you wanted returned to the grid. One formula, one value. If you wanted another value, you wrote (or copied) another formula.

With dynamic arrays, that all changes.

Now, you can write a formula, hit the enter key, and get an array of values returned. One formula, many values. This will allow you to build more capable spreadsheets, faster, with fewer formulas and less chance of error.

Here is the full set of functions that will be accompanying dynamic arrays:

  • FILTER Filters an array of data based on criteria you define.
  • UNIQUE Returns a list of unique values from a list or range.
  • SORT Sorts an array of values.
  • SORTBY Sorts an array based on a corresponding array.
  • SEQUENCE Generates a list of sequential numbers, such as 1, 2, 3, 4.
  • SINGLE Accepts a range or array and returns a single value using implicit intersection. (Superseded)
  • RANDARRAY Returns an array of random numbers between 0 and 1.

For more information, also see Mr. Excel: Major calc engine change.

The guide to finding and removing external links from your Excel file

The guide to finding and removing external links from your Excel file

23 September 2018

So you're on a mission to remove external links from your Excel workbook, huh?

Seems like it should be easy, but as you are probably finding out, it sometimes isn't as easy as clicking the "break links" button (unfortunately).

This post walks you through all the hiding places that external links may be lurking, including:

  • Cells.
  • Charts.
  • Shapes.
  • Names ranges.
  • Pivot Tables.
  • Data Validation.
Go to top