Naming Cells In Microsoft Excel To Then Use In Formulas And Functions


Using Range Names in Microsoft Excel will make it more efficient to navigate an Excel Workbook, but best of all use a Range Name in a function in place of the Cell References. What this does is make a lengthy function much more user friendly by making it easier to read. Take for example a Vlookup which is a popular function that matches data from one list to another then returns specified data from a column in the Master list.

Instead of reading: = Vlookup(A6,StockList!$A$1:$F$300,4,False)

In place of the table item you can use a Range Name like ProductList, notice there is no space in the Name, that’s important.

Vlookup(A6,ProductList,2,False) or = Sum(SalesTotals).

Another idea where you can use Range Names is in Data Validation Lists,  or to make it easier to print reports you could create a Range Name that selects the area to be printed.

  • Excel-Training-Courses-Sydney
  • Excel-Training-Courses-Sydney

Naming Convention Rules

There are rules about Naming Ranges: – No Spaces is the big point to make as most people forget. The Range Names must start with a letter, no symbols, you can use text and numbers, but it can’t be the name of a cell eg: You Can’t use QTR1 as this is a cell. The next big point that people forget is you must press ENTER otherwise you have not completed the steps and your Range Name will not work.

Steps To Creating A Range Name

Select your data, then go to the Name Box (top left hand corner) and type the name you would like to give your Range Name, remember there are rules. Pressing Enter after naming your Range Name correctly is critical.

If you have several range names to create you can create them in one hit. Select the cells – include the headings (rows and columns) then in the Formulas tab, Select Create from Selection. If the heading label starts with a number ie: 2017 Budget, Excel will add the underscore for your automatically.

Microsoft Office Small Group Training Sessions

AZ Solutions Pty Ltd delivers customized training courses here in Sydney – Australia. We come to you. All you need is a board room, PC’s for each student and a TV/ Projector with a HDMI connection cable. In our training sessions you are welcomed to bring examples of your work to class. We prefer it.

Call Now M 0414 417 059 visit www.azsolutions.com.au

About Customised Computer Courses Sydney - Sutherland Shire M 0414 417 059

On-Site customised computer training courses Sydney - Sutherland Shire. Excel, Word,Power Point, Access, Outlook, Visio, Publisher, Excel VBA e: Info@azsolutions.com.au m: 0414 417 059 www.azsolutions.com.au
This entry was posted in Excel and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s