Refer to cells by using Named Ranges

Instructions: You can type values or formulas into cells above but to access Excel functionality, or if it is easier to work directly in Excel, open the file by clicking on the Excel Icon at the bottom.

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

Rather than referring to cells in Excel by their anonymous cell co-ordinates like ‘A5’ or ‘Sales! B2:B10’ we can give the cell (or range of cells) a name and refer to that name in our formulas instead.

The advantages of doing this are that:

• The formulas you write become easier to understand – e.g. rather than writing a formula for Revenue as =D2*E2’ the formula could be something like =Price * Quantity which makes more sense to someone reading it.
• The named ranges become available to view/update globally in one place (in the Name Manager) and become part of the intelli-sense when writing formulas. You can also use them as a Data Validation source for a Drop-down list regardless of which sheet the values are located in.
• You can have Dynamic named ranges which flex either according to the amount of data or on the basis of a value in the workbook.

Note that if you use Tables instead then this automatically creates the appropriate named ranges for you so you don’t need to define these yourself.

To name a cell or a range of cells, click on the cell or range of cells, and in then in the name box to the left of the formula bar (which lists the cell reference e.g. ‘A2’) type over the cell reference with a name.

For example in the Topic Example workbook, select the box to the left of the formula bar and replace the text ‘B2’ with the name Interest_Rate.
Now in cell B5 type in =A5 * Interest_Rate. As you start typing the first few characters of dInterest_Rate, the intelli-sense should pick up the named range. The formula now refers to the value in cell A5 multiplied by the constant Interest_Rate which is a reference to the cell B2. If the value in cell B2 changes then this will be reflected in the values returned in cells B5:B12.

This is shown in the video below:

It is also possible to name an entire column or row as a named range. The advantage of doing this is that whilst having a single cell reference as a named range works like an absolute formula, naming an entire column works like a row-relative formula, and naming an entire row works like a column-relative formula. So for example if column B is named ‘ColBvalues’ then a formula in cell C1 of =A1*colBvalues would be equivalent to =A1*$B1 and the same formula in Cell C2 will be equivalent to =A1*$B2 – i.e. by naming an entire column as a named range, any formula referring to this references the value in that column and the current row thereby adapting as you drag the formula down.

To see an example of this, highlight the whole of Column D on the ‘Named Ranges’ worksheet and then in the Name box replace the text ‘D1’ with the name ‘Quantity’ and then hit Enter.

Note that the name you type in can’t contain spaces or special characters (for where you want multiple words in a name separate these with an underscore instead of a space).

To quickly create named ranges based on headings either contained in the column to the left/right or in the row above/below then you can use the ‘Create from Selection’ option in the Defined Names tab of the Formulas Ribbon.

Having named the whole of Column D as Quantity’, in Cell I3 where we want to have the sum of quantity sold, rather than typing =SUM(D:D) type =SUM(Quantity). To see a list of all the named ranges available to insert one into the formula you’re typing, click on the ‘Use in formula’ button in the ‘Defined Names’ tab of the Formulas Ribbon or use the keyboard shortcut F3

Note that if you already had formulas in the workbook which were referencing the whole of column D and wanted to update these to refer to your new named range instead then you could do this by clicking on the arrow next to ‘Define Name’ in the Formulas ribbon and selecting ‘Apply Names’ and then choosing ‘Quantity’.

If we now populate the ‘Price Per Product’ column with prices (e.g. by using an INDEX/MATCH to look up the relevant price on the ‘Price List’ Tab), and then name the entire column ‘Price_per_Product’ then in Column F we can populate our Revenue Column: rather than having the formula as =D2*E2 we can type in =Quantity*Price_per_Product.

This will take the current row in the Quantity range (Column D) and multiply it by the value in the current row of the Price_per_Product range (column E).

To now see the Named Ranges we’ve created, we can click on ‘Name Manager’ in the Formulas Ribbon. For each Named Range this lists the name, the current value(s), the range it refers to and the scope. From here we can update the named ranges – for example we can edit the named range and update the cell reference it points to and then any formulas which are using this named range will now point to the new location.

We can also create new named ranges from here directly rather than using the Name Box – we might choose to do this if we want to create a named range which has a fixed (constant) value rather than referring to a value in a cell. For example if our key product was always ‘Laptop’ and we wanted to hard-code this into the workbook (rather than have it as a cell value) we could create a new named range called ‘KeyProduct’ and as ‘Refers to’ just type in “Laptop”. We can then refer to this value in any formulas within the workbook. This might be useful if we want to have multiple workbooks all of identical format (with the same formulas) but referencing different static values.

We might also want to create a Named Range in this way if we wanted one with scope ‘Worksheet’ instead of ‘Workbook’. All Named Ranges created using the Name Box have a scope of Workbook which means they can be accessed from anywhere within the workbook and all references to that named range will point to the one single location referenced by the name. Creating one with Scope ‘Worksheet’ means that the Named Range will only be accessible on that sheet and will point to a cell reference on that worksheet. Whilst mostly a global workbook Named Range will be preferable, if you want to have multiple Named Ranges all with the same name but referring to separate values depending on which worksheet it is referenced on, then you should set one up with scope of Worksheet. When you create a worksheet Named Range and then copy the sheet (through right-clicking on a tab and selecting ‘Move or Copy’) then the worksheet named ranges are copied across too – new worksheet named ranges are created with the same name but referencing the new worksheet instead.

Finally it’s worth mentioning that in the Name Manager you can have formulas as well as cell references – for example rather than having our Sales Revenue column as ‘Quantity*Price_per_product’ we could create a named range/formula (e.g. called ‘SalesFormula’) and in the ‘Refers to’ type in ‘=Quantity*Price_per_Product’ – then in Column G we can type a single formula called =SalesFormula. This won’t work in the example we have above because we’ve defined our ‘Quantity’ and ‘Price_per_product’ formulas as an entire column including values in Row 1 which contain text rather than numbers (and hence can’t be multiplied). So instead we can have our SalesFormula refer to =Column Named Ranges’!$D2 * ‘ Column Named Ranges’!$E2 (without the Dollar signs on the row) or change our named ranges to not include any text values.

In the example workbook above, carry out the following steps:

• Create a named range to cell B2 called Interest_rate. In cells B5:B12, write a formula that multiplies the balance (in Column A) by this named range value.
• In the Price list Tab define Column A as a new named range (called e.g. ‘Product’) and Column B as a named Range (called ‘e.g. Price’)
• In the ‘Named Ranges’ tab in Column E, create a lookup formula (using INDEX/MATCH) that references the new named ranges to return the price of the product in Column C
• In the Named Ranges tab, create a Named Range referring to Column D (e.g. as ‘Quantity’) and one referring to Column E (Called e.g. ‘Price_per_Product’).
• In Column F create a formula referencing these named ranges as =Quantity*Price_per_Product. The formula will not work in cell F1 so start the formula from cell F2.
• Create a new Named range from the Name Manager called ‘FirstCell’, define the scope as worksheet referring to =’Named Ranges’!A1.
• In Cell I5, reference this value by typing ‘=FirstCell’. It should display the value ‘Salesperson’
• Copy the ‘Named Ranges’ Tab to another sheet in the same workbook (right-click on the tab and select ‘Move or Copy’ and ensure ‘Copy’ is checked). Call this new sheet ‘Copied Named Ranges Sheet’
• In this new sheet, update the value in cell A1 from ‘Salesperson’ to something else. Your new value should now be reflected in cell I5 of the new sheet whilst the one in the ‘Named Ranges’ tab still refers to the value in that sheet.

A demonstration is shown in the video clip below. Resist the temptation to view the answers file below until you have tried this!

Once you have completed the ‘Try it Out’ exercise, the completed Excel file should look like this:

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.