Spreadsheet error checker

• US 7,117,430 B2
• Filed: 02/27/2001
• Issued: 10/03/2006
• Est. Priority Date: 02/27/2001
• Status: Expired due to Fees

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Patterson, Eric W., Novack, May, Mears, Keith, Maguire, Justin M. III, Rader, Marcella
• Primary Examiner(s): Huynh, Cong-Lac
• Application Number:
  US09/794,595
• Publication Number:
  US 20020161799A1
• Time in Patent Office: 2,044 Days
• Field of Search: 715/503, 715/504, 715/500, 715/509
• US Class Current: 715/212
• 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 rules, then the cell is identified as containing a potential spreadsheet error. 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.

Claims

1. A computer-implemented method for checking a cell in a spreadsheet document for errors of calculation caused by the spreadsheet and displayed in the cell, the method comprising the steps of:

comparing a value calculated by the spreadsheet and displayed in the cell to a spreadsheet error rule, to determine whether the displayed value matches the spreadsheet error rule;

associating an error indicator with the cell, in response to a determination that the calculated value matches the spreadsheet error rule, wherein the error indicator is rendered within the cell in addition to any error of calculation displayed in the cell;

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

modifying the calculated value, so that the calculated 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 associated with a control, the control being operative to provide a drop-down menu.

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

6. The method of claim 4, 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.

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

8. The method of claim 1, wherein the error of calculation comprises an error of defective calculation that results in an erroneous value displayed in the cell.

9. A computer-implemented error checker for checking a cell in a spreadsheet document for errors of calculation caused by the spreadsheet and displayed in the cell, the error checker comprising:

a spreadsheet rules component operative to compare a value calculated by the spreadsheet and displayed in 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 in addition to any error of calculation in a calculated value displayed in 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.