How to Show Formula in Excel?
In excel, we have an option to show those formulas to study the relationship of the formula. There is also a keyboard shortcut to show formulas in excel, which we’ll explore further. Follow steps to find that option in excel.
Click on Formula Tab, then Formula Auditing and Show Formulas.
Once we click on those options, all the formulas in the excel sheet will be visible instead of the values of the formula.
If you again click on that option, excel will start to show the values instead of the formula itself.
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
Below is an example of that showing formulas.
- Step 1: Select the formula cells range.
- Step 2: Now go to the formula tab and click on the Show Formulas option.
- Step 3: Once you click on that option, excel starts to show the formula instead of the formula results.
Shortcut to Show Formula in Excel
shortcut to show formula in excel is Ctrl + `. The key (‘) is located on the keyboard just below the escape button
How to Enable Show Formula in Workbook Option?
Showing formulas is not a workbook level option. But we can show all the formulas at one by changing our default settings of the excel.
Follow the below steps to enable this option.
- Step 1: Go to the File option in excel.
- Step 2: Now click on Options.
- Step 3: Go to the Advanced option.
- Step 4: Scroll down and find Show formulas in cells instead of their calculated results.
This seems a lengthy process but very useful if you want to show the formula for multiple sheets. Making one sheet at a time is a lot of time-consuming processes. So we can rely on these settings options.
Print Formulas using Show Formula Option
We can print these formulas instead of the values by using this Formula Option.
- Step 1: Select the formula cells (if you need particular cells to be displayed then select those cells only) and click on this formula option. (We can use shortcut Ctrl + ` ).
This would show up all the selected cell formulas.
- Step 2: Now select the printable region data and press ALT + P + R + S. This would set up the print area.
- Step 3: Now press Ctrl + P. This will print all the formulas. Now you can go back and press Ctrl + ` to remove the visible formulas option and make the values visible.
Hide Formulas in Excel
Not only can we show the formula; rather, we can hide those formulas to be visible from the other users by protecting the sheet.
- Step 1: Select the formula cells.
Step 2: Right-click and select Format Cells.
- Step 3: Select the Protection tab and check the Hidden box.
- Step 4: Now click on the Review tab and select Protect Sheet.
- Step 5: Type the password and protect the sheet.
Things to Remember
- Since Ctrl + ` is a toggle option, we can show formula and values after every alternative click.
- Always study the formula and go back to formula values but never ever lose formulas.
- Often when we are using shortcuts, we tend to type Ctrl + `. So do not panic; try typing Ctrl +`. This will give back your values.
- If the format of the cell is TEXT, then it may show the formula in excel rather than the values of the formula. Apply general formatting to show the values of the formula.
This has been a guide to Show Formula in excel. Here we discuss how to Show Formula in Excel and its shortcut along with practical examples. You may learn more about excel from the following articles –