| Using worksheet functions to help manage data might seem, at the outset, a little odd. It's natural to think of functions that you enter on the worksheet to do calculations and return values, such as the average of a set of numbers, a monthly payment amount, or pi. But there is a class of worksheet functions, valuable ones indeed, that help you find data and display it in different configurations. Some of the lookup and reference functions are presented in the following list, along with a description of how they're used. This is intended not as a comprehensive discussion of the functions, but as a brief summary of the sort of thing that's available to you right on the worksheet. ADDRESS returns a cell reference when given a row number and a column number. ADDRESS(3,4) returns $D$3, which is the third row and fourth column. Optional arguments enable you to specify a fixed, mixed, or relative reference; an A1 or R1C1 style; and a worksheet name. AREAS returns the number of areas in (usually) multiple references. An area is a range of contiguous cells. AREAS((A1:C3,E4:G6)) returns 2. The extra pair of parentheses is required with multiple references. If you leave out the extra parentheses--for example, AREAS(A1:C3,E4:G6)--Excel thinks you are providing more than one argument when it expects one argument only. COLUMN returns the column number of a reference. COLUMN(E6) returns 5. COLUMN(F8:G9) returns { 6,7} , but you see only the 6 unless you array-enter the formula in two horizontally adjacent cells. For example, you might select A1:B1, type this formula =COLUMN(F8:G9)
and enter it with Ctrl+Shift+Enter, instead of just pressing the Enter key. COLUMNS returns the number of columns in a reference. COLUMNS(F8:G10) returns 2. GETPIVOTDATA returns a data field value from a pivot table, given the data field's name, a cell reference that identifies the pivot table, a field name, and an item name. In Excel 2002 and 2003, this formula =GETPIVOTDATA("Voters",$A$1,"Affiliation","Independent")
returns information about Voters (the count, perhaps; it depends on what summary statistic you've called for in the pivot table itself) whose Affiliation is Independent, in the pivot table that includes cell $A$1. VLOOKUP finds a value in the first column of a lookup range, and returns the corresponding value in a different column of that range. If A3 contains Smith and C3 contains $5,000, VLOOKUP("Smith",A1:D10,3) returns $5,000. HLOOKUP returns results in much the same way that VLOOKUP does. The difference is that the lookup table is rotated 90 degrees: Instead of occupying, say, A1:B10 as it might if used by VLOOKUP, the table might occupy A1:J2 if used by HLOOKUP. Because Excel's list structure calls for records in rows and fields in columns, you're much more likely to have use for VLOOKUP than for HLOOKUP. INDEX returns a value from a worksheet range (more generally, from an array, which is often a worksheet range). If cell C23 contains the value 3, INDEX(B20:C26,4,2) returns 3. This is the value in the fourth row (row 23) and second column (column C) of the range B20:C26. INDIRECT returns a cell address, given a string. Often, the string is in another cell. If A20 contains the string E28, and cell E28 contains 5, INDIRECT(A20) returns 5. In tandem with ROW or COLUMN, INDIRECT is also a handy way to get an array of integers: ROW(INDIRECT("1:5")) returns { 1;2;3;4;5} . This has broad applicability. Suppose that A1 contains a string of indeterminate length. Then ROW(INDIRECT("1:" & LEN(A1))) returns an array of as many integers as there are characters in A1. If A1 contains "Fred", ROW(INDIRECT("1:" & LEN(A1))) returns { 1;2;3;4}. MATCH returns the position of a value in an array. If A1:A5 contains A, B, C, D, and E, MATCH("C",A1:A5) returns 3--the position that C occupies in the range A1:A5. Contrast this with INDEX: Given a value, MATCH returns a position; given a position, INDEX returns a value. OFFSET returns the contents of a cell or range that is shifted--offset--from another cell, sometimes termed the basis cell. Suppose that cell C4 contains 42. Then OFFSET(A1,3,2) returns 42: the contents of the cell that is offset three rows and two columns from A1, the basis cell. ROW returns the row number of a reference. ROW(E6) returns 6. If you array-enter this formula =ROW(F8:G9)
it will return the array { 8;9} . You would begin by selecting two vertically adjacent cells, such as A1:A2, and type the ROW formula as shown, finishing up with Ctrl+Shift+Enter. TIP Notice how, in contrast to COLUMN, the ROW function returns an array of numbers separated by a semicolon. In general, Excel uses commas to separate cell references that occupy different columns; it uses semicolons to separate cell references that occupy different rows. You can see this directly by selecting a cell that the array formula occupies, and choosing Insert, Function.
ROWS returns the number of rows in a reference. ROWS(F8:G10) returns 3. TRANSPOSE switches the orientation of rows and columns in a range. Except when the original range contains one cell only (and then there's no point to using TRANSPOSE), the function must be array-entered with Ctrl+Shift+Enter. It's much easier to see what TRANSPOSE does than to read it (see Figure 2.1). Figure 2.1. Before you enter the TRANSPOSE function, select a range with as many rows as the original range has columns, and as many columns as the original range has rows. 
With the preceding overview as a backdrop, the next few sections go into considerably more detail about how to use the worksheet functions to help manage data. |
