Spreadsheet Formula Debugging via Stepped Execution Trace

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Debugging spreadsheet formulas is challenging due to a lack of visibility into formula evaluation, error cascades, and difficulties in visualizing off-screen data ranges, leading to inefficient debugging processes and excessive computing resources.

Innovation Solution

The implementation of an execution trace that displays step-by-step formula evaluation, a stepped view of formula evaluation that combines multiple atomic operators into a single step, and a range preview system that optimizes data visualization by eliding and contextualizing data ranges.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Reliability

If trial-and-error debugging techniques are used, then users can eventually find the source of an error, but computing resources are excessively consumed and time is wasted

Engineering Contradiction:

Improveerror detection capabilityVSAvoidcomputing resource consumption

Core Design Contradiction:

ReliabilityVSUse of energy by moving object

Solution Approach 1:

The system performs preliminary evaluation of sub-expressions before the main formula execution. By pre-evaluating potential error sources and tracking their values, the system can identify errors without requiring multiple full recalculations of the entire spreadsheet, thus reducing computing resource consumption while maintaining reliable error detection

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The system implements feedback mechanisms by displaying execution traces that show intermediate values and evaluation steps. This allows users to see exactly how formulas are evaluated and where errors occur, enabling them to correct issues without repeated trial-and-error recalculations that would consume excessive computing resources

Inventive Principle:

Principle #23Feedback

2Loss of information

If step-by-step formula evaluation is displayed, then users gain visibility into intermediate calculation steps, but the complexity of the displayed information increases

Engineering Contradiction:

Improvevisibility into formula evaluationVSAvoidcomplexity of execution trace

Core Design Contradiction:

Loss of informationVSDevice complexity

Solution Approach 1:

The system segments the formula evaluation into manageable steps by grouping atomic operators into logical steps. Instead of displaying every single atomic operation separately, it consolidates related operations into coherent evaluation steps, making the execution trace easier to understand while maintaining complete visibility into the formula evaluation process

Inventive Principle:

Principle #1Segmentation

Solution Approach 2:

The system merges multiple atomic evaluations into single display steps when they belong to the same logical group. By combining related operations into unified steps, the system reduces the visual complexity of the execution trace while preserving the detailed information needed for users to understand formula evaluation

Inventive Principle:

Principle #5Merging (Combining)

3Loss of information

If all data ranges are displayed in the range preview, then users have complete visibility of referenced data, but the screen real estate required increases significantly

Engineering Contradiction:

Improvevisibility of referenced dataVSAvoidscreen real estate for data visualization

Core Design Contradiction:

Loss of informationVSArea of stationary object

Solution Approach 1:

The system applies local quality by selectively displaying only the portions of data ranges that are actually referenced by the formula. Instead of showing entire columns or rows, it highlights only the specific cells or ranges that the formula needs, reducing screen real estate requirements while maintaining complete information visibility for relevant data

Inventive Principle:

Principle #3Local quality

Solution Approach 2:

The system uses partial action by displaying only the necessary portion of data ranges rather than all available data. It identifies and presents just the minimal set of data needed for formula evaluation, avoiding the excessive display of unnecessary data while ensuring users have access to all referenced information

Inventive Principle:

Principle #16Partial or excessive action

4Loss of information

If users scroll back and forth to view different sections of the spreadsheet, then they can locate off-screen referenced data, but the debugging process becomes tedious and inefficient

Engineering Contradiction:

Improveaccess to off-screen dataVSAvoidtime for data location

Core Design Contradiction:

Loss of informationVSLoss of time

Solution Approach 1:

The system introduces an intermediary mechanism in the form of a range preview panel that mediates between the user and off-screen data. This panel displays referenced data ranges without requiring users to manually scroll through the spreadsheet, eliminating the time-consuming scrolling process while maintaining access to all necessary data information

Inventive Principle:

Principle #24Intermediary (Mediator)

Data Source

PatentUS20250111134A1Techniques for formula debugging

Publication Date: 2025.04.03 MICROSOFT TECHNOLOGY LICENSING LLC

AI summary

Disclosed are novel approaches to debugging a formula in a spreadsheet environment. An execution trace shows step-by-step how a formula is evaluated. Instead of overwhelming users by displaying a step for every atomic evaluation, multiple evaluations are displayed in the same step. This makes the execution trace compact yet intuitive, enabling users to quickly and efficiently understand how the formula is evaluated. Visualizing formula execution in this way also reduces the computing and energy costs of excess recalculations incurred by trial-and-error based debugging techniques.