Technology

4 Easy Steps to Display Formulas in Excel 2023 Accurately

Knowing how to display formulas in Excel makes learning and improving your skills easier, while also allowing you to quickly check if your data file is functioning correctly.

Administrator

29 November, 2023

215

For many years, Microsoft Excel has been an essential part of everyday office life. Not only does it help users organize data efficiently, but this spreadsheet software is also known for its numerous calculation formulas and its ability to handle large amounts of data in a simple way. However, not everyone knows how to display formulas in Excel. So how can you perform this trick? Let’s find out in the following article.

Why display formulas in Excel?

Displaying formulas in Excel is an important trick that you need to master when working with spreadsheets. This operation not only helps you check the accuracy of calculations but also allows users to better understand how data is processed in an Excel file. Here are some reasons why displaying formulas in Excel is truly significant:

Check the accuracy of formulas: When formulas are displayed, you can check if the calculations have been entered correctly. This helps you detect and fix errors quickly.
Understand how formulas work: This trick helps you understand how functions and formulas work, so you can optimize your work and improve your Excel skills.
Share information: When you share a spreadsheet with others, displaying formulas helps them understand how you have calculated the data inside.
Customize and adjust: Knowing how to display formulas in Excel allows you to customize the formulas in a data file according to your preferences.

How to display formulas in Excel

There are several ways to display formulas in Excel. Therefore, you should choose the method that suits your needs to check and manage formulas in your spreadsheet.

Method 1: Use keyboard shortcuts

The quickest and simplest way to display formulas in Excel is to use keyboard shortcuts. To perform this trick, press the Ctrl + ~ (tilde) key combination on your keyboard. This is the shortcut key to toggle between showing values and showing formulas in an Excel spreadsheet.

After that, you will see the formulas appear in the corresponding cells. To switch back to the original values, press the Ctrl + ~ key combination again.

Method 2: Use the Formulas tab

In addition to using keyboard shortcuts, you can also use the Formulas tab to display formulas in Excel. This tab manages formulas and is located on the toolbar of the spreadsheet.

Step 1: Open the Excel document that you want to display the formulas.

Step 2: Select the Formulas tab on the toolbar.

Step 3: In the Formula Auditing group, you can find the Show Formulas button. Click on this button.

After that, you will see the formulas appear in the cells that contain them. To switch back to the calculated values, click on the Show Formulas button again.

Method 3: Use the formula bar

In this method, you should note that you should not click on any other data cell. This operation can change the formula in the current cell.

Step 1: Open the Excel document.

Step 2: In the spreadsheet, select the cell that you want to display the formula.

Step 3: Double-click on the formula (or click on the formula and press F2). Then, the corresponding window will appear and display the formula applied to that data cell. You can drag this window to view the entire formula if it is too long.

Note: This method is only suitable for viewing and editing the formula for one data cell.

Method 4: Use the Evaluate Formula feature

Excel has a very useful feature called Evaluate Formula. In addition to evaluating data in spreadsheets, this feature allows you to view the formulas that have been entered into the spreadsheet. Therefore, it can be seen as a very useful way to display formulas in Excel that you can take advantage of.

Step 1: Open the Excel document.

Step 2: Select the cell that contains the formula you want to check.

Step 3: In the Formulas tab on the toolbar, move the mouse to the Formula Auditing group and click on Evaluate Formula.

Then, a new window will appear on the screen, allowing you to examine each part of the formula and view its corresponding value.

Some frequently asked questions and solutions

Here are some common questions about displaying formulas in Excel and how to solve them:

Question 1: How do I switch between showing values and showing formulas in Excel?

Solution: You can use the Ctrl + ~ (tilde) keyboard shortcut to toggle between showing values and showing formulas.

Question 2: How do I check each part of a formula in Excel?

Solution: Use the Evaluate Formula feature in Excel. You can see each step of the formula and its corresponding value.

Question 3: How do I find errors in my formula?

Solution: Use the Formula Auditing feature in Excel. Additionally, you can use the Error Checking feature to see errors in the formula and fix them.

Question 4: How do I understand how a formula works?

Solution: When you double-click on a cell with a formula or press the F2 button, Excel will display the formula bar for you to view.

Question 5: How do I protect my formulas from being modified by others?

Solution: You can protect formulas with a password or use the worksheet protection feature. This will prevent others from viewing or editing the formulas you have created.

Conclusion

As you can see, there are many ways to display formulas in Excel. Therefore, FPT Shop hopes that you will choose the most suitable method. We wish you to work efficiently with formulas in Excel and solve common problems easily.