How to Show Formulas in Google Sheets [Complete Guide]

In this article we will learn about how to show Formulas in Google Sheets using two different methods. As the most widely used spreadsheet and data management tool in the world. Google sheets provide greater flexibility and compatibility to the user for inserting a pivot table, sorting tabulated data, analyzing or monitoring data, and calculating large data with the help of formulas and functions as google sheets have all formulas in it.

While making our spreadsheet, if there is a need for looking for formulas inserted in the data google sheet also provides the facility of showing formulas instead of values. Because when you put the formula in a cell and hit the ENTER button you will promptly find your value. Although you may also watch the formula in the formula bar applied on it, there are also some individual options to show the formula in Google Sheets.

Importance of Showing Formulas in Google Sheets

As we know google sheets is an online based program providing features of the spreadsheet which may be used by multiple users as well. It is often large projects or big organizations that have large no. of data operated by many users causing a lack of understanding of formulas and functions which had been applied to data. This is why we need to show formulas in google sheets so that we may know easily which formula has been applied to the specific cell or an entire sheet.

In google Sheets, we usually have data that contains formulas and simple text both. But physically we may not say which cells are applied by formulas and which are simple text in the data. By showing formulas we may find the cells that contain formulas and can monitor them separately. We may also check for errors in formulas by showing formulas in google sheets. So this article is going to be very helpful to you.

How to Show Formulas in Google Sheets?

One of the best things in google sheets is that you will find that most of the options are toggle and present in our menu bar which is easily accessible. Similarly, we have the option for showing formulas in our menu tab so that we may access them with a few clicks in a jiffy.

Although there are two different conditions, you want to see the formulas of the entire sheet or a specific cell so that there are different methods for both conditions. We will study both of them step by step. Let’s start without wasting our time.

How to Show Formulas in Entire Sheets

If you want to see all formulas applied in the entire sheet then follow these steps.

Step 1

Let’s have some sample data which must contain formulas in it.

Step 2

In the menu bar, you will find the “View” button open by clicking on it.

Step 3

As you will click on the “View” button a drop-down menu will open where you will find an option “Showing” now expand it by clicking the small arrow button on the right of the option.

Step 4

Again, a drop-down menu will appear at the right where you may see some checked and unchecked options. Where you will find the “Formulas” option unchecked. Checked it by clicking on it to show all formulas in the entire sheet.

Step 5

As you checked the “Formulas” option, all formulas applied in the entire sheet will be shown as you may see below.

As we discussed, this is a toggle option that can be reversed in its previous state to show value and hide formulas by doing the above steps again as,

Step 6

Again, go to the “View” option from the menu bar and open “Show”, you will find the “Formulas” button checked, Click on it.

Step 7

As you click on it, the “Formulas” button will be unchecked.

Step 8

All formulas are hidden now in the entire sheet as you may see.

There is also a shortcut key to show formulas in Google sheets “Ctrl + ` “. You may show all the formulas in google sheets by just holding the control button on your keyboard and pressing the backtick button found in the tilde key above the Tab button on the left corner of the keyboard. This shortcut key will show the all formulas in the entire sheet.

Sometimes the user faces no result while applying the shortcut keys in Google sheets; this is because by default disabled of Keyboard shortcuts. If you are finding a problem while applying any shortcut key in Google sheets, you have to enable Keyboard shortcuts first from the menu bar.

Let’s see how to enable Keyboard shortcuts in Google sheets.

Step 1

In the menu bar, you go to the “help” menu.

Step 2

A drop-down menu will appear where you will see the “Keyboard shortcuts” option open it by clicking on it.

Step 3

A pop-up window will open in front of you where you will see the keyboard shortcuts button disabled at the bottom.

Step 4

Just enable it with one click on it, and close the keyboard shortcuts pop-up window.

You have enabled all keyboard shortcuts in Google sheets now you will not face any problem while applying any shortcut key.

This method was to show formulas in the entire sheet. Wherever you have applied formulas in the whole sheet, it will show all the formulas.

How to Show Formulas in Specific Cells

If you don’t want to show all the formulas in the entire sheet instead you want to show formulas only in a specific cell instead of value then this method will be applicable. Let’s do it practically, how is this method applied?

Step 1

As usual first we will have some samples of data that contain formulas.

Step 2

Select the specific cell that you want to show the formula.

NOTE: Keep in mind that you select the cell that must contain the formula.

Step 3

As you select the cell that contains the formula, you will automatically see the formula in the formula bar.

Step 4

Now simply move the cursor and click on the formula bar to edit the formula.

Step 5

As we know that every syntax or formula starts with the (=) sign, so to show just put the apostrophe (‘) sign before the equal sign and hit the Enter button.

Step 6

You are done now you may see the applied formula has been shown in the selected cell.

Step 7

Similarly, you may show the formulas in one or more cells by the same above method.

Step 8

Now if you want to show the resulting value again, simply go through the cells that are shown in the formulas.

Step 9

After selecting the cell go to the formula bar to edit, where you put the apostrophe sign and remove it from the syntax.

Step 10

When you remove the apostrophe sign from the syntax, you will see it will automatically become the formula instead of formula text like,

Step 11

Now just press the Enter button your resulting value will be in front of you.

As you may see formula has been hidden and the resulting value has come.

Important Notes

Frequently Asked Questions

Is it Possible to Hide #DIV/0 Errors When Showing Formulas in Google Sheets?

Yes, it is possible to hide #DIV/0 errors when showing formulas in Google Sheets. By using the IFERROR function and conditional formatting, you can replace these errors with a blank cell or custom message. This helps to maintain a clean and error-free display of your formulas. Hence, you can effectively hide #DIV/0 using google sheets.

Why do we need to show formulas in Google sheets?

The main purpose of this feature is to find those cells that contain formulas. Which may take time to find one by one checking the cells. This is why Google sheets provide the “Show formulas” option. There may be more criteria to use this feature depending upon your requirements.

Can we show formulas in Google sheets in a specific cell or more than one cell at a time?

“Show formulas” shows the all formulas in the entire sheet, Yes! you may also show formulas in one or more than one specific cell that you want by editing the formula ( ‘ ) sign before the formula.

Can You Give Me a Step-by-Step Guide on Creating Formulas in Google Sheets?

When it comes to creating formulas in google sheets, having a step-by-step guide can be quite helpful. Start by opening a Google Sheet and selecting a cell where you want to input your formula. Then, type an equal sign followed by the desired formula using appropriate functions and cell references. Lastly, hit enter to see the calculated result.

Can we hide the formulas again after they show formulas in Google sheets?

Yes! This is the toggle option, once you show the formulas and now you want to hide the formula to see the resulting values you can reverse the task.

Conclusion

Google sheets are full of hundreds of features and functions for making spreadsheet documents. But still, Google sheets is providing its users with more efficient methods to do work more smoothly and perfectly. Similarly, show formulas in Google sheets is an efficient feature, you do not need to find formulas containing cells one by one by checking manually. You may look at all the formulas in the entire sheet in just a few clicks which makes the flow of work pretty smooth and effective.

I hope the above topic is found very helpful and expedient. For more informative articles, stay connected with the Office Demy. Thank you.