Spreadsheet error checker

• US 20020161799A1
• Filed: 02/27/2001
• Published: 10/31/2002
• Est. Priority Date: 02/27/2001
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Patterson, Eric W., Maguire, Justin M. III, Rader, Marcella, Novack, May, Mears, Keith
• Granted Patent: US 7,117,430 B2
• Time in Patent Office: Days
• US Class Current: 715/503
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174

Abstract

An error checker identifies spreadsheet errors by traversing a spreadsheet cell-by-cell and comparing each cell'"'"'s contents to a list of error rules. When a cell'"'"'s contents satisfy an error rule, then the cell is identified as containing a potential spreadsheet error. The error checker operates in both background and foreground modes. In background mode, the error checker identifies potential spreadsheet errors and marks the cells containing the potential errors with an error indicator. A drop-down menu is provided that includes an element identifying the error rule that was invoked by the cell'"'"'s contents and may include one or more recommended actions to resolve the identified error. The menu can also include selections for referencing a help facility for more information about the identified error, ignoring the error, accessing the cell to edit the cell'"'"'s contents, and accessing a debugging utility for evaluating and modifying a spreadsheet formula. In background mode, the error indicator can be removed when a user disposes of the identified error either through modifying the cell'"'"'s contents or by choosing to ignore the error. Foreground mode is invoked when a user affirmatively chooses to commence error checking, usually by selecting error checking from the main menu of a spreadsheet application. In foreground mode, the error checker traverses the spreadsheet cell-by-cell applying the spreadsheet error rules to each cell. When an error is identified, a dialog box is displayed prompting the user to dispose of the error and providing selectable commands to dispose of the error.

Claims

1. A method for checking a cell in a spreadsheet document for errors, the method comprising the steps of:

comparing a value in the cell to a spreadsheet error rule, to determine whether the value matches the spreadsheet error rule;

associating an error indicator with the cell, in response to a determination that the value matches the spreadsheet error rule;

providing a selectable recommended command for modifying the value, so that the value does not match the spreadsheet error rule; and

modifying the value, so that the value does not match the spreadsheet error rule, in response to a selection of the selectable recommended command.

2. A computer readable medium having stored thereon computer-executable instructions for performing the method of claim 1.

3. The method of claim 1, wherein the spreadsheet error rule is one of a plurality of spreadsheet error rules maintained in a spreadsheet rules component.

4. The method of claim 1, wherein the error indicator is rendered within the cell.

5. The method of claim 4, wherein the error indicator is associated with a control, the control being operative to provide a drop-down menu.

6. The method of claim 5, wherein the drop-down menu contains the selectable recommended command.

7. The method of claim 5, wherein the drop-down menu contains a help command, an ignore error command, an edit in formula bar command, and a show formula auditing toolbar command.

8. The method of claim 1, wherein the spreadsheet error rule can be disabled by a checkbox in an options dialog box.

9. An error checker for checking a cell in a spreadsheet document for errors, the error comprising:

a spreadsheet rules component operative to compare the cell to a plurality of spreadsheet error rules to determine whether a match exists;

a recommended commands component operative to associate at least one recommended command with each spreadsheet error rule for which a match is determined; and

a user interface rendering component operative to associate an error indicator with the cell and to provide a selectable list of commands, the list including the at least one recommended command.

10. The error checker of claim 9, wherein the plurality of spreadsheet error rules includes a rule for determining whether the cell includes a reference to an empty cell.

11. The error checker of claim 10, wherein the at least one recommended command is a command to identify the empty cell.

12. The error checker of claim 9, wherein the plurality of spreadsheet error rules includes a rule for determining whether the cell includes a number stored as text.

13. The error checker of claim 12, wherein the at least one recommended command is a command to convert the number stored as text to a numeric representation.

14. The error checker of claim 9, wherein the plurality of spreadsheet error rules includes a rule for determining whether the cell includes a formula that omits adjacent cells.

15. The error checker of claim 14, wherein the at least one recommended command is a command to modify the formula to refer to the adjacent cells.

16. The error checker of claim 9, wherein the plurality of spreadsheet error rules includes a rule for determining whether the cell includes an inconsistent formula.

17. The error checker of claim 16, wherein the at least one recommended command is a command to copy an adjacent formula into the cell.

18. The error checker of claim 17, wherein the plurality of spreadsheet error rules includes a rule for determining whether the cell includes a text date with a two digit year representation.

19. The error checker of claim 18, wherein the at least one recommended command is a command to convert the text date with a two digit year representation to a four digit representation.

20. The error checker of claim 9, wherein the plurality of spreadsheet error rules includes a rule for determining whether the cell includes a formula and is an unprotected cell.

21. The error checker of claim 20, wherein the at least one recommended command is a command to protect the cell.