Profile Workbook

 Profile Workbook: Profile the active workbook and all its worksheets. Profile workbook produces information in several tables:

• Workbook Information and Calculation Options.
• Worksheet Cell Counts, Calculation Times and Memory Used.
• Workbook Summary.

Potential Bottlenecks are highlighted in orange.

The large and complex workbook used to demonstrate the Profiling Tools had already been extensively optimised for calculation speed, and the time taken had been reduced from over 10 minutes to less than 25 seconds. Nevertheless the Profiling Tools rapidly identify ways of reducing this even further to just over 10 seconds.

Cells with red dots contain on-sheet  Notes: hover your cursor to make them pop-up.

The Workbook Information Table shows basic information on the profiled workbook:

• The name and path of the profiled workbook.
• The date and time that the profile was created.
• Memory Used (K): for all open workbooks and the sum of the Pivot caches.
• Physical RAM (K): the amount of RAM installed on the PC.
• Shared is TRUE if the workbook is currently shared.
• Keep Changes: if both Shared and Keep Changes are true the workbook size will grow rapidly.
• Pivot Cache (K): the amount of memory being used by Pivot caches.
• Excel Version: the version number of Excel used: Excel97 is version 8, Excel2000 is version 9, Excel2002 is version 10.
• File Format: the format of the saved workbook: Dual Format (Orange) doubles the file size.
• Backup is TRUE if a backup copy will be created when you save.
• Views and Styles: the count of Custom Views and Styles.
• CalcEng: for Excel 2000 and 2002 only, the version number of the last-used calculation engine.

The Calculation Options Table shows the status of the Excel calculation options when the profile was created.

Use FastExcel's Calculation Options button to change any of these settings.

• Calculation Mode: Automatic, Manual or Semi-automatic.
• CalcB4Save: If TRUE Excel will recalculate the workbook before each save.
• Iteration: If TRUE Excel will attempt to resolve circular references by iteration.
• Max Iters: the maximum number of iterations used to resolve circular references.
• MaxChange: Iteration will stop when no cell changes by more than MaxChange.
• Update Remote Refs: If TRUE Automatically update any remote references (DDE Links to other programs) whenever Excel recalculates.
• Precision as Displayed: If TRUE use the displayed value of all cells rather than their real value as input to subsequent formulas.
• 1904 Date System: if TRUE changes the starting date from which all dates are calculated from January 1 1900 to January 2 1904.
• Save External Link Values: If TRUE Excel saves values for links to external workbooks.
• Accept Labels in Formulas: If TRUE allows the use of row and column labels in your formulas.

The Worksheet Profiles Table show Cell Counts, Calculation Times and Memory Used for each Worksheet. The worksheets are sorted in descending sheet calculation time.

Cell Counts show you the key size statistics for each Sheet.

• The Cell Counts columns show counts of the number of cells for:
• The Used Range: The rectangular Table of Cells that Excel considers used: Excel’s opinion is not always the same as yours.
• %Waste: The cells in the Used Range (formatted cells, constants and formulas) beyond the real used range (formulas and constants), as a percentage of the Used Range: coloured orange if greater than 2000 cells. Significant waste can use additional memory and calculation time.
• Constants.
• Conditionally formatted cells.
• Formulas.

Use this table to identify the sheets that use the most calculation time, and which sheets have the most expensive formulae.

• Calc Times: Milliseconds to calculate the Used Range and the sheet.
• Microsecs per Formula: Calculated from the smaller of Used Range calculation time and sheet calculation time. A high value may indicate bottleneck formulas.
• Sheet Overhead: sheet time minus range time, an indicator of complex sheet structure.
• Calc Enable: TRUE if calculation is enabled for this worksheet.
• Page Breaks: TRUE if Display Page Breaks is enabled for this sheet (can slow VBA and calculation).

See where the memory is being used.

• Memory Used (K): The memory used by this worksheet. Memory fragmentation and compaction issues may cause this number to vary slightly.
• Non worksheet memory shows memory used by the workbook not attributable to worksheets, (external link values, chart sheets, VBA modules, book-level defined names…).
• Shapes Count shows the count of the number of shapes in the drawing layer for this sheet. Includes hidden and zero-height shapes.
• OleObjectsCount shows the count of the number of OLEObjects in this sheet. These can be either ActiveX Controls or linked or embedded OLE Objects. Includes zero-height and hidden OLE objects.
• HyperLinksCount shows the count of the number of Hyperlinks in this sheet.

Volatility% and Book Overhead are key measures of calculation efficiency.

The Workbook Summary table shows cell counts, workbook calculation times, workbook volatility, workbook overhead, bytes per cell and names count, for the profiled workbook.

The actual FastExcel Workbook Summary output includes Cell Count columns showing similar counts to the Worksheet Profiles Table. These columns are not shown in the above Figure.

• Recalc: Milliseconds to recalculate (F9) the workbook immediately after a full calculation.
• FullCalc: Milliseconds to fully calculate the workbook (Ctrl/Alt/F9).
• MicroSecs per Formula is based on full calculation time.

Keeping Workbook Volatility low is often an excellent way to improve response time.

• Volatility% is recalculate time as a percentage of full calculation time: a high value means that high proportions of formulae are volatile or that the workbook is too complex for Excel to build a dependency tree (Status bar always shows Calculate and there are no circular references).

Use the Optimise Worksheet Sequence command to reduce book overhead.
Book Overhead: The Difference between full calculation time and sum of the sheets calculation time. A large value indicates Worksheet Cross-reference Bottlenecks. This time could potentially be saved by changing the calculation sequence of the worksheets (Excel97 and 2000) to minimise forward worksheet cross-references, or by simplifying the workbook structure by eliminating worksheet cross-reference loops or by merging worksheets.

• Note: In Excel 97 and Excel 2000 worksheets are calculated in alphabetic name sequence. Excel 2002 automatically determines the sequence.
• Bytes per Cell: Memory used/(constants + formulas).
• Names Count: Count of the defined Names in the workbook.