IMAGE: Excel In Seconds Tips and Tricks from The Excel Addict - Microsoft Excel 2003, 2007, 2010, 2013, 2016, 365
   TheExcelAddict.com

 
March 6, 2019
 
Hi fellow Excel Addict,
 
Thanks for taking some time out of your busy week to learn something new in Excel that will hopefully make your week less busy.

If you like this week's tip, please share it and recommend your colleagues and any other Excel users you know to sign up for my newsletter.

Keep on Excelling,
Francis Hayes (The Excel Addict)
Email:  fhayes[AT]TheExcelAddict.com

Send Email. Connect With Customers. Grow Your Business.

With AWeber, you get all the email marketing tools you need to create and send beautiful and engaging emails. For a behind-the-scenes look at how you can use AWeber, sign up to our Test Drive email series:
 
Missed my last newsletter?

Click Here to View it Online


Having a positive attitude can help us in difficult times.
I hope today's quote will help you have a positive attitude today.

Quote of the Day

"What the teacher is, is more important than what he teaches."

-- Karl Menninger --

 
If you have a favourite quote, send it to me and I may post it in my newsletter.

THIS WEEK'S 'EXCEL IN SECONDS' TIP
 
Build Smarter Formulas By Using Named Constants

Do you have workbooks that contain formulas with fixed values such as tax rates or sales commission percentages entered directly into the formulas?
Fixed Value Entered Directly In Each Formula in Microsoft Excel 2007 2010 2013 2016 2019 365

Rather than manually typing those fixed values directly in each formula, it would be much easier to enter the value in a single cell, give that cell a name and use the name in the formulas instead of the fixed value. Then, whenever you need to change the fixed value, you simply enter a new value in that single 'named' cell and all related formulas will be updated.
Assign A Name To A Cell That Can Be Used In Formulas in Microsoft Excel 2007 2010 2013 2016 2019 365
To assign a name to a value in a cell and then use the name in formulas:

1) First choose a cell where you want to store the value and enter the constant value. Sometimes you will want this cell to be within your print area and other times you may want to store it outside;

2) With the cell selected, click in the Name box (that white space to the left of the Formula Bar) and type a name. The name must start with a letter or an underscore and cannot include spaces or special characters. That name now takes on any value you enter in the cell;

3) In the formulas where you would typically enter the cell reference containing the constant value, type the name instead.
Defined Name Used In Formula in Microsoft Excel 2007 2010 2013 2016 2019 365
This named cell now acts as a variable, so you can now change the value in the cell and all of the formulas that contain that name will recalculate using the new value.

Storing a Constant Value NOT in a cell

Another method of referring to a constant value for your formulas that doesn't require storing the value anywhere on your worksheet is by creating a named constant. With this method, you store the constant value in a 'defined name'. This is best used for values that will not change or rarely need to be changed. This method also makes it less likely that the value will be accidentally changed or deleted.

To create a named constant...

1) From the Formulas tab, click Name Manager and then New...;

2) Give your constant a name in the Name field. The name must start with a letter or an underscore. The name cannot include spaces or special characters;
3) The Scope field in the New Name dialog refers to where you want to use the Name. If you will be using the name only in the current sheet, you can choose the sheet from the dropdown. If you plan to use the name throughout the workbook, you can leave the scope as 'Workbook'.

4) In the Refers to field, type your constant value, then click OK.

5) Press Enter or click Close to close the Name Manager dialog.
Adding A Named Constant Value For Use In Formulas in Microsoft Excel 2007 2010 2013 2016 2019 365

You can now use that Name in your formulas.

To change the value of the Name...

1) From the Formulas tab, click Name Manager;

2) From the list, select the name you want to change;

2) Give your constant a name. The name must start with a letter or an underscore. The name cannot include spaces or special characters;

3) In the Refers to field, type a new value then click OK;

4) Press Enter or click Close to close the Name Manager dialog.

All formulas containing this named constant will recalculate with the new value.

This sure beats going through each formula to update those fixed values.

To share this tip with your friends and
colleagues, choose one of these options...
'Excel in Seconds' with The Excel Addict
Build Smarter Formulas By Using Named Constants
 

 
Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose.
 
 
 
"Spreadsheets Tips From An Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630