Formula Breakdown Tutorial

When a formula becomes long and incomprehensible, it can be difficult to see if it’s still correct. In this formula breakdown tutorial we teach you how to use PerfectXL’s Formula Breakdown feature, to assist you in understanding the formulas used in your spreadsheet. By clicking a formula you can, much better than in Excel itself, discover how they work.

Break Down That Formula!

To perform a formula breakdown with PerfectXL, you start by uploading and analysing your spreadsheet in the tool. Then click on the name of your file to see its full analysis and navigate to the ‘Formula Breakdown’ tab. Here you’ll find an overview of all the formulas that are found in your spreadsheet. The overview shows you per worksheet which formula is found in what cell.

Formula Breakdown Tutorial :: PerfectXL

A. Use our filter options to find the formula you’re looking for; B. Inspect a specific formula’s structure directly by specifying the worksheet and cell here; C. Click to start a thorough inspection of a formula; D. Location in your spreadsheet; E. Formula and (if applicable) the highest risk level detected; F. Formula result.

Inspect a Formula

Click a formula of choice in the overview to start the breakdown.

Formula Breakdown Tutorial :: PerfectXL

A. The selected formula from the formula overview; B. The Formula Breakdown shows all references in your formula as a list. A range is split up when it refers to different types of cell content (formulas, numbers, dates, text).

Keep clicking on a cell or range of an underlying formula to follow it down to the source. Formulas you have clicked on stay put, so you can always return to a previous formula by clicking it again.

Formula Breakdown Tutorial :: PerfectXL

A. Cells and ranges are highlighted in orange in the current (the white) formula as you hover the cursor over references in the Breakdown table.

Direct Risks per Formula

PerfectXL’s risk detection is integrated into the formula inspection. Direct Risks are risks that are directly related to the formula; Indirect Risks are found in underlying formulas. After all, a risk in an underlying formula could be just as important for the cell result as a risk in the formula itself.

Formula Breakdown Tutorial - Direct Risks :: PerfectXL

A. Direct risks are indicated with a closed triangle.

Indirect Risks per Formula

This overview shows a list of risks that are found in underlying formulas.

Formula Breakdown Tutorial - Indirect Risks :: PerfectXL

A. Detected risk in underlying formula; B. Indirect risks are indicated with an open triangle. Find out more about an indirect risk by clicking it; C. Location in your spreadsheet; D. Number of steps (cell references) from the original formula to the risky formula.

Formula Breakdown Tutorial - Indirect Risks Original Formula :: PerfectXL

A. Click on an indirect risk to reveal the path from the original formula to the formula where the indirect risk was found.

Watch our Video Tutorials

In the video’s below, several features and possibilities of PerfectXL are described and demonstrated thoroughly. In time we will add more video tutorials.

Video Tutorial: Tracing Problems in Spreadsheet Calculations :: PerfectXL

Tracing Problems in Calculations

When spreadsheets contain inconsistencies or suspected miscalculations, it takes great effort to find the problem manually. In this video we’ll show you how to use PerfectXL’s Formula Breakdown feature to locate and fix the problems in spreadsheet calculations within a matter of minutes. 

Learn how to trace problems in spreadsheet calculations

Video Tutorial: How to Check Your Input Source in Excel :: PerfectXL

How to Check Your Input Sources in Excel

In this video we’ll show you how to run a quick basic check of the input sources of your spreadsheet and how to determine if there is any unknown input. We’ll also show you a powerful way to visualize your spreadsheet’s information flow to help determine the function and dependability of each worksheet.

Learn how to check your input sources in Excel

Video Tutorial: How to Quickly Validate Your Spreadsheet :: PerfectXL

How to Quickly Validate your Spreadsheet

88% of spreadsheets contain hidden issues. These issues can be a serious risk to the outcome of your spreadsheet. This video shows you how PerfectXL helps you to quickly validate your spreadsheet on 29 types of risk, without having to check every cell manually.

Learn how to validate your spreadsheet