Display The Formulas In The Worksheet

Displaying formulas in a worksheet, rather than their calculated results, is an essential skill for auditing, debugging, and understanding spreadsheet logic. Whether you're using Microsoft Excel, Google Sheets, or another spreadsheet application, knowing how to reveal these underlying formulas can significantly improve your productivity and accuracy. This comprehensive guide explores various methods and techniques to display formulas effectively, ensuring you can master this vital aspect of spreadsheet management.

Why Display Formulas?

Displaying formulas in a worksheet serves several critical purposes:

• Auditing: When reviewing a complex spreadsheet, displaying formulas allows you to verify the logic and accuracy of calculations. This is crucial for financial modeling, scientific analysis, and any situation where data integrity is paramount.
• Debugging: If a spreadsheet produces unexpected results, viewing the formulas can help you identify errors in logic, incorrect cell references, or other issues that might be causing problems.
• Learning: Examining formulas used by others can be an excellent way to learn advanced spreadsheet techniques and understand how different functions can be combined to achieve specific outcomes.
• Documentation: Displaying formulas can be part of documenting a spreadsheet's design, making it easier for others (or yourself, in the future) to understand how the calculations work.
• Presentations: Sometimes, showcasing the underlying logic of a spreadsheet is necessary during presentations or demonstrations, allowing the audience to understand the methodology behind the data.

Methods to Display Formulas in Excel

Microsoft Excel offers several ways to display formulas in a worksheet. Here's a detailed look at each method:

1. Using the "Show Formulas" Button

The simplest and most direct method is to use the "Show Formulas" button, located in the "Formulas" tab of the Excel ribbon.

Steps:

1. Open the Excel workbook: Start by opening the Excel file containing the formulas you want to display.
2. Navigate to the "Formulas" tab: Click on the "Formulas" tab in the Excel ribbon at the top of the screen.
3. Click the "Show Formulas" button: In the "Formula Auditing" group, you'll find the "Show Formulas" button (it looks like a document with a formula on it). Click this button.

What Happens:

• Excel toggles between displaying the calculated results and the actual formulas in each cell.
• When formulas are displayed, the column widths are automatically adjusted to accommodate the longer text strings.
• The "Show Formulas" button acts as a toggle; clicking it again will revert the display to show the calculated results.

Advantages:

• Extremely quick and easy to use.
• Applies to the entire worksheet at once.

Disadvantages:

• Not suitable for selectively displaying formulas in only certain cells.
• The automatic adjustment of column widths can sometimes make the worksheet difficult to read.

2. Using the FORMULATEXT Function

The FORMULATEXT function is a powerful tool for displaying the formula of a specific cell in another cell. This is particularly useful when you want to document formulas alongside their results.

Syntax:

=FORMULATEXT(cell_reference)

• cell_reference: The cell containing the formula you want to display.

Steps:

1. Select a cell: Choose an empty cell where you want to display the formula.
2. Enter the formula: Type =FORMULATEXT(A1) (replace A1 with the actual cell reference) and press Enter.

Example:

If cell B2 contains the formula =A2*2, and you enter =FORMULATEXT(B2) in cell C2, then cell C2 will display the text =A2*2.

Advantages:

• Allows selective display of formulas for specific cells.
• Useful for documenting formulas and their corresponding results side-by-side.
• The displayed formula is a text string, which can be formatted and manipulated like any other text.

Disadvantages:

• Requires entering the formula manually for each cell.
• If the formula in the referenced cell changes, the FORMULATEXT result will update automatically, ensuring that the display remains current.
• FORMULATEXT function is available only in Excel 2013 and later versions.

3. Using Find & Replace to Precede Formulas with an Apostrophe

This method involves adding an apostrophe (') before the equal sign (=) in formulas, effectively converting them into text strings. This prevents Excel from evaluating the formula and instead displays it as entered.

Steps:

1. Select the range of cells: Select the cells containing the formulas you want to display. You can select the entire worksheet by clicking the triangle in the upper-left corner.
2. Open the "Find & Replace" dialog box: Press Ctrl+H (or Cmd+H on a Mac) to open the "Find & Replace" dialog box.
3. Enter the search criteria:
   • In the "Find what" field, enter =.
   • In the "Replace with" field, enter ='.
4. Click "Replace All": Click the "Replace All" button to replace all occurrences of = with ='.
5. Format the cells (Optional): You may need to adjust the cell formatting (e.g., alignment) to make the displayed formulas more readable.

What Happens:

Excel treats the cells as text, displaying the formulas exactly as they were entered, including the equal sign and all cell references.

Advantages:

• Works in older versions of Excel that don't have the FORMULATEXT function.
• Allows you to selectively display formulas in a large range of cells.

Disadvantages:

• Modifies the actual cell content, converting formulas into text strings. If you want to restore the formulas, you'll need to reverse the process.
• Reversing the process (removing the apostrophe) can be tricky if you've made other changes to the worksheet.
• Less dynamic than FORMULATEXT; changes to the original formula will not be reflected in the displayed text.

4. Using VBA (Visual Basic for Applications)

For more advanced users, VBA provides a flexible way to display formulas based on specific criteria. You can write a VBA macro to loop through a range of cells and display the formulas in adjacent cells or based on certain conditions.

Example VBA Code:

Sub ShowFormulas()
  Dim cell As Range
  For Each cell In Selection
    If cell.HasFormula Then
      cell.Offset(0, 1).Value = cell.Formula 'Displays the formula in the adjacent column
    End If
  Next cell
End Sub

Steps:

1. Open the VBA editor: Press Alt+F11 to open the Visual Basic Editor (VBE).
2. Insert a module: In the VBE, go to "Insert" > "Module".
3. Paste the code: Copy and paste the VBA code into the module.
4. Modify the code (if needed): Adjust the code to suit your specific needs (e.g., change the offset to display the formula in a different column, add conditions to only display certain formulas).
5. Run the macro:
   • Select the range of cells containing the formulas you want to display.
   • In Excel, go to the "View" tab and click "Macros" > "View Macros".
   • Select the "ShowFormulas" macro and click "Run".

What Happens:

The VBA code iterates through the selected cells and, if a cell contains a formula, displays that formula in the adjacent cell to the right.

Advantages:

• Highly customizable; you can define specific criteria for displaying formulas.
• Can automate the process of displaying formulas across a large worksheet.

Disadvantages:

• Requires knowledge of VBA programming.
• More complex to implement than other methods.

Methods to Display Formulas in Google Sheets

Google Sheets provides similar, though sometimes slightly different, methods for displaying formulas.

1. Using the "Show Formulas" Option

Like Excel, Google Sheets has a "Show formulas" option, though it's located in a different menu.

Steps:

1. Open the Google Sheet: Open the Google Sheet containing the formulas you want to display.
2. Navigate to the "View" menu: Click on the "View" menu in the Google Sheets toolbar.
3. Select "Show formulas": In the "View" menu, select "Show formulas".

What Happens:

• Google Sheets toggles between displaying the calculated results and the actual formulas in each cell.
• Column widths are automatically adjusted to accommodate the longer text strings.
• The "Show formulas" option acts as a toggle; selecting it again will revert the display to show the calculated results.

Advantages:

• Very simple and straightforward to use.
• Applies to the entire sheet at once.

Disadvantages:

• Not suitable for selectively displaying formulas in only certain cells.
• The automatic column width adjustment can sometimes be disruptive.

2. Using the FORMULATEXT Function

Google Sheets also supports the FORMULATEXT function, which works identically to the Excel version.

Syntax:

=FORMULATEXT(cell_reference)

• cell_reference: The cell containing the formula you want to display.

Steps:

1. Select a cell: Choose an empty cell where you want to display the formula.
2. Enter the formula: Type =FORMULATEXT(A1) (replace A1 with the actual cell reference) and press Enter.

Example:

If cell B2 contains the formula =A2*2, and you enter =FORMULATEXT(B2) in cell C2, then cell C2 will display the text =A2*2.

Advantages:

• Allows selective display of formulas for specific cells.
• Useful for documenting formulas and their corresponding results side-by-side.
• The displayed formula is a text string, which can be formatted and manipulated like any other text.

Disadvantages:

• Requires entering the formula manually for each cell.
• If the formula in the referenced cell changes, the FORMULATEXT result will update automatically, ensuring that the display remains current.

3. Using Find & Replace to Precede Formulas with an Apostrophe

This method works the same way in Google Sheets as it does in Excel.

Steps:

1. Select the range of cells: Select the cells containing the formulas you want to display. You can select the entire sheet by clicking the rectangle at the intersection of the row and column headers.
2. Open the "Find and replace" dialog: Press Ctrl+H (or Cmd+H on a Mac) to open the "Find and replace" dialog.
3. Enter the search criteria:
   • In the "Find" field, enter =.
   • In the "Replace with" field, enter ='.
4. Click "Replace all": Click the "Replace all" button to replace all occurrences of = with ='.
5. Format the cells (Optional): You may need to adjust the cell formatting (e.g., alignment) to make the displayed formulas more readable.

What Happens:

Google Sheets treats the cells as text, displaying the formulas exactly as they were entered, including the equal sign and all cell references.

Advantages:

• Works in all versions of Google Sheets.
• Allows you to selectively display formulas in a large range of cells.

Disadvantages:

• Modifies the actual cell content, converting formulas into text strings. If you want to restore the formulas, you'll need to reverse the process.
• Reversing the process (removing the apostrophe) can be tricky if you've made other changes to the worksheet.
• Less dynamic than FORMULATEXT; changes to the original formula will not be reflected in the displayed text.

4. Using Google Apps Script

Similar to VBA in Excel, Google Apps Script offers a powerful way to automate tasks and customize Google Sheets. You can use Apps Script to create a custom function or a menu item to display formulas based on specific criteria.

Example Google Apps Script Code:

function showFormulas() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var selection = sheet.getActiveRange();
  
  for (var i = 1; i <= selection.getNumRows(); i++) {
    for (var j = 1; j <= selection.getNumColumns(); j++) {
      var cell = selection.getCell(i, j);
      if (cell.getFormula()) {
        sheet.getRange(cell.getRow(), cell.getColumn() + 1).setValue(cell.getFormula());
      }
    }
  }
}

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
      .addItem('Show Formulas', 'showFormulas')
      .addToUi();
}

Steps:

1. Open the Script editor: In Google Sheets, go to "Tools" > "Script editor".
2. Paste the code: Copy and paste the Google Apps Script code into the script editor.
3. Modify the code (if needed): Adjust the code to suit your specific needs (e.g., change the offset to display the formula in a different column, add conditions to only display certain formulas).
4. Save the script: Click the save icon and give your script a name (e.g., "ShowFormulas").
5. Run the onOpen function: The onOpen function will create a custom menu in Google Sheets. Run it once by selecting it from the function dropdown and clicking the play button. Authorize the script when prompted.
6. Use the custom menu:
   • Select the range of cells containing the formulas you want to display.
   • Click on the "Custom Menu" in the Google Sheets menu bar and select "Show Formulas".

What Happens:

The Google Apps Script code iterates through the selected cells and, if a cell contains a formula, displays that formula in the adjacent cell to the right. A custom menu item is created for easy access to the function.

Advantages:

• Highly customizable; you can define specific criteria for displaying formulas.
• Can automate the process of displaying formulas across a large sheet.

Disadvantages:

• Requires knowledge of Google Apps Script programming.
• More complex to implement than other methods.

Best Practices for Displaying Formulas

Regardless of the method you choose, consider these best practices for effectively displaying formulas:

• Use consistent formatting: Apply consistent formatting (e.g., font, alignment) to the displayed formulas to improve readability.
• Adjust column widths: Ensure that column widths are wide enough to fully display the formulas without truncation.
• Document your methods: If you're using a complex method (e.g., VBA or Google Apps Script), document the code and how to use it.
• Consider using comments: Add comments to your formulas to explain their purpose and logic. This can be particularly helpful when working with complex calculations.
• Test your formulas thoroughly: After making changes to formulas, test them thoroughly to ensure they produce the correct results.
• Use color-coding: Consider using color-coding to highlight different parts of formulas or to identify cells with specific types of calculations.

Conclusion

Displaying formulas in a worksheet is a fundamental skill for anyone working with spreadsheets. Whether you choose to use the built-in "Show Formulas" option, the FORMULATEXT function, the Find & Replace method, or a custom VBA/Google Apps Script solution, understanding how to reveal the underlying logic of your spreadsheets is essential for auditing, debugging, learning, and documenting your work. By mastering these techniques, you can significantly improve your productivity and accuracy when working with complex spreadsheets. Choose the method that best suits your needs and skill level, and start exploring the power of displayed formulas today.