Home » formula friday

How To Check Cells Have The Same Excel Formula.

by —Leave a Comment

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.

FORMULATEXT check cells have the same formula.

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
  • 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

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.

Formula Friday – Setting Excel To Calculate Formulas Manually

by —1 Comment

Happy #formulafriday Excellers and welcome to another blog post in my #Excel series.  Today let’s look at setting Excel to calculate formulas manually.  Are you tired of Excel recalculating your formulas automatically? You can force Excel to calculate a formula only when you need it recalculated by using the manual calculation mode. In this post, we’ll show you how to turn on manual calculation and walk through an example of how it works. Stay in control of your Excel calculations with manual mode!

So,if you are working on large spreadsheets, they may take some time to update and calculate especially if you have a slow pc or laptop.  A great easy way to avoid this happening is to cut short the updating time by switching the Excel formulas to calculate manually or on demand not automatically.

Excel Macro

Default Calculation Settings In Excel.

By default, Excel will automatically calculate formulas that you type in or insert into it. That’s what you want it to do right?.  Most times yes, but its great to have the choice if it is just taking too long.  This may be for a few reasons.  First, you may have a large workbook with a lot of formulas, or you may have some formulas which take longer to recalculate depending on their complexity.  So if you want to delay updating then you can set Exel not to automatically recalculate or refresh your formulas.

What Are The Calculation Options In Excel?

There are a few options available, let’s walk through them.

FORMULA FRIDAY MANUAL AUTOMATIC FORMULA CALCULATIONS

Automatic Calculations

This is the default mode for Excel.  All of the formulas on your spreadsheet are updated when a change is made to a value, formula, or name referenced in a formula. You can imagine that if your worksheet grows and contains more and more formulas and references then it may take some time eventually to update.

Automatic Calculations Except For Data Tables

Calculates all dependent formulas and updates open or embedded charts. Does not calculate data tables created with the Data Table feature. To recalculate data tables when this option button is selected, click the Calculate Now (F9) command button on the Formulas tab of the Ribbon or press F9 in the worksheet.

Manual Formula Calculation.

Turning to manual calculations lets you decide when Excel will update.  To enable manual calculations.

  • Office Button
  • Excel Options
  • Formulas Tab
  • Calculation Options
  • Select Manual
  • Hit Ok

To recalculate the workbook press F9, there are few options associated with this function

F9
Calculates formulas since the last calculation in all of your open workbooks

SHIFT+F9
Calculates formulas that have changed since the last calculation just in the active worksheet

CTL+ALT+F9
Calculates all formulas in all open workbooks regardless of when they were last calculated

If you want more tips then sign up to my monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 50 Excel Tips.

How To Excel At Excel – Formula Friday Blog Posts.

Learn Excel With Our Excel Online Courses
Learn Excel Dashboard Course

Formula Friday – Edit Multiple Formulas In Excel – FAST

by —Leave a Comment

Hello, time for more #formulafriday fun with Excel Formulas. Do you ever need to update multiple Excel formulas in Excel? If so, you’ll be happy to know that there is an easy way to do this. In this blog post, we will show you how to edit multiple formulas in Excel. Sometimes, you just need a faster way to do stuff in Excel, especially when it comes to creating and updating formulas. So, here is a way to update multiple formulas in Excel really fast using a method you have probably used before, but maybe not for updating formulas.

Excel Macro

Update Multiple Excel Formulas.

I will share with the process I used on my own Excel workbooks. My Excel workbook calculated the value of sales generated per sales person. These sale are then ranked each month over a period of 3 individual months.

update multiple formulas at once

Example Formula.

I have my formula set up to calculate the descending rank (the highest value sales are awarded the highest rank – or lowest value!) for the month of June 2016 as below.

=RANK(C3,$C$3:$C$11)

If I want to now show the ranking of July 2016 I do not need to re write my formulas, I can simply use Find and Replace method. Yes, the simple and often used feature in Excel.

Using Find And Replace To Update The Formulas.

  • Select the cells you want to use Find and Replace in – in my example it is Column H
  • Hit CTRL+H to bring up the Find & Replace Dialog Box
  • On the Find Tab, we can type C
  • Hit the Options Tab
  • Select Within Sheet – By Columns – Look In  – Formulas
  • Select the Replace Tab – Type D
  • Hit Replace All

Note- Any cells that ou have highighted that contain C will be updated.

  • Fially, job done – all of my cells have been updated.
update multiple Excel formulas

That was easy wasn’t it?. I can then again just update to show August 2016 ranking by using Find & Replace to find D and replace with E.

Have you ever had to edit a lot of Excel formulas at once? Maybe you copied and pasted them from one sheet to another, or maybe you just had a lot of cells that needed the same formatting change. In any case, if you’ve ever done this kind of bulk editing, then you know it can be a pain. Especially if you have to do it over and over again. I hope you found this method quick and easy.

More Excel Formula Tips


Dont forget to sign up to the Excel at Excel Newletter for 3 free Excel tips the first Wednesday of the month. Just click on the Sign Up Form to the right or use the link below.

Why not bookmark my Formula Friday and Macro Monday pages. I update them every week with new Excel tutorials.

Formula Friday – How To Make An Exact Copy OF An Excel Formula

by

Hello it is #formulafriday again, today let’s look at how to make an EXACT copy of an Excel Formula. So, if you ever needed to make an exact copy of an Excel formula it can be a bit tricky. Or, sometimes give unexpected results. What do I mean by that?. Well, when you copy of formula and paste it to a new range of cells, like another location in your worksheet or workbook, Excel is really helpful. All of the relative references are changed to reflect the new location of the formula. This would be the expected behaviour of Excel. However, sometimes you want to move the formula to another location on the worksheet but do not want to change the cells references. In this tutorial, Ishow you how to make a copy of a formula without having to retype the formula with the cells references you want.

Excel Macro

Let’s Work Through An Example.

My example formula is in the screen grab below. I have the following formula which sums cells A1 to A4.

=SUM(A1:A4)

When I copy the formula to Column C, the formula is adjusted accordingly by Excel. Thanks Excel but in the instance it is not exactly what I wanted to happen. I just wanted to change the location of the formula on the worksheet, not the cells the formula refers to.

make an exact copy of an Excel formula

The formula copies with the cells references below.

=SUM(C1:C4)

Fortunately, there is a solution to the auto adjusted cell referecenes. So, if you do not want this helpful assumption by Excel then it is easy to make a copy without these adjustments. Simply follow the steps below.

How To Copy Your Excel Formula.

  • Hit F2 to enter the Edit mode for formulas.
  • Next, highlight the formula the hit CTL+C to copy the formula as text from the formula bar.
  • Exit out of Edit mode by hitting the ESC key
  • Go ahead and paste it into the new cell where you want the formula.
  • That’s it you now should have correctly made an exact copy of your Excel formula.

When you exit out of Edit mode in Excel by hitting the Escape key (ESC) on your keyboard, Excel return back to regular enter mode. To confirm the current mode of Excel, simply look at the bottom left hand screen. If Excel is in Edit mode the word Edit will be displayed.

If you want to learn more about how awesome Excel is you to take a look our Recommended Excel Online
For more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on every month and receive my free Ebook, 50 Excel Tips.

Read all of my Macro Monday and Formula Friday blog posts using the links below.

All Macro Monday Blog Posts

All Formula Friday Blog Posts

Learn Excel Dashboard Course

Need Some Extra Help With Your Excel Formulas?

Formula Friday – Add Leading Zeros To Numbers In Excel Using The TEXT Function

by

Hello Excellers, it is time for some more #FormulaFiday Excel fun. If you need to add leading zeros to a number in Excel, the TEXT function is the way to go. This function converts a text string into a number, allowing you to add leading zeros as needed. This tutorial will show you how to use the TEXT function to add leading zeros to numbers in Excel. Let’s get started!

In certYou may need to have data displayed with leading zeros in certain situations. For example, customer ID records, or in my case, I use to store numbers in my databases. So I add leading zeros to maintain data consistency, like 00564 and 03453 as the store ID.

I know I will never use these fields in any calculations, (I am never going to add the actual value of the stI will never use these fields in any calculations (I will never add the actual value of the store numbers together or the exact number values of customer IDs), so maintaining the leading zeros gives a consistent data set. In addition, this proves helpful in using the value in VLOOKUPS or INDEX MATCH to find and fetch the details of my stores in my database.

So, there are few ways to do this but today let’s look at one I do use a lot, and this is the TEXT function.

Excel add leading zeros

Add Leading Zeros.

Here is an extract from my data set in the above screen grab. My database has a customer number with five digits. Some customers only have one to four digits, but I want to maintain the five number format. In column C, which will be a helper column, I select C3:C10 and type the following formula.

=TEXT(B4,”00000″)

Then hit CTRL+ENTER to auto-fill the column of selected cells with the formula. All Customer IDs now have five digits, padding out the smaller IDs with zeros until they all have five numbers.

Text Function Syntax Review.

TEXT is a straightforward Excel Function, with two arguments

=TEXT(value format_text)

where

value is a numeric value you want converted into text and

format_text is the formatting you want applied to the value by using the formatting “00000” this forces a zero to be displayed in place of a digit.

Here is the result of the formula being applied.

add-leading-zeros-to-numbers-1

In order to add leading zeros in Excel, you can use the TEXT function. This function takes a number and converts it into text form. The syntax for this function is =TEXT(number,format). You can use the format codes to change how the number appears. For example, if you want to add leading zeros so that your numbers always have two digits, you would use the code “00”. To try this out yourself, follow the example data set above.

Dont forget to sign up to the Excel at Excel Newletter for 3 free Excel tips the first Wednesday of the month. Just click on the Sign Up Form to the right or use the link below.

Excel Macro

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
Share
Additional Info