Profile Worksheet Areas: Details
of the Formula areas on the Sheets.
|
|
Profile Worksheet Areas: Shows the workbook information
and calculation options tables, and profiles each rectangular
block of formulae and each column on each of the selected
worksheets.
|
Worksheet Areas Profile Table
| |
There will be one Worksheet Areas Profile Table
for each of the selected worksheets.
The table profiles each separate contiguous rectangular
area of formulae and columns on the worksheet, and shows:
|
|
Use this table to identify bottleneck
formula blocks.
|
- Cell counts of formulas, array cells and conditional
formats.
- Calculation times for the area and the sheet.
- Total, average and maximum formula lengths.
The areas and columns are sorted by descending calculation
time.
|
I chose to drill-down on the Coverage worksheet,
because it has a high microsecond-per-formula time, and takes
2 seconds to calculate. I wanted to know where the time was
being used.
The Worksheet Areas Profile Table,
Counts Columns.
| Array formulae
and conditional formats can use significant calculation
time. |
- The Counts columns show, for each area:
- Area address: the range address for the area
- Formulas: count of the number of formulae in the
area
- Array Cells: count of the number of cells containing
array formulas in the area.
- CondFormats: count of the number of format conditions
in the area. There may be more than one conditional
format per cell, so this may be a larger number than
the count of cells containing conditional formats.
|
The Worksheet Areas Profile Table,
Calculation Time and Formula Length Columns.
| |
In the actual FastExcel table these
columns are next to the count columns, rather than as
shown in this Figure.
You can see in the example that the
dominant area of formulas is A6:AD109. This area takes
over 1 second to calculate. I timed columns in this area,
using Calculate Selected Range, and found that 90% of
the time was used by a COUNTIF formula in C6:C109, and
was able to change the formula to save nearly 1 second
calculate time.
See Optimising Excel Calculations
for more details.
|
|
Use this table to see where the calculation
time is being used.
|
- The area calculation and formula length columns show:
- Calc Time Area: the calculation time for the area
(milliseconds)
- Calc Time Sheet: the calculation time for the sheet
(milliseconds)
- MicroSecs per Formula: the smaller of area calculation
time and sheet calculation time divided by formula count
- Area % of Sheet: calculation time for the area as
a percentage of calculation time for the sheet
- Formula Length Total: total number of characters
of formulas.
- Average: average number of characters per formula
in this area
- Maximum: maximum number of characters in a single
formula.
|
|
