Windows 7 Forums

Welcome to Windows 7 Forums. Our forum is dedicated to helping you find support and solutions for any problems regarding your Windows 7 PC be it Dell, HP, Acer, Asus or a custom build. We also provide an extensive Windows 7 tutorial section that covers a wide range of tips and tricks.


Windows 7: Excel 2016: Displaying formulas in the formula bar

21 Mar 2016   #1
Regulus Leonis

MS Windows 7 Home Premium 64-bit
 
 
Excel 2016: Displaying formulas in the formula bar

I am using Win 7 Pro 7601 Multiprocessor Free SP1, 64-bit. Intel Core i7-4770, 16GB installed memory. Excel 2016. I have a spreadsheet with formulas, none of which display in the formula bar. All my other spreadsheets display their formulas in the bar.

I have tried the following to display the formulas in their cells: Ctrl `, Show Formulas tab in the Formula Auditing group, and Excel Options>Display options for this worksheet (checked the box for "Show formulas in cells instead..."). When I use either one of these, the cells expand but no formulas appear. I found these functions on this website:
https://www.ablebits.com/office-addi...ormulas-excel/

So it's strange that I can't get the formulas to display by issuing those commands. But all I want is to display the them in the formula bar and that should happen automatically when I select the cell. Is there a way I can fix this?


My System SpecsSystem Spec
.
21 Mar 2016   #2
Ranger4

Windows 7 Home Premium 64 bit sp1
 
 

You need to open the Excel spreadsheet in question, click the upper LH corner to Select All & then right click on a cell & select Format Cells, when that opens you should see a Tab that says Protection, select that & untick Hidden. There is also a note there that says you need to check Review tab & check on Protections.
My System SpecsSystem Spec
21 Mar 2016   #3
Regulus Leonis

MS Windows 7 Home Premium 64-bit
 
 

Sorry, I forgot to mention the cells are not hidden. I don't actually see the Protection tab when I go into Format Cells.
My System SpecsSystem Spec
.

21 Mar 2016   #4
Ranger4

Windows 7 Home Premium 64 bit sp1
 
 

This is what I am referring to, from Excel 2013.

Excel 2016: Displaying formulas in the formula bar-excel_protect_cells_2.png

Is your spreadsheet one you created or has it been supplied from someone else. If from someone else it's possible the formulas have been hidden for a reason.

I use Excel 2013 as part of Office 2013 Pro, so it's possible Excel 2016 could be different. Have you clicked on the little ? on the top RH corner near the minimise, maximise, close buttons when in Excel. This will open the help files. When you are there you can do a search on hiding formulas & see if it provides any help.


My System SpecsSystem Spec
22 Mar 2016   #5
Regulus Leonis

MS Windows 7 Home Premium 64-bit
 
 

I protected and then unprotected to see what would happen. So now I see the Protection tab in Format Cells. You can see it's not hidden and the cell that is highlighted displays the result in the formula bar instead of its formula.
Also, when I copy and paste a cell to a new spreadsheet, it will display only the result in the formula bar regardless of whether I choose Paste or Paste Formula. But it will display the link in the formula bar when I choose Paste Link.


Attached Images
Excel 2016: Displaying formulas in the formula bar-withheld-results-scrnsht.jpg 
My System SpecsSystem Spec
22 Mar 2016   #6
Ranger4

Windows 7 Home Premium 64 bit sp1
 
 

You have mentioned this is the only spreadsheet you have this problem with, so what is different about this one?.

Did you create this spreadsheet yourself. Are you on a workplace network.
My System SpecsSystem Spec
22 Mar 2016   #7
Regulus Leonis

MS Windows 7 Home Premium 64-bit
 
 

The only thing that is different in the faulty spreadsheet is I pasted the formulas in random spots, as shown at the red arrow. Blue arrow shows from where I copied the formula at the red arrow. The other image shows an example of one of my spreadsheets correctly displaying a formula in the formula bar. Cell Z2 is highlighted, right below where I have the formula entered in Z1.

I created all my spreadsheets myself and I do not work on the shared network; I'm working from a drive in my computer. However, the faulty spreadsheet was likely on the shared workplace network at some point. The spreadsheets that are on that drive display the formulas as they should.


Attached Thumbnails
Excel 2016: Displaying formulas in the formula bar-withheld-results-scrnsht2b.jpg  
Attached Images
Excel 2016: Displaying formulas in the formula bar-withheld-results-scrnsht2c.jpg 
My System SpecsSystem Spec
22 Mar 2016   #8
Ranger4

Windows 7 Home Premium 64 bit sp1
 
 

Probably at some point during the copying process some data has been missed or become corrupted & is not displaying properly.

Short of trying to access the original spreadsheet again & recopying it I can't suggest a solution.

I assume that the drive you refer to, that displays the formulas correctly is at your old work place & no longer accessible.
My System SpecsSystem Spec
Reply

 Excel 2016: Displaying formulas in the formula bar




Thread Tools Search this Thread
Search this Thread:

Advanced Search




Similar help and support threads
Thread Forum
Excel 2013 - how to keep a formula consisten
Hi I am trying to tie one cell into a formula that is being pasted into multiple rows. I know you can use $ to keep the cell consistent but it doesn't seem to be working. So if the value is in c9 and then first row gets c9 then the new row gets c10 etc... I need it to be c9 throughout the...
Microsoft Office
Excel Formula
Hello Sir, Can you let me know the formula in Excel: I have a table as below: This table, At the column "A,Status" when i Put the word" Completed", it's will automatic put current Date to Column "B, Row 2". But i don't know after that when tomorrow arrived,at the column "B,Row 2",...
Microsoft Office
Excel 2003 sum formula problem
I have been entering currency data in Column B in Euros to an EXCEL 2003 s/s and the formula used has added these totals entered in that Column correctly until I reached a total sum of 74364.70 Euros ,this being the sum of entries in Rows B6 to B82 . This was reached with the formula showing as...
Microsoft Office
Want to copy data and formula from one Excel Sheet to another.
I have a budget workbook set up in Excel, and in that workbook there are many different sheets all part of the one book. I have created a formula in one sheet from the data that is collected there, and that data is important for information collected on another sheet. I want to copy the...
Microsoft Office
Excel Formula Help please...
This is probably very simple and I have got it working but it isn't working how I want it to work. I have created an Excel document to show my monthly payments of a bank loan. I have created 3 columns in the first column is the payment number, in the second column is the amount that the loan...
Microsoft Office
Cant scroll while editing formula Excel 2007
Hi I'm unable to scroll while editig a formula in excel 2007. i know it can be done if i edit directly in cell but i prefer editing in the formula bar. I've seen an apparent solution in some forum which says change the mouse setting in the control panel (the wheel tab). Start --> Settings -->...
Microsoft Office


Our Sites

Site Links

About Us

Find Us

Windows 7 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 7" and related materials are trademarks of Microsoft Corp.

Designer Media Ltd

All times are GMT -5. The time now is 15:09.

Twitter Facebook Google+



Windows 7 Forums

Seven Forums Android App Seven Forums IOS App