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.
Program Successfully in Excel! This guide will provide you with all the information you need to automate any task in Excel and save time and effort. Learn how to extend Excel's functionality with VBA to create solutions not possible with the standard features. Includes latest information for Excel 2024 and Microsoft 365. Check out Mastering Excel VBA Programming today!
Sometimes you need to look backward, through the information above your formula, to find the data you need. This can be ...
Discover MoreIf you want to figure out the sum of all cells that contain formulas, there are a couple of ways you can go about it. ...
Discover MoreSometimes it can be helpful to show both a numeric value and a percentage in the same cell. This can be done through ...
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