How to Show Formulas in Excel

Exploring the art of Excel formulas can turn up your data analysis and reporting capabilities. Whether you’re a financial analyst, digital marketer, or an Excel learner, understanding how to display formulas in Excel is important. This article will make you learn the simple steps to show the formulas of your Excel spreadsheets, enhancing your efficiency and proficiency. By learning how to show formulas in Excel, you’ll learn to ensure accuracy and transparency in your calculations.

How to Show Formulas in MS Excel

When you enter a formula in a cell and press the Enter key, Excel immediately displays the calculated result. To check all the formulas in the cell, follow one of the methods provided below for your reference,

Show the Formulas Option on the Excel Ribbon

The Ribbon: Head over to the Formulas tab, find the Formula Auditing section, and hit the Show Formulas button. Your formulas will step into the spotlight, replacing the results on stage. To get the calculated values back, click the Show Formulas button again to toggle it off.

Show Formulas in cells instead of their results in Excel options

In Excel go to File > Options > Select Advanced on the left pane > scroll down to the Display options for this worksheet section and select the option Show formulas in cells instead of their calculated results.

How to Show Formula in Excel Shortcut

The fastest and easiest way to see every formula in your Excel spreadsheet is pressing the following shortcut: Ctrl + The grave accent key (`). The Show Formulas shortcut between displaying cell values and cell formulas. To get the formula results back, press the shortcut again.

NOTE: Keep this in mind, no matter which method you choose, Excel will reveal the formulas only for the sheet you’re currently on. If you want to see the formulas on different sheets or in other workbooks, you’ll have to do these steps for each one separately.

How to Highlight Formulas in Excel

Step 1: Go to Home Tab

Step 2: Click on Find & Select Button

Step 3: Select Formulas

Manage Your Excel Formulas

When it comes to managing formulas in Excel, a few smart practices can make a big difference in maintaining the of your data. Here are some tips and tricks to keep your Excel formulas in top shape,

1. Simplicity is Key: Keep your formulas as simple as possible. Complex formulas are more prone to errors and harder to troubleshoot.
2. Consistent Referencing: Use absolute ($A$1) and relative (A1) cell references effectively. Consistent referencing ensures that your formulas work correctly when copied or moved around.
3. Document Your Work: Annotate your formulas with comments to explain their purpose. This is especially helpful for future reference and for others who might use your spreadsheet.
4. Error Handling: Incorporate error handling functions like IFERROR to manage potential errors gracefully. This prevents your entire spreadsheet from being affected by one erroneous cell.
5. Regular Checks: Double-check your formulas for accuracy. Ensure that cell references are correct and that the formula is doing what you expect it to do.
6. Protect Your Formulas: Prevent accidental changes by locking cells that contain formulas. You can do this by formatting the cells and setting them as ‘Locked’, then protecting the worksheet.
7. Use Named Ranges: Instead of using cell references, use named ranges to make your formulas easier to understand and manage.
8. Audit Your Formulas: Use Excel’s formula auditing tools to trace precedents and dependents, and to evaluate parts of your formulas.

Conclusion

In the conclusion, exploring the Excel formula display is a vital skill that can enhance your analytical ability to new heights. As you apply these insights, remember that Excel is more than a mere spreadsheet application; it’s a robust tool for strategic decision-making. By learning the formulas that drive your data, you’re not just enhancing your Excel fluency—you’re also making the way for more informed, data-driven decisions. Explore these techniques to show formulas in Excel.

How do you show formulas instead of results in Excel?

Use the Ribbon: Go to the Formulas tab and click on Show Formulas in the Formula Auditing group.

How to display the formula bar in Excel?

To display the formula bar in Excel:

• Use the Ribbon: Click on the View tab and check the Formula Bar option in the Show group.
• Excel Options: Click on the File tab, select Options, go to Advanced, scroll down to the Display section, and check the Show Formula Bar option.