• OAK Features

OAK Features


Workbook and Worksheet operations

Worksheet Manager

View, insert, delete, copy, reorder, rename with find and replace, edit protection, calculation and visibility; manage local names used for indicating sheet type.

 

Map
Create a detailed map of the active workbook or a selection of worksheets within that workbook. It uses text, color, symbols, highlighting, and outlining to draw attention to various features on the worksheet.

Summarize
Summarizes a workbook or a selection of worksheets. This command can generate three reports:

A workbook summary that lists all the selected worksheets contained within the workbook, with detailed statistics for all the unprotected worksheets.

A list of all distinct formulas (on unprotected worksheets) in the selected worksheets, along with details for each, including a measure of their complexity.

A list of all referred constants in the selected worksheets, along with details for each.

Remove Color Formatting

Removes the non-conditional color formatting from a selection of workbooks or worksheets.

Unhide Cells
Quickly unhide all hidden rows and columns in a workbook or a selection of worksheets.

Columns and Rows

Insert
Insert rows or columns through arrays and merged cells.

Copies formulas into the newly inserted region; this option will be of interest to users who follow the practice in developing models of using left-to-right, or top-to-bottom, consistent formulas.

Delete
Delete rows or columns that pass through an array, data table (though not a pivot table), or merged cells.

Compare

Workbooks
Aligns and compares a pair of workbooks, highlighting the differences.

Worksheets
Aligns and compares a selection of pairs of worksheets, highlighting the differences.

Ranges
Compares a pair of ranges, highlighting the differences.

Unmodify
Removes the modifications made to a workbook or selection of worksheets by a compare operation.

Names

ReDefine
Change the spelling of existing names, propagating changes throughout the workspace.

Define local names with ease.

ReCreate
Resembles Excel’s Create Names feature, and also:
• Renames ranges that are already named, propagating the changes throughout the workspace.
• Creates names locally if desired.

Apply
Apply a selection of names all at once.

Deapply
Completely or partially eliminate the use of names in a workbook by replacing them with the cell coordinates they represent.

Remove #REF! Names
Builds a list of all the names in the active workbook that have the text #REF! in their definition, and delete all or a selection of them in a single step.

Delete in Cells
Identifies names listed in the selected cells and deletes them.

Localize in Cells
Identifies names listed in the selected cells and makes them local to the active worksheet.

Build Database
Create a sortable list of all names in a workbook, with their locations and characteristics.

Formula

Reconstruct
Find the cells that contribute to the value of a formula in a range and display their contents on a single worksheet.

Optimize
Apply expression simplification to the formula in the active cell. If it finds that the formula can be expressed more simply, it offers the option to replace the original formula with the simplified one.

Prune Inactive Path
Rewrite a formula so that it easy to understand how its result is derived. It does this by removing branches in conditional functions that are not active given current inputs.

For example, the expression IF(A1>B2,C3,D4) evaluates to either C3 or D4, depending on the result of the A1>B2. Replacing the function with simply C3 or C4 leaves the spreadsheet giving the same answer as before, given unchanged inputs, and makes the calculation simpler and easier to understand.

Analyze Discrepancy
Shows a comparison of the derivation of two selected cells.

Highlights values that are common to the left and right halves of the report, and so contribute to each of the two cells being compared. It identifies like cells by color, and hyperlinks them together.

Search

Arrays
Select all cells within the current selection that form part of arrays, including data tables.

Merged Cells
Select all the merged cells that intersect the current selection.

Primary Error Cells
Select those error cells in the current selection that do not in turn reference another error cell in the current selection.

Hardwired Constants
Select all formula cells within the current selection that contain a numeric element. Formula cells that contain numbers only as part of text strings, function names, and name labels are not selected by this command.

Constant Formula Cells
Select all constant formula cells within the current selection. A constant formula does not reference any other cells; instead, for example, it reads “=10+40”. This type of structure might be used when you want to record the derivation of a particular input value.

Unreferenced Cells
Select all cells within the current selection that are not referenced by any formulas within the workbook.

References to Blank Cells
Select all cells on the current worksheet that reference blank cells within the current selection (which is taken to include the blank cells in the unused area that surrounds each worksheet).

Conditional
This command selects all the cells within the current selection that match conditional criteria chosen by you.

You can choose between three types of conditional search criteria:
• numerical cell values can be compared to threshold values;
• cell values can be searched for a particular string;
• cell formulas can be searched for a string.

Range Operations

Transpose
Transpose a selection of cells either in place, or to another location, while preserving references and removing names.

Copy Literal
Copy the contents of the selected cells to another location, preserving relative references. This means that the copied formula will always refer to the same cells as the original.

Copy Address
Format the address of the currently selected group of ranges using a variety of options, and copy the resulting value to the clipboard for easy pasting into your non-spreadsheet documents.