How to trace precedents in Excel formulas? [tip+music from Prague]
Here is a very useful and almost secret Excel tip for you. Imagine you are looking at a big, complex workbook with lots of calculations. You want to understand where everything is pointing to and how the workbook is set up.
You can use trace precedents in Excel to do this. Read this tip to learn how it works.
CP037: Error error on the wall, How do I fix you all? – Understanding & Fixing Excel Errors
Podcast: Play in new window | Download
Subscribe: Apple Podcasts | Spotify | RSS
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
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?
CP006: How to be a better analyst? – Road map for getting better at Data Analysis & Improving your career prospects
Podcast: Play in new window | Download
Subscribe: Apple Podcasts | Spotify | RSS
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
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…,
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.
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 […]