By Sophia | Last Updated
Have you ever worked on a spreadsheet that is densely packed with formulas? In this case, you may want to see the formulas in the cells in order to figure out how each formula works and how the results are derived. Showing formulas in Excel instead of their results can help you keep track of the data used in each calculation and quickly check your formulas for errors. Therefore, in this article, I will guide you step by step on how to show formulas in Excel.
Step 1: Press the Ctrl+~ key (the key to the left of the number 1) to switch between displaying formulas and results.
Step 2: Alternatively, click the "Formulas > Show Formulas" on the Excel ribbon. This method can only display the formula in the source cell.
Step 1: In Excel 2010, 2013, and 2016, click File > Options on the Ribbon. In Excel 2007, click the Office Button > Excel Options.
Step 2: Click Advanced Options in the left pane, scroll down in the right pane and find the Display options for this worksheet section, check Show formulas in cells instead of their calculated results, click OK to complete the settings.
This may seem like a rather complicated method, but you will find it useful when you want to display formulas in several Excel worksheets in the currently open workbook. In this case, you simply select the worksheet name from the drop-down list and then for each worksheet check the "Show formulas in cells... " option.
Step 1: Open the excel worksheet where you need to show the formulas and press Alt+F11 at the same time to open the VBA Editor. Click Insert > Module and then copy the following two pieces of code. When finished, close your VBA Editor.
Function ftext(f As Range) ftext = f.Formula End Function Function test(vTarget As Range) test = Evaluate(vTarget.Value) End Function
Step 2: Back to the Excel worksheet, cell E2 enter the formula: =ftext(D2), drop down to copy, you can display the formula in column D in column E. If you need to display the value of the formula, again, you can use the formula: =test(D2).
The principle of this method of showing formulas is to use the cell set to text, and then paste the option to select "Match Destination Formatting" to achieve.
Step 1: Select the cell area with the formula, such as D2: D4, press Ctrl+~ to display the formula, and then press Ctrl+C to copy it.
Step 2: Select cells E2:E4 and press Ctrl+1 to open the Format Cells dialog box and set the cell format to Text.
Step 3: Save E2: E4 cells selected state, click "Home > Clipboard", the "Clipboard" will be displayed, and then click to "Paste All" the item, the bottom right will appear a Paste Options, we select the second "Match Destination Formatting".
Step 4: Press Ctrl+~ again to restore the formula displayed in column D.
The above is all about how to show formulas in Excel. By the way, if you have important data in your Excel sheet and don't want others to see it, I recommend you to encrypt your Excel. iSunshare SafeFile Genius is a great choice. This software can encrypt many types of files, including Word, Excel, PPT, etc. You can download it to try!
Related Articles:
