• Formulas
  • Excel Tips & Tricks
  • Excel Charting
  • Tutorials
  • VBA
  • Book Store
  • More
    • Blog
    • Download Area
    • Excel Video Tutorials
Home » How To Check Cells Have The Same Excel Formula.

How To Check Cells Have The Same Excel Formula.

February 19, 2021 by Barbara

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.

Learn Excel With Our Excel Online Courses
Excel Macro

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.

FORMULATEXT check cells have the same formula. Pin it! Share on Facebook

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.

CHECK CELLS HAVE SAME FORMULA WITH FORMULATEXT Pin it! Share on Facebook

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.

Pin it! Share on Facebook

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.

This image has an empty alt attribute; its file name is Excel-Rescue.png
“Get Quick Statistics About Your Excel Workbook.

Filed Under: Blog, Formula Friday, Formulas Tagged With: Excel Tips, Formula, formula friday, Formulas

Tags

Blog Cells Excel Excel Charting Excel Excel Tips Excel Formulas excel tip Excel Tip Category Excel Tips Excel Tips& Tricks excel tutorials Excel Videos Excel Video Tutorials formatting Formula formula friday Formulas macro Macro Mondays Macros Pivot Table Pivot Tables Power BI Power Query Tutorials vb VBA Worksheets

Recommended Excel Resources

Free Excel Dashboard Webinar

Copyright © 2021 · Enterprise Pro Theme on Genesis Framework · WordPress · Log in

Search

More Info

Want FREE Excel Tips?

Click on my FREE eBook, its my bonus for joining thousands of others who receive my 3 xFREE Excel tips every month in my Excel Newsletter. Join Us!.

Need Answers To Excel Questions Like These?

*How Do I Create A Timestamp In Excel?
*I Want To Change The Width Of The bars On My Excel Chart
*How Can I Find Out The Length Of My Text in Excel? Click the link below to receive more Excel tips' and my Free Ebook

New To Excel? This Is A Great Place To Start!!

Learn Excel Dashboard Course Excel Basics
Scrollbars
Colour of Sheet Tabs
Conditional Formatting
Calculations Introduction

Excel Charting

Self Expanding Charts
One Click Charts
Create Quick Dynamic Charts
Easy Combination Charts

Video Tutorials-If You Like To Learn By Watching

Subscribe to YouTube to get notified of of new Excel videos.


Self Expanding Chart
Delete Obsolete Items From A Pivot Table-Excel 2007
What Are Pivot Tables- Create Your First One
Enable The DeveloperTab In Excel

Some Fun Stuff

Pivot Table Song

Popular Pages

  • Home
© howtoexcelatexcel.com. All rights reserved.
  • Privacy Policy