Forum Discussion
Cell Displaying Wrong
For many years I have linked cells from the same page, or separate tabs within a document, or even from document to document, never with any issues. But now I am trying to link a text field cell to another text field cell in a separate tab within the the same document. The tab with the info is just a persons name which I want to have display in another page.
I have tried everything I can find which toggles the display of cells from formula to the results of the formula, and no matter what I do it ONLY show's the formula and not the results of the formula.
Now as I've been working trying to fix this issue all cells anywhere in the document now are doing this. Be it same page, different page / tab, or separate document!
It sounds like Excel is displaying the formula itself (e.g., =Sheet2!A1) instead of the result of the formula (the value in the referenced cell). This issue is usually caused by one of the following:
Possible Causes and Solutions
- Formula Display Setting is Enabled:
- Excel has an option that toggles between displaying the formulas and their results. If this option is turned on, all cells will show their formulas instead of their calculated values.
- Fix:
- Press Ctrl + (grave accent key) on your keyboard to toggle between showing formulas and showing results. The grave accent key is usually found above the Tab key on most keyboards.
- Alternatively, go to the Formulas tab in the Ribbon and check if "Show Formulas" is enabled. If it is, click it to disable.
- Cell Format is Set to Text:
- If the cell where you are entering the formula is formatted as Text, Excel will treat the formula as a text string and display it as-is, rather than calculating it.
- Fix:
- Select the cell(s) showing the formula.
- Right-click and choose Format Cells.
- In the Number tab, select General or Number and click OK.
- After changing the format, double-click the cell (or press F2 and then Enter) to apply the formatting change.
- Formula Preceded by an Apostrophe:
- If you accidentally typed an apostrophe (') before the equal sign in your formula, Excel will treat the entire formula as text.
- Fix:
- Edit the cell to remove the apostrophe. For example, change '=Sheet2!A1 to =Sheet2!A1.
- Workbook Calculation Mode:
- If the workbook's calculation mode is set to Manual, Excel may not update the cell values automatically.
- Fix:
- Go to the Formulas tab.
- In the Calculation Options group, make sure "Automatic" is selected.
- Accidental Copy of Formula as Text:
- If you copied and pasted the formula from another source, it might have been pasted as text.
- Fix:
- Re-enter the formula manually, ensuring that it's not pasted as text.
Steps to Resolve the Issue
- Check if "Show Formulas" is Enabled:
- Press Ctrl + (toggle formula display).
- Alternatively, check the Formulas tab for the "Show Formulas" button.
- Verify Cell Formatting:
- Right-click the problematic cell(s) > Format Cells > Change to General or Number.
- Ensure Formulas are Entered Correctly:
- Double-check that there is no leading apostrophe and that the formula is typed correctly.
- Check Workbook Calculation Mode:
- Ensure that calculation mode is set to "Automatic."
Summary
By following these steps, you should be able to resolve the issue and have Excel display the results of your formulas rather than the formulas themselves. The text was created with the help of AI.
These steps are nothing more than additional steps to the steps shown by the previous user OliverScheurich . In the end, however, without more information about your specific situation, it's difficult to say for sure that this will solve the problem.
My answers are voluntary and without guarantee!
Hope this will help you.
10 Replies
- It was just suggested to try making sure the "ZOOM" level is 100% because often the zoom level can cause many issues if not at 100%.
SOOOo, tried that and it made no difference.It sounds like Excel is displaying the formula itself (e.g., =Sheet2!A1) instead of the result of the formula (the value in the referenced cell). This issue is usually caused by one of the following:
Possible Causes and Solutions
- Formula Display Setting is Enabled:
- Excel has an option that toggles between displaying the formulas and their results. If this option is turned on, all cells will show their formulas instead of their calculated values.
- Fix:
- Press Ctrl + (grave accent key) on your keyboard to toggle between showing formulas and showing results. The grave accent key is usually found above the Tab key on most keyboards.
- Alternatively, go to the Formulas tab in the Ribbon and check if "Show Formulas" is enabled. If it is, click it to disable.
- Cell Format is Set to Text:
- If the cell where you are entering the formula is formatted as Text, Excel will treat the formula as a text string and display it as-is, rather than calculating it.
- Fix:
- Select the cell(s) showing the formula.
- Right-click and choose Format Cells.
- In the Number tab, select General or Number and click OK.
- After changing the format, double-click the cell (or press F2 and then Enter) to apply the formatting change.
- Formula Preceded by an Apostrophe:
- If you accidentally typed an apostrophe (') before the equal sign in your formula, Excel will treat the entire formula as text.
- Fix:
- Edit the cell to remove the apostrophe. For example, change '=Sheet2!A1 to =Sheet2!A1.
- Workbook Calculation Mode:
- If the workbook's calculation mode is set to Manual, Excel may not update the cell values automatically.
- Fix:
- Go to the Formulas tab.
- In the Calculation Options group, make sure "Automatic" is selected.
- Accidental Copy of Formula as Text:
- If you copied and pasted the formula from another source, it might have been pasted as text.
- Fix:
- Re-enter the formula manually, ensuring that it's not pasted as text.
Steps to Resolve the Issue
- Check if "Show Formulas" is Enabled:
- Press Ctrl + (toggle formula display).
- Alternatively, check the Formulas tab for the "Show Formulas" button.
- Verify Cell Formatting:
- Right-click the problematic cell(s) > Format Cells > Change to General or Number.
- Ensure Formulas are Entered Correctly:
- Double-check that there is no leading apostrophe and that the formula is typed correctly.
- Check Workbook Calculation Mode:
- Ensure that calculation mode is set to "Automatic."
Summary
By following these steps, you should be able to resolve the issue and have Excel display the results of your formulas rather than the formulas themselves. The text was created with the help of AI.
These steps are nothing more than additional steps to the steps shown by the previous user OliverScheurich . In the end, however, without more information about your specific situation, it's difficult to say for sure that this will solve the problem.
My answers are voluntary and without guarantee!
Hope this will help you.
I tried to reply but it seems it didn't work?? So here is what I said..
----------------------------------------------
I have tried everything suggested by everybody with no joy.
Here is an example of the formula in one of the cells.
I have a "Tab" Named "Final-Info-Sheet" This tab is where the "Text" I want to have shown in the cell within another "Tab" is stored.
Here is that formula being used which did work, then stopped working for some reason. ='Final-Info-Sheet'!G5
Now how did I write the formula? I clicked in the cell I want the results shown in... typed the "=" (no "") then went to the Final-Info-Sheet tab and clicked in the cell holding the text I want referenced. Then I hit the "Enter" key.
That process has always worked before.
Now FYI
This workbook I am turning into a compilation of approximately 5 other workbooks. All this data works perfectly in each of the separate workbooks in which I created the data.
Now I no longer want to have five separate workbooks all interlinked to each other to have all the data calculated for the final sixth workbook which is a set of report forms for all five of the previous ones.
Therefore my goal in what I'm doing is to copy and paste a page out of each of those five workbooks and create a new tab within a singular workbook for each of those previous workbooks pages.
Then go in and interlink the pages or in other words tabs within one workbook instead of having 5 or 6 separate workbooks all interlinking to each other.
I started this project thinking I could do it in a few hours, and it ran into an issue where after 6-months+ of 12-hrs a day creating all of this I had 6 seperate workbooks all interlinked, THEN one of the workbooks stored on the hard drive of my brand new master computer blew up on me a week ago when a co-worker spilled a glass of water on my laptops keyboard and shorted out the motherboard..
Now I have saved all the workbooks in my Dropbox, but within Dropbox they're not interlinked to each other. I intended that they would be, but unfortunately somehow, they got some linked to the workbooks that were on the hard drive and others to the Dropbox saved workbooks.
Therefore to avoid this happening ever again I'm trying to consolidate everything into one singular workbook.
Maybe knowing this might help?
I knew when starting this that copying and pasting would not work because none of the formulas would work and that I knew I would have to interlink tabs rather than workbooks. But once I started trying to interlink tabs I ran into this issue.
I know this is a very big pain in the butt for everybody to figure out but I do appreciate any help anybody can give me
Last FYI
I'll just give you the names of two (out of many) of the tabs I'm currently working on to help make sure you can see what I'm doing.
Here is the tab with the text data within it. The data is stuff like first name and last name home address e-mail address phone number etc, etc… and that tab is named: Final-Info-Sheet
The tab where I am working at and wanting the data to be transferred to is a tab named for the persons name.
In other words, each file within the Final-Info-Sheet as a separate tab where I have a report form created for that person. So, for example, I have a tab name of Bob Smith and within the Bob Smith tab I'm trying to pull the data from his information row within the "Final-Info-Sheet" As in His first name is in cell "G5" within the Final-Info-Sheet tab. His middle name is in "H5", His last name is in cell "I5" in the Final-Info-Sheet Tab etc... etc...
Perhaps the below section in the link is helpful:
Switch between displaying formulas and their results from the ribbonThis is my point.
Not trying to be arrogant or rude, however....
I ALREADY went through all of this before making my post.
NONE of works!
