How to show formula in Excel cells instead of value?

Microsoft Excel is a versatile software program widely used for various purposes like calculations, data analysis, and more. By default, Excel displays the calculated results in cells rather than the underlying formula. However, in some cases, it can be handy to display the formula itself in cells instead of the value it produces. This article will guide you on how to show formulas in Excel cells instead of values.

Table of Contents

Showing Formulas in Excel

To display formulas in Excel cells, you need to modify the cell formatting settings. Follow these steps:

1. Open your Excel spreadsheet.
2. Select the cells or range containing the formulas you want to display.
3. Press the “Ctrl+1” shortcut or right-click on the selected cells and choose “Format Cells.”
4. In the “Format Cells” dialog box, go to the “Number” tab.
5. Select the “Custom” category.
6. In the “Type” box, enter the following syntax: =General;=General;=General;=General;“‘”{@}

The syntax above instructs Excel to display the formula in the cell by starting with an equals sign (=), followed by the cell’s original format specified as “General” for four conditions, and ending with the formula itself within curly braces {@}. The additional single quotation mark (‘) before the {@} prevents Excel from treating the formula as text or executing it.

7. Click “OK” to save the changes.

Now, the selected cells will display the formula instead of the calculated value. You can easily identify cells containing formulas by looking for those starting with an equal sign in the formula bar.

Frequently Asked Questions

1. Can I display formulas in specific cells only?

Yes, you can show formulas in specific cells by selecting those cells and following the steps mentioned above.

2. Will the formula still calculate the values?

Yes, even if you display the formula, Excel will continue to calculate the values based on the formula.

3. Can I copy these formulas to other cells?

Yes, when you copy cells with displayed formulas, the formulas will be copied instead of the values.

4. What should I do if the formula displays as text?

Make sure you have followed the correct syntax mentioned above, including the ‘ before the {@}. If it’s still not working, check if the cells are set to the “Text” format.

5. How can I hide the formula again and show the results?

To revert to displaying the calculated values instead of the formulas, repeat the process mentioned above but select the “General” category in the “Format Cells” dialog instead of using the custom formatting.

6. Does this setting affect calculations or file size?

No, changing the cell formatting to display formulas instead of values does not affect calculations or file size. The formulas are still processed as usual.

7. Can I protect cells with displayed formulas?

Yes, you can protect cells containing displayed formulas by enabling sheet protection. This can be done by going to the “Review” tab and selecting “Protect Sheet.”

8. How can I display formulas without equal sign (=)?

By default, an equal sign precedes all formulas in Excel. It is not possible to display formulas without the equals sign.

9. What if I want to display the formula and the result simultaneously?

To display both the formula and its result, use a combination of custom formatting (as mentioned above) and cell comments containing the results. The formula will still be calculated.

10. Can I display formulas in Google Sheets?

Yes, you can display formulas in Google Sheets as well. The process of displaying formulas is similar to the steps mentioned above, albeit with slight differences in the user interface.

11. Can I print the spreadsheet with displayed formulas?

Yes, when you print an Excel spreadsheet with displayed formulas, the formulas will be printed instead of the calculated values.

12. Will displaying formulas impact the performance of Excel?

No, displaying formulas instead of values has no performance impact on Excel. The calculations are still done internally, regardless of whether they are displayed or not.

By following the straightforward steps outlined above, you can easily display formulas in Excel cells instead of values. This feature can be particularly helpful for auditing or sharing spreadsheets where understanding the underlying calculations is essential.

ncG1vNJzZmimkaLAsHnGnqVnm59kr627xmifqK9dqbxuv8eormaen6e6trjAZqCnZZWtsKa4jJycpaSjYravv9OemJ1ln5t6t63Lrpxo

Na Lin

Update: 2024-07-17

SnapBlog