Make Excel a Little Smarter

xcel is a very smart application, but—and it’s a very big but—there are times it acts pretty dumb. However, it’s not hard to teach it to perform some very useful functions, and that’s what this article is all about—making Excel smarter. For example, when you download data to the spreadsheet from the Web or a database, Excel often takes separate numbers—such as 10, 15 and 17—and jams them all into one cell, which then looks like this:

Rather than what you would have preferred:

Or say you want to sort a list of clients by last names and each cell contains both first and last names with the first name listed first:

But what you want is

Or maybe you have data in separate cells and you want to combine them into one cell.

SPLITTING CELLS Problem: You have multiple names or numbers in one cell and you need to separate them into different cells. Begin by highlighting the cell or cells you want to split. The range of cells can be any number of rows tall but no more than one column wide. Then go to the taskbar and select Data and Text to Columns to bring up the screen shown in exhibit 1 , at right. You are asked to choose between the Delimited or Fixed width option buttons—although Excel likely will suggest something for you. To understand the choices, you must understand what is meant by a delimiter. A delimiter is simply a character that identifies (delimits) the end of one number or word and the beginning of another. The character can be a comma, space or a tab. Excel is smart enough to examine your data and suggest whether you have delimited or fixed-width data.

Exhibit 1

If your data appear in neatly aligned columns, as shown in the section of exhibit 1 titled Preview of selected data , it will select the Fixed width option button. If the data do not appear in neatly aligned columns, it will choose the Delimited option button, as illustrated in exhibit 2 , below.

Exhibit 2

Exhibit 3

Once you have chosen the data type—either accepting or rejecting Excel’s choice—click on Next .

If you choose the Fixed width option, the Step 2 dialog box (as shown in exhibit 3 , above) will appear with the data you highlighted already lined up in columns, as shown under the Data preview panel.

If you don’t like the columns Excel has recognized, you can create, delete or move them by following the dialog box directions.

If your data contain delimiters and you choose the Delimited option button in Step 1 , the Step 2 dialog box will appear, as shown in exhibit 4 , below.

Exhibit 4

Exhibit 5

You now need to tell Excel the delimiters contained in your data—that is, whether the numbers or words are separated by tabs, semicolons, commas, spaces or something else.

Under Delimiters , click the type of delimiter your data use. If you are uncertain, Excel will show you how your data will appear in the worksheet for each delimiter choice. To see that, simply click in a box next to the different delimiters and view the Data preview box. If you use a delimiter other than the ones provided in the dialog box, click on the Other box and enter the type of delimiter in the box to the right. For example, if you have a date in a cell that contains a slash between the date, month and year (5/2/02), click on the Other check box and enter a slash ( / ) in the box next to it. Excel will then put the day, month and year into three different cells.

Once you are satisfied with the way the data look, click on the Next button and the Step 3 dialog box will appear, as shown in exhibit 5 , above.

This dialog box lets you format each new column you created; it also lets you stop Excel from importing any column. To format a column under Data preview , left-click on it to highlight it and then click on one of the buttons under Column data format—General, Text, Date . Or, if you do not want a column to appear in your worksheet, click on Do not import column (skip) .

Now you have to tell Excel where in your worksheet you want it to export the data. To do that enter the address (in exhibit 5 it’s $A$3) in the Destination box. Finally, click on the Finish button to complete the conversion, and your data will now appear in separate cells in your Excel spreadsheet.

COMBINING CELLS
Problem: You have data in two cells and you want to combine them into a single cell.

To do that we’ll use a formula with the & operator, which connects—or combines—the text or numbers in two or more cells to produce one text or number value.

For example, you have a list of first names in column A and last names in column B. To combine them into one cell, enter the following formula in C1:

=A1&B1

If A1 contains Janet and B1 contains Oaks , C1 will display JanetOaks . To add a space between the first and last name, use the following formula—adding the quote marks and a second &:

=A1&” “&B1

If you want C1 to read Oaks , Janet so you can sort the list by last name, use this:

=B1&”, “&A1

DATA IN DIFFERENT PLACES
Problem: You have a spreadsheet that contains information in two different areas of a worksheet or in two different worksheets and you need to combine them or enter them in a formula.

We’ll use the LOOKUP function to solve this problem. LOOKUP can automatically fill a cell or even a column of data in your worksheet that is dependent upon a value entered elsewhere. For example, say you’re calculating salespersons’ bonuses based upon their revenue. LOOKUP can look at each person’s revenue, go to a bonus table, find the correct amount to award and then record it elsewhere in the spreadsheet. So it makes the calculation more efficient—you don’t have to go from one data source to another to make the calculation—and it reduces the likelihood of errors.

There are two LOOKUP functions: VLOOKUP (vertical) and HLOOKUP (horizontal). Whether you use the VLOOKUP or HLOOKUP function depends on how your data are organized in your spreadsheet. If the data are arranged in columns, then use VLOOKUP . If your data are in rows, use HLOOKUP .

Although the formula and its syntax may look intimidating, as you’ll see, it is not difficult to use. You can enter VLOOKUP into your spreadsheet in one of two ways: with the function wizard or by typing the formula directly into a cell. VLOOKUP has one optional and three required elements ( range_lookup ). Here is the general format for the VLOOKUP formula:

=VLOOKUP(lookup_value,table_array,
col_index_num,range_lookup)

Lookup_value —the value to find in the first column of the table.

Table_array —cell range of the table containing values you are seeking.

Col_index_num —column number in the table containing information you want to retrieve.

Range_lookup —where TRUE finds the approximate match to the lookup_value , and FALSE finds exact match.

We’ll use VLOOKUP with the data in exhibit 6, below, to compute a salesperson’s bonus based on the units sold and a progressive bonus scheme. Enter the VLOOKUP formula in column E ( Bonus ) to compute the bonus. To calculate a bonus, the VLOOKUP formula combines information from two different sections of your worksheet—the sales data in column D and the bonus table in H8 through I14. Not only can the information be in two different areas of the worksheet, it can be in a different worksheet and even a different file.

Here is the VLOOKUP formula in E6 that will calculate Smith’s bonus:

E6 = VLOOKUP(D6,H$8:I$14,2,TRUE), where

Lookup_value = D6, look up this value (77) in the first column of the bonus table.

Table_array = H$8:I$14, cell range of where the bonus table is located.

Col_index_num = 2, retrieve bonus amount from the second column of bonus table.

Range_lookup = TRUE, if it can’t find the lookup_value in the first column of the bonus table, it matches the largest value that is less than or equal to lookup_value.

It’s necessary to place the information to be matched to the lookup_value as the first column of your table_array ( Bonus Table ); the information must be sorted in ascending order if an approximate match is to be used ( range_value = TRUE ). Finally, be sure there is no repeating or redundant values in the first column of the table_array.

Exhibit 6

Exhibit 7

Now we’ll use a wizard to guide you through the steps needed to enter the VLOOKUP function in E6. We selected VLOOKUP in this example because the lookup_value to be searched in exhibit 6 is arranged in columns H ( Units sold ) and I ( Amount of bonus ). Go to the toolbar and click on Insert and then Function . That will evoke the Paste Function screen (see exhibit 7 ).

Click on Lookup & Reference and select VLOOKUP and then click on OK , which will produce a VLOOKUP screen (see exhibit 8) where you will enter the data to complete the function.

Enter the cell location of the lookup_value . This refers to the value that will be matched in the left-most column of the table (table_array). Since Sales (in exhibit 6 ) is in column D, enter D6 to calculate the bonus for Smith.

Now enter the range of the table where the desired data are stored, that is, in the bonus information in columns H and I, rows 8 to 14. Therefore, you enter the range as H$8:I$14 .

Next enter the col_index_num . This refers to the column in the table_array from which the matching value will be returned. In this example we enter 2 to select the value in the second column of the table ( Amount of bonus ) from the same row as the lookup_value match ( Sales ).

Finally, enter either TRUE or FALSE as the range_lookup . Enter FALSE if you want an exact match or TRUE for an approximate match. If you request an exact match and one doesn’t exist, Excel will give you the message #N/A . If you request an approximate match and Excel can’t find one, it matches the largest value that is less than or equal to the lookup_value . Remember, if you are looking for an approximate match, the first column of the table_array must be sorted in ascending order. If the range_lookup is omitted, Excel assumes an approximate match.

Exhibit 8 assumes an approximate match, so TRUE is entered as the logical value in the range_lookup . Click on OK , and the function is entered in cell E6 in your spreadsheet. You then can copy the function in E6 down the remainder of column E to compute the bonus for the rest of the salespeople.

Exhibit 8

Exhibit 9

Remember to use an absolute cell reference when referring to your table_array before copying. To do this add the dollar sign ( $ ) in front of the row reference before copying the formula—for example, H$8:I$14 .

A closer look at the VLOOKUP function in E6 will show why it displays a $7 bonus for Smith. Since Smith sold 77 units, the VLOOKUP function searches the Bonus Table and discovers an exact match does not exist; so it goes to 70, the next largest value less than 77. Smith would have to sell at least 80 units to receive a $10 bonus.

If you want the formula to find an exact match, you must enter FALSE as the logical value in the range_lookup . Exhibit 9 , above, shows the results of a FALSE entry: Most bonus payments now are displayed as #N/A , the default when an exact match is not found. Since only two salespersons, Smith and Dawins, had sales that exactly matched the units sold, only these two had a bonus calculated.

Once you have mastered the VLOOKUP function, you will find the HLOOKUP easy to learn. HLOOKUP operates the same way as VLOOKUP with two minor differences. First, instead of searching down the first column for a match, HLOOKUP searches across the first row for a match. Second, rather than return the value from the column specified, it returns the value from the row specified by the row_index_num .

You may want to consider naming your table_array . Then you can enter the name of the table in the formula instead of typing in the columns and rows. Naming your table (instead of using absolute references) is recommended if it is on another worksheet or you plan to copy your LOOKUP formula. To name your table, highlight the table (H8 to I14 in our example), go to the drop-down box in the upper left-hand area and type in the name (our table was named rates) and press Enter.

Applying the Text to Columns , the & operator and LOOKUP functions transforms Excel into a much smarter and versatile tool.

LOIS S. MAHONEY, CPA, PhD, CMA, is an assistant professor at the University of Central Florida’s School of Accounting in Orlando. Her e-mail address is lois.mahoney@bus.ucf.edu . CHARLES KELLIHER, CPA, PhD, is an associate professor at the same school. His e-mail address is charles.kelliher@bus.ucf.edu .