Excel is Amazing! Name Cells Like a Variable

Matthew Soucoup -

Excel is so much more than a place to track who isn't ... I mean is ... coming to a party that I throw.

It pretty much is a full-blown programming language upon itself. The more I mess around with it, the more I'm amazed with what it can do.

Here's something I just learned that kinda blew my mind. You can give individual (or a range) of cells a name. Like naming a variable. And then refer to that name in various formulas.

Think about how useful that can be - when you need to have a formula that calculates the sum of the cells that hold the values for the cost of eggs, milk, and bread - you could have:

That'll do the trick - but you gotta hope that A2, B2, and C2 refer to the correct cells. I mean by look at it who knows what they refer to.

But ... but ... what if you could name those cells, so in the formula you would know? It would be super apparent and there would never be a question again.

You can! Because Excel is amazing. And here's how.

  1. Highlight the cell you want to name
  2. Go to the formulas menu / tab / ribbon (or whatever the proper nomenclature is).
  3. Click on Define Name
  4. A modal window will show & it'll guess what you want to name the cell. The cell (or range of cells) that should be named. And will show you other cells that have been named. Go ahead and set the values (most likely just accepting the defaults).

That's it!

Then when you go back to your formula & start typing - Excel will even prompt you with the name of the cell - like Intellisense would.

(And if you're more of a click-to-select-a-cell person than a typer when doing formulas - Excel still will use the name!)

And the formula would look something like this:

And what's nice is that you can refer to those named cells on any sheet - not just the one they were defined on - super easy. No more trying to remember what the proper syntax is for cross-sheet cell reference.

What about cell ranges?

Pretty much same as before. Select the range you want to name - give it a name.

And then when you reference it in a formula - reference it as before.

Maybe everybody already knows about this - but when I found out that I could clean up my formulas and make them easier to understand ... so amazing!