Formulas power Excel. But, when you’re starting off, it takes time to learn how formulas work. A great way to study is to show formulas in Excel.
Whether you want to see formulas to audit your own spreadsheet or learn from others, this is a must-know skill. Let’s learn how to show formulas in Excel.
How to View Formulas in Excel
First, let’s learn how to view a single formula in Excel. Click on a cell, then review the formula bar. It’s directly above the spreadsheet area at the bottom of Excel’s ribbon.
Excel always displays the formula for the selected cell in this area. Click inside the formula bar to see the cells referenced. Notice the color-coding to help you see the referenced cells. For example, Tax Rate is red in the formula and is surrounded with a red border.
How to Show All Formulas in Excel
Instead of clicking cell-by-cell, it helps to show all formulas at once. To see every formula, go to the Formulas section on the ribbon. Then, click on Show Formulas. This toggles the option to see formulas off and on.
With a helpful keyboard shortcut, you can change your view quickly:
Keyboard shortcut: Ctrl + `
(That small character is called the backtick, and you’ll find it in the upper left corner of most keyboards.)
How to Type Formulas in Excel (Without Calculating)
Let’s say that you want to type a formula without calculating it. Maybe you’re using a spreadsheet to teach someone Excel formulas and need to show how a formula works.
So, how do you type a formula without calculating that formula? It’s simple: just add an apostrophe (‘) before the equals sign. That forces Excel to look at the cell as simple text and won’t calculate the formula that follows it.
Check out the example below. The =PI() formula calculates pi. But when I type it with an apostrophe before it, you’ll see the formula written (but not calculated!) in the spreadsheet.
»