Simple situation. We have a table with formulas:
For the convenience of debugging and finding errors, I would like to simultaneously see both the values in the cells and the formulas by which these values are calculated. For example, like this:
To get such beauty, you need to take just a few simple steps:
- Create a copy of the current book window by clicking on the tab View (View) button New window (New Window). In older versions of Excel, this can be done through the menu Window – New Window (Window — New window).
- Place both windows from top to bottom under each other by clicking on the same tab View (View) button Organize Everything (Arrange all). In Excel 2003 and later – menu Window – Arrange All (Window — Arrange all).
- Having selected one of the resulting windows, switch to the formula view mode by clicking on the tab formula (Formulas) button Show formulas (Show Formulas). In old excel this is the menu Tools – Formula Dependencies – Formula Check Mode (Tools — Formula Auditing — Show Formulas).
If such a view of formulas is useful to you and will be needed often, then you can use a simple macro that will do all of the above for you:
'macro for turning on formula view mode Sub FormulaViewOn() ActiveWindow.NewWindow ActiveWorkbook.Windows.Arrange ArrangeStyle:=xlHorizontal ActiveWindow.DisplayFormulas = True End Sub 'macro for turning off formula view mode Sub FormulaViewOff() If ActiveWindow.WindowNumber = 2 Then ActiveWindow.Close ActiveWindow .WindowState = xlMaximized ActiveWindow.DisplayFormulas = False End If End Sub
Press the keyboard shortcut ALT + F11to switch to the Visual Basic Editor. Then create a new empty module via the menu Insert – Module and copy the text of the above two macros there. The first enables and the second disables our two-window formula view. Keyboard shortcuts can be used to run macros ALT + F8, then button Run (run) or assign hotkeys to macros in the same window using the button Parameters (Options).
- Selecting all cells with formulas or constants on a sheet at once
- What are macros, where to insert macro code in VBA, how to run macros.
- Colormap for highlighting cells with specific content types in PLEX add-on