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