How To Show Formulas In Excel: Simple & Easy Steps

Have you ever looked at a spreadsheet someone gave you and felt lost? Are the numbers all over the place, but you don’t know where they came from? Or maybe you’ve been working on a spreadsheet and something just doesn’t seem right, but you can’t figure out what’s wrong.

That’s where “peeking behind the curtain” in Excel comes in handy! By revealing the formulas, you can uncover the hidden secrets of your spreadsheet. It’s like having a cheat sheet that shows you exactly how each number was calculated. This can be really helpful for:

• Finding Mistakes: You can catch any errors or typos in the formulas that might be causing the wrong results.
• Understanding Complex Spreadsheets: If you inherited a complicated spreadsheet, you can figure out how it works by looking at the calculations.
• Learning from Others: You can see how other people made their spreadsheets and formulas, which helps you learn new tricks and techniques.

Things to Remember 1. Pressing “Ctrl + `” switches between showing formulas and showing values in Excel. 2. Always understand the formula, but don’t forget the values it produces. 3. If you accidentally see formulas instead of values, press “Ctrl + `” again to switch back. 4. If Excel is showing formulas instead of values because of the cell format, change the format to “General” to see the values.

How To Show Formulas In Excel?

When you put a math formula into a cell in Excel and hit Enter, Excel usually shows you the answer right away. But if you want to see the actual formula instead of the answer, here’s what you can do:

1. When you click on a cell in Excel, the formula for that cell appears at the top of the formula bar.

2. If you want to see all the formulas for every cell, you can press CTRL + ` (you’ll find this key above the tab key on your keyboard).

3. Press the down arrow key twice.

4. Note: Excel will highlight all the cells that are used in a formula.

5. To hide all the formulas again, just press CTRL + `.

6. If you can’t find the grave accent (`) key on your keyboard, you can click on the Formulas tab, then go to the Formula Auditing group, and click on Show Formulas.

7. To hide the formulas again, just click on Show Formulas once more.

You can make Excel highlight all cells with formulas by using the ISFORMULA function along with conditional formatting. Here’s how: Select the range you want, go to the Home tab, click on Conditional Formatting, choose New Rule, and then select “Use a formula to determine which cells to format”. Enter the formula =ISFORMULA(A1), choose your formatting options, and apply the rule. Remember to replace “A1” with the first cell of your selected range.

8. You can also use the FORMULATEXT function in Excel to show formulas in cells next to your data.

This is the easiest steps on how to show formulas in excel.

How To Display Formulas In Excel In Specific Cells Only?

If you only want to see the formula for one specific cell? There are two ways to do that.

1. Add a space before the formula

Imagine you have a table with some cells that contain formulas. You want to see the formulas for two cells, but not for another one.

1. First, click on the cells you want to check the formulas for (let’s say A1 and A2).

2. Now, go to the “Home” tab, then click on “Find & Select” in the “Editing” group, and choose “Replace”.

3. A box will pop up. Click on the “Find” tab and type in an equal sign (=).

4. Then, click on the “Replace” tab and type in an equal sign but with a space before it ( =).

This way, the formulas in the selected cells won’t start with an equal sign, but with a space. Excel only recognizes a formula when it starts with an equal sign.

5. Now, the formulas in the selected cells (A1 and A2) will be visible, but the result in cell A3 will stay the same.

2. Add an apostrophe (‘) before the formula

If you don’t want to see a space in a cell, try this:

1. Choose Cell A1 and A2.
2. Go to the Home Tab > Editing group > Find & Select > Replace.
3. In the Find and Replace box, type an equal sign (=) in the Find box.
4. In the Replace box, type an equal sign after an apostrophe (‘=).

By doing this, the formulas in the selected cells won’t begin with an equal sign but with an apostrophe. When something starts with an apostrophe in Excel, it’s treated as a text string.

5. Only the results in the selected cells will be replaced by formulas in this manner.

The apostrophe in a cell isn’t seen like a space. You only see it when you’re editing the cell or looking at the formula bar.

Quick Tip The usual methods might not be fast enough if you just want to look at the formula in a cell quickly. To see the formula in a cell, press ‘F2’. This shows you the formula immediately. But be aware that once you click away or press enter, you’ll see the result, not the formula anymore.

How To Show Formulas Using Shortcut Key

There’s a quick way to see all the formulas in an Excel sheet. It’s a shortcut key that activates the Show Formula mode we talked about earlier. Using this shortcut makes it much faster to see all the formulas.

Just press Ctrl+` to see the formulas. This switches on the formula mode.

Press Ctrl+` again to switch off the formula mode.

This shortcut is the fastest and easiest way to view all the formulas in Excel. Instead of going through many steps, you use this shortcut to turn on and off the Show Formula mode. It saves a lot of time because you don’t have to remember multiple steps, just the shortcut key.

How To Print Formulas In Excel?

When you print an Excel sheet, it shows the answers, not the calculations that got those answers.

Do you want the printout to show the calculations instead of the answers?

1. Show the formulas in the cells using one of the methods mentioned earlier.

2. Once your sheet shows the formulas, go to the File tab and select Print. Or just press Control + P.

Here’s how the preview will look.

3. The printout will now display the formulas instead of the answers.

The printout will now display the formulas instead of the answers.

That’s It!

This tutorial covers everything about formulas in Excel, from displaying them to printing them.

Once you understand how formulas work in your sheet, everything will start to make more sense.

Did this article help you gain better insight into Excel? If so, we suggest you take a quick look at some other relevant articles.

Like Top Excel Tips and MS Excel for Statistical Analysis.

FAQs