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

Excel & genes: mutation and curation

3 September 2016

On 23 August a paper by three Australian authors gained much publicity about how Excel interprets data entry and how the users don't check their data.

The root problem is that the researchers who uploaded their data into Excel NEVER checked them. The reason they never check is because Excel is seen as a simple application, not programming or software development or data management.

Abstract of the source paper: "The spreadsheet software Microsoft Excel, when used with default settings, is known to convert gene names to dates and floating-point numbers. A programmatic scan of leading genomics journals reveals that approximately one-fifth of papers with supplementary Excel gene lists contain erroneous gene name conversions."

Writing a long formula in steps

Writing a long formula in steps

14 August 2016

Writing a long formula is not easy, even for an advanced Excel user. What I mean long is a formula with many nested FUNCTIONS within a single formula. The difficulty I am talking about is not related to whether you understand the functions. It is more about the concentration and carefulness required for writing the formula.

The key to writing a long, complex formula is to break the formula into bite-size formulas with helper columns. This article illustrates the process using an example of converting poorly written text into a consistent format.

How to calculate CAGR in Excel

How to calculate CAGR in Excel

5 August 2016

We explain what the Compound Annual Growth Rate (CAGR) is, and how to make a clear and easy-to-understand CAGR formula in Excel.

In simple terms, CAGR measures the return on an investment over a period of time. CAGR is often used by financial analysts, investment managers and business owners to figure out how their business has developed or compare revenue growth of competing companies.

We show four methods for calculating a CAGR:

  • Create a CAGR calculator in Excel using an arithmetic equation.
  • CAGR formula based on the POWER function.
  • CAGR formula based on the RATE function.
  • CAGR formula based on the IRR function.
Spreadsheet competency framework

Spreadsheet competency framework

8 July 2016

This spreadsheet competency framework is a structure for assessing ability and proficiency when using spreadsheets.

It can be used by recruitment agencies, companies, job seekers, training providers and anyone else who uses spreadsheets to carry out their role.

The framework consists of four key skill groups, described as levels. Each has its own expected core competencies, and other indicative skills:

  • Basic user. Carries out data entry tasks in spreadsheets, and will have only the most fundamental knowledge necessary to be able to interact with a spreadsheet.
  • General user. Modifies spreadsheets, rather than creates sophisticated spreadsheets from scratch.
  • Creator. Uses spreadsheets as a primary element of their role, and needs to consider how to create and manage spreadsheets of a greater degree of complexity.
  • Developer. Truly expert spreadsheet users, who are familiar with most of the core functionality of spreadsheet packages, and are able to develop high-complexity spreadsheets in a multi-user environment.

This competency framework builds on the twenty principles for good spreadsheet practice.

VBA objects – The ultimate guide

VBA objects – The ultimate guide

30 June 2016

This is a comprehensive article about creating and using objects in Excel VBA.

If you are serious about learning VBA then it is important to understand VBA Objects. Using objects is not that difficult. In fact, they make your life much easier.

In this post, you will see how VBA makes brilliant use of objects. How objects such as Collections, Workbooks and Worksheets save you much complexity, time and effort.

Topics include:

  • What is a VBA object?
  • Object components.
  • Creating a VBA object.
  • Assigning VBA objects.
  • VBA objects in memory.
  • Why Set is useful.
Estimating projects - Spreadsheet Development Life Cycle

Estimating projects - Spreadsheet Development Life Cycle

11 June 2016

Modeling involves more than just putting formulas in a spreadsheet. We need to do all steps in the Spreadsheet Development Life Cycle (SDLC). These steps are essential to successful projects and making sure our client's expectations are met.

The steps in the Spreadsheet Development Life Cycle, and the proportion of project time that they typically take, are:

  • Research (5%-15%).
  • Analysis (5%-15%).
  • Design (5%-15%).
  • Construction (25%-50%).
  • Testing (25%-50%).
  • Implementation (1%-20%)
Count cells with text and formula – Excel stats

Count cells with text and formula – Excel stats

7 May 2016

In Excel we often like to count things. Sometimes those things are cells with text, formulas or formatting. Other times we want to count blank or non-blank cells… and so on.

This article teaches you all about counting things in Excel, using:

  • Worksheet functions to count various types of cell contents.
  • A neat VBA macro that counts every function used in all Excel formulas and presents a neat report as a result.

Note: for the VBA code to work, you'll need to save your workbook as an "Excel Macro-Enabled Workbook (*.xlsm)". The code also requires that each worksheet contains at least one formula and at least one non-formula (otherwise it will crash).

10 ways to present variance analysis reports in Excel

10 ways to present variance analysis reports in Excel

30 April 2016

Whether it is in daily life or at work, we are always making comparisons to see what's good and what is not so good. This differential analysis is called variance analysis.

Here are 10 techniques to take variance analysis beyond simple numbers and percentages:

  • Use brackets for negative numbers.
  • Color the content or data – custom number format.
  • Use arrows.
  • Use cell color.
  • Use arrows – Conditional Formatting.
  • Use data bars.
  • "In-cell" charts.
  • Use scaled-down actual charts.
  • Make better variance charts.
  • Highlight instances in chart.
How to properly filter zeros and numbers with the Filter drop-down menus

How to properly filter zeros and numbers with the Filter drop-down menus

17 April 2016

Often times we are working with a list or data set that contains a lot of zeros in a column. We might want to filter these zeros out to shorten the list, so that we only see numbers that are greater than or less than zero. One easy way to do this is to uncheck the zero (0) item in the filter drop-down box.

However, this can produce unexpected results. Learn the correct way to filter out zeros and numbers with the filter drop-down menus in Excel, and avoid embarrassing mistakes.

Go to top