Is it possible to have Named Constants in Google Sheets

Question

Excel has a notion of defined names which can be used to define constants/literals. Does GSheets allow this?

You can see this in action in the following Excel screenshot:

.

Cell B2 has the formula =A2*cm_per_inch and via the name manager window, you can see cm_per_inch is a constant value of 2.54 and not a reference to a cell address.

When I import this spreadsheet into GSheets I get the following with #NAME? errors. Also, note in the GSheets Named ranges list there are only two entries whereas in Excel there were three. cm_per_inch appears to be missing, presumably because GSheets does not support a defined constant.

One workaround would be to do as this Stack Overflow Q&A proposes but that is more of a lookup table and not a constant as per Excel constructs. You can see this in action in a revision of the previous GSheet here:

Of note, I wasn't able to use the cm_per_inch as the name of my range and had to use a new range name entirely in order for the formulae to resolve properly.

Answer 1

Currently, this is not possible in Google Sheets, because Select data range field can intake only cell/array references. Therefore, best you can have is to create the second sheet where you will store your variables/constants and name them with Named ranges - and then right-click the sheet and select Hide sheet

Answer 2

This could be also resolved by storing variables/constants into the script and then call for it like:

var variables = [2.54]
function cm_per_inch() {
  return variables[0];
}

=ARRAYFORMULA(A2:A5*cm_per_inch())

=A7*cm_per_inch()

Answer 3

cm_per_inch appears to be missing, presumably because GSheets does not support a defined constant.

You are right, Google Sheets doesn't support a defined constant as well it doesn't support other Excel features like Excel tables as many others.

Please send your feedback directly to Google by using Google Feedback.

It's worth to note that Google Sheets also have many features that are not supported on Excel like the built-in IMAGE(url) among others.

The alternatives are too many, from a) importing only the Excel values to b) create a new spreadsheet from scratch.