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

Connexion

Connexion is a collection of the most useful and interesting spreadsheet-related articles from the web.

We review more than 200 websites and blogs to collect the best articles on tools, tips, and techniques that help you to improve your spreadsheets. Each article here is just a snippet — click on the title to open the full article.

Number of Connexion articles: 246

Plot blank cells and #N/A in Excel charts

Plot blank cells and #N/A in Excel charts

7 February 2018

A common problem around web forums and blogs is how to plot blank cells in Excel charts.

There is a lot of confusion about plotting of hidden and empty cells, about what constitutes a blank cell, and about various workarounds that purport to produce blank cells that will or will not be displayed in a chart.

A new feature in Excel 2016, Show #N/A as an empty cell, solves the pain and frustration experienced by generations of Excel users trying to avoid plotting what look like apparently blank cells.

Header and footer in Excel: how to insert, edit and remove

Header and footer in Excel: how to insert, edit and remove

18 January 2018

Do you want to know how to make a header in Excel? Or are you wondering how to add the footer "page 1" to the current worksheet?

This tutorial will teach you how to quickly insert one of the predefined headers and footers and how to create a custom one with your own text and graphics.

Topics include:

  • How to insert header in Excel.
  • How to add footer in Excel.
  • Insert a preset header or footer.
  • Create a custom header or footer.
  • How to change header and footer in Excel.
  • How to close header and footer.
  • How to remove header and footer in Excel.
  • Excel header & footer tips and tricks.
Five ways to perform a forensic audit using Excel

Five ways to perform a forensic audit using Excel

12 January 2018

Excel can be used to conduct a forensic audit, gathering evidence of possible fraud.

We cannot eliminate mistakes or "fudging" in financial data, however we can positively try to minimize it.

Here are five techniques that can be applied using Excel for tracing such issues:

  • Identifying duplicate transactions using highlight values.
  • Analyzing round numbered transactions.
  • Above average payments to vendors or checking the ratio between a maximum and minimum.
  • Gap detection.
  • Checking ratio of the highest to the second highest number.
Documenting Excel projects

Documenting Excel projects

9 January 2018

Excel projects of any significance are very often complicated. Documenting such projects is crucial for auditing and maintainability.

Fortunately, Microsoft provides several options for documenting Excel projects:

  • Workbook name and path.
  • Workbook Properties.
  • Model structure.
  • Titles.
  • Value labels.
  • Names and Structured References.
  • Cell Comments.
  • Text boxes.
  • Data Validation.
  • Hyperlinks.
  • Documentation worksheets.
  • External documentation.

[Note: The article also suggests using the N() function to include documentation in a formula. This is a risky practice that may result in errors. i advises not to use the N() function for documentation.]

Four ways to specify dates using Excel data validation

Four ways to specify dates using Excel data validation

8 January 2018

Excel's data validation feature is underused because many users don't realize how versatile it is, especially where dates are concerned.

Dates seem to complicate things, but only in your head! This feature handles dates fine.

Here are four ways to express dates using data validation:

  • Literal values. Just enter the first and last acceptable dates.
  • Input values. Refer to input cells instead of entering literal date values.
  • A dynamic list. Create the date list as a Table, so the validation updates as you modify the list.
  • Formulas. Create a formula to validate the dates. This approach is dynamic and very flexible.
7 rules for spreadsheets and data preparation for analysis and machine learning

7 rules for spreadsheets and data preparation for analysis and machine learning

20 December 2017

With the hype of deep learning neural nets, and machine learning algorithms, it's easy to forget that most of the work in data science involves accessing and preparing data for analysis. Indeed, not all data is Kaggle-ready. The reality is: data is often far from perfect.

Do your consultant (and budget) a favor and follow these rules-of-thumb when using spreadsheets to collect and organize your data:

  • Do not rely on spreadsheet formatting to indicate associations in your data.
  • Never merge spreadsheet cells.
  • Always use Data Validation tools for data entry.
  • Never (ever!) delete rows of data if you want the data excluded from the analysis.
  • Create a key that explains each column of data in a table.
  • Preserve the integrity of the data by separating the data from the analysis.
  • Use a fixed spreadsheet template and collect data in a series of spreadsheet files (rather than a series of tabs in a file).
Top Excel data cleansing techniques

Top Excel data cleansing techniques

15 December 2017

Data cleansing is an important activity within Excel and one that we find ourselves doing day in day out, sometimes without even knowing it.

My top data cleansing techniques are:

  • Unpivot data.
  • Find & Replace.
  • Find errors with Go to Special constants.
  • Find blank cells in Excel with a color.
  • Remove duplicates in an Excel table.
  • Text to Columns: Dates.
  • Using formulas to clean data.
  • Excel add-Ins.
Displaying decimals versus Rounding in Excel

Displaying decimals versus Rounding in Excel

9 December 2017

This article describes a situation where there was a significant difference between the calculated and expected values.

The problem was caused by the way that Excel rounds values for display purposes, but the formula is calculating using the actual cell content.

Using the ROUND function can help resolve this type of problem.

Go to top