It's time to take you through an alternative use for names. Names are not just for ranges, names can also be applied to constants or formula. These are constants or things that, don't change which are stored off the worksheet and the same with formula.

How To Use Name Constant Formula? There's a formula which are stored off the worksheet. Firstly, let's look at off sheet constants. We go up to define name and decide on a name. To call this com sales this is going to be a constant of 0.15 or 15 it's envisaged ,that it's a sales commission rate and for whatever reason. we don't want this to be what the sales people see when they look at this spreadsheet.

How To Use Name Constant Formula 1

How To Use Name Constant Formula Explore Define Names Option

 Now go down to the refers to section and replace this with 0.15 Click OK .We now need to apply the offshoot constant. So, using the most basic function alt equals to sum at the end of this function.  Type times and then start typing com and you'll see that a drop down comes up and each time. We type another letter, it gets smaller with one choice left we can use the tab key to accept and control enter. We can see that we have pulled this off sheet constant into this formula the next way that we can use names is for formula.This example show you away of hiding a formula. 

How To Use Name Constant Formula 2 (1)
How To Use Name Constant Formula 2 (2)

Advantages OF Formula

We can think of two good advantages of this one, if we have a proprietary formula and we don't want users to see that formula. We could use this approach secondly, if we wanted to overcome the shortcoming of name ranges in that they don't copy relatively. We can use an offshoot formula which has a relative property. So, again this time in cell C13 define name. To call this sum Q1 and at the bottom type in directly the formula that, we want sum open brackets C 3 colon C 12 close brackets control enter in cell C13. Type in equals and F3 and paste the name which was sum q1. Click on there and okay and we can see that we have called that name which actually indirectly is a formula.


How To Use Name Constant Formula? Ctrl Enter and we can see that name in the cell which is adding up the cells. Copy this alt edit copy my preferred copy shortcut but not everyone's highlight the three cells that i want to paste. To alter edit paste and we can see that, we have added up each of these columns and in effect. We have done this relatively the one drawback of this approach is it reduces visibility. If we want users to be able to understand what we're doing understand our workings. Then they're going to need to understand about names and how names can be a way of creating hidden formula thank you.