Interested in becoming an Affiliate Trainer? We are now accepting applications for local and international positions. No need for cold calling! Receive referrals of people looking for specialized training in your area and get access to all of the training curriculum I have created and continue to create to use at your appointments.

Excel 2010 Formulas Tab

EXCEL 2010 FORMULA RIBBON

Defined Names

EXCEL 2010 FORMULAS DEFINED NAMES GROUP

Names can be used in formulas as substitutes for cell references, which can make your worksheet more personalized. In the Defined Names section, you can define the name of a cell and/or range. You can then manage the defined names in the Name Manager.

We will use the same excel worksheet as in the Function Library tutorial (as shown below): Golf Scores for 2019 Season. Let’s start by defining cell K13. This cell equals the average of the total scores for all of the players.

EXCEL 2010 FORMULAS DEFINED NAMES EXAMPLE

Select cell K13 and then click on Define Name in the Defined Names section of the Formulas tab. 

EXCEL 2010 FORUMLAS DEFINED NAMES TOTAL AVERAGE

 


The New Name dialog box will appear as shown in the image to the right. In the Name box, we will type Total_Average and select OK. Now we are able to use this name cell in our Score Percentage formula.

EXCEL 2010 FORMULAS DEFINED NAMES OVERALL PERCENTAGE

We need to find the overall percentage received by each player. This will tell us how much below or above average each player is. Start by selecting cell L3. Type in this formula =K3*100/to. You will notice in the image to the left that after you type to, you now get an option for Total_Average. Go ahead and select this to complete our perecentage formula which will be =K3*100/Total_Average.

 

Now we need to complete this formual for the restof the players. Click on cell L3 and then click and drag the fill handle down to L11. As you can see in the image below, we now have a percentage formula completed for each player. We can see that Kim Byers is about 10% below the average (which is favorable in golf) and Christine Marley is the highest above average at 106.82%.

EXCEL 2010 FORMULAS DEFINED NAMES AUTOFILL PERCENTAGE

We can manage our named cells by selecting the Name Manager button in the Defined Names section of the Formulas tab. The Name Manager dialog box will appear as shown in the image below.

EXCEL 2010 FORMULAS DEFINED NAMES NAME MANAGER

You can see a list of the named cells in the box. Select the name you would like to manage and you have the option to Edit or Delete it. You can create a New name as well.

EXCEL 2010 FORMULAS DEFINED NAMES EDIT NAME



If you select Edit, the Edit Name dialog box will appear. Make your changes and select OK.

EXCEL 2010 FORMULAS DEFINED NAMES CREATE FROM SELECTION

The Create from Selection button allows you to automatically generate names from the selected cells. The Click Names from Selection dialog box appears (as shown in the image to the left) when selecting this button.

EXCEL 2010 FORMULAS DEFINED NAMES USE IN FORMULA

Finally, the last command, Use in Formula, has a list of the named cells in the drop-down box. When creating your formula, you can select your named cell from this list. This especially comes in handy if you have a large report and instead of searching for the specific cell to use in your formula, you can select it from this list of named cells.

Next Tutorial


This concludes the Defined Names tutorial.
Click next to continue to the Excel 2010 Formula Auditing tutorial.