All articles with 'spreadsheet audit' Tag

CP037: Error error on the wall, How do I fix you all? – Understanding & Fixing Excel Errors

Published on Jun 25, 2015 in Chandoo.org Podcast Sessions
CP037: Error error on the wall, How do I fix you all? – Understanding & Fixing Excel Errors

In the 37th session of Chandoo.org podcast, Let’s debug ’em #VALUEs & #N/As.

What is in this session?

In this podcast,

  • Introduction to Excel formula errors
  • The easy kind: syntax errors
  • The triky ones: # ERRORs
  • Fixing errors – using IFERROR & ISERROR
  • Error checking & debug options
  • Using Errors deliberately – charts & data validation
  • A challenge for you – produce #NULL error
  • Conclusions
Continue »

How to check for hard-coded values in Excel formulas?

Published on Jan 14, 2015 in VBA Macros
How to check for hard-coded values in Excel formulas?

Here is a common problem. Imagine you are looking a complex spreadsheet, aptly titled “Corporate Strategy 2020.xlsx” which as 17 tabs, umpteen formulas and unclean structure. Whoever designed it was in insane hurry. The workbook has formulas like this, =SUM(Budget!A2:A30, 3600)+7925 .

It was as if Homer Simpson created it while Peter Griffin oversaw the project.

So how do you go about detecting all cells containing formulas with hard-coded values?

Continue »

CP006: How to be a better analyst? – Road map for getting better at Data Analysis & Improving your career prospects

Published on Apr 24, 2014 in Chandoo.org Podcast Sessions
CP006: How to be a better analyst? – Road map for getting better at Data Analysis & Improving your career prospects

In the 6th session of Chandoo.org podcast, we focus on making you a better analyst and propose a road map for getting better at data analysis & improving your career prospects.

In this podcast you will learn,

  • Why become a better analyst?
  • The road map for becoming a better analyst – BETTER framework
  • B for Business Knowledge
  • E for Examining user needs
  • T for Thinking about analysis
  • T for Tools of Trade ie Excel
  • E for Expression
  • R for Refining yourself
  • Conclusions
Continue »

What is so special about Go To Special? [15 tips]

Published on Mar 12, 2012 in Excel Howtos
What is so special about Go To Special? [15 tips]

We briefly covered Excel’s Go To Special function in the Managing Spreadsheet Risk series and in this post, we are going to explore Go to special feature in detail and learn how to use it.

What is Go To Special?

Go To Special is a tool within Microsoft Excel that enables you to quickly select cells of a specified type within your Excel worksheet. Once you get to grips with this function and what it can be used for you will wonder how you ever lived without it. Read on…,

Continue »

Using external software packages to manage your spreadsheet risk [Part 4 of 4]

Published on Feb 8, 2012 in Financial Modeling, Learn Excel
Using external software packages to manage your spreadsheet risk [Part 4 of 4]

Background – Spreadsheet Risk Management

In the Managing Spreadsheet Risk series so far we have looked at the concept of spreadsheet risk and how to manage it both at a company level and at a spreadsheet level using Excel functionality. In this final article we are going to have a quick look at an example of spreadsheet auditing software.

What to look for in a Spreadsheet Risk Management Software

First off I should state that there is a wide range of spreadsheet auditing solutions in the marketplace of different types and styles and at a variety of costs. In this section I would like to take a little time to explain the criteria we applied when we were sourcing auditing software.

Continue »

Excel’s Auditing Functions [Spreadsheet Risk Management – Part 3 of 4]

Published on Jan 18, 2012 in Financial Modeling, Learn Excel
Excel’s Auditing Functions [Spreadsheet Risk Management – Part 3 of 4]

This series of articles will give you an overview of how to manage spreadsheet risk. These articles are written by Myles Arnott from Excel Audit Part 1: An Introduction to managing spreadsheet risk Part 2: How companies can manage their spreadsheet risk Part 3: Excel’s auditing functions Part 4: Using external software packages to manage […]

Continue »

How Companies Can Manage Spreadsheet Risk [Part 2 of 4]

Published on Jan 3, 2012 in Financial Modeling, Learn Excel
How Companies Can Manage Spreadsheet Risk [Part 2 of 4]

In the second installment on Excel Spreadsheet Risk Management, Myles takes a look at How companies can manage spreadsheet risk?.

He tells us, how we can set a formal governance framework to reduce or mitigate spreadsheet risk. Go ahead and read. And please tell us how your company manages spreadsheet risk in comments.

Continue »

Introduction to Spreadsheet Risk Management

Published on Dec 7, 2011 in Financial Modeling, Learn Excel
Introduction to Spreadsheet Risk Management

This series of articles will give you an overview of how to manage spreadsheet risk. These articles are written by Myles Arnott from Excel Audit

Part 1: An Introduction to managing spreadsheet risk
Part 2: How companies can manage their spreadsheet risk
Part 3: Excel’s auditing functions
Part 4: Using external software packages to manage your spreadsheet risk

The potential impact of spreadsheet error hit the UK business news recently after a mistake in a spreadsheet resulted in outsourcing specialist Mouchel issuing a major profits warning and sparked the resignation of its chief executive. Over the next few weeks we will look at the risk spreadsheets can introduce to an organisation and the steps that can be taken to minimise this risk.

Continue »

Show all the names & their References [Spreadsheet Audit]

Published on Sep 20, 2010 in Excel Howtos
Show all the names & their References [Spreadsheet Audit]

We all have been there. You started to build a simple workbook to keep track of an ongoing project or dashboard or something equally complicated. Even before you realize the workbook has 23 sheets and 41 named ranges all going from one place to another, like flying spaghetti monster, only less awesome. Now, how do […]

Continue »