Hello Excellers, and welcome back to another #FormulaFriday #Excel formula tip in my 2021 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, 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, by using the FORMULATEXT function the formula in cell C15 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. This function returns what is displayed in the formula bar if you select the referenced cell.
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
- If there is worksheet protection, the formula cannot be displayed
- 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).
The result of this is TRUE. Exactly what we wanted. Just repeat the formula to accommodate all areas B and C.
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.
