How to Display Cell Formulas in Excel (6 Methods)

Get FREE Advanced Excel Exercises with Solutions!

While working on a spreadsheet in Excel with tons of data and formulas, you may feel the necessity to display the cell formulas at once for several reasons. Excel can also show a single formula present in a specific cell. From time to time, you may want to see reference cells, edit them, understand how they are working, or even how different cells are related to each other. Whatever the purposes you possess, go through the whole article carefully to learn how to display cell formulas in Excel.

overview image of how to display cell formulas in Excel using Show Formulas command

The above image displays the cell formulas in the Excel worksheet using the Show Formulas command in the Formulas tab.


How to Display Cell Formulas in Excel: 6 Methods

dataset containing sales amounts and total sales

The above sample dataset contains sales amounts of different products in January, February, and March. Then there is the total sales column where we use the SUM formula to combine these 3 months. In this article, we’ll show 6 methods to display those formulas.


1. Use Show Formulas Command to Display All Cell Formulas in Excel

displaying cell formulas through Show Formulas Command

To see formulas instead of results, go to the Formulas tab > click Formula Auditing drop-down > select Show Formulas. Hence, you’ll see the formulas in the Total Sales column.


2. Show Formulas in All Cells Through Keyboard Shortcut

displaying cell formulas through keyboard shortcut

In the current worksheet, press the Ctrl + ` keys together to display the cell formulas in Excel. The formulas in the Total Sales column will appear as it’s shown in the above image.


3. Insert FORMULATEXT Function to Display Cell Formulas in Excel

displaying cell formulas using FORMULATEXT Function

The FORMULATEXT function returns the formula present in a cell. Here in cell G5, we insert the formula:

=FORMULATEXT(F5)

Then, we apply AutoFill to get other formulas as well.

NOTE: The FORMULATEXT function returns a #N/A error if the reference cell is in another workbook that is not opened in the background.

4. Check Advanced Excel Options to Show Cell Formulas

Another way to display cell formulas is through a settings change in the Excel Options. In this way, you will only see the formulas instead of the results.

click options in File tab

First, go to the File tab > click Options.

check necessary settings in Excel options dialog box

In the Excel Options dialog box, go to the Advanced tab and check for Show formulas in cells instead of their calculated results. Press OK.

Cell Formulas on display as an outcome of the settings change in Excel options

Thus you’ll see the formulas in the Total Sales column.


5. Temporarily Display Excel Formula in a Cell

You can temporarily view the formula present in a cell instead of displaying all the formulas in the worksheet. This section will go over 3 ways to perform the task.


5.1 Press F2 Function Key to Show Cell Formula

Pressing F2 Function Key Shows Cell Formula

To get a quick view of the formula, select the F5 cell and press the F2 key. You can do that with other cells too. However, the cell will display the sum result again after you click elsewhere.


5.2 View Cell Formula in Formula Bar

displaying Formula in Formula Bar

You can also see the formula in the Excel Formula Bar. Click on the desired cell and the formula bar shows the formula. Here the F5 cell has the formula:

=SUM(C5:E5)

5.3 Double-Click on Cell to Display Formula

Double-Clicking on Cell Displays Formula

Another way to display formulas temporarily is by double-clicking on the cell. The above image shows the formula in the F5 cell by double-clicking on it.


6. Modify Formula to Display Cell Formulas in Excel

Now we will show you some modifications of the formulas to display alongside the results.

select desired range and press ctrl and H keys

Copy the F5:F14 range through the Ctrl + C keys. Then paste them into G5:G14 with Ctrl + V keys. Now, select the G5:G14 range and press Ctrl and H keys together.

find target and replace it with desired changes through Find and Replace

The Find and Replace dialog box pops out. Insert

Find what >> =

Replace with >>  = (there’s a space before =)

Press Replace All.

find target and replace it with desired value

Another way is to insert:

Find what >> =

Replace with >> ‘=

Press Replace All.

displaying cell formulas after modifying the original formula

Therefore, you’ll see the formulas beside the Total Sales column.


How to Hide Cell Formulas in Excel

click Format cells after selecting range

To hide cell formulas present in F5:F14 in Excel, select the range. Go to the Home tab > click Cells drop-down > click Format drop-down > choose Format Cells.

check for hidden in protection tab

The Format Cells dialog box pops out. Check for Hidden and press OK.

protect sheet

Now go to the Review tab > select Protect drop-down > click Protect Sheet.

Check for Protect worksheet and contents of locked cells in protect sheet box

The Protect Sheet dialog box appears. Check for Protect worksheet and contents of locked cells. Press OK. It’s your wish whether or not to give the password to unprotect the sheet.

formula bar showing blanks instead of formulas

Click on any cell that has the formula in it. The formula bar will show blanks. In this way, we can hide the cell formulas in Excel.


Why Is Excel Showing Formula Instead of Result

Sometimes you may see that Excel shows formulas instead of results. Some of the reasons are:

1. If the Show Formulas mode is enabled, you’ll see the formulas. To disable it, go to the Formulas tab > click the Formula Auditing drop-down > select Show Formulas. Or you can press the Ctrl and ` keys together. It’ll give back the calculated values.

2. If you accidentally put an apostrophe (‘) before the = sign in the formula, it’ll only display the formulas.

total Sales column showing formulas due to apostrophe in the front

In the above image, the cells in the Total Sales column show the formulas. But in the formula bar, you can actually see an apostrophe that hinders the cell from displaying the result.

3. Similarly if you put a space before the sign, you’ll see the formulas only.

Total Sales column showing formulas due to space in the front

The above image shows the space in the formula bar which keeps it from displaying the calculated values.

4. If you enter a formula in the cell that is set to the Text format, the cell won’t show the calculated value. Because Excel assumes the formula to be a text string. So, change the format to General to get the formula results.

Total Sales column showing formulas due to Text format

The above image shows the Total Sales column in formulas as it’s set in the Text format.


Things to Remember

  1. All the above methods work for the current Excel worksheet only. That means it’ll display the cell formulas in the active worksheet. You have to repeat the method for other worksheets too if you want to display cell formulas there.
  2. Formula Bar is the dedicated place to show cell formulas in Excel. If the formula bar shows blank after clicking a cell that contains the formula, it’s likely that the sheet is protected. Unprotect the worksheet first to display the formulas.

Download Practice Workbook

You are recommended to download the practice workbook and practice along with it.


Conclusion

In this article, we’ve demonstrated how to display cell formulas in Excel with 6 distinct methods. Go with the Show Formulas mode to display all the cell formulas present in the sheet.

If you want to see the formulas alongside the results, you can use the FORMULATEXT function. You are recommended to practice all of them along with the attached practice workbook that has been prepared exclusively for your practice purpose.

Don’t forget to leave any questions in the comment section, you all try to respond to your problems ASAP.


Related Articles


<< Go Back To Show Excel Formulas | Excel Formulas | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo