![How To Show Formulas In Excel: Simple & Easy Steps 1 How To Show Formulas In Excel 1](https://topexceltips.com/wp-content/uploads/2024/04/How-To-Show-Formulas-In-Excel-1.webp)
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:
- When you click on a cell in Excel, the formula for that cell appears at the top of the formula bar.
![How To Show Formulas In Excel: Simple & Easy Steps 2 image 5](https://topexceltips.com/wp-content/uploads/2024/04/image-5.png)
- 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).
![How To Show Formulas In Excel: Simple & Easy Steps 3 image 2](https://topexceltips.com/wp-content/uploads/2024/04/image-2.png)
- Press the down arrow key twice.
![How To Show Formulas In Excel: Simple & Easy Steps 4 image 1](https://topexceltips.com/wp-content/uploads/2024/04/image-1.png)
- Note: Excel will highlight all the cells that are used in a formula.
- To hide all the formulas again, just press CTRL + `.
![How To Show Formulas In Excel: Simple & Easy Steps 5 image 3](https://topexceltips.com/wp-content/uploads/2024/04/image-3.png)
- 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.
- To hide the formulas again, just click on Show Formulas once more.
![How To Show Formulas In Excel: Simple & Easy Steps 6 image 7](https://topexceltips.com/wp-content/uploads/2024/04/image-7.png)
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.
- You can also use the FORMULATEXT function in Excel to show formulas in cells next to your data.
![How To Show Formulas In Excel: Simple & Easy Steps 7 image 4](https://topexceltips.com/wp-content/uploads/2024/04/image-4.png)
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.
- First, click on the cells you want to check the formulas for (let’s say A1 and A2).
![How To Show Formulas In Excel: Simple & Easy Steps 8 co1](https://topexceltips.com/wp-content/uploads/2024/04/co1.webp)
- Now, go to the “Home” tab, then click on “Find & Select” in the “Editing” group, and choose “Replace”.
![How To Show Formulas In Excel: Simple & Easy Steps 9 co2](https://topexceltips.com/wp-content/uploads/2024/04/co2.webp)
- A box will pop up. Click on the “Find” tab and type in an equal sign (=).
- Then, click on the “Replace” tab and type in an equal sign but with a space before it ( =).
![How To Show Formulas In Excel: Simple & Easy Steps 10 co3](https://topexceltips.com/wp-content/uploads/2024/04/co3.webp)
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.
- Now, the formulas in the selected cells (A1 and A2) will be visible, but the result in cell A3 will stay the same.
![How To Show Formulas In Excel: Simple & Easy Steps 11 co5](https://topexceltips.com/wp-content/uploads/2024/04/co5.webp)
2. Add an apostrophe (‘) before the formula
If you don’t want to see a space in a cell, try this:
- Choose Cell A1 and A2.
- Go to the Home Tab > Editing group > Find & Select > Replace.
- In the Find and Replace box, type an equal sign (=) in the Find box.
- In the Replace box, type an equal sign after an apostrophe (‘=).
![How To Show Formulas In Excel: Simple & Easy Steps 12 coo4](https://topexceltips.com/wp-content/uploads/2024/04/coo4.webp)
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.
- Only the results in the selected cells will be replaced by formulas in this manner.
![How To Show Formulas In Excel: Simple & Easy Steps 13 coo5](https://topexceltips.com/wp-content/uploads/2024/04/coo5.webp)
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.
![How To Show Formulas In Excel: Simple & Easy Steps 14 image](https://topexceltips.com/wp-content/uploads/2024/04/image.png)
Just press Ctrl+` to see the formulas. This switches on the formula mode.
![How To Show Formulas In Excel: Simple & Easy Steps 15 image 8](https://topexceltips.com/wp-content/uploads/2024/04/image-8.png)
Press Ctrl+` again to switch off the formula mode.
![How To Show Formulas In Excel: Simple & Easy Steps 16 image 6](https://topexceltips.com/wp-content/uploads/2024/04/image-6.png)
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.
![How To Show Formulas In Excel: Simple & Easy Steps 17 p2](https://topexceltips.com/wp-content/uploads/2024/04/p2.webp)
Here’s how the preview will look.
3. The printout will now display the formulas instead of the answers.
![How To Show Formulas In Excel: Simple & Easy Steps 18 p3](https://topexceltips.com/wp-content/uploads/2024/04/p3.webp)
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
How to Hide Formulas in Excel?
To hide formulas in Excel, click on the “Formulas” tab at the top, then choose “Hide Formulas” from the menu. This makes Excel show the results of the formulas in the cells, not the formulas themselves.
How to Change Formulas in Excel?
To change a formula in Excel, just double-click on the cell with the formula. This opens a box where you can edit the formula. Once you’ve made your changes, click “OK” to save them.
How to Copy Formulas in Excel?
To copy a formula from one cell to another, first, click on the cell with the formula you want to copy. Then press “Ctrl + C” to copy it. Next, click on the cell where you want to paste the formula and press “Ctrl + V” to paste it there.
How to Remove Formulas in Excel?
To get rid of a formula from a cell, simply select the cell with the formula and press the “Delete” key. This will erase the formula from the cell, leaving it empty.