Hello Excellers, and welcome back to another #FormulaFriday #Excel formula tip in my Excel Series. Today, I want to show you how to use the FORMULATEXT function to check if your cells contain the same Excel formula.
Why would you need to do this?. Well, one of my subscribers needed to do exactly this. They needed an easy way to check formulas on multiple pages were using the same (correct) formula. In particular, is the Sales Tax is being calculated correctly.
Each of the worksheets in their Excel workbook had a formula that calculated the sales tax for each month. On each worksheet, the sales tax was housed in cell C3. The monthly sales run B3 to B14 as can be seen in the screen grab below. The ALL AREAS tab of the worksheet provided a summary of all of the data area.
Each sales area has the same template. All of the templates sum up to the ALL AREAS template. So, use the FORMULATEXT function the formula in cell C15. In this way it can be checked as to being correctly used across all the templates.
FORMULATEXT Function.
If you have never used the FORMULATEXT function then a quick reminder of the syntax is below.
Syntax
FORMULATEXT(reference)
where
Reference This is a require argument- A reference to a cell or range of cells.
Some points to note.
- You can use the function to reference another worksheet or workbook.
- If the reference argument used is to an entire row or column then the top upper left cell will be used as the reference in this case.
When you will get an error with FORMULATEXT.
- If you reference a cell that does not contain a formula
- The formula in the cell is longer than 8192 characters in length
- The formula cannot be displayed if worksheet protection is enabled.
- You are referencing a workbook that is not open
Ok, let’s set up the solution for this question. I want to check if the cell C15 in the ALL AREAS summary tab contains the same formula as Area A, B, and C.
Writing The Formula.
The formula we are using is below to check Area A. This will give a TRUE or FALSE result. If the formula in ALL AREAS is the same as AREA A then the result is TRUE (which is the result we want).
So, the result of this is TRUE. Exactly what we wanted. Just repeat the formula to accommodate all areas B and C.
This method is a simple way to check an Excel formula to ensure it is consistent across your Excel workbook. I hope find it useful.
So, if you want more Excel and VBA tips then sign up for my Monthly Newsletter. I share 3 Tips on the first Wednesday of the month. You will receive my free Ebook as well.
