What it does
Uses a reusable settings sheet to specify sheets/columns to toggle formulas and values to speed up files with lots of formulas that constantly recalculate.  The specified columns will keep formulas in the top data row when converting formulas to values such that the values can be converted back to formulas.

When to use it
When you want to replace formulas with values for all but the top row of data to speed up your workbook.

Why to use it
It quickly and easily allows you to toggle formulas to values and vice versa, allowing you to navigate, filter, and edit much faster.

Default shortcut
None

Other Details

• Category: Formulas / General
• Difficulty: 3/5
• Usage/frequency: 4/5
• Automation factor: 5/5 (estimated 180 seconds saved each time used)
• Type: Bulk
• Date added: 8/18/2023
• Tags: Bulk, toggle, formulas, values

Related Macros
Toggle Formula Calculation

Other Articles
None

Toggle Formulas Values Example File

Prerequisites
Identify the sheets and columns containing formulas that you’d like to convert to values.

Instructions
With the sheets and columns identified, run the Toggle Formulas Values macro.  It will create a new sheet called Toggle_Formulas_Values. This is where you’ll configure all the sheets, columns, and header rows for the columns you want to toggle from formulas to values or values to formulas.  These are the columns within the Toggle_Formulas_Values sheet you’ll want to fill in:

• Column A – Sheet name (required): enter the sheet name containing the column(s) you want to toggle.
• Column B – Column letter (required) – enter the column letter for each column you want to toggle.
• Column C – Header row (required) – enter the number of the row containing headers above the formulas (if multiple rows, enter the bottom row number).  This is used to determine the first row of data formulas to copy (or paste values/formulas for rows below).

The macro looks at the first sheet/column to determine if the second row of data contains a formula or not.  If it contains a formula, the formulas in all sheets/columns listed are converted to values.  If it contains a value, the values in all sheets/columns listed are converted back to formulas based on the formulas remaining in the top row of data.

TBD…