Excel formulas showing as formulas

Show Formulas in Excel Instead of the Values

As soon as you type a formula in Excel and hit enter, it would return the calculated result, and the formula would disappear.

That’s how it’s supposed to work.

But what if you want to show formulas in the cells and not the calculated values.

In this Excel tutorial, I will cover the following topics:

• How to Show Formulas in Excel instead of the values.
• How to Print the formulas in Excel.
• How to Show Formulas in Excel in Selected Cells Only.
• What to Do when Excel Shows Formulas Instead of the Calculated Values.

Show Formulas in Excel Instead of the Values

Here are the steps to show formulas in Excel instead of the value:

• Click on the ‘Formulas’ Tab in the ribbon.
• In the Formula Auditing group, click on the Show Formulas option.

As soon as you click on Show Formulas, it will make the formulas in the worksheet visible. It’s a toggle button, so you can click on it again to make the formulas be replaced by its calculated result.

It’s a toggle button, so you can click on it again to make the formulas be replaced by its calculated result.

As shown below, column I has the formulas. As soon as ‘Show Formulas’ button is clicked, the cells show the formulas instead of the value.

You can also use the Excel keyboard shortcut – Control + ` (you will find this key in the top-left part of the keyboard, under the Escape key).

Note: This is a sheet level technique. This means that when you use the Show Formulas option or the shortcut, it will only show the formulas in the active sheet. All the other worksheets will be unaffected. To show formulas in other worksheets, you will have to go to that sheet and use this shortcut (or ribbon button).

In some cases, you may have a lot of worksheets and you want to show the formulas in all the worksheets in the workbook.

Here are the steps that will show the formulas in all the worksheets in Excel:

• Go to the ‘File’ tab. If you’re using Excel 2007, go to Office button.
• Click on ‘Options’.
• In the left pane, select Advanced.
• On the right, scroll down to the ‘Display options for this worksheet’ section.
• From the drop down, select the worksheet in which you want to show the formulas instead of values.
• Check the option – ‘Show formulas in cells instead of their calculated results’.
• Click OK.

As mentioned, while this may seem to have more steps as compared to a shortcut or the ‘Show Formulas’ button in the ribbon, it’s useful when you have multiple worksheets and you want to show the formulas in all these worksheets.

How to Print Formulas in Excel

Here are the steps to print formulas in Excel:

• Go to Formula tab.
• Click on the Show Formula option.
• Go to File –> Print.

The above steps would ensure that it prints the formulas and not the values.

Show Formulas in Excel Instead of the Value in Selected Cells Only

The above methods covered so far would show all the formulas in a worksheet.

However, you may want to show the formulas in some selected cells only.

For example, as an Excel trainer, I often create templates where I show the formula in one cell and its result in another cell (as shown below).

Here are the steps to show formulas in Excel in selected cells only:

• Select the cell where you want to show the formula instead of the value.
• Go to Home –> Find & Select –> Replace (keyboard shortcut – Control + H).
• In the Find and Replace dialog box, within the replace tab, enter = in the ‘Find what’ field and ‘= in the ‘Replace with’ field.
• Click on Replace All.

This will show formulas in all the selected cell while the remaining cells would remain unchanged.

Note: Entering a space before the formula makes it a text string and the space character is visible before the equal to sign. On the other hand, using an apostrophe before the equal to sign make the formula a text string, however, the apostrophe isn’t visible in the cell (it shows up only in the formula bar and in the edit mode).

How to Handle Excel Showing Formulas Instead of Calculated Values

Sometimes, you may find that the cells in Excel are showing the formula instead of the value.

There are a couple of reasons why this may happen:

• The ‘Show Formulas’ mode is enabled or you may have accidently hit the Control + ` shortcut. To disable it, simply use the shortcut again or click on the ‘Show Formula’ option in the Formulas tab.
• It could be due to the presence of a space character or apostrophe before the equal to sign in the formula. The presence of these before the equal to sign makes the cell format as text and the formula shows up instead of the value. To handle this, simply remove these. You can use find and replace to do this.
• If a cell has ‘Text’ formatting applied to it and you enter the formula and hit enter, it will continue to show the formula instead of the calculated value. To fix this issue, go to the Home tab and with the Number group, change the formatting to General.

You May Also Like the Following Excel Tutorials:

Источник

Excel shows formula but not result

Summary

Have you entered a formula, but Excel is not showing a result? This can be very confusing, and you might think you’ve somehow broken your spreadsheet. However, it’s likely a simple problem. With a little troubleshooting, you can get things working again.

Every once in a while, you might find Excel behaving in a bizarre or unexpected way. One example is when you accidentally trigger the scroll lock feature. Another example is when one or more formulas suddenly stops working. Instead of a result, you see only a formula, as in the screen below:

The VLOOKUP formula is correct, why no result?

This can be very confusing, and you might think you’ve somehow broken your spreadsheet. However, it’s likely a simple problem. With a little troubleshooting, you can get things working again. There are two main reasons you might see a formula instead of a result:

1. You accidentally enabled Show Formulas
2. Excel thinks your formula is text

I’ll walk through each case with some examples.

Show Formulas is enabled

Excel has a feature called Show Formulas that toggles the display of formula results and actual formulas. Show Formulas is meant to give you a quick way to see all formulas in a worksheet. However, if you accidentally trigger this mode, it can be quite disorienting. With Show Formulas enabled, columns are expanded, and every formula in a worksheet is displayed with no results anywhere in sight, as shown below.

Show Formulas disabled (normal mode)

Show Formulas enabled

To check if Show Formulas is turned on, visit the Formula tab in the ribbon and check the Show Formulas button:

Show Formulas enabled — just click to disable

The reason Show Formulas can be accidentally enabled is because it has a keyboard shortcut (Control +`) that a user might accidentally type. Try typing Control + ` in a worksheet to see how it works. You should see formulas toggled on and off each time you use the shortcut.

Show Formulas toggles the display of every formula in a worksheet. If you are having trouble with a single formula, the problem isn’t Show Formulas. Instead, Excel probably thinks the formula is text. Read on for more information.

Excel thinks your formula is text

If Excel thinks a formula is just text, and not an actual formula, it will simply display the text without trying to evaluate it as a formula. There are several situations that might cause this behavior.

No equal sign

First, you may have forgotten the equal sign. All formulas in Excel must begin with an equal sign (=). If you leave this out, Excel will simply treat the formula as text:

Broken formula example — no equal sign (=)

Space before equal sign

A subtle variation of this problem can occur if there is one or more spaces before the equal sign. A single space can be hard to spot, but it breaks the rule that all formulas must start with an equal sign, so it will break the formula as shown below:

Formula wrapped in quotes

Finally, make sure the formula is not wrapped in quotes. Sometimes, when people mention a formula online, they will use quotes, like this:

In Excel, quotes are used to signify text, so the formula will not be evaluated, as seen below:

Note: you are free to use quotes inside formulas. In this case, the formula above requires quotes around criteria.

In all of the examples above, just edit the formula so it begins with an equal sign and all should be well:

For reference, here is the working formula:

Cell format set to Text

Finally, every once in a while, you might see a formula that is well-formed in every way, but somehow does not display a result. If you run into a formula like this, check to see if the cell format is set to Text.

If so, set the format to General, or another suitable number format. You may need to enter cell edit mode (click into the formula bar, or use F2, then enter) to get Excel to recognize the format change. Excel should then evaluate as a formula.

Tip — Save formula in progress as text

Although a broken formula is never fun, you can sometimes use the «formula as text problem» to your advantage, as a way to save work in progress on a tricky formula. Normally, if you try to enter a formula in an unfinished state, Excel will throw an error, stopping you from entering the formula. However, if you add a single apostrophe before the equal sign Excel will treat the formula as text and let you enter without complaint. The single quote reminds you that the formula has been intentionally converted to text:

Later, you can then come back later to work on the formula again, starting where you left off. See #17 in this list for more info.

Источник

Excel Showing Formula Instead of Result (How to FIX!)

Sometimes, it may happen that your Excel workbook shows formulas instead of the result.

Something as shown below:

But there is no need to panic… All’s well!

This is something that’s quite common and happens to a lot of users. There could be multiple reasons behind Excel showing formula instead of the result, and in this tutorial, I will show you the potential fixes that will make this issue go away.

So let’s get started!

Table of Contents

Show Formulas May Be Enabled (Top Reason for Formulas Showing as Text)

In some cases, people actually want to see all formulas as text in the worksheet.

There could be multiple reasons for this, the most common one is when you’re auditing the worksheet and need to know where and what formulas are being used.

This problem has an easy fix – you just need to disable ‘Show Formulas’ option (which might be enabled and is causing this issue)

Below are the steps to do this:

1. Click the Formulas tab
2. In the Formula Auditing group, click on Show Formulas.

The keyboard shortcut to enable/disable showing formulas as text options is Control + `

In most cases, this should fix the problem and your formulas should show the result and not show up as text itself.

And what if this doesn’t solve the problem and you’re still seeing formulas as text?

Keep reading and try the other fixes!

Cells are Formatted as Text

Another issue that you may face is that when you insert a formula, it shows the formulas and not the value.

One possible culprit could be the cell being formatted as Text.

And it has a really easy fix too:

1. Click the Home tab
2. In the Number group, click on the Formatting drop-down and select General (you might see Text being the option that is already selected).

Note that when you apply text formatting to a cell, it will only show formulas as a text for the formulas you enter after applying the formatting. Any formulas results before the formatting is applied will not change.

Apostrophe Before the Formula

Another issue (not as common) you may face is having an apostrophe before the formula.

When you add an apostrophe at the beginning in any cell, Excel considers the cell content as a text string. So if you do it with numbers, you will notice that they align to the left and are treated as text now.

This also sometimes happens when you get a data dump from a database, where an apostrophe is added automatically at the beginning.

To fix this, you can go to the cell and manually remove the apostrophe.

So these are some of the reasons that may make your formulas show up as text and you can easily fix them and show the results instead.

Hope you found this tutorial useful!

You may also like the following Excel tutorials:

Источник