Best Excel 2013 tricks: Naming a Cell

Most of us are required to do calculations, reports or draw up a simple graph at some point in our careers. When that point comes it is handy to know a few tricks for using Excel to make your work easier.

Knowing how to name a cell or a range of cells is one of the things that will make your life easier when working in Excel and it is especially practical when using formulas in Excel.

Naming a cell or range of cells can help in different ways. When a cell is named it behaves as an Absolute Cell Reference, by giving the cell a name such as VAT or Price, Excel knows to refer to that particular cell when you use it in a formula.

Name a range of cells and you can navigate around easily. When used in a formula Excel will pick up the right cell in the formula.

Named Ranges are also handy for summarizing data quickly or finding a piece of data at the intersection between two ranges. Read on to learn how this works.

But at first you have to open your Excel worksheet:

 

How to name a single cell

1. Click on the cell you want to name (in our example F1)

excel blog1

 

 

 

 

 

 

 

2. Click into the Name Box (to the left of the Formula Bar)

3. Over type the cell reference with the Name for the cell (In our example VAT)

 

Excel blog2

 

 

 

 

 

 

 

 

 

4. Press Enter to confirm the name

Now I have a cell called VAT which can be seen from the drop down arrow in the Name Box

Here you can see that my cursor is in Cell G7 and in the drop down arrow is the name VAT.

To move the cursor to the cell called VAT I can select it from the drop down list.

 

Excel blog3

 

 

 

 

 

 

 

 

Here you can see that I have now jumped to the cell called VAT and this is where the cursor is now positioned.

Now you have given your cell a name. Well done!

In this next section I will show you how you can use this named cell or range of cells in a formula.

 

Using a named cell in a formula

The advantages of a named cell in a formula are:

• it makes it more readable
• you can use it as an Absolute Cell Reference

 

To use the named cell in a formula you just have to follow these simple steps:

ex1

 

 

 

 

 

 
Create the formula as you normally would.

1. Click into the cell where the result will display
2. Type an = sign
3. Click on the first cell to include
4. Type the mathematical Operator ( in our example an Asterix for multiply)
5. Click on the cell with the multiplier in (F1 which is called VAT ) Notice that Excel calls the cell by its name not the cell reference
6. Press Enter

Auto fill the formula

ex2

 

 

 

 

 

 

Use the Auto fill Handle (the little black plus sign in the bottom right hand corner of the cell to copy the formula down all the other rows.

ex3

 

 

 

 

 

 

 

 

Notice that each of the rows has adjusted the first part so B2 then B3 then B4 etc however all of the formula reference the cell called VAT.
Now you know how to name a cell and use it in a formula. I wish you good luck with your next Excel report!