How to see the underlying formula instead of the resultant values in an Excel spreadsheet.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
- - 03:56
- - Skill Level: 4
- - What's my skill level
Excel see formula instead of value
When working with a spreadsheet, it is sometimes important to understand the formulas that are in the cells. So you may want to use the Excel see formula instead of value tools.
This video has a couple of ways we can do that.
So the first way is at the moment the way a spreadsheet works, is when you look at the cell the answer will be there – when you look in the formula bar there will be a formula of sorts. If you would like to see all the formula here – what you can do is under formulas – you’ll see over here there is a button which is called “show formulas” or you can use the control and the tilled key – so if I click this button- you’ll see that now instead of seeing the numbers, the answers – what we are seeing is the underlying formula. If you move across and look through the spreadsheet for any obvious errors and when we want to toggle back, click the button again and we are back to the original view.
Another nice feature is the ability to look at a formula and find out what the reference is referring to from a number perspective. So if we look at D13 we see it is referring to 80000. Instead of searching for the cell, what we could have done is we could have highlighted the reference and if we push our F9 function key you will see it brings through the appropriate number. I click on B9 without even looking at it – I will get .21 which is this 21%. It is important to remember if I click enter now this will stay and the formulas will be lost, so I must push the escape key. Just again if I click on that cell there, there is a bit of a function in here- so I could click on that reference – push F9 and see the individual items or I could highlight the entire function when I push F9 – I get the result of that formula.
This is effectively a calculator and if we go back here – I could just as easily let’s say highlight that and we get .21 divided by 12. If I want to see what the answer of that is without a calculator, I highlight the whole area – when I push F9 it tells me what that answer would have been – push F9 so now I have got 80 000 x .01 – before I see what the answer would be, I highlight it – push F9 and I get my answer. If I want it to stay like that I push Enter, otherwise I push Escape to get back to the original formula. So this is very useful if you want to see if the calculation is correct and especially if these references are in other sheets because Excel will go get the information for you and bring it here instead of you jumping between the sheets.
Another useful aspect of it, is let’s say I am going to do a copy/paste as values here. However, when I do this, this breakdown of the calculation is quite useful in the sense that it gives us a bit of an audit trail. So instead of just doing a copy/paste as values and having the answer there, I click my F9 key a few times and now when I push Enter – when I look at the cell it gives me a bit of a breakdown of how we got to that number.