Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and 2021. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Shortcut for Viewing Formulas.
Written by Allen Wyatt (last updated April 4, 2025)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and 2021
You probably already know how to display the formulas in cells rather than the results of those formulas, right? If you're like most people, you follow these steps:
Figure 1. The Display Options for a worksheet.
A much faster way to get the same result is to simply press Ctrl+`. (That's hold down the Ctrl key while you press the accent grave, which is the backwards apostrophe just to the left of the 1 key and above the Tab key.) The shortcut is a toggle, which means that you can press it repeatedly to switch between the display of formulas and results.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (6198) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and 2021. You can find a version of this tip for the older menu interface of Excel here: Shortcut for Viewing Formulas.
Best-Selling VBA Tutorial for Beginners Take your Excel knowledge to the next level. With a little background in VBA programming, you can go well beyond basic spreadsheets and functions. Use macros to reduce errors, save time, and integrate with other Microsoft applications. Fully updated for the latest version of Office 365. Check out Microsoft 365 Excel VBA Programming For Dummies today!
If you need to generate a random sequence of characters, of a fixed length, then you'll appreciate the discussion in this ...
Discover MoreIf you want to find out the Zodiac sign for a birthdate, there are a number of ways you can do it. This tip provides ...
Discover MoreDo you need to determine the top three values in a range of columns? The techniques discussed in this tip will come in ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2022-12-21 11:29:45
J. Woolley
The following function mentioned in Peter's comment below will also return an array of formulas when Target is a range of cells on any worksheet:
=FORMULATEXT(Target)
In this case the returned array will be the same size as Target; cells that are blank or contain constants will be marked #N/A.
2022-12-20 14:40:57
J. Woolley
My Excel Toolbox includes the following dynamic array function to return all non-blank formulas with optional values from a Target range of cells on any worksheet:
=ListFormulas(Target,[WithValues],[SkipConstants],[SkipHeader])
Expect 2 columns (Cell, Formula) or 3 columns (Cell, Formula, Value) and N rows (plus header). When SkipConstants is TRUE only formulas that begin with = will be returned.
My Excel Toolbox's SpillArray function simulates a dynamic array in older versions of Excel:
=SpillArray(ListFormulas(...))
See UseSpillArray.pdf at https://sites.google.com/view/MyExcelToolbox
2021-05-11 05:25:33
Peter
I like the options for invoking formulas quickly, but I am not a fan of the all or nothing mode of operation.
I have recently discovered =FORMULATEXT(), which gives me the ability to review formulae and calculated results at the same time.
2021-05-10 07:52:36
Russell Stainer
Another way to do it, is to go to the 'Formulas' tab in the ribbon, go to the 'Formula Auditing' group, and toggle 'Show Formulas'. If you right-click it, you can also add it to the Quick Access Toolbar.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and 2021. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here:
Comments