How to View Cell Types in Excel (6 Methods) (2023)

When working in an Excel spreadsheet with a lot of data and formulas, you may feel the need to view the formulas in the cells at once for various reasons. Excel can also display a formula that exists in a specific cell. From time to time, you may want to see reference cells, edit them, understand how they work and even how they differ.cellsthey are interrelated. Whatever your goals are, read the entire article carefully to learn how to view cell types in Excel.

The image above shows cell types in an Excel spreadsheet usingshow typescommand insideformulaspato.

Download the workbook

It is recommended that you download the workbook and practice it.

How to view cell types in Excel: 6 methods

The example data set above contains sales data for various products in January, February, and March. Then there is the total sales column that we useSUMMARY typecombine these 3 months. In this article, we will show you 6 methods to display these types.

1. Use the Show Formulas command to display all cell types in Excel

To see formulas instead of results, go toformulastab > clicktype controldrop-down menu > selectshow types. Therefore, you will see the formulas in the Total Sales column.

2. Show formulas in all cells with keyboard shortcuts

On the current sheet, tapcontrol key+`keys together to display cell types in excel. The formulas in the Total Sales column will appear as shown in the image above.

Read more: Multiple Excel Cells Selected in One Click (4 Causes + Solutions)

3. Enter the FORMULATEXT function to display cell formulas in Excel

FORM TEXT functionreturns the type present in the cell. here in the cellG5, we enter the formula:

Then we applyautomatic fillingto get other formulas.

Read more: Select All Cells with Data in Excel (5 Easy Methods)

4. Select Excel Advanced Options to display cell types

Another way to view cell types is to change the settings in Excel Options. That way, you'll only see formulas instead of results.

First go toFiletab > clickChoices.

wexcel optionsgo to the dialog boxAdvancedcard and checkDisplay formulas in cells instead of their calculated results. TypeOkay.

This way, you will see the formulas in the Total Sales column.

similar readings

• How to Select Cells in Excel Using the Keyboard (9 Ways)
• If one cell equals another, return another cell in Excel
• How to Select a Range of Cells in Excel (9 Methods)
• Select Non-Contiguous Cells in Excel (5 Easy Ways)
• How to Select Multiple Cells in Excel (7 Useful Methods)

5. Temporarily display the Excel formula in the cell

You can temporarily display the formula that is in the cell instead of displaying all the formulas in the worksheet. In this section, we will discuss 3 ways to complete the task.

5.1 Press the F2 function key to display the cell type

To get a quick preview of the formula, selectF5cell and pressF2key. You can also do this with other cells. However, the cell will show the sum result again when you click elsewhere.

5.2 Display the cell type in the type bar

You can also view the formula in Excel's formula bar. Click on the desired cell and the formula bar will display the formula. HereF5the cell has the formula:

5.3 Double-click a cell to display the formula

Another way to temporarily display formulas is to double-click a cell. The image above shows type wF5cell by double-clicking on it.

6. Modify the formula to display cell types in Excel

Now we'll show you some formula tweaks that will appear next to the results.

copyF5:F14coverage bycontrol key+doKeys. Then paste themG5: G14zcontrol key+VKeys. now chooseG5: G14reach and presscontrol keyIHkeys together.

Hefind and replacedialog box appears. I put

find this>>=

exchange>>=(there is a space before =)

Typereplace all.

Another way is to enter:

find this>>=

exchange>>"=

Typereplace all.

Therefore, you will see the formulas outside the Total Sales column.

How to hide cell types in Excel

To hide cell types present inF5:F14in Excel, select a range. I willdomtab > clickcellsdrop-down menu > clickFormdrop-down menu > selectcell formatting.

Hecell formattingdialog box appears. VerifyHiddenand pressOkay.

now go toReviewtab > selectProtectdrop-down menu > clickprotective sheet.

Heprotective sheetdialog box will appear. VerifyProtect worksheet and content from locked cells. TypeOkay. It is up to you to provide a password to unprotect the sheet.

Click any cell that contains a formula. The formula bar will display blank spaces. In this way, we can hide cell types in Excel.

Why does Excel display a formula instead of a result?

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

1. Yesshow typesis enabled, you will see the types. To turn it off, go toformulastab > clicktype controldrop-down menu > selectshow types. Or you can tapcontrol keyI`keys together. It will return the calculated values.

2. If you accidentally enter an apostrophe («) Before=log in type, it will show only types.

In the image above, the cells in the Total Sales column represent formulas. But in the formula bar, you can see the apostrophe, which makes it difficult to display the result in the cell.

3. Likewise, if you put a space before the character, you will only see types.

The image above shows a gap in the formula bar preventing the calculated values ​​from being displayed.

4. If you enter a formula in the cell defined inTextformat, the cell will not display the calculated value. Because Excel assumes the formula is a text string. So change the format toGeneralto get the results of the formula.

The image above shows the Total Sales column in formulas defined in text format.

things to remember

1. All the above methods work only on the current Excel worksheet. That is, it will display the cell types in the active worksheet. You should also repeat this method for other sheets if you want to display cell types there.
2. The formula bar is a special place for displaying cell formulas in Excel. If the formula bar is empty when you click a cell that contains a formula, the worksheet is probably protected. First, unprotect the sheet to see the formulas.

Application

In this article, we show you how to display cell types in Excel using 6 different methods. Go to Show Types mode to see all cell types present in the worksheet.

If you want to see the formulas next to the results, you can use the methodTYPE TEXTmode. It is recommended that you practice all of them together with the attached Workbook, which has been prepared for practice purposes only.

Don't forget to leave any questions in the comment section, we all try to answer your problems as soon as possible.

related reading

• How to Select Multiple Cells in Excel Without a Mouse (9 Easy Methods)
• Data Cleaning Techniques: Filling Blank Cells in Excel (4 Ways)
• How to Scroll Cells Down in Excel Without Changing the Formula (4 Methods)
• [Fix]: Arrow keys do not move cells in Excel (2 methods)
• How to Move Cells Down in Excel (7 Methods)