![]() Full Annotationsįull annotations also enables “A” lines to graphically show the range of aggregates which makes it easy to verify that the Code has not been accidentally included in the Average in cell I32. The value of the first cell in each range is also listed as a convenience. The report includes any named ranges that refer to cells on the selected worksheet. In this case the local named range Gross_Profit refers to cells E37:I37, which has the AutoName FixedCosts. This makes it easy to see that the named range has been defined incorrectly.Īny potential errors found by the Spreadsheet Detective are also shown, together with ticks and crosses derived from the Audit Process Control. Double click on a row in the report to go directly to the cells in the original workbook. Sophisticated heuristics are used to identify the correct cells to use, they do not have to be in any particular row or column. It is also possible to override AutoNames where necessary. AutoNames are an essential tool for understanding and verifying A1 references, particularly to more distant cells. There are several other notations, for example the “#” indicates that the precedent cell E33 is a simple input, it does not contain a formula.ĪutoNames are automatically derived from labels on the worksheet. This makes it easy to verify that they are referring to the correct cells. The formula is then displayed with AutoNames again interspersed with the A1 references. ![]() ![]() The notation also shows the formula has been copied into cells E35:H35, but with the exception of cell G35, again highlighting the error. The cells containing the formulas are indicated using both A1 references in the first column and with AutoNames in the second column. Thus it is clear that cell E35 is the first Quarter’s Gross Profit. Note that only the distinct formulas are listed which greatly reduces the number of formulas that need to be reviewed. The Spreadsheet Detective can create a report of all formulas in a spreadsheet. The Audit Process Control may flag cells as being correct, questionable or dubious and these are also shown on the map with ticks, question marks and crosses respectively. Potential errors found by the Spreadsheet Detective are included in the cell comments, and the cells are coloured to provide a heat map of potential issues. The original values of the formulas are placed to the right of the symbols, and so can be seen in the formula bar by simply selecting a cell, or by making a column wider. Excel also enables large numbers to be seen simply by hovering the mouse over a cell. In the cells, means new formula, “<” means copied from left, “^” means copied from above, and “&” corresponds to speckled. “#” is just Excel’s normal way to show numbers that are too wide to fit in the narrow columns. They are also condensed which makes it easier to understand large worksheets. Like earlier Shaded option, it shows how formulas have been copied, and cell comments describe the formulas with AutoNames. However, unlike the Shaded option the results are placed on a newly created, separate report worksheet. The map report shown above makes it easy to see the structure of larger worksheets. These errors could easily be overlooked without the Spreadsheet Detective. In the example spreadsheet, Excel has flagged four valid cells as wrong but none of all seeded errors have been detected. Many valid constructs are flagged as erroneous while important errors are overlooked. However, the result is not very satisfactory. These are described in detail in the AutoName Formula Report below.Įxcel has used green triangles to highlight cells such as I32, which it thinks has errors in them. This highlights the fact that that the formula in cell H37 is inconsistent with that in cell G37.īy default cell notes/comments are also added that use AutoNames such as “`Sales” to describe each unique formula as shown for cell E35. ![]() Horizontal stripes indicate that the formula has been copied from the formula to the left, while vertical stripes indicate that the formula has been copied from above. This makes it clear that G35 does not contain a formula which is why the total in I38 is wrong. The Spreadsheet Detective has automatically added blue shading to all cells that contain a formula.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |