Formulas are an integral part of any Excel spreadsheet. Using a formula in a cell or range of cells returns the calculated result by default, so all you see is the numeric value rather than the formula itself. Therefore, taking a closer look at the formula structure in the spreadsheet requires a bit of digging.
Below, you’ll find several methods to reveal the formula in individual cells or throughout an Excel spreadsheet.
1. Use the Formula
The simplest way to view formulas in Excel is by using the formula bar. Although it might seem obvious, this approach is suitable for most situations. If you need to refer to specific cells in a spreadsheet to verify the formula in them, the formula bar is the way to go. Select the cell where you wish to view the formula, and Excel will instantly display it in the formula bar.
This method is handy for checking formulas in individual cells but isn’t practical for viewing formulas in the whole spreadsheet, as you’ll have to navigate each cell separately. If you want Excel to display every formula in a spreadsheet all at once, explore the methods below.
2. Use the Show Formulas Feature in Excel
The Show Formulas feature offers the easiest way to uncover formulas throughout an Excel spreadsheet. With this option enabled, each cell displays the formula rather than the resulting value. This allows you to quickly find the formula you are looking for or check the accuracy of formulas used throughout the entire spreadsheet in a quick glance.
Follow the below steps to show formulas using this method:
Following the above steps will reveal formulas across an entire worksheet. If you want to opt out of the formula view, click theShow Formulaoption again.
The downside of this method is that you’ll have to turn the feature on or off each time you hide or unhide a formula, which can be a hassle if you need to do this frequently.
3. Show Formulas From the Excel Options Window
This method works analogously to enabling the Show Formulas feature from the Formula tab. However, this method allows you to easily apply different settings to each worksheet in your workbook at once. To display formulas rather than numeric values using the Excel Options window (called Preferences on macOS), follow the below steps:
Like the previous method, this approach requires you to enable this option for every worksheet separately and turn it off once you have checked the formulas, which can make it counterproductive. If you want to avoid that headache absolutely, you have two options to pick from.
The first option is to simply grab the screenshot of the spreadsheet showing the formulas and use that image as a reference. It’s an easier method, and you’ll have a record of the spreadsheet showing formulas. If this method doesn’t suit your needs, you can turn on or off the formula preview in Excel using the shortcut below.
4. Use the Keyboard Shortcut to Hide and Unhide Formulas
Excel supports a wide range of keyboard shortcutsto help users get work done faster. Luckily, this includes a shortcut for showing and hiding formulas too. The shortcut eliminates the need to go through the hassle of enabling or disabling the Show Formulas feature frequently. On Windows, the shortcut isCTRL + ‘; on macOS, it’sCommand + Option + ‘.
If you’ve assigned these keys tocreate a custom Excel shortcut, then this shortcut may not work. As a result, you’ll need to delete the custom shortcut to use these effectively.
5. Use the FORMULATEXT Function
Using the FORMULATEXT function is the only way to permanently display formulas next to numeric values. Unlike the other methods discussed thus far, this function converts the formula into plain text, making it useful in the following scenarios:
To use the FORMULATEXT function in Excel, follow the below steps:
One drawback of this method to display formulas is that it requires allocating a separate cell, column, or row.
While this will be useful when teaching a group of students or designing tutorial worksheets for a targeted audience, a dummy row or column might not be suitable whencreating professional documents.
Most of the methods covered above can only be used to show formulas in a single Excel worksheet; for each sheet, you will need to repeat the process. The only way to display formulas across every sheet is with the help of aVBA macro.
Reveal the Formulas Hidden Behind Numerical Values
Checking and tweaking formulas in an Excel spreadsheet is a common procedure. Without the right method, you’re doomed to spend precious time on verifying formulas one by one.
Hopefully, now that you know the different ways you’re able to display formulas in Excel, you can effectively view the various formulas in your spreadsheet. Each method has a different use case, so pick the one that suits your needs best.
