65

Is it possible to declare variables in Excel cells and use them as parameters for formulas in other cells?

For example I would declare var1=10 in one of the cells. In another cell I would use var1 for calculation like: =var1*20.

g t
  • 7,287
  • 7
  • 50
  • 85
Manoj
  • 5,011
  • 12
  • 52
  • 76

4 Answers4

100

You can name cells. This is done by clicking the Name Box (that thing next to the formula bar which says "A1" for example) and typing a name, such as, "myvar". Now you can use that name instead of the cell reference:

= myvar*25
AKX
  • 152,115
  • 15
  • 115
  • 172
  • 1
    For reference, you can also name groups of cells. Using their values is a bit trickier, but it can be done :) – Damovisa Apr 09 '09 at 06:38
  • @AKX: That was a nice trick! Thanks... @Damovisa: Thanks for the additional tip! Also is there a way of displaying the Cell name we assign in another cell. I would like to display the cell name in the adjacent cell so that the user is able to identify the cell name without clicking it. – Manoj Apr 09 '09 at 06:50
  • 2
    Note:- This is particulary useful when copying formulas. Excel is very "helpful" and adjusts any cell references in the formula. Quite often you want the references to reamin fixed - refering to a nemed cell does the trick. – James Anderson Apr 09 '09 at 07:28
  • 1
    Note that you cannot use the names 'r', 'c', 'R', 'C', 'R1', 'C1', etc. because they conflict with built-in row and cell references. – ishmael Apr 20 '15 at 07:09
  • You can also name formula. This will reference all populated cells in column A on Sheet1:`=Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))`. Create a Data Validation List cell using the name and you'll get a drop-down box listing everything which also extends as you add to the list, or use it in another formula `=SUM(MyList)`. Such a useful feature. – Darren Bartrup-Cook Jan 21 '16 at 17:30
48

I also just found out how to do this with the Excel Name Manager (Formulas > Defined Names Section > Name Manager).

You can define a variable that doesn't have to "live" within a cell and then you can use it in formulas.

Excel Name Manager

Community
  • 1
  • 1
Colorado Techie
  • 1,302
  • 1
  • 13
  • 21
6

The lingo in excel is different, you don't "declare variables", you "name" cells or arrays.

A good overview of how you do that is below: http://office.microsoft.com/en-001/excel-help/define-and-use-names-in-formulas-HA010342417.aspx

user3825260
  • 61
  • 1
  • 1
1

You can use (hidden) cells as variables. E.g., you could hide Column C, set C1 to

=20

and use it as

=c1*20

Alternatively you can write VBA Macros which set and read a global variable.

Edit: AKX renders my Answer partially incorrect. I had no idea you could name cells in Excel.

Community
  • 1
  • 1
ymihere
  • 381
  • 1
  • 4
  • 11