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 formulas. These are constants or things that don’t change, which are stored off the worksheet and the same with the formula.

How to Make Something Constant in Excel

There’s a formula, which is stored off the worksheet. Firstly, let’s look at off-sheet constants. We go up to define and decide on a name. To call this com sale, 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 salespeople see when they look at this spreadsheet.

Explore Define Names Option

Now go down to refer 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 time and then start typing com. And you’ll see that a drop-down menu 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 formulas. This example shows you a way of hiding a formula. This is how to make something constant in excel.

Advantages of Using Constant Formula in Excel

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 want to overcome the shortcoming of name ranges, 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 some open brackets C 3 colon C 12 close brackets control and enter. You need to type in equals, F3, and paste the name of the sum of q1 cells. Click on there, and we can see that we have called that name, which actually indirectly is a formula.

Press Ctrl-Enter, and we can see that name in the cell, which is adding up the cells. Copy this alt edit, copy shortcut but not everyone’s highlight the three cells that I want to paste.

To alter edit paste, we can see that we have added up each of these columns. And in fact, we have done this relatively; the one drawback of this approach is it reduces visibility. If we want users to understand what we’re doing, they need to understand names and how names can be a way of creating a hidden formula. Thank you!