Hi Excellers, #formulafriday time again. Welcome back to another blog post in my 2021 series. Today is a bit different, I want to share with you how to rename or apply names to formulas that you may already have in your Excel worksheet.  Let’s take a look at an example.

So, for example, you have inherited a worksheet that calculates the Sales Tax applicable to your monthly sales.  Currently the value for Sales Tax sits in cell G1.

Now, I like to use named ranges in my formulas, (if you want to read some more reasons to use named ranges in your Excel formulas then take a look at my blog post below), for all three of the reasons I talk about in my blog post.

Formula Friday – 3 Reasons To Use Named Ranges In Your Excel Formulas.

So, back to our inherited spreadsheet, where our sales tax is in cell G1.  I want to name that cell as sales_tax, and I can do this by using either using the Name Manager (Formulas – Defined Names – Name manager) or highlight Cell G2 and type the name in the Name Box to the left of the formula bar.

We can see that the named range now exists, but the formula to calculate the sales tax still only shows the reference to the sales tax cell as G3.  Excel does not automatically update the names to the formulas.

I could manually amend the formulas, which may not take much time for an odd formula or two, but there is a quicker way to apply names to formulas if you have a number of formulas in your Excel worksheet.

Applying A Name To A Formula.

• Select the cells to apply the name to ( in this example it is the range D3:D10)
• Formulas Tab – Defined Names – Define Name – Apply Names

• Select the name you want to apply and hit Ok.
• All the formulas will update with the name sales_tax.

It is a way more efficient way to apply the defined names to Excel formulas.

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