How to show cell reference (as text) instead of value and vice versa in Excel?
Normally formula cells display calculated results in Excel, and it’s boring to click each cell to view its cell reference. Here I will share three methods to show all cell references instead of values and vice versa in current worksheet, and to show specified cell references as text instead of values and vice versa.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
We can quickly show all cell references instead of calculated results of formulas with clicking Formulas > Show Formulas. See screenshot below:
If you need to show all calculated results again in cells, please click the Show Formulas button again.
On the other hand, some users may want to show specified cell reference as text instead of values, but not all cell references of all formula cells. Here I recommend you to try Kutools for Excel’s Convert Formula to Text utility and Convert Text to Formula utility.
Select the formula cells in which you want to display cell references as text instead of calculated results, and click Kutools > Content > Convert Formula to Text.
Note: In contrast, for showing calculated results instead of formulas, please select these formulas and click Kutools > Content > Convert Text to Formula. See screenshot above.
As we know, the Formulas > Show Formulas can only show/hide formulas in the active sheet. For show or hide all cell reference instead of values or vice versa in all worksheets, please try Kutools for Excel's View Options feature.
1. Click the Kutools > Show / Hide > View Options. See screenshot below:
2. In the opening View Options dialog box, please check the Formulas option, and then click the Apply to all sheets button and Ok button successively. See screenshot below: