Evaluate Formula in Excel – how can you use this option in Excel to explain complex formulas?
This week’s hint and tip is on the the evaluate formula option in Excel. Formula auditing can be a very useful feature in Excel as it can help you debug and explain complex formulas that you might struggle to understand. This can be especially useful if the formulas are in a spreadsheet that someone else created. We cover some formula auditing in our Intermediate Excel training course, but not this exact option so we decided to do a hint and tip on it. We are going to go through it now below.
Formula Auditing in Excel
Formula auditing can be a very helpful and useful feature in Excel for users to help them understand formulas in their spreadsheets. Formulas can be hard to understand for anyone but especially if you aren’t the creator of them. The options available in the formula auditing area in Excel are a great way to try and help you understand and debug or troubleshoot formulas. There are a number of options in the area on the ribbon, we are going to go through one now below.
Evaluate Formula option
This evaluate option allows you to go through the formula step by step and shows the result of the underlined part of the formula in the box. As you click through, it will keep giving results until it finally gives you the answer of the formula.
To use this option, you click on a cell with a formula in it and then click on the Evaluate Formula button. This brings up a pop up window that you can see below in the screenshot.
The pop up window will tell you the cell that you are referring to and then will let you evaluate the formula in the box next to it.
The video below talks through the evaluate formula option and how you can use it in Excel. Take a look to find out more and then try them out on your own computer!
We hope you have enjoyed this hint and tip on the evaluate formula in Excel option. Why not take a look at our previous video hint and tip on the the functions SUMIFS and COUNTIFS in Excel?