How to See Formulas in Excel

Excel formulas drive the core functionality of the Microsoft Excel program. With the ability to create complex formulas, Excel has become the go-to software for data analysis and manipulation in many industries. Yet, sometimes it is necessary to view the formulas, especially when dealing with complex spreadsheets and when troubleshooting any errors that are occurring within a range of cells. In this post, we will explore how to see formulas in Excel, allowing you to more effectively use and troubleshoot your spreadsheets.

Introduction

Microsoft Excel is a powerful tool that enables users to manage and analyze data effectively. One of the key features of Excel is its ability to use formulas to perform complex calculations. Whether you are a beginner or an experienced user, you may sometimes need to view the formulas behind your Excel sheet. This post will take you through a step-by-step guide on how to see the formulas in Excel.

Why you may need to see formulas in Excel

There are various reasons why you may need to view the formulas in your Excel sheet:

• You need to verify a formula: At times, a cell may not display the correct value, and you need to double-check the formula.
• You need to copy formulas to another sheet: By seeing the formulas, you can copy them from one sheet to another.
• You want to replicate a formula: By viewing the formula, you can replicate it to another cell with similar data.

How to see formulas in Excel

To see the formulas in Excel, follow these simple steps:

Step 1: Open the Excel sheet

Launch Microsoft Excel and open the sheet that you want to view.

Step 2: Click on ‘Formulas’

Click on the ‘Formulas’ tab on the ribbon at the top of your Excel sheet. This tab is located between the ‘Data’ and ‘Data Tools’ tabs.

Step 3: Click on ‘Show Formulas’

In the ‘Formulas’ tab, click on the ‘Show Formulas’ button. This button is located in the ‘Formula Auditing’ section of the tab.

Step 4: View the formulas

After clicking on the ‘Show Formulas’ button, Excel will display the formulas in all cells instead of the formulas’ results. All formulas will have an equal sign before them as a prefix. You can now review the formulas, copy them to another sheet, or make any necessary changes.

By following these simple steps, you can easily view formulas in Excel and make necessary adjustments. Seeing formulas in Excel is useful for verifying formulas, copying them to another sheet, or replicating them to similar cells. A good understanding of Excel formulas can help you save significant time and achieve your Excel tasks more efficiently.

Other Ways to View Formulas in Excel

Aside from the ‘Show Formulas’ feature, there are two other ways to view formulas in Excel:

1. Using the Keyboard Shortcut

You can view the formulas in an Excel sheet by using the keyboard shortcut ‘Ctrl’ + ‘`”. The ‘`’ key is located to the left of the ‘1’ key on most keyboards. Pressing ‘Ctrl’ + ‘`” will toggle Excel between displaying formulas and displaying the results of the formulas.

2. Using the Error Checking Feature

If you are having trouble with your Excel sheet, the ‘Error Checking’ feature can help you identify any errors in your formulas. To access this feature, click on the ‘Formulas’ tab on the ribbon, then click on the ‘Error Checking’ button in the ‘Formula Auditing’ section. Excel will guide you through any potential errors in your formulas, including an option to view the formula.

Protecting Your Formulas

While it can be helpful to view the formulas in your Excel sheet, you may sometimes want to protect your formulas to avoid them being accidentally altered. To protect your formulas, you can use Excel’s ‘Protect Sheet’ feature. This feature allows you to lock specific cells in your sheet, including cells with formulas.

To protect your formulas, follow these steps:

• Select all the cells that contain your formulas.
• Right-click and choose ‘Format Cells’.
• In the Format Cells dialog box, click on the ‘Protection’ tab.
• Check the ‘Locked’ box and click ‘OK.’
• Now, click on the ‘Review’ tab on the ribbon and select ‘Protect Sheet.’
• In the ‘Protect Sheet’ dialog box, select the options you want to apply, including password protection if necessary. Click ‘OK.’

Viewing formulas in Excel can help you optimize your spreadsheet, identify errors, and make necessary adjustments to your formulas. Whether you prefer to use the ‘Show Formulas’ button, the keyboard shortcut, or the Error Checking feature, Excel makes it easy to access your formulas. Additionally, protecting your formulas is an important safeguard to ensure that your data remains secure and accurate. By following these tips, you’ll be on your way to becoming an Excel expert in no time!

FAQs

Here are some common questions and answers related to viewing formulas in Excel:

1. Can I view formulas in Excel without showing every single formula?

Yes! You can choose to view the formulas in a single cell by selecting the cell and then clicking on the formula bar. This feature is useful when you only want to check a specific cell’s formula instead of viewing all the formulas in your sheet.

2. What is the difference between displaying formulas and displaying their results?

Excel defaults to display the results of formulas, but you can toggle to display the formulas themselves. This is useful if you need to see what calculations are being performed. Formulas are always preceded by an equal sign (‘=’) while their results are not. Additionally, displaying formulas instead of results can help you identify any errors in your Excel sheet easier.

3. How can I switch back to displaying results instead of formulas?

You can switch back to displaying results instead of formulas by pressing the ‘Ctrl’ + ‘`” keyboard shortcut or by clicking on the ‘Show Formulas’ button again. This will toggle back to displaying the results of the formulas.

4. Can I print an Excel sheet with formulas displayed instead of the results?

Yes, you can! Before printing, navigate to the ‘Formulas’ tab and click on ‘Show Formulas’ to ensure that your sheet’s formulas are visible instead of their results. Then adjust any formatting, such as font size and orientation, and go back to the ‘File’ tab to print your sheet. The formulas will remain visible while printing.

5. Why do some cells still show the results of the formula even after I clicked on ‘Show Formulas’?

This can happen when certain cells have been individually formatted to show their values. To fix this, select the cell(s) you want to display the formulas for, right-click on the selection, choose ‘Format Cells,’ click on the ‘Number’ tab, and then select the ‘General’ format. This should allow the cells to display their formulas instead of their results.