Quick Tools Navigation and Relationships Selections, Copy & Paste Key Functions
Subtotal
Sum
C&P Σ
Sumifs
IndexMatch
Convert
'Sumifs' to Cell References
'IndexMatch' to Cell Reference
Create 'Indirect'
Remove 'Indirect'
Convert 'Subtotal' to 'Sum'
Convert 'Sum' to 'Subtotal'
Utilities PivotTables Charts General

Shortcut to quickly insert a SUBTOTAL function (e.g., SUBTOTAL(9, A1:A3)). The SUBTOTAL will reference the current selection and be placed in the cell right below it. A great feature for those who prefer SUBTOTAL over SUM. Learn more.

Lock the current selection and specify a cell to insert the SUM function referencing that selection. Ideal if SUM function and the range it references are in different locations so that you cannot use Excel’s Autosum feature.

Copy-paste only cells with SUBTOTAL or SUM functions across the selection without overwriting other cells. Ideal for cases where you can't simply copy-paste the entire column due to hardcoded values or differing formulas, saving you from the hassle of copy-pasting each row individually.

The tool scans the leftmost column and applies the SUM/SUBTOTAL formulas row-by-row to your selection. The selection can also be non-contiguous, allowing you to skip over columns where you don't want the formulas applied.

Easily insert a SUMIFS formula based on user prompts:

  1. First Input: Specify the range to sum.

  2. Second Input: Select the criteria range. Single cell selections are sufficient as the tool automatically aligns the range with the sum range. Hold CTRL to add multiple criteria.

  3. Third Input: Select the cells containing the criteria. They must be in the same order as the criteria ranges.

  4. Fourth Input: Choose the cell where you want the SUMIFS formula.

This tool focuses on convenience by only requiring exact range selection for the sum range and reducing the need to switch between sheets.

Easily insert an INDEX MATCH formula based on user prompts:

  1. First Input: Specify the index range (lookup range).

  2. Second Input: Select the row criteria range. Single cell selections are sufficient as the tool automatically aligns the range with the index range. Hold CTRL to add multiple criteria.

  3. Third Input: Select the cells containing the row criteria. They must be in the same order as the row criteria ranges.

  4. Fourth Input: Choose the cell where you want the INDEX MATCH formula.

Note: If your index range contains multiple columns, 2 additional inputs for columns will appear.

Convert all SUMIFS functions in the selection to direct cell references. If multiple matches are found, they are consolidated in a SUM function.

Convert all INDEX MATCH functions in the selection to direct cell references. Ideal to reduce complexity.

This utility removes the sheet name from a range reference (e.g., the "Sheet" in "Sheet!A1"), places the sheet name in a separate cell, and constructs the range reference by linking to that cell using the INDIRECT function. This method allows you to create dynamic range references, making it easy to quickly reference the same range across multiple sheets.

Note: Use with caution, as INDIRECT references don’t automatically adjust when inserting or deleting rows/columns and, as a volatile function, can negatively impact performance.

Convert all INDIRECT functions in the selection to their resulting "normal" cell references, effectively removing the INDIRECT references.

Due to the potential complexities and risks associated with the INDIRECT function, we recommend converting these functions to standard references after setting up your analysis to reduce error potential and simplify your workbook.

Convert SUBTOTAL formulas to SUM formulas.

Note: For this to work properly, the hierarchy must be free of errors, the SUBTOTAL formulas must be on the same sheet as their sum range, refer to a single column (i.e. standard vertical summation logic), and the selection must encompass all sum ranges.

Convert SUM formulas to SUBTOTAL formulas.

Note: For this to work properly, the hierarchy must be free of errors, the SUM formulas must be on the same sheet as their sum range, refer to a single column (i.e. standard vertical summation logic), and the selection must encompass all sum ranges.