There’s a useful but under-used feature in Excel that can make your formulas much easier to read and understand.
In the image below, the Name Box is the white box where it says A2.
You can see that I have an Important Value in cell A2. I’m going to use that value all over my workbook in lots of formulas.
If I want my formulas to be easier to read, I can give cell A2 a name by typing something in the name box.
I’ve given cell A2 the name “importantvalue”. Now I can use that name in my formulas anywhere in the workbook.
I can start typing the name in a formula and the name “importantvalue” comes up as a recognized name.
I can use the name as I would any other cell reference. I can multiply it by 2, for example.
“So what?”
Ok, so the above example isn’t really that impressive. The point is that if you’re doing any kind of extensive work in Excel, you’ll sometimes end up with a workbook that has a lot of formulas. And then you might want to send that file to someone. They’ll probably want to verify what you’ve done and check some of the formulas. If you use names, they can instantly see what the calculation really is.
After you’ve set up all your names, you can review all the names in the workbook on the Formulas tab by using the Name Manager.
Sounds like a lot of effort for only a little benefit. Let’s look at something a bit more useful.
I downloaded that data from the USDA FAS custom query builder.
I want to create formulas somewhere else that refer to the row names in this table, so whoever is using the file can easily understand what’s going on.
To do this quickly and easy, I can use “Create from selection” in the “Defined Names” group on the Formulas tab.
First, I select my data, including the row headers.
Then I click “Create from selection”. I have some options to choose from.
In this case, I want to use the text in the left column (i.e. the country name) as names for my data.
When I click OK, it doesn’t look like much has happened. But if I review the names in the Name Manager, I can see I now have some names to use in my formulas.
Now I can create formulas like this:
That’s it! I hope you can see that in more complex situations, this can make it easier for the people you’re sending your files to. They can spend more time focusing on the data and less time decoding what you’ve done.