Advanced Features

Cell Highlights

  • FormulaChop looks for certain functions and highlights the cells in their precedent ranges that contribute to their results.
_images/highlight-only.png

A dark outline shows highlighted cells

Tip

Any time a cell is highlighted, the label for its range becomes a link to only the highlighted cells

This can be very useful to focus on only the cells that are relevant to a calculation

Example

Navigate to the ‘Examples’ tab of this file to try the examples below for yourself

INDEX()

_images/highlight-index.png

FormulaChop highlights the cell(s) returned by the INDEX() function

LOOKUP()

_images/highlight-lookup.png

FormulaChop highlights the cell(s) in which the search value is found, as well as the return value.

  • This works for LOOKUP(), HLOOKUP() and VLOOKUP()

CHOOSE()

_images/highlight-choose.png

FormulaChop highlights cell(s) returned by CHOOSE()

SUMIF(), COUNTIF(), AVERAGEIF()

_images/highlight-sumif.png

FormulaChop highlights the cell(s) where the condition is true, as well as the cell(s) being summed/counted/averaged

_images/highlight-sumifs.png

This works for the plural versions of these functions: SUMIFS() and COUNITFS()

MATCH()

_images/highlight-match.png

FormulaChop highlights matching cell(s)

Advanced Formulas

  • FormulaChop supports advanced formula constructions which are used in large, complex spreadsheets.

Example

See this file to try the examples below for yourself

Formulas Which Return Ranges

  • Formulas can be written such that they return a range, instead of a single value.
  • In this situation, Excel will pick the value in the returned range that aligns with the calling cell.
_images/advanced-align.png

FormulaChop handling a formula which returns a range

Example:

The figure above shows FormulaChop output from cell D9. The chopped formula is:

=IF(True_False,Factor_1, Factor_2)

This IF() statement returns a range of cells, depending on the value in True_False.

Excel uses the relative alignment of the calling cell, G3 and the returned range, Factor_1 (F7:Q7), to decide which value to in the returned range to show. Because the calling cell is in column G, the value in column G of the range Factor_1 will be returned

FormulaChop recognizes this situation and recreates all the ranges’ alignment on the FormulaChop Tab.

  • A common example of this situation is the use of INDEX() with a 0 argument.
  • When 0 is given as an argument for a row or column, INDEX() returns the entire row or column.
  • Because INDEX() is returning a range, Excel will use the relative alignment of the calling cell and the resulting range to give the formula’s single result
_images/advanced-index-zero.png

FormulaChop handling INDEX() Zero Argument

Example

The figure above shows FormulaChop output from cell D10. The chopped formula is:

=INDEX(Precedent1,Scenario_Select,0)

Instead of specifying a column, this formula uses 0 as its column argument.

Because the cell containing this formula is in column G, INDEX() will use column G in the range Precedent1 (F6:Q8). This feature also applies when the calling cell is on a different tab from the range in the first argument to INDEX().

OFFSET()

  • Unique among all Excel functions, the OFFSET() function is able to depend on ranges without explicitly referring to them.
_images/advanced-offset.png

FormulaChop handling an OFFSET() function

  • FormulaChop evaluates each OFFSET() in the formula, and determines the range it is referring to, and copies that range to the FormulaChop Tab

INDIRECT()

  • The INDIRECT() function accepts as text the address of a range in the workbook, then it returns that range.
_images/advanced-indirect-input-1.png

A formula using the INDIRECT() function

_images/advanced-indirect-input-2.png

The cell referenced by the INDIRECT() function: Data!C50. Notice it contains a cell address as text.

_images/advanced-indirect-result.png

FormulaChop handling an INDIRECT() function. The INDIRECT() function has been replaced by its result: Data!C18.

  • FormulaChop evaluates each call to INDIRECT() and finds its resulting range
  • Then FormulaChop replaces the call to INDIRECT() in the formula with the range
  • The range returned by INDIRECT() appears on the FormulaChop Tab

Dynamic Ranges

  • Excel allows two functions, OFFSET() and INDEX(), to be used in the place of a cell when defining a range
_images/advanced-dynamic.png

FormulaChop handling a Dynamic Range.

Example

The figure above shows FormulaChop output from cell D12. The chopped formula is:

=SUM(Factor_1_01:INDEX(Factor_1_02,,Sum_Through))

  • The value of Sum_Through (F11) is 9

  • Factor_1_01 is the range B10, Factor_1_02 is the range B10:M10

  • INDEX(Factor_1_02,,Sum_Through) will return the cell J10

    • B10 + 9 columns = J10

  • Now, the formula reads SUM(B10:J10)

The value in Sum_Through defines the size of the range being summed. This is what makes the range “dynamic”

  • FormulaChop is able to handle Dynamic Ranges by only showing a single range on the FormulaChop Tab
  • The start and end of the Dynamic Range is highlighted

3D Ranges

  • Excel allows ranges to be defined across many tabs. This is called a 3D Range.
_images/advanced-3d-input.png

A formula with a 3D range. Notice the range of tabs specified: Proj1:Proj3.

_images/advanced-3d-output.png

FormulaChop handling a 3D Range. Notice the 3D range has been replaced with a list of ranges.

Example

The figure above shows FormulaChop output from a cell using a 3D Range. This workbook has tabs Proj1, Proj2 and Proj3. Each of these tabs has the same layout, with a “Capacity” value in cell C2. The chopped formula is:

=SUM('Proj1:Proj3'!C2)

This formula sums the values in cell C2 in all of the tabs between Proj1 and Proj3.

  • FormulaChop looks for 3D ranges in the formula, and replaces them with a comma-separated list of ranges on each tab

Labels

  • FormulaChop does its best to automatically find the labels for the ranges in the Precedents and Dependents sections

  • FormulaChop will look for labels

    • Horizontally if the range is a row (determined by the Orientation setting)
    • Vertically if the range is a column (determined by the Orientation setting)
    • In the direction set by the Orientation setting if the range is a single cell. Then in the other direction if no label is found.

  • The start and direction of the search depends on the setting of Label Search Direction

    • Out<-Inside The search starts from the first cell in the precedent and steps outward
    • Outside->In The search starts from the first row/column of the worksheet and steps inward

  • If FormulaChop finds a cell containing text, and the text meets some criteria, FormulaChop decides that the text is the label

  • Ranges that are blocks (i.e. have more than 2 rows and columns) are only labelled if they are named ranges

Label Criteria

While FormulaChop is looking for a label, it will skip a cell if:

  • Cell does not contain text
  • Cell is empty or blank
  • Text is fewer than 3 characters long
  • Text is surrounded by parentheses or brackets (e.g. “(kWh)” and “[kWh]” would not be considered labels)
  • Text is “USD”

Custom Criteria

You may specify your own text for FormulaChop to ignore while searching for labels

FormulaChop will look for a Named Range named fcIgnoreText

  • Any text inside fcIgnoreText will be ignored when FormulaChop searches for labels
  • This range may be any shape or size, and may be on any tab
  • Locating fcIgnoreText on the FormulaChop Tab is not recommended

Rules for Turning Labels into Range Names

FormulaChop follows these rules when labels are being used to name precedent ranges

  • Characters which are invalid in Named Ranges will be replaced with an underscore (“_”)

Example

A range with the label “Special Expenses ($)” would be named “Special_Expenses____”

A range with the label “11th” would be named “_1th”

  • Leading underscores will be removed if possible

Example

A range with the label “(-) Cash Distributions” would be named “Cash_Distributions”

A range with the label “101st” would be named “_01st”

  • Repeat names will be numbered

Example

Two ranges with the label “date” would be named “date_01” and “date_02”

  • Labels which could be interpreted as cell addresses will not be labelled

Example

A range with the label “P50” will not be named, because “P50” is the address of a cell

  • Ranges with no label are named “PrecedentX”, where X is position in the order of appearance in the formula

Example

A range with no label, which appears 3rd in the formula, would be named “Precedent3”

Unsupported Functions

FormulaChop is unable to support some Excel functions. FormulaChop will not run on formulas which contain these functions.

  • In general, this is because these functions rely on aspects of the precedents cells which cannot be copied to the FormulaChop Tab
  • For example, the ROW() function returns the row number of its argument
    • FormulaChop rebuilds the formula on the FormulaChop Tab, and would be unable to always place the precedent cells in the same row as the chopped formula

Unsupported Function List

  • CELL()
  • COLUMN()
  • FILTER()
  • FORMULATEXT()
  • ROW()
  • SINGLE()
  • SORT()
  • TRANSPOSE()
  • UNIQUE()

Array Formulas

FormulaChop is unable to support Array Formulas.

  • Because FormulaChop is designed to only be run on one formula in one cell, it is unable to copy an array of cells to the FormulaChop Tab

INDIRECT() Array Output

FormulaChop is unable to support formulas which use an INDIRECT() function that returns an array of ranges. In this context, there is no way to replace the INDIRECT() function with an ordinary range.

Please see this SuperUser post to know more about this issue.

Deleting The FormulaChop Tab

  • The FormulaChop Tab is automatically deleted before the spreadsheet is saved or closed
  • Any tabs which were saved using the Save command (or renamed manually) will not be deleted
  • Running FormulaChop will not require the spreadsheet to be saved unless:
    • It needed to be saved before FormulaChop was run
    • Calculating the spreadsheet (i.e. pressing F9) causes it to require saving

FormulaChop History

  • FormulaChop saves a history of cells which have been chopped
  • To go back in the history, use Ctrl + Shift + V (see Chop Commands)
  • To go forward in the history, use Ctrl + Shift + F (see Chop Commands)
  • If you are back in the history and you chop a new cell, this cell will become the latest in the history