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