Cpt 01 Microcomputer Applications Lesson Topic Title: Range Names, Validation, and Lookup Functions Reading

CPT 301 Microcomputer Applications Lesson 3 Topic 2

Title: Range Names, Validation, and Lookup Functions
Reading:
The Excel lessons are not in your textbook. Please read the Range Names and Lookup Functions Activities and complete the steps on your own.

Notes:

Cell range names in Excel may be used in functions and formulas instead of the cell references. Isn’t it easier to understand Rate instead of C12? Watch a video that explains how using range names makes formulas easier to read and understand.

Insert CDrates.jpg Another great use of the range name is defining a print area. Suppose you are tracking Certificate of Deposit (CD) rates from several banks in the area. You have a table for each bank on one worksheet. Instead of looking for the cell addresses for each bank every time you want to print, you could give each cell range a bank’s name and then use the F5 button or Go To command to highlight that bank’s area. For example, to give the cell range A1:E21 the name PNC, highlight A1:E21 and then click in the name box (to the left of the formula bar) and type PNC. Remember to press the Enter key for the name to assign it to the range.

Insert bank_names.jpg To print the PNC table/range, click the down arrow to the right of the name box and click on PNC. You can also press the F5 button on the keyboard and type PNC. Once Excel highlights the range, you can print that selection by clicking the Office button, Print, Selection. Then you can Go To another bank’s area by range name and print it.

If a table or cell range has labels or headings, you can use those labels instead of range names. Excel knows to use the cells defined by row and column headings. Watch a video on calculating with labels and range names.

Insert range_names.jpg Range names may also refer to text, constants, or formulas instead of cells. For instance, suppose you defined the range name City as equal to “Washington, DC”. If you typed =City in a cell, it would display “Washington, DC” in the cell. Range names work the same way if they define the name as formulas. If you named the range Sum and you define it as the formula =SUM(A1:A10), where cells A1:A10 have the number 1 in each cell, the answer would be 10. So, wherever you type =Sum the number 10 would display.

Data Validation

Suppose another employee at the bank where you work calls the other banks to get their CD rates. When he enters the data on the sheet, you want it to be correct. Data validation can help. In this economy, no one is offering a high rate, so you can use data validation to display a message that the rate must be less than or equal to 4%.

Data validation allows you to limit what will be entered into a cell. However, note that someone could override it by using Edit > Clear > Clear All or pasting into the cell. This may be circumvented by protecting the worksheet or cell.

Lookup Functions
The VLOOKUP and HLOOKUP functions help you quickly find information in a spreadsheet. VLOOKUP looks for data in an array (column), while the HLOOKUP searches a vector (row). Both LOOKUP functions require that the first column of the vector (or the first column or row for the array form) is sorted in ascending order, unless you are looking for an exact match or use a range lookup argument.
In the bank CD scenario, suppose you have 10 banks in your market area. It is your job to update the list of CDs each bank offers and their APY (annual percentage yield). Not only will you need to print each list as explained earlier, but you will also create “on-the-fly” reports requested each week by the asset committee.
What if a report was needed to compared the 3 and 6 month certificate rates from the banks that offer them? Instead of you looking at each bank’s list, a VLOOKUP can search for the 3 month CDs in one cell for each bank, and if the cell is not blank, returning the rate. You could add the formula for each bank and as long as you don’t move the location of each list, it will update each week as the rates in the list change. Finding the 6 month CDs would be the same formula, but would look for the 6 month CD and rate.
You could do the same thing in this instance using HLOOKUP. It would look for the Rate column in top row and then pull the rate from the second row. However, each week the list could not change the 3 month Certificate to another row.
Range names, validation, and lookup functions can make it easier to work with large sets of data in Excel. They take a little practice to master, but can help save you time once you’ve figured out how to use them.

Click the Back button on your browser, then click Topic: Get Data From External Sources to continue.