|
Profile Workbook
.png) Profile Workbook: Profile the active workbook and all its worksheets.
|
Because Excel calculates all open workbooks rather than just the active workbook you can get misleading calculation times if you have multiple workbooks open during Profile Workbook.
.png)
Choose Profile Workbook Options
Because Profile Workbook can take a long time to execute these options allow you to include or omit various profiling options:
Profile all the worksheets: if switched off the individual worksheets will not be profiled.
Profile Multi-Threaded Calculation: If checked each worksheet will be calculated once using multi-threaded calculation and once without so that the multi-threaded calculation efficiency of each sheet can be determined.
Profile Worksheet Volatility: If checked each worksheet will be calculated once using smart Recalc and once using Full Calculation so that the worksheet volatility can be determined.
Profile Worksheet Sparseness: If checked the number of cells containing constants will be counted and the sparseness % of the real used range will be calculated.
Profile the workbook itself: If switched off the workbook itself will not be profiled.
Profile Multi-threaded Calculation: if checked the workbook will be calculated once using MTC and once without so that the multi-threaded calculation efficiency of the workbook can be determined.
Profile Workbook Volatility: If checked the workbook will be calculated once using smart Recalc and once using Full Calculation so that the workbook volatility can be determined.
Events during Calculation: Profile Workbook will by default attempt to prevent VBA events from occurring during the timed calculations. If your workbook contains event-handling VBA code you should consider whether to include or exclude it when running Profile Workbook.
Ignore VBA Errors. Profiler will attempt to ignore VBA errors in the workbook being profiled.
Set Warning Tolerance: a larger tolerance number reduces the number of orange warnings produced.
|
|
|
|
|
Profile Workbook produces information in several tables:
Excel and Workbook environment information and calculation options.
Worksheet statistics, calculation times, volatility and multi-threaded efficiency
Workbook summary
Potential bottlenecks are highlighted in orange. The sensitivity of the orange highlighting can be controlled by setting FastExcel’s Orange Tolerance (default 0.2).
.png)
The Worksheet Profiles Table.
The Worksheet Profiles Table shows the cell counts, calculation times and Memory Used for each worksheet. The table is only produced if the Profile All the Worksheets option is checked in the Profile Workbook Options.
The worksheets are sorted in descending sheet calculation time.
The Worksheet Profiles Table: Cell Counts Columns
|
Cell counts show you the key size statistics for each worksheet.
|
.png)
|
The Cell Counts columns show counts of the number of cells for:
Used: The Used Range: The rectangular table of cells that Excel considers used: Excel considers cells with formatting, data or formulas used. The Used Range is not automatically reset when cells are deleted or cleared.
You can clean up the Used Range with Clean Workbook.
Constants: the number of constants on this worksheet.
Formulas: the number of formulas on this worksheet.
Unique Formulas: the number of different formulas on this worksheet.
CSE Formulas: the number of array entered (Control-Shift-Enter) formulas on this worksheet.
CondFmt: the total number of conditional formats on this worksheet. Colored orange if the average number of conditional formats per conditionally formatted cells is > 3.
What-If Tables: the number of iterative what-if data tables on this worksheet.
The Worksheet Profiles Table: Calculation Times.
.png)
In the actual FastExcel output these columns are adjacent to the cell counts columns rather than as shown above.
Calc Time (Millisec); the elapsed calculation times in milliseconds.
|
Use this table to identify the sheets that use the most calculation time, any sheets with significant volatility or MTC inefficiency and the sheets that have the most expensive formulas.
|
Single-Thread: Time in milliseconds for a Range.Calculate of the real used range in the worksheet, not using multi-threaded calculation, minus the time taken to range calculate an empty worksheet (empty sheet overhead).
Recalc: The time taken to recalculate the sheet after the workbook has been calculated, minus the time taken to recalculate an empty sheet (empty sheet overhead).
Multi-threaded calculation is used if enabled.
For most workbooks this will give a minimum worksheet calculation time because only the minimum set of formulas (mostly volatile formulas and their dependents) will be calculated.
Full Calc: The time taken to calculate all the formulae on the sheet, minus the time taken to fullcalc an empty sheet (empty sheet overhead).
Multi-threaded calculation is used if enabled.
Colored orange if > (1+ orange tolerance) *average sheet full calc time and >orange tolerance *500
Sheet Volatile %: The Ratio of Sheet Recalc time to Sheet Full Calc time.
Measures how many of the formulae on the sheet are volatile. Colored orange if >orange tolerance * .125
Multi-Thread Efficiency: A measure of the efficiency of multi-threaded calculation at sheet level. The ratio of Range calculation (single-threaded) time to Full Calculation (multi-threaded) time.
Other: The three times shown in the Other row are the times taken to calculate an empty worksheet. These times are used as standard calculation overheads and are subtracted from the sheet calculation times.
Sheet % Volatile: The ratio of Recalc time to Full Calc time, excluding standard calculation overhead. When you have a high workbook volatility % you can use the sheet % volatile to determine which sheets are causing the problem.
Microseconds per Formula: calculated from the smaller of used range calc time and sheet calc time. A high value may indicate bottleneck formulas.
|
|
Colored orange if >orange tolerance * total sheet calc time and > (1+orange tolerance)*minimum sheet overhead and > orange tolerance * 500
|
|
Colored orange if >(1+ orange tolerance) * average and > orange tolerance * 500
|
Worksheet Profiles Table (2)
.png)
In the actual FastExcel output these columns are adjacent to the Calc Times columns rather than as shown above.
|
Unused Cells:
Eliminate the waste by using FastExcel’s Workbook Cleaner
|
%Waste: The cells in the Used Range (formatted cells, constants, formulas, deleted/cleared cells) beyond the real Used Range (formulas and constants), as a percentage of the Used Range:
Colored orange if > orange tolerance * 10000 cells. Significant waste can use additional memory and calculation time.
|
%Sparse: Cells not containing formulas or constants, as a percentage of the used range.
Colored Orange if > orange tolerance * 10000 cells.
|
On sheet notes are counted as shapes
|
Shapes Count: Count of the number of shapes in the Drawing layer for this sheet. Includes hidden and zero-height shapes. Use FastExcel’s Clean Workbook command to remove zero-height shapes.
Colored orange if any of the shapes are linked by formula (slows calculation and editing).
|
HyperLinks Count: Count of the number of Hyperlinks on this sheet. The hyperlinks can be internal or external to the workbook. Large numbers of hyperlinks can be slow.
Sheet Calc Mode: Shows the MixMode calculation mode for the worksheet.
Book: The Sheet will be calculated whenever the Workbook calculates
F: The Sheet will be calculated at every Full workbook calculation.
M: The Sheet will be calculated at every Manual workbook re-calculation.
S: Shift-F9/worksheet Calculate will calculate the worksheet when it is selected
Either Book or any combination of F M and S is allowed.
The Workbook Summary Table.
|
Volatility% and MTC are key measures of calculation efficiency
|
The Workbook Summary table shows the cell counts, workbook calculation times, workbook volatility, workbook overhead, bytes per cell, and names count, for the profiled workbook. The workbook Summary table is only produced if the Analyze the Workbook option is checked in the FastExcel Settings.
|
The Workbook Summary Table Cell Counts.
.png)
This shows totals of the counts and what-if tables for all the worksheets.
Sheets Count: the total number of sheets in the workbook including Chart, Dialog, XLM Macro sheets as well as normal worksheets.
The Workbook Summary Table Calculation Times.
.png)
Single-Thread: Total Time in milliseconds for Range.Calculate for the worksheets. The calculation is single-threaded.
Recalc: milliseconds to recalculate (F9) the workbook immediately after a full calculation.
FullCalc: milliseconds to fully calculate the workbook (Ctrl/Alt/F9).
|
Keeping Workbook Volatility low is often an excellent way to improve response time.
Colored orange if > orange tolerance * 0.125
|
Book Volatile % is Recalculate Time as a percentage of full calculation time: a high value means one or more of:
High proportions of the formulas are Volatile
Evaluating the dependency trees takes a long time.
The workbook is too complex for Excel to build a dependency tree (status bar always shows Calculate and there are no circular references).
ForceFull calculation is true
Multi-Thread Efficiency: A measure of the efficiency of multi-threaded calculation at book level. The ratio of the sum of range calculation (single-threaded) times for each sheet to Full Calculation (multi-threaded) time for the book.
|
|
|
MicroSecs per Formula: Microseconds per formula based on full calculation time.
The Workbook Summary Table Used Range etc.
.png)
The actual FastExcel workbook summary output includes cell count column and calculation times. These columns are not shown in the above figure.
%Waste: Cells in the UsedRange Outside the last Row and Column containing data or formulas, as a percentage of the cells in the usedrange.
%Sparse: Sparseness of the UsedRange: Cells inside the used range, but not containing formulas or constants, as a percentage of the used range.
Pivot Cache (K): Memory Used by Pivot Table Caches in this workbook in K bytes.
XLLinks Count: The Number of Excel workbooks that are linked to by the profiled workbook.
Names Count: count of the defined Names in the workbook.
Open Time (Secs): The time taken to reopen the workbook.
|
