Check And Evaluate Formulas In Excel 2010

We have just finished a formula in Excel (which we are proud of) but we want to make sure that what we did is correct. What do we do?

We can use Excel’s Evaluate Formula function. It helps us go through the steps that we used when we were creating the formula. Very useful function especially for long formulas and ones that have precedents, because it makes it very easy to see how Excel has calculated this formula.

First of all we must select the cell that contains the formula and then select the Functions tab and then from the area of the ribbon named Formula Auditing we select the command Evaluate Formula as we can see in the image below.

01

Once selected the Evaluate Formula dialog box appears as we can see in the image below. At the top left corner of the window and under Reference, we see the cells reference which is IF!$I$4 (the actual name of the sheet and the actual cell).

In the Evaluation area of the Evaluate Formula window we see our formula. In this example the formula is =IF(Criteria!A2>=10000;“Bronze”; “Gold”).

02

We notice that a part of the formula is underlined (Criteria!A2), so if we click the Evaluate button, we will see the actual value that the specific cell contains, as we can see in the following image.

03

Now the part 6300>=10000 is underlined. If we click the Evaluate button, we will see the result of the first part of the formula. Seeing that it was an If function and the criteria 6300>=10000 is not True, the result that appears is False as we can see below.

04

By pressing once more the Evaluate button the actual result of the formula appears as we can see. The Evaluate button changes to Restart in order to start evaluating the formula again.

05

When we are evaluating a formula, we notice the Step In and Step Out buttons that appear on the Evaluate Formula dialog box. These buttons are enabled during the steps that have got to do with precedents as we can see in the image below.

06

Here we see the actual sheet and cells reference Criteria!$A$2 that we have stepped in, and at the right we see the actual formula that the specific formula contains. Once we have checked it we can press the Step Out button in order to continue Evaluating the formula.

Below you can check out the video which describes how to use the Evaluate Formula in Excel 2010.

About officesmart

MVP: Honored with the MVP (Most Valuable Professional) for OFFICE SYSTEM title for the years 2011, 2012, 2013, 2014 and 2015 by Microsoft, for my contribution and commitment to the technical communities worldwide. Microsoft Master Specialist: This certification provides skill-verification tools that not only help assess a person's skills in using Microsoft Office programs but also the ability to quickly complete on-the-job tasks across multiple programs in the Microsoft Office system

Posted on June 4, 2012, in Excel 2010 English, Microsoft Office 2010 English and tagged , , , , , , , , , , . Bookmark the permalink. Comments Off on Check And Evaluate Formulas In Excel 2010.

Comments are closed.