How to Show Formulas in Excel

by

Show Formulas in Excel: Excel is a powerful spreadsheet software that offers a multitude of features for data analysis and organization.

Among its many functionalities, the ability to create formulas stands out as a fundamental aspect of Excel’s capabilities.

Formulas allow users to perform calculations, automate tasks, and make data-driven decisions.

While Excel typically displays the results of formulas by default, there are instances where it becomes necessary to show the actual formulas themselves.

This can be beneficial for various reasons, such as troubleshooting errors, verifying complex calculations, or sharing spreadsheets with colleagues who need to understand the underlying calculations.

In this comprehensive guide, we will explore different methods to show formulas in Excel. Whether you need to display formulas for specific cells, an entire worksheet, or even print formulas along with the data, we have got you covered.

We will also delve into hiding formulas when necessary and provide insights into protecting your formulas from accidental changes.

By the end of this guide, you will have a thorough understanding of how to effectively show and utilize formulas in Excel.

Whether you are a beginner or an experienced Excel user, this step-by-step guide will equip you with the knowledge and techniques to confidently work with formulas and harness the full potential of Excel’s calculation capabilities.

So let’s dive in and learn how to show formulas in Excel, empowering you to make the most out of your data and streamline your spreadsheet tasks.

How to Show Formulas in Excel: A Step-by-Step Guide

1. Understanding Formulas in Excel

Formulas in Excel are expressions that perform calculations based on the values of cells. They can be as simple as adding two numbers or as complex as creating nested functions and logical operations.

Excel formulas start with an equal sign (=) and can include cell references, constants, mathematical operators, and functions.

2. Displaying Formulas in Excel

Showing Formulas Using the Formula Bar

To view the formulas in a specific cell, you can select the cell, and the formula will be displayed in the formula bar at the top of the Excel window.

This method allows you to see the formula and its components, making it easier to understand the calculation.

Using the Show Formulas Shortcut

Excel provides a convenient shortcut to toggle between showing formulas and showing the results. By pressing Ctrl + ~ (tilde) on your keyboard, you can switch between the two display modes instantly.

This is particularly useful when you want to quickly check the formulas in your worksheet.

Applying the Show Formulas Option

If you prefer to show formulas for the entire worksheet instead of individual cells, you can use the “Show Formulas” option. Here’s how to enable it:

  1. Go to the “Formulas” tab in the Excel ribbon.
  2. In the “Formula Auditing” group, click on the “Show Formulas” button.
  3. All the formulas in the worksheet will be displayed, replacing the cell values temporarily.

Hiding Formulas in Excel

Hiding Formulas Using the Formula Bar

If you want to hide the formulas and only display the results, you can select the cell or range of cells, and simply delete the equal sign (=) from the formula bar.

The calculated values will remain visible, but the formulas will be concealed.

Using the Hide Formulas Shortcut

Similar to the shortcut for showing formulas, Excel provides a shortcut to hide formulas as well. Pressing Ctrl + ~ (tilde) will toggle the display mode between showing formulas and showing results.

Applying the Hide Formulas Option

To hide formulas for the entire worksheet, follow these steps:

  1. Go to the “Formulas” tab in the Excel ribbon.
  2. In the “Formula Auditing” group, click on the “Show Formulas” button. This will deactivate the “Show Formulas” option, hiding all the formulas in the worksheet.

Printing Formulas in Excel

Excel allows you to print your worksheets with either the formulas or the calculated results. To include formulas in the printed output, follow these steps:

  1. Go to the “File” tab and select “Print” from the menu.
  2. In the print settings, under “Settings,” choose the “Print Active Sheets” option.
  3. Enable the “Print Formulas” checkbox.
  4. Click on the “Print” button to start printing the worksheet, including the formulas.

Protecting Formulas in Excel

To prevent accidental changes to your formulas, Excel provides a feature called “Protect Sheet.”

By protecting the sheet, you can ensure that the formulas remain intact while allowing users to enter data or make changes in other non-formula cells. Here’s how to protect a sheet:

  1. Go to the “Review” tab in the Excel ribbon.
  2. In the “Changes” group, click on the “Protect Sheet” button.
  3. Set a password if desired and specify the options you want to allow for users.
  4. Click “OK” to protect the sheet and safeguard your formulas.

Using Conditional Formatting to Highlight Formulas

Conditional formatting is a powerful feature in Excel that allows you to apply formatting rules based on specific conditions.

You can utilize this feature to highlight cells that contain formulas, making them stand out in your worksheets. Here’s how:

  1. Select the range of cells you want to apply conditional formatting to.
  2. Go to the “Home” tab in the Excel ribbon.
  3. In the “Styles” group, click on “Conditional Formatting” and choose “New Rule.”
  4. In the “New Formatting Rule” dialog box, select “Use a formula to determine which cells to format.”
  5. Enter the formula =ISFORMULA(A1) in the “Format values where this formula is true” field. Adjust the cell reference accordingly.
  6. Specify the formatting options you want to apply to the cells containing formulas.
  7. Click “OK” to apply the conditional formatting rule.

Troubleshooting Common Formula Display Issues

Formulas Not Displaying Correctly

If your formulas are not displaying correctly or are producing unexpected results, check for the following issues:

  • Ensure that the formulas are entered correctly, with the correct syntax and references.
  • Check for any hidden or filtered rows or columns that may affect the calculation.
  • Verify that the cell formatting is appropriate for the expected result.

Circular References

Circular references occur when a formula refers to the cell it is located in or creates a loop of references. Excel usually detects and alerts you about circular references.

To resolve this issue, you can either adjust the formulas or enable iterative calculations in Excel’s options.

Error Messages

Excel provides error messages to help you identify and resolve formula-related issues. The most common error messages include #VALUE!, #REF!, #DIV/0!, #NAME?, and #N/A.

Understanding these error messages and addressing their causes can help you troubleshoot and fix formula errors effectively.

Final Note:

Showing formulas in Excel is an essential skill that empowers users to understand and validate their calculations.

By following the methods outlined in this article, you can easily display, hide, print, and protect formulas in Excel.

Additionally, leveraging conditional formatting and troubleshooting techniques will enable you to work with formulas more efficiently and identify and resolve common formula-related issues.

FAQ’s

[WPSM_AC id=92176]