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:
- Open the spreadsheet where you want to see formulas.
- Go to the Formula tab in the top menu.
- Enable Show Formulas in the Formula Auditing section.
Following the above steps will reveal formulas across an entire worksheet. If you want to opt out of the formula view, click the Show Formula option 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:
- Press Alt + F + T (on Windows) or Command + , (on macOS) to open the Excel Options (or Preferences) window.
- Go to the Advanced tab in the left pane.
- Navigate to the Display section and select the worksheet you want to show formulas on in the dropdown next to Display options for this worksheet.
- Check the box beside Show formulas in cells instead of their calculated results.
- Hit OK.
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 shortcuts to 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 is CTRL + '; on macOS, it's Command + Option + '.
If you've assigned these keys to create 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:
- You need both the calculated value and the formula visible at all times so that you can look them up whenever needed.
- You want to display formulas used in specific cells, columns, or rows rather than everywhere in the Excel sheet.
To use the FORMULATEXT function in Excel, follow the below steps:
- Go to the column right next to the one where the formula was used.
- Type =FORMULATEXT (cell number) and press Enter.
- Drag the cell downward to fill all the cells down the column.
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 when creating 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 a VBA 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 can 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.