Examples of Information Functions
Examples of Lookup and Reference Functions
This chapter covers four groups of workhorse functions. If you process spreadsheets of medium complexity, you turn to logical and lookup functions regularly.
The logical functions, including the ubiquitous IF
function, help make decisions.
The information functions might be less important than they once were because Microsoft has added the IFERROR
function, but INFO
, CELL
, and TYPE
still come in handy. The lookup functions include the powerful VLOOKUP
, MATCH
, and INDIRECT
functions. These functions are invaluable, particularly when you are doing something in Excel when it would be better to use Access 2019.
The database functions provide the D
functions, such as DSUM
and DMIN
. Even though these functions fell out of favor with the introduction of pivot tables, they are a powerful set of functions that are worthwhile to master.
Linked Data Types debuted in 2018 with support for Stock Quotes and Geography. Over time, expect Excel to support more data types. This chapter ends with a discussion of formulas to pull data from Linked Data Types.
Table 9.1 provides an alphabetical list of all the logical functions in Excel 2019. Detailed examples of these functions are provided later in this chapter.
Function |
Description |
---|---|
|
Returns |
|
Returns the logical value |
|
Returns one value if a condition specified evaluates to |
|
Returns |
|
Returns |
|
Checks whether one or more conditions are met and returns a value corresponding to the first TRUE condition. |
|
Reverses the value of its argument. You use |
|
Returns |
|
Evaluates an expression against a list of values and returns the result corresponding to the first matching value. If there is no match, an optional default value is returned. |
|
Returns the logical value |
|
Returns the logical Exclusive Or of the arguments. However, to be compatible with an XOR chip frequently used in electrical engineering, this function actually measures if an odd number of arguments are |
Table 9.2 provides an alphabetical list of the information functions in Excel 2019. Detailed examples of these functions are provided in the remainder of the chapter.
Function |
Description |
---|---|
|
Returns information about the formatting, location, or contents of the upper-left cell in a reference. |
|
Returns a number corresponding to one of the error values in Microsoft Excel or returns an |
|
Returns information about the current operating environment. |
|
Returns |
|
Returns |
|
Returns |
|
Returns |
|
Checks whether a reference is to a cell containing a formula and returns |
|
Returns |
|
Returns |
|
Returns |
|
Returns |
|
Returns |
|
Returns |
|
Returns |
|
Returns a |
|
Returns the error value |
|
Returns the sheet number of the referenced sheet. |
|
Returns the number of sheets in a reference. |
|
Returns the type of |
Table 9.3 provides an alphabetical list of the lookup functions in Excel 2019. Detailed examples of these functions are provided later in this chapter.
Function |
Description |
---|---|
|
Creates a cell address as text, given specified row and column numbers. |
|
Returns the number of areas in a reference. An area is a range of contiguous cells or a single cell. |
|
Uses |
|
Returns the column number of the given reference. |
|
Returns the number of columns in an array or a reference. |
|
Filter a range or array. Office 365 exclusive. |
|
Returns a formula as a string. |
|
Returns data stored in a pivot table report. You can use |
|
Searches for a value in the top row of a table or an array of values, and then returns a value in the same column from a row you specify in the table or array. You use |
|
Creates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet. When you click the cell that contains the |
|
Returns the value of a specified cell or array of cells within the |
|
Returns a reference to a specified cell or cells within the |
|
Returns the reference specified by a text string. References are evaluated immediately to display their contents. You use |
|
Returns a value from either a one-row or one-column range. This vector form of |
|
Returns a value from an array. The array form of |
|
Returns the relative position of an item in an array that matches a specified value in a specified order. You use |
|
Returns a reference to a range that is a specified number of rows and columns away from a cell or range of cells. The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and the number of columns to be returned. |
|
Returns the row number of a reference. |
|
Returns the number of rows in a reference or an array. |
|
Retrieves real-time data from a program that supports COM automation. |
|
Returns a single value when given a value, range or array. Used instead of implicit intersection. Office 365 exclusive. |
|
Sorts a range or array. Office 365 exclusive. |
|
Sorts a range or array based on the values in a corresponding range or array. Office 365 exclusive. |
|
Returns a vertical range of cells as a horizontal range, or vice versa. |
|
Returns the unique values from a range or array. Office 365 exclusive. |
|
Searches for a value in the leftmost column of a table and then returns a value in the same row from a column you specify in the table. You use |
Table 9.4 provides an alphabetical list of all the database functions in Excel 2019. Detailed examples of these functions are provided later in this chapter.
Function |
Description |
---|---|
|
Averages the values in a column in a list or database that match the conditions specified. |
|
Counts the cells that contain numbers in a column in a list or database that match the conditions specified. |
|
Counts all the nonblank cells in a column in a list or database that match the conditions specified. |
|
Extracts a single value from a column in a list or database that matches the conditions specified. If multiple matches are found, returns |
|
Returns the largest number in a column in a list or database that matches the conditions specified. |
|
Returns the smallest number in a column in a list or database that matches the conditions specified. |
|
Multiplies the values in a column in a list or database that match the conditions specified. |
|
Estimates the standard deviation of a population based on a sample, using the numbers in a column in a list or database that match the conditions specified. |
|
Calculates the standard deviation of a population based on the entire population, using the numbers in a column in a list or database that match the conditions specified. |
|
Adds the numbers in a column in a list or database that match the conditions specified. |
|
Estimates the variance of a population based on a sample, using the numbers in a column in a list or database that match the conditions specified. |
|
Calculates the variance of a population based on the entire population, using the numbers in a column in a list or database that match the conditions specified. |
With only eight functions, the logical function group is one of the smallest in Excel. The IF
function is easy to understand, and it enables you to solve a variety of problems.
IF
Function to Make a DecisionMany calculations in our lives are not straightforward. Suppose that a manager offers a bonus program if her team meets its goals. Or perhaps a commission plan offers a bonus if a certain profit goal is met. You can solve these types of calculations by using the IF
function.
IF(logical_test,value_if_true,value_if_false)
There are three arguments in the IF
function. The first argument is any logical test that results in a TRUE
or FALSE
. For example, you might have logical tests such as these:
A2>100 B5="West" C99<=D99
All logical tests involve one of the comparison operators shown in Table 9.5.
Comparison Operator |
Meaning |
Example |
---|---|---|
= |
Equal to |
C1=D1 |
> |
Greater than |
A1>B1 |
< |
Less than |
A1<B1 |
>= |
Greater than or equal to |
A1>=0 |
<= |
Less than or equal to |
A1<=99 |
<> |
Not equal to |
A2<>B2 |
The remaining two arguments are the formula or value to use if the logical test is TRUE
and the formula or value to use if the logical test is FALSE
.
When you read an IF
function, you should think of the first comma as the word then and the second comma as the word otherwise. For example, =IF(A2>10,25,0)
would be read as “If A2>10, then 25; otherwise, 0.”
Figure 9.1 calculates a sales commission. The commission rate is 1.5 percent of revenue. However, if the gross profit percentage is 50% or higher, the commission rate is 2.5 percent of revenue.
Note
Mathematicians would correctly note that in both the second and third arguments of the formula =IF(H2>=50%,0.025*F2,0.015*F2)
, you are multiplying by F2
. Therefore, you could simplify the formula by using =IF(H2>=50%,0.025,0.015)*F2
.
In this case, the logical test is H2>=50%
. The formula for whether that test is true is 0.025*F2
. Otherwise, the formula is 0.015*F2
. You could build the formula as =IF(H2>=50%,0.025*F2,0.015*F2)
.
AND
Function to Check for Two or More ConditionsThe previous example had one simple condition: If the value in column H was greater than or equal to 50%, the commission rate changed.
However, in many cases, you might need to test for two or more conditions. For example, suppose that a retail store manager offers a $25 bonus for every leather jacket sold on Fridays this month. In this case, the logical test requires you to determine whether both conditions are true. You can do this with the AND
function.
AND(logical1,logical2,...)
The arguments logical1,logical2,...
are from one to 255 expressions that evaluate to either TRUE
or FALSE
. The function returns TRUE
only if all arguments are TRUE
.
In Figure 9.2, the function in cell F2 checks whether cell E2 is a jacket and whether the date in cell D2 falls on a Friday:
=AND(E2="Jacket",WEEKDAY(D2,2)=5)
OR
to Check Whether One or More Conditions Are MetIn the earlier examples, all the conditions had to be met for the IF
function to be true. In other cases, you might need to identify when exactly one condition is true, or when one or more conditions are true.
For example, a sales manager may want to reward big orders and orders from new customers. The manager may offer a commission bonus if the order is more than $50,000 or if the customer is a new customer this year. The bonus is awarded if either condition is true. But only one bonus is paid; you do not give two bonuses if a customer is both new and the order is large. In this case, you would use the OR
function with logical tests to check whether the customer is new or if the order is large.
To test whether a particular sale meets either condition, use the OR
function. The OR
function returns TRUE
if any condition is TRUE
and returns FALSE
if none of the conditions are TRUE
.
OR(logical1,logical2,...)
The OR
function checks whether any of the arguments are TRUE
. It returns a FALSE
only if all the arguments are FALSE
. If any argument is TRUE
, the function returns TRUE
.
The arguments logical1,logical2,...
are 1 to 255 conditions that can evaluate to TRUE
or FALSE
.
IF
Functions Versus IFS SWITCH
or CHOOSE
The IF
function offers only two possible values: Either the logical test is TRUE,
and the first formula or value is used, or the logical test is FALSE
and the second formula or value is used.
Many situations have a series of choices. For example, in a human resources department, annual merit raises might be given based on the employee’s numeric rating in an annual review in which employees are ranked on a five-point scale. The rules for setting the raise are as follows:
5: 8% raise
4: 7% raise
3: 5% raise
2: 3% raise
1: No raise
Traditionally, you would test for five conditions by nesting four IF functions:
=IF(D2=5,8%,IF(D2=4,7%,IF(D2=3,5%,IF(D2=2,3%,0%))))
You only needed four IF functions to test for five conditions. After testing for the first four conditions, the fifth answer would be provided in the Value_If_False
for the last IF function.
In February 2017, Office 365 customers were offered two alternatives:
=IFS(D2=5,8%,D2=4,7%,D2=3,5%,D2=2,3%,TRUE,0%) =SWITCH(D2,5,8%,4,7%,3,5%,2,3%,0%)
IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2…])
SWITCH(Expression, Value1, Result1,[Default_or_value2],[Result2]…)
In the IFS function, you can handle multiple conditions without nesting new functions. The IFS means that you have multiple IF conditions. The IFS formula above reads, “If D2 is 5, then return 8%. Otherwise, if D2=4, then return 7%. Otherwise, if D2=3, then return 5%. Otherwise, if D2=2, then return 3%.” The last two arguments in IFS are a little bizarre. You essentially want to have a value to return if none of the previous conditions are true. You need to put a logical test that is always True. Explicitly typing TRUE solves the problem.
The SWITCH function is better, in this case, because you only specify cell D2 once. You tell the SWITCH function that you want to return a value based on the value of D2. If it is 5, then 8%. If it is 4, then 7%. If it is 3, then 5%. If it is 2, then 3%. For any other value, use 0%. Note that SWITCH does not require you to enter the TRUE argument as the second-to-last argument.
In this particular case, because the five possible scores are 1 through 5, the CHOOSE function will be shortest:
=CHOOSE(D2,0,3%,5%,7%,8%)
The CHOOSE function points to a single value and then expects the value to return if the answer is 1, 2, 3, 4, 5, and so on.
Note that Excel classifies CHOOSE as a Lookup and Reference function instead of a Logical function.
Read more about CHOOSE in “Examples of Lookup and Reference Functions” on page 244.
Figure 9.3 compares the four formulas. In this case, CHOOSE is the shortest.
Caution
These IF
formulas are hard to read. There is a temptation to use them for situations with very long lists of conditions. Whereas Excel 2003 prevented you from nesting more than seven levels of IF
functions, Excel 2007 and later allow you to nest up to 64 IF
statements. Before you start nesting that many IF
statements, you should consider using VLOOKUP
, which is explained later in this chapter.
CHOOSE will not always be the shortest formula. SWITCH will win if you have to look for values that are not sequential or don’t have to start with 1:
=SWITCH(A2,30,"CURRY",35,"DURANT",23,"GREEN",11,"THOMPSON","OTHER")
IFS will be better if you need to look for ranges of values:
=IFS(A2>80,"Top Tier",A2>50,"Group 2",A2>20,"Group 3",TRUE,"Bottom Tier")
Nested IF will be better if there is any chance the workbook will be opened in Excel 2016, Excel 2013, Excel 2010, or an earlier version. The new IFS and SWITCH functions will return #NAME? error if opened in a prior version of Excel.
NOT
Function to Simplify the Use of AND
and OR
In the language of Boolean logic, there are typically NAND
, NOR
, and XOR
functions, which stand for Not And, Not Or, and Exclusive Or. To simplify matters, Excel offers the NOT
function.
NOT(logical)
Quite simply, NOT
reverses a logical value. TRUE
becomes FALSE
, and FALSE
becomes TRUE
when processed through a NOT
function.
For example, suppose you need to find all flights landing outside Oklahoma. You can build a massive OR
statement to find every airport code in the United States. Alternatively, you can build an OR
function to find Tulsa and Oklahoma City and then use a NOT
function to reverse the result: =NOT(OR(A2="Tulsa",A2="Oklahoma City"))
.
IFERROR
or IFNA
Function to Simplify Error CheckingThe IFERROR
function, which was introduced in Excel 2007, was added at the request of many customers. To better understand the IFERROR
function, you need to understand how error checking was performed during the 22 years before Excel 2007 was released.
Consider a typical spreadsheet that calculates a ratio of sales to hours. A formula of =B2/C2
returns the #DIV/0
error in the records when column C contains a zero. The typical workaround is to test for the error condition: =IF(C2=0,0,B2/C2)
.
In legacy versions of Excel, it was typical to use this type of IF
formula on thousands of rows of data. The formula is more complex and takes longer to calculate than the new IFERROR
function. However, this particular formula is tame compared to some of the formulas needed to check for errors.
A common error occurs when you use the VLOOKUP
function to retrieve a value from a lookup table. In Figure 9.4, the VLOOKUP
function in cell D2 asks Excel to look for the rep number S07 from cell B2 and find the corresponding name in the lookup table of F2:G9. This works great, returning JESSE
from the table. However, a problem arises when the sales rep is not found in the table. In row 7, rep S09 is new and has not yet been added to the table, so Excel returns the #N/A
result.
If you want to avoid #N/A
errors, the generally accepted workaround in legacy versions of Excel was to write this horrible formula:
=IF(ISNA(VLOOKUP(B7,$F$2:$G$9,2,FALSE)),"New Rep", VLOOKUP(B7,$F$2:$G$9,2,FALSE))
In English, this formula says first to find the rep name in the lookup table. If the rep is not found and returns the #N/A
error, then use some other text, which in this case is the words New Rep. If the rep is found, then perform the lookup again and use that result.
Because VLOOKUP
was one of the most time-intensive functions, it was horrible to have Excel perform every VLOOKUP
twice in this formula. In a data set with 50,000 records, it could take minutes for the VLOOKUP
to complete. Microsoft wisely added the new IFERROR
function in Excel 2010 to handle all these error-checking situations.
Starting in Excel 2013, Microsoft has added the IFNA
function. It works just like the IFERROR
function, but the second argument is used only when the first argument results in an #N/A
error. You might be able to imagine a situation in which you want to replace the #N/A
errors but allow other errors to appear.
IFERROR(value,value_if_error)
The advantage of the IFERROR
function is that the calculation is evaluated only once. If the calculation results in any type of an error value, such as #N/A
, #VALUE!
, #REF!
, #DIV/0!
, #NUM!
, #NAME?
, or #NULL!
, Excel returns the alternative value. If the calculation results in any other valid value, whether it is numeric, logical, or text, Excel returns the calculated value.
IFNA(value,value_if_na)
If the expression evaluates to a value of #N/A
, then IFNA
returns value_if_na
instead of the expression. Added in Excel 2013, this function replaces only #N/A
errors and allows other errors to appear as the result.
The formula from the preceding section can be rewritten as =IFERROR(VLOOKUP(B7, $F$2:$G$9,2,FALSE),"New Rep")
or as =IFNA(VLOOKUP(B7, $F$2:$G$9,2,FALSE),"New Rep")
. Although IFNA
is a bit shorter than IFERROR
, the new IFNA
function fails for anyone using Excel 2010 or earlier. This makes IFERROR
a safer function to use for the next several years. Either IFERROR
or IFNA
calculates much more quickly than putting two VLOOKUP
s in an IF
function.
Found under the More Function icon, the 20 information functions return eclectic information about any cell. Eleven of the 20 functions are called the IS
functions because they test for various conditions.
ISFORMULA
Function with Conditional Formatting to Mark Formula CellsThe Excel team introduced the ISFORMULA
function in Excel 2013 to identify whether a cell contains a formula. A hack had been floating around for years to mark formula cells using an old XL4 Macro Language function. Being able to use ISFORMULA
is a great improvement.
ISFORMULA(reference)
Checks whether reference
contains a formula. Returns TRUE
or FALSE
.
Figure 9.5 shows a worksheet in which all the cells have a conditional formatting formula that uses =ISFORMULA
. Any cells that contain a formula are shown in white text on black fill.
IS
Functions to Test for Types of ValuesThe remaining IS
functions enable you to test whether a cell contains numbers, text, or various other data types.
Figure 9.6 shows a common solution. Column C contains a mix of text and numeric ZIP Codes. The formula in column D, =IF(ISNONTEXT(C5),RIGHT("0000"&C5,5),C5)
, replaces numeric ZIP Codes with text ZIP Codes. If the value in column C is nontext, the program pads the left side of the ZIP Code with zeros and then takes the five rightmost digits.
N
Function to Add a Comment to a FormulaYou can call Excel’s N
function a creative use for an obsolete function. Lotus 1-2-3 used to offer an N()
function that converted True to 1 and False to 0. The N of any text is zero. Some have figured they could use this function to add a comment to a formula:
=VLOOKUP(A2,MyTable,2,False)+N("The False ensures an exact match. Don't omit False")
NA
Function to Force Charts to Not Plot Missing DataSuppose that you are in charge of a school’s annual fund drive. Each day, you mark the fundraising total on a worksheet by following these steps:
In column A, you enter the results of each day’s collection through nine days of the fund drive (see Figure 9.7).
You enter a formula in column C to keep track of the total collected throughout the fund drive.
To avoid making it look like the fund drive collected nothing in days 10 through 14, you enter a formula in column C to check whether column A is blank. If it is, then the IF
function inserts a null cell in column C. For example, the formula in cell C15 is =IF(ISBLANK(A15),"",A15+C14)
.
You build a line chart based on B1:C15. You then add a trendline to the chart to predict future fundraising totals.
As shown in columns A:C of Figure 9.7, this technique fails. Even though the totals for days 10 through 14 are blank, Excel charts those days as zero. The linear trendline predicts that your fundraising will go down, with a projected total of just over $2,000.
You try the same chart again, but this time you use the NA
function instead of ""
in the IF
statement in step 3. The formula is shown in cell H16, and the results are in cell J15. Excel understands that NA
values should not be plotted. The trendline is calculated based on only the data points available and projects a total just under $18,000.
In many cases, you are trying to avoid #N/A!
errors. However, in the case of charting a calculated column, you might want to have #N/A!
produce the correct look to the chart.
CELL
Function to Return the Worksheet NameThe CELL
function can tell you information about a specific cell. Although the function can return many ancient bits of information (Excel 2003 color index, for example), it has one argument that allows you to put the worksheet name in a cell.
=CELL("filename",A1)
returns the complete path, filename, and worksheet name. The technique is to locate the right square bracket at the end of the filename. Everything after that character is the worksheet tab name.
Figure 9.8 shows an example.
The Lookup & Reference icon contains 18 functions. The all-star of this group is the venerable VLOOKUP
function, which is one of the most powerful and most used functions in Excel. As database people point out, a lot of work done in Excel should probably be done in Access. The VLOOKUP
function enables you to perform the equivalent of a join operation in a database.
This lookup and reference group also includes several functions that seem useless when considered alone. However, when combined, they allow for some very powerful manipulations of data. The examples in the following sections reveal details on how to use the lookup functions and how to combine them to create powerful results.
CHOOSE
Function for Simple LookupsMost lookup functions require you to set up a lookup table in a range on the worksheet. However, the CHOOSE
function enables you to specify up to 254 choices right in the syntax of the function. The formula that requires the lookup should be able to calculate an integer from 1 to 254 to use the CHOOSE
function.
CHOOSE(index_num,value1,value2,...)
The CHOOSE
function chooses a value from a list of values, based on an index number. The CHOOSE
function takes the following arguments:
index_num
—This specifies which value argument is selected. index_num
must be a number between 1 and 254 or a formula or reference to a cell containing a number between 1 and 254:
If index_num
is 1
, CHOOSE
returns value1
; if it is 2
, CHOOSE
returns value2
; and so on.
If index_num
is a decimal, it is rounded down to the next lowest integer before being used.
If index_num
is less than 1 or greater than the number of the last value in the list, CHOOSE
returns a #VALUE!
error.
value1,value2,...
—These are 1 to 254 value arguments from which CHOOSE
selects a value or an action to perform based on index_num
. The arguments can be numbers, cell references, defined names, formulas, functions, or text.
The example in Figure 9.9 shows survey data from some respondents. Columns B:F indicate their responses on five measures of your service. Column G calculates an average that ranges from 1 to 5. Suppose that you want to add words to column H to characterize the overall rating from the respondent. The following formula is used in cell H4:
=CHOOSE(G4,"Strongly Disagree","Disagree","Neutral","Agree","Strongly Agree")
VLOOKUP
with TRUE
to Find a Value Based on a RangeVLOOKUP
stands for vertical lookup. This function behaves differently, depending on the fourth parameter. This section describes using VLOOKUP
in which you need to choose a value based on a table that contains ranges.
Suppose that you have a list of students and their scores on a test. The school grading scale is based on these ranges:
92–100 is an A.
85–91 is a B.
70–84 is a C.
65–69 is a D.
Below 65 is an F.
Follow these steps to set up a VLOOKUP
for this scenario:
Because in this version of VLOOKUP
you do not have to list every possible grade, build a table showing the scores where the grading scale changes from one grade to the next.
Although the published grading scale starts with the higher values, your lookup table must be sorted in ascending sequence. This requires a bit of translation as you set up the table. Although the grading scale says that below 65 is an F, you need to set up the table to show that an F corresponds to any grade at 0 or higher. Therefore, in cell E2 enter 0
, and in cell F2, enter F
(see Figure 9.10).
Continue building the grading scale in successive rows of columns E and F. Anything above a 65 is given a D. Anything above 70 is given a C. Note that this is somewhat counterintuitive because it is the opposite order that you would use if you were building a grading scale using nested IF
functions.
Ensure that the numeric values are the leftmost column in your lookup table. In Figure 9.10, the lookup table range is E2:F6. When you use VLOOKUP
, Excel searches the first column of the lookup table for the appropriate score.
When using this version of VLOOKUP
with ranges, sort the list in ascending order. If you are not sure of the proper order, use the Sort command from the Home tab to sort the table.
Because the first argument in the VLOOKUP
function is the student’s score, in cell C2, enter =VLOOKUP(B2,
.
Because the next argument is the range of the lookup table, be sure to press the F4 key after entering E2:F6
to change to an absolute reference of $E$2:$F$6
.
Ensure that the third argument specifies which column of the lookup table should be returned. Because the letter grade is in the second column of E2:F6, use 2
for the third argument.
Ensure that the final argument is either TRUE
or simply omitted. This tells Excel that you are using the sorted range variety of lookup.
After you enter the formula in cell C2, again select cell C2 and double-click the fill handle to copy the formula down to all students.
VLOOKUP
with FALSE
to Find an Exact ValueIn some situations, you do not want VLOOKUP
to return a value based on a close match. Instead, you want Excel to find the exact match in the lookup table.
Figure 9.11 shows a table of sales. The original table had columns A through C: Rep, Date, and Sale Amount. Although a data analyst might have all the rep numbers memorized, the manager who is going to see the report prefers to have the rep names on the report.
To fill in the rep names from a lookup table, you follow these steps:
In columns F and G, enter a table of rep numbers and rep names. Note that it is not important for this table to be sorted by the rep number field. It is fine that the table is sorted alphabetically by name.
Use FALSE
as the fourth parameter in VLOOKUP
. You need to do this because close matches are not acceptable here. If something was sold by a new rep with number R9, you do not want to give credit to the name associated with R8 just because it is a close match. Either Excel finds an exact match and returns the result, or Excel does not give you a result.
For cell D2, you want Excel to use the rep number in A2, so in cell D2, enter =VLOOKUP(A2,
.
The lookup table is in F2:G7, so enter F2:G7
and then press the F4 key to make the reference absolute. This enables you to copy the formula in step 7. After pressing F4, type a comma.
In the lookup table, the rep name is in column 2 of the table, so type 2
to specify that you want to return the second column of the lookup table.
Finish the function with FALSE)
. Press Ctrl+Enter to accept the formula and keep the cursor in cell D2.
Double-click the fill handle to copy the formula down to all the rows.
VLOOKUP
is a very time-intensive calculation. Having thousands of VLOOKUP
formulas significantly affects your recalculation times. In this particular case, you have successfully added rep names. It would be appropriate to convert these live formulas to their current values. Therefore, press Ctrl+C to copy. Then, from the Home tab, select Paste, Paste Values to convert the formulas to values.
Note
If your lookup table is arranged sideways, with going across a row, you should use HLOOKUP
. If your data is vertical, but the key field is not the leftmost column, you can use a combination of INDEX
and MATCH
, also explained later in this chapter.
Look through the results. If a sale was credited to a new rep who is not in the table, the name appears as #N/A
. Manually fix these records, if needed.
To recap, the two versions of the VLOOKUP
formula behave very differently. VLOOKUP
with FALSE
as the fourth parameter looks for an exact match, whereas VLOOKUP
with TRUE
as the fourth parameter looks for the closest (lower) match. In the TRUE
version, the lookup table must be sorted. In the FALSE
version, the table can be in any sequence. In every case, the key field must be in the left column of the lookup table.
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
VLOOKUP
searches for a value in the leftmost column of a table and then returns a value in the same row from a column you specify in the table. The VLOOKUP
function takes the following arguments:
lookup_value
—This is the value to be found in the first column of the table. lookup_value
can be a value, reference, or text string.
table_array
—This is the table of information in which data is looked up. You can use a reference to a range, such as E2:F9
, or a range name such as RepTable
.
col_index_num
—This is the column number in table_array
from which the matching value must be returned. A col_index_num
value of 1
returns the value in the first column in table_array
; a col_index_num
value of 2
returns the value in the second column in table_array
, and so on. If col_index_num
is less than 1
, VLOOKUP
returns the #VALUE!
error value; if col_index_num
is greater than the number of columns in table_array
, VLOOKUP
returns the #REF!
error value.
range_lookup
—This is a logical value that specifies whether VLOOKUP
should find an exact match or an approximate match. If it is TRUE
or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value
is returned. If it is FALSE
, VLOOKUP
finds an exact match. If one is not found, the error value #N/A
is returned. If VLOOKUP
cannot find lookup_value
and if range_lookup
is TRUE
, it uses the largest value that is less than or equal to lookup_value
. If lookup_value
is smaller than the smallest value in the first column of table_array
, VLOOKUP
returns an #N/A
error. If VLOOKUP
cannot find lookup_value
, and range_lookup
is FALSE
, VLOOKUP
returns an #N/A
error.
VLOOKUP
to Match Two ListsIf Excel is used throughout your company, you undoubtedly have many lists in Excel. People use Excel to track everything. How many times are you faced with a situation in which you have two versions of a list and you need to match them up?
In Figure 9.12, the worksheet has two simple lists. Column A shows last week’s version of who was coming to an event. Column C shows this week’s version of who is coming to an event. Follow these steps if you want to find out quickly if anyone is new:
Add the heading There?
to cell D2.
Because the formula in cell D3 should look at the value in cell C3 to see whether that person is in the original list in column A, start the formula with =VLOOKUP(C3,$A$3:$A$15,
.
Because your only choice for the column number is to return the first column from the original list, finish the function with 1,FALSE)
. Then press Ctrl+Enter to accept the formula and stay in cell D3.
Double-click the fill handle to copy the formula down to all rows.
For any cells in which column D contains a name, it means that the person was on the RSVP list from last week. If the result of the VLOOKUP
is #N/A
, you know that this person is new since the previous week.
Tip
If you study the data in Figure 9.12, you see that three more names are in the column C list than in the column A list, yet four people were reported as being new this week. This means that one of the people from last week has dropped off the list. To quickly find who dropped off the list, use the formula =VLOOKUP(A3,$C$3:$C$18,1,FALSE)
in B3:B15 to find that Donald Tyler has dropped off the list.
Note that you can also use MATCH
to solve this problem.
MATCH
Function to Locate the Position of a Matching ValueAt first glance, MATCH
seems like a function that would rarely be useful. MATCH
returns the relative position of an item in a range that matches a specified value in a specified order. You use MATCH
instead of one of the lookup functions when you need the position of an item in a range instead of the item itself.
Suppose that your manager asks, “Can you tell me on which row I would find this value?” The manager wants to know the value or some piece of data on that record. However, rarely would the manager want to know that XYZ is found on the 111th relative row within the range A99:A11432.
MATCH
comes in handy in several instances. In the first instance, consider a situation in which you are using VLOOKUP
to find whether an item is in a list. In this case, you do not care what value is returned. You are interested in seeing either whether a valid value is returned, meaning that the entry is in the old list, or whether an #N/A
is returned, meaning that the entry is new. In this case, using MATCH
is a slightly faster way to achieve the same result.
Another handy way to use MATCH
is with the INDEX
function. MATCH
has two features that make it more versatile than VLOOKUP
. MATCH
allows for wildcard matches. MATCH
also allows for a search based on an exact match, based on the number just below the value, or based on a value greater than or equal to the lookup value. This third option is not available in the VLOOKUP
or HLOOKUP
functions.
MATCH(lookup_value,lookup_array,match_type)
The MATCH
function returns the relative position of an item in a column or row of values. It is useful for determining if a certain value exists in a list.
The MATCH
function takes the following arguments:
lookup_value
—This is the value you use to find the value you want in a table. lookup_value
can be a value, which is a number, text, or logical value or a cell reference to a number, text, or logical value.
lookup_array
—This is a contiguous range of cells that contains possible lookup values. lookup_array
can be an array or an array reference.
match_type
—This is the number –1, 0, or 1. Note that you can use TRUE
instead of 1 and FALSE
instead of 0. match_type
specifies how Microsoft Excel matches lookup_value
with values in lookup_array
. If match_type
is 1, MATCH
finds the largest value that is less than or equal to lookup_value
. lookup_array
must be placed in ascending order, such as –2, –1, 0, 1, 2,...; A–Z; or FALSE
, TRUE
. If match_type
is 0, MATCH
finds the first value that is exactly equal to lookup_value
. lookup_array
can be in any order. If match_type
is –1, MATCH
finds the smallest value that is greater than or equal to lookup_value
. lookup_array
must be placed in descending order, such as TRUE
, FALSE
; Z–A; or 2, 1, 0, –1, –2,... If match_type
is omitted, it is assumed to be 1.
MATCH
returns the position of the matched value within lookup_array
, not the value itself. For example, MATCH("b",{"a","b","c"},0)
returns 2
, the relative position of b
within the array {"a","b","c"}
.
MATCH
does not distinguish between uppercase and lowercase letters when matching text values. If MATCH
is unsuccessful in finding a match, it returns an #N/A
error.
If match_type
is 0
and lookup_value
is text, lookup_value
can contain the wildcard characters asterisk (*) and question mark (?). An asterisk matches any sequence of characters; a question mark matches any single character.
MATCH
to Compare Two ListsYou might face situations in which you have two versions of a list, and you need to match them up.
In Figure 9.13, the worksheet has two simple lists. Column A shows last week’s list. Column C shows this week’s version of the list. You want to find out quickly which items are new. Here’s how you do it:
Add the heading There?
to cell D2.
Because the formula in cell D3 looks at the value in cell C3 to see if that value is in the original list in column A, start the formula with =MATCH(C3,$A$3:$A$11,
.
Because you want an exact match, use 0
as the third parameter. Finish the function with a )
. Press Ctrl+Enter to accept the formula and stay in cell C3.
Double-click the fill handle to copy the formula down to all rows.
For any cells in which column D contains a number, it means that the entry was on the original list from last week. If the result of MATCH
is #N/A
, you know that this item is new since the previous week.
INDEX
and MATCH
for a Left LookupINDEX
is another function that does not immediately seem to have many great uses. In its basic form, INDEX
returns the value from a particular row and column of a rectangular range. It returns a value from a particular position of a vertical or horizontal vector.
Typically, you specify a rectangular range and then indicate the row number and column number of the value that you want to return. In Figure 9.14, the formula in C3 returns the third row and second column of B5:D9. Certainly, this is a needlessly complicated way to point to cell C7.
INDEX
becomes interesting when you have a formula calculating the position argument. Still in Figure 9.14, a list of people is in M1:M7. You can randomly select from the list by using INDEX
and RANDBETWEEN(1,7)
, as shown in C4.
If you specify zero as the row or column argument, INDEX
returns the entire row or column. The INDEX
in H8 is returning all three values from row 4 of the table, so you have to wrap the index function in a SUM
or COUNT
or AVERAGE
function.
The data in row 14 illustrates an undocumented feature of INDEX
. When the reference contains data in a single row, you can specify the column number as the second argument. To get the data for September, you can use the correct =INDEX(A14:L14,0,9)
or the shortened =INDEX(A14:L14,9)
. In Figure 9.14, the formula in C11 returns the value from the current month by using =MONTH(TODAY())
to return a 9
as the second argument of the INDEX
function. (This was written in September, hence the 9.0.)
You’ve reached Excel guru status when you start combining INDEX
and MATCH
. On its own, neither INDEX
nor MATCH
seems particularly useful. Used together, though, they become a powerful combination that is more flexible than VLOOKUP
and often faster to calculate than VLOOKUP
.
In Figure 9.15, a customer number is in cell B1. The customer lookup table appears in columns F, G, and H. The main problem is that the customer table does not have the customer number on the left side.
In many cases, you would copy column H to column E and use column E as the key of the table. However, the table in F:H is likely to be repopulated every day from a web query or an OLAP query. Therefore, it might become monotonous to move the data after every refresh. The solution is to use a combination of INDEX
and MATCH
. Here’s what you do:
Use the formula =MATCH(B1,H2:H89,0)
to search through column H to find the row with the customer number that matches the one in cell B1. In this case, C499 is in row 9, which is the eighth row of the table.
Be sure to use exactly the same shape range as the first argument in the INDEX
function: =INDEX(F2:F89,WhichRow,WhichColumn)
searches through the customer names in column F.
For the second parameter of the INDEX
function, specify the relative row number. This information was provided by the MATCH
function in step 1.
Ensure that the third parameter of the INDEX
function is the relative column number. Because the range F2:F89 has only one column, this is either 1 or can simply be omitted.
Putting the formula together, the formula in cell B2 is =INDEX(F2:F89,MATCH(B1,H2:H89,0),1)
.
INDEX(array,row_num,[column_num]) INDEX(reference,row_num,[column_num],[area_num])
The INDEX
function returns the value at the intersection of a particular row and column within a range. This function takes the following arguments:
array
—This is a range of cells or an array constant. If array
contains only one row or column, the corresponding row_num
or column_num
argument is optional. If array
has more than one row and more than one column, and if only row_num
or column_num
is used, INDEX
returns an array of the entire row or column in array
.
row_num
—This selects the row in array
from which to return a value. If row_num
is omitted, column_num
is required.
column_num
—This selects the column in array
from which to return a value. If column_num
is omitted, row_num
is required.
If both the row_num
and the column_num
arguments are used, INDEX
returns the value in the cell at the intersection of row_num
and column_num
.
If you set row_num
or column_num
to 0
, INDEX
returns the array of values for the entire column or row, respectively. To use values returned as an array, you use the INDEX
function as an array formula in a horizontal range of cells for a row and in a vertical range of cells for a column. To enter an array formula, you press Ctrl+Shift+Enter. Thanks to modern arrays introduced in September 2018, customers using Office 365 will not need to press Ctrl+Shift+Enter.
row_num
and column_num
must point to a cell within array
; otherwise, INDEX
returns a #REF!
error.
MATCH
and INDEX
to Fill a Wide TableThe lookup functions VLOOKUP
, HLOOKUP
, and MATCH
can be very processor intensive when the lookup table contains hundreds of thousands of rows. The problem is worse when you have to return multiple columns from the same row of the lookup table. If it takes Excel 3 seconds to find the matching row for column 2 of the table, it will take another 3 seconds to find the matching row for column 3 of the table. If you hope to return 12 monthly columns, it could take 36 seconds.
Instead, you could find the matching row once using a MATCH
function in a helper column. After the MATCH
identifies the correct row, 12 INDEX
functions can return the values for each month. INDEX
is incredibly fast. The 13 formulas will run in 12% of the time it takes to run 12 VLOOKUP
formulas.
Figure 9.16 illustrates the technique. A MATCH
function in column C figures out which row contains the match. INDEX
functions in D5:O12 return the monthly numbers.
LOOKUP
Even Excel Help tells you to avoid the old LOOKUP
function. However, LOOKUP
can do one useful trick that VLOOKUP
and HLOOKUP
cannot do—it can process many lookups in one single array formula. LOOKUP
can also deal with a lookup range that is vertical and a return range that is horizontal, or vice versa.
One additional superpower of the old LOOKUP
function is the capability to look up several values at once. You have to use Ctrl+Shift+Enter to accept the formula, and because LOOKUP
will be returning an array of answers, you should enclose the LOOKUP
in a wrapper function such as SUM
to add all the results from the function.
In Figure 9.17, a series of invoices appear in rows 4 through 17. A GP% (gross profit percentage) is associated with each invoice. The sales rep will earn a bonus depending on the GP% of each invoice, as shown in E6:F10. Instead of calculating a bonus for each row, you can calculate a bonus for all the rows at once. The formula in B1 of Figure 9.17 specifies an array of B4:B17 as the lookup value. This causes Excel to perform the LOOKUP
14 times, once for each value in the range B4:B17. The formula wraps the LOOKUP
results in a SUM
function to add up all the bonus results. To calculate correctly, you must hold down Ctrl+Shift while pressing Enter after typing this formula. When you press Ctrl+Shift+Enter, Excel adds the curly braces around the formula. You do not type the curly braces manually. Typing the curly braces will not work.
As of late 2018, any Office 365 customers will have access to modern arrays. This is a major change to how Excel calculates. You will no longer have to use Ctrl+Shift+Enter in the example above. Also, with the introduction of modern arrays, every Excel function can accept an array, so you can use =SUM(VLOOKUP(B4:B17,E6:F10,2,False))
, and it will calculate correctly. Modern arrays are not in Excel 2019, so those customers will still have to use LOOKUP
and Ctrl+Shift+Enter.
FORMULATEXT
to Document a WorksheetQuiz: Which Excel function is used the most in this book? It is FORMULATEXT
. The FORMULATEXT
function was added in Excel 2013. If you ask for the =FORMULATEXT(A1)
, Excel shows the formula that is in cell A1 as text. All the formulas shown in this book (such as cell C1 in Figure 9.17) are generated with the FORMULATEXT
function.
You can use FORMULATEXT
to document the formulas used in your worksheet. Normally, you can either print your worksheet with formulas showing or with the results from the formulas. By using FORMULATEXT
, you can show both the formula and the result.
If you use FORMULATEXT
on a cell with an array formula, the resulting text will be wrapped in curly braces that would be shown in the formula bar.
In Figure 9.18, the text of the formula shown in C3 comes from a FORMULATEXT
function.
FORMULATEXT(reference)
This function returns a formula as text.
Troubleshooting
FORMULATEXT fails when your reference does not contain a formula.
There are people who had written their own versions of FormulaText
as a VBA function before Excel 2013 added FormulaText
. The version that was made popular on the Internet would return the formula as text if the cell contained a formula. Otherwise, it would return the value in the cell.
However, Excel’s built-in version of FORMULATEXT
returns a #N/A error if the cell does not contain a formula.
One workaround is to wrap FORMULATEXT
in IFNA
. Instead of =FORMULATEXT(C2)
, use =IFNA(FORMULATEXT(C2),C2)
.
Two cells show an answer of 4. Next to each cell, a FORMULATEXT
function attempts to show the formula in the adjacent cell. This works for the first cell, where the formula is =2+2
. However, in the second cell, the FORMULATEXT
returns a #N/A error because the cell simply contains the number 4 instead of a formula. A workaround of =IFNA(FORMULATEXT(B2),B2)
will work for either formulas or values.
OFFSET
to Describe a RangeThe language of Excel is numbers. There are functions that count the number of entries in a range. There are functions that can tell you the numeric position of a looked-up value. You might know that a particular value is found in row 20, but what if you want to perform calculations on other cells in row 20?
The OFFSET
function handles this very situation. You can use OFFSET
to describe a range using mostly numbers. OFFSET
is flexible: It can describe a single cell, or it can describe a rectangular range.
Although INDEX
can return a single cell, row, or column from a rectangular range, it has limitations. If you specify C5:Z99 as the range for an INDEX
function, you can select only cells below and/or to the right of C5. The OFFSET
function can move up and down or left and right from the starting cell, which is C5.
OFFSET(reference,rows,cols,height,width)
The OFFSET
function returns a reference to a range that is a given number of rows and columns from a given reference. This function takes the following arguments:
reference
—This is the reference from which you want to base the offset. reference
must be a reference to a cell or range of adjacent cells; otherwise, OFFSET
returns a #VALUE!
error.
rows
—This is the number of rows, up or down, that you want the upper-left cell to refer to. Using 5
as the rows
argument, for example, specifies that the upper-left cell in the reference is five rows below reference
. rows
can be positive, which means below the starting reference, or negative, which means above the starting reference.
cols
—This is the number of columns to the left or right that you want the upper-left cell of the result to refer to. For example, using 5
as the cols
argument specifies that the upper-left cell in the reference is five columns to the right of reference
. cols
can be positive, which means to the right of the starting reference, or negative, which means to the left of the starting reference. If rows
and cols
offset reference
over the edge of the worksheet, OFFSET
returns a #REF!
error. Figure 9.19 demonstrates various combinations of rows
and cols
from a starting cell of cell C5.
height
—This is the height, in number of rows, that you want the returned reference to be. height
must be a positive number.
width
—This is the width, in number of columns, that you want the returned reference to be. width
must be a positive number. If height
or width
is omitted, Excel assumes it is the same height or width as reference
.
OFFSET
enables you to specify a reference. It does not move a cell. It does not change the selection. It is just a numeric way to describe a reference. OFFSET
can be used in any function that is expecting a reference argument.
Excel Help provides a trivial example of =SUM(OFFSET(C2,1,2,3,1))
, which sums E3:E5. However, this example is silly because no one would ever write such a formula! If you were to write such a formula, you would just write =SUM(E3:E5)
instead. The power of OFFSET
comes when at least one of the four numeric arguments is calculated by the COUNT
function or a lookup function.
In Figure 9.20, you can use COUNT(A5:A99)
to count how many entries are in column A. If you assume that there are no blanks in the range of data, you can use the COUNT
result as the height
argument in OFFSET
to describe the range of numbers. Here’s what you do:
There is nothing magical about the reference, so write it as =OFFSET(A5,
.
Do not move the starting position any rows or columns from cell A5. The starting position is A5, so you always use 0 and 0 for rows and columns. Therefore, the formula is now =OFFSET(A5,0,0,
.
If you want to include only the number of entries in the list, use COUNT(A5:A999)
as the height of the range. The formula is now =OFFSET(A5,0,0,COUNT(A5:A999),
.
The width is one column, so make the function =OFFSET(A5,0,0,COUNT(A5:A999),1)
.
Use your OFFSET
function anywhere you would normally specify a reference. You can use =SUM(OFFSET(A5,0,0,COUNT(A5:A999),1))
or specify that formula as the series in a chart. This creates a dynamic chart that grows or shrinks as the number of entries changes.
Troubleshooting
OFFSET is a volatile function and will slow recalculation of your worksheet. Avoid OFFSET using a little-known version of INDEX.
Normally, =INDEX(A1:A10,5)
will return the value stored in the fifth row of A1:A12. Excel guru Dan Mayoh discovered an alternate use for INDEX
. If the INDEX
function is placed adjacent to a colon, the INDEX
function returns the cell address instead of the value stored in the cell. =SUM(A1:INDEX(A1:A12,5))
will sum A1:A5.
Of course, you would not hard-code the 5 in the INDEX function. You might use MONTH(TODAY())
to dynamically choose the number corresponding to the current month.
=SUM(A1:INDEX(A1:A12,MONTH(TODAY())))
is not volatile and does the same thing as =SUM(OFFSET(A1,0,0,MONTH(TODAY()),1))
.
INDIRECT
to Build and Evaluate Cell References on the FlyThe INDIRECT
function is deceivingly powerful. Consider this trivial example: In cell A1, enter the text B2
. In cell B2, enter a number. In cell C3, enter the formula =INDIRECT(A1)
. Excel returns the number that you entered in cell B2 in cell C3. The INDIRECT
function looks in cell A1 and expects to find something that is a valid cell or range reference. It then looks in that address to return the answer for the function.
The reference text can be any text that you can string together using various text functions. This enables you to create complex references that dynamically point to other sheets or to other open workbooks.
The reference text can also be a range name. You could have a validation list box in which someone selects a value from a list. If you have predefined a named range that corresponds to each possible entry on the list, INDIRECT
can point to the various named ranges on the fly.
When you use traditional formulas, even absolute formulas, there is a chance that someone might insert rows or columns that will move the reference. If you need a formula to always point to cell J10, no matter how someone rearranges the worksheet, you can use =INDIRECT("J10")
to handle this.
INDIRECT(ref_text,a1)
The INDIRECT
function returns the reference specified by a text string. This function takes the following arguments:
ref_text
—This is a reference to a cell that contains an A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference to a cell as a text string. If ref_text
is not a valid cell reference, INDIRECT
returns a #REF!
error. If ref_text
refers to an external workbook, the other workbook must be open. If the source workbook is not open, INDIRECT
returns a #REF!
error.
a1
—This is a logical value that specifies what type of reference is contained in the cell ref_text
. If a1
is TRUE
or omitted, ref_text
is interpreted as an A1-style reference. If a1
is FALSE
, ref_text
is interpreted as an R1C1-style reference.
Figure 9.21 is a monthly worksheet in a workbook that has 12 similar sheets. In each worksheet, the data headings are in row 6, and the total for the worksheet appears in cell D2. To build a summary sheet that points to D2 on the individual worksheets, you can concatenate the month name from column A with “!D2” to build a reference.
HYPERLINK
Function to Add Hyperlinks QuicklyExcel enables you to add a hyperlink by using the Excel interface. On the Insert tab, select the Hyperlink icon. Next, you specify text to appear in the cell and the underlying address. Building links in this way is easy, but it is tedious to build them one at a time. If you have hundreds of links to add, you can add them quickly by using the HYPERLINK
function.
HYPERLINK(link_location,friendly_name)
The HYPERLINK
function creates a shortcut that opens a document stored on your hard drive, a network server, or the Internet. This function takes the following arguments:
link_location
—This is the URL address on the Internet. It could also be a path, filename, location in the same workbook, and location in another file. For example, you could link to "[C:filesJan2018.xls]!Sheet1!A15"
. Note that link_location
can be a text string enclosed in quotes or a cell that contains the link.
friendly_name
—This is the underlined text or numeric value that is displayed in the cell. friendly_name
is displayed in blue and is underlined. If friendly_name
is omitted, the cell displays the link_location
value as the jump text. friendly_name
can be a value, a text string, a name, or a cell that contains the jump text or value. If friendly_name
returns an error (for example, #VALUE!
), the cell displays the error instead of the jump text.
Figure 9.22 shows a list of web pages in column A. Column B contains the titles of those web pages. To quickly build a table of hyperlinks, you use =HYPERLINK(A2,B2)
in cell C2 and copy the formula down the column. Unfortunately, you must keep columns A and B intact for the hyperlink to keep working. You can hide those columns, but there is no Paste Special option to convert the formula to values that will keep the hyperlink.
Note
Note that Excel does not check whether the link location is valid at the time you created the link. If the link is not valid when someone clicks it, the person encounters an error.
Tip
It is difficult to select a cell that contains a HYPERLINK
function. If you click the cell, Excel attempts to follow the hyperlink. Instead, click the cell and hold the mouse button until the pointer changes from a hand to a plus. Alternatively, click a nearby cell and use the arrow keys to move to the cell with the hyperlink.
To keep only the hyperlinks, copy column C and paste to a blank Word document. Open a new workbook. Copy from Word and paste back to the new Excel document.
Alternatively, use ="#HYPERLINK("""&A2&""""&", "&""""&B2&""""&")"
in C2. Copy down and paste special values. Use Find and Replace to change #
to =
.
TRANSPOSE
Function to Formulaically Turn DataWith many people using Excel in a company, there are bound to be different usage styles from person to person. Some people build their worksheets horizontally, and other people build their worksheets vertically. For example, in Figure 9.23, the monthly totals stretch horizontally across row 80. However, for some reason, you need these figures to be arranged going vertically down from cell B84.
The typical method is to copy C80:N80 and then use Home, Paste, Transpose. This copies a snapshot of the totals in row 80 to a column of data.
This is fine if you need only a snapshot of the totals. However, what if you want to see the totals continually updated in column B? Excel provides the TRANSPOSE
function for such situations.
Because the function returns several answers, you need to use special care when entering the formula. Here’s how:
Note that C80:N80 contains 12 cells.
Select an identical number of cells starting in B84. Select B84:B95.
Even though you have 12 cells selected, type the formula, =TRANSPOSE(C80:N80)
, as if you had only one cell selected.
To tell Excel 2019 that this is a special type of formula called an array formula, hold down Ctrl+Shift while you press Enter. Pressing Ctrl+Shift+Enter is not required in Office 365 thanks to modern array formulas introduced in September 2018.
Excel shows the formula surrounded by curly braces in the formula bar. This is one single formula entered in 12 cells. Therefore, you cannot delete or change one cell in the range. If you want to change the formula, you need to delete all 12 cells in B84:B95 in a single command.
TRANSPOSE(array)
The TRANSPOSE
function transposes a vertical range into a horizontal array or vice versa.
The argument array
is an array or a range of cells on a worksheet that you want to transpose. The transposition of an array is accomplished by using the first row of the array as the first column of the new array, the second row of the array as the second column of the new array, and so on.
Note
You can also use TRANSPOSE
to turn a vertical range into a horizontal range.
GETPIVOTDATA
to Retrieve One Cell from a Pivot TableYou might turn to this book to find out how to use most of the Excel functions. However, for the GETPIVOTDATA
function, you are likely to turn to this book to find out why the function is being automatically generated for you.
Suppose that you have a pivot table on a worksheet. You should click outside the pivot table. Next, you type an equal sign and then use the mouse to click one of the cells in the data area of the pivot table. Although you might expect this to generate a formula such as =E9
, instead, Excel puts in the formula =GETPIVOTDATA("Sales",$B$5,"Customer","Astonishing Glass Company","Region","West")
, as shown in Figure 9.24.
This function is annoying. As you copy the formula down to more rows, the function keeps retrieving sales to Astonishing Glass in the West region. By default, Excel is generating this function instead of a simple formula such as =E9
. This happens whether you use the mouse or the arrow keys to specify the cell in the formula.
To avoid this behavior, you can enter the entire formula by manually typing it on the keyboard. Typing =E9
in a cell forces Excel to create a relative reference to cell E9. You are then free to copy the formula to other cells.
There is also a way to turn off this behavior permanently:
Select a cell inside an active pivot table.
The Pivot Table Tools tabs are displayed. Select the Analyze tab. From the PivotTable group, select the Options drop-down menu and then select the Generate GetPivotData icon. The behavior turns off.
Enter formulas by using the mouse, arrow keys, or keyboard without generating the GETPIVOTDATA
function.
Microsoft made GETPIVOTDATA
the default behavior because the function is pretty cool. Now that you have learned how to turn off the behavior, you might want to understand exactly how it works in case you ever need to use the function.
GETPIVOTDATA(data_field,pivot_table,field1,item1,field2,item2,...)
The GETPIVOTDATA
function returns data stored in a pivot table report. You can use GETPIVOTDATA
to retrieve summary data from a pivot table report, provided that the summary data is visible in the report. This function takes the following arguments:
data_field
—This is the name, enclosed in quotation marks, for the data field that contains the data you want to retrieve.
pivot_table
—This is a reference to any cell, range of cells, or named range of cells in a pivot table report. This information is used to determine which pivot table report contains the data you want to retrieve.
field1
, item1
, field2
, item2,...
—These are 1 to 126 pairs of field names and item names that describe the data you want to retrieve. The pairs can be in any order. Fieldnames and names for items other than dates and numbers are enclosed in quotation marks. For OLAP pivot table reports, items can contain the source name of the dimension as well as the source name of the item.
Calculated fields or items and custom calculations are included in GETPIVOTDATA
calculations.
If pivot_table
is a range that includes two or more pivot table reports, data is retrieved from whichever report was created in the range most recently.
If the field
and item
arguments describe a single cell, the value of that cell is returned, regardless of whether it is a string, a number, an error, and so on.
If an item contains a date, the value must be expressed as a serial number or populated by using the DATE
function so that the value is retained if the spreadsheet is opened in a different locale. For example, an item referring to the date March 5, 2015, could be entered as 42068
or DATE(2015,3,5)
. Times can be entered as decimal values or by using the TIME
function.
If pivot_table
is not a range in which a pivot table report is found, GETPIVOTDATA
returns #REF!
. If the arguments do not describe a visible field, or if they include a page field that is not displayed, GETPIVOTDATA
returns #REF!
.
If you were a data analyst in the 1980s and the early 1990s, you would have been enamored with the database functions. I used @DSUM
every hour of my work life for many years. It was one of the most powerful weapons in any spreadsheet arsenal. Combined with a data table, the DSUM
, DMIN
, DMAX
, and DAVERAGE
functions got a serious workout when people performed data analysis in a spreadsheet.
Then, in 1993, Microsoft Excel added the pivot table to the Data menu in Excel. Pivot tables changed everything. Those powerful database functions seemed tired and worn out. Since that day in 1993, I had never used DSUM
again until I created the example described in the following section. As far as I knew, the database functions had been living in a cave in South Carolina.
Maybe it is like the nostalgia of finding a box of photos of an old girlfriend, but I realized that the database functions are still pretty powerful. Customers whined enough to have Microsoft add AVERAGEIF
to the COUNTIF
and SUMIF
arsenal. This was unnecessary: Customers could have done this easily by setting up a small criteria range and using DAVERAGE
.
Eleven of the 12 database functions are similar. DSUM
, DAVERAGE
, DCOUNT
, DCOUNTA
, DMAX
, DMIN
, DPRODUCT
, DSTDEV
, DSTDEVP
, DVAR
, and DVARP
all perform the equivalent operation of their non-D
equivalents, but they allow for complex criteria to include records that meet certain criteria. See examples of each of these in Figure 9.25.
To save you the hassle of looking up the confusing few, DCOUNT
counts numeric cells, and DCOUNTA
counts nonblank cells. DSTDEV
and DVAR
calculate the standard deviation and variance of a sample of a population, respectively. DSTDEVP
and DVARP
calculate the standard deviation and variance of the entire population, respectively. The 12th database function, DGET
, has the same arguments, but it acts a bit differently, as explained later in this chapter.
DSUM
to Conditionally Sum Records from a DatabaseThere are three arguments to every database function. It is very easy to get your first DSUM
working. The criteria
argument is the one that offers vast flexibility. The following section explains the syntax for DSUM
. The syntax for the other 11 database functions is identical to this.
DSUM(database,field,criteria)
The DSUM
function adds records from one field in a data set, provided that the records meet some criteria that you specify. The DSUM
function takes the following arguments:
database
—This is the range of cells that make up the list or database, including the heading row. A database
is a list of related data in which rows of related information are records, and columns of data are fields. In Figure 9.25, the database is the 5,002 rows of data located at A23:I5024.
field
—This indicates which column is used in the function. You have three options when specifying a field:
You can point to the cell with the fieldname
, such as H23 for Revenue.
You can include the word Revenue
as the field
argument.
You can use the number 8
to indicate that Revenue
is the eighth field in the database.
criteria
—This is the range of cells that contains the conditions specified. You can use any range for the criteria
argument. The criteria range typically includes at least one column label and at least one cell below the column label for specifying a condition for the column. You can also use the computed criteria discussed in “Using the Miracle Version of a Criteria Range,” later in this chapter. Learning how to create powerful criteria ranges enables you to unlock the powerful potential of the database functions. Several examples are provided in the following sections.
Note
To conserve space, the remaining examples in the following sections show only the DSUM
result. You can compare the various results to the $657,028 of revenue for the current example.
Although a criteria range needs only one field heading from the database, it is just as easy to copy the entire set of headings to a blank section of the worksheet. In Figure 9.25, for example, the headings in A17:I17, along with at least one additional row, create a criteria range.
In Figure 9.25, you see results of the 11 database functions for a simple criteria in which the customer is Best Paint Inc. Each formula specifies a database of $A$23:$I$5024. The field is H23, which is the heading for Revenue. The criteria range is A17:I18. In this example, the criteria range could have easily been A17:A18, but the A17:I18 form enables you to enter future criteria without specifying the criteria range again.
This is a trivial example, but if the second row of the criteria range is completely blank, the database function returns the total of all rows in the data set. As shown in Figure 9.26, this is $256.6 million. This is equivalent to using the SUM
function.
AND
to Join CriteriaMany people who have used SUMIF
in Excel 2003 and earlier are likely to want to know how to conditionally sum based on two conditions. This is simple to do with DSUM
. If two criteria are placed on the same row of the criteria range, they are joined by an AND
. In Figure 9.27, for example, the $123,275 is the sum of records in which the customer is Best Paint Inc. and the product is V937.
OR
to Join CriteriaWhen two criteria are placed on separate rows of the criteria range, they are joined by an OR
function. In Figure 9.28, the $2.1 million represents records for either Improved Radio Traders or Best Paint Inc.
You can use OR
to join criteria from different fields. The criteria range in Figure 9.29 shows a Region
value of West
joined by an OR
with a District
value of Texas
. This pulls a superset of all the West records plus just the Texas records, which happen to fall in the Central region.
The example in Figure 9.30 finds records with a date after 2017 and with revenue under $50,000. The criteria in F18 for the date could have used any of these formats:
>12/31/2017 >=1/1/2018 >31-Dec-2017
Using the criteria ranges in the preceding examples, you could easily build any complex criteria with multiple AND
or OR
operators.
However, this could get complex. Imagine if you wanted to pull all the records for five specific customers and five specific products. You would have to build a criteria range that is 26 rows tall. Basically, the first row is the headings for customer and product. The second row indicates that you want to see records for Customer1 and Product1. The third row indicates that you want to see records for Customer1
and Product2
. The fourth row indicates that you want to see records for Customer1
and Product3
. The seventh row indicates Customer2
and Product1
. The 26th row indicates Customer5
and Product5
.
If you need to pull the records for seven customers and seven products from five districts, your criteria range would grow to 246 rows tall and would probably never finish calculating.
There is a miraculous version of the criteria range that completely avoids this problem. Here’s how it works:
The criteria range consists of a range that is two cells tall and one or more cells wide.
Contrary to instructions in Excel Help, the top cell of the criteria range cannot contain a field heading. The top cell must be blank or contain anything that does not match the database header row. For example, you could use a heading of “Computed Criteria.”
The second row in the criteria range can contain any formula that evaluates to TRUE
or FALSE
. This formula must point to cells in the first data row of the database. The formula can be as complex as you want provided the formula returns TRUE
or FALSE
. You can combine AND
, OR
, VLOOKUP
, NOT
, MATCH
, and any other functions.
For a simple example, suppose you want to find records that match one of 15 customers. You copy the customers to K24:K38. In the second row of the criteria field, write the formula =NOT(ISNA( MATCH(A24,$K$24:$K$38,0)))
. This formula does a MATCH
on the first customer in the database to see if it is in the list in K
. The ISNA
and NOT
functions make sure that the criteria cell returns a TRUE
when the customer is one of the 15 customers.
Very quickly and without complaint, Excel compares the 5,000 rows of your database with this complex formula, and the DSUM
produces the correct value, as shown in Figure 9.31.
DGET
FunctionThe DGET
function returns a single cell from a database. The problem is that this function is picky. If your criteria range matches zero records, DGET
returns a #VALUE
error. If your criteria range returns more than one row, DGET
returns a #NUM!
error.
To have DGET
work, you need to write a criteria record that causes one and only one row to be evaluated as TRUE
.
DGET(database,field,criteria)
The DGET
function returns a single cell matching criteria from a data set.
Microsoft debuted linked data types in 2018. The first two data types are stock market data and geographic data.
Enter a list of states, cities, or countries in some cells. Select those cells and choose Geography from the Data Types gallery on the Data tab. As shown in Figure 9.32, a Map icon appears next to each value to tell you that the cells contain a geography data type.
If you click the map icon for a cell, a data card appears with information from Wikipedia about the state listed in the cell.
However, the more interesting feature is the new formulas that can point to the linked cell. In this example, Ohio is in H2. Go to any cell and type =H2 followed by a period. A list of fields appears. You can choose any of these fields to retrieve information about the geography. For example, =H2.Capital
or =H2.Population
. As shown in Figure 9.33, if the field name contains a space, wrap the name in square brackets: =H2.[Building Permits]
.
After entering the formula for the first state, you can copy the formula to pull similar data for each state. Figure 9.34 shows the largest city in each state.
If you type a different state name in a cell, the formulas will update. In Figure 9.35, Hawaii replaced Illinois. In a second or so, Chicago is replaced with Honolulu.
What if you mistype a name? You will see a brand-new error value called #FIELD!
.
Given that this is a new feature, there are some odd limitations:
The cell containing the Linked Data cannot be a formula. It must be a value. In Figure 9.35, once you have Columbus in I2, it would be nice to mark Columbus as geography and get the Columbus Ohio population with =I2.Population
. However, this will not work.
My computer always includes a custom list with the U.S. states from Alabama to Wyoming. This allows me to type Alabama and drag the fill handle to get the other state names. You must be careful to drag the states before marking Alabama as a Geography type. Once the cell is marked as Geography, the fill handle will not fill from custom lists.
Your computer will have to be connected to the Internet to retrieve fields associated with a Linked Data Type.