How to Manage Excel Lists

Excel is a good tool for managing lists at the end-user desk. Short of working with a full-fledged database (way beyond the scope of this column), Excel provides the ability to sort, filter, subtotal, parse, join (concatenate) and change the letter casing of your information for easier data handling and reporting.

This is particularly useful when receiving what is commonly referred to as an “ad-hoc” list. These lists often come when requesting data from an outside database, be it from client or opposing counsel, Accounting, Marketing, Billing, Time Reporting, or a Web site. The list is rarely, if ever, formatted in a way that can be easily managed, such as a .txt file format.

Too often, unnecessary time is spent retyping information in the list. This article presents methods of manipulating the data with minimal typing.

Parsing Information From a Single Cell

Usually when importing a list, all of the fields of information for each record have been stored in a single cell. For example, last name, first name, client number, matter number and country would ideally be in five separate columns. Having those categories of information in one cell won't do us any good if we need to sort by country, or filter by client number.

The feature used to “parse” this information is Excel's “Text to Columns” feature. This will invoke the “Convert Text to Columns Wizard,” a relatively easy process.

1. Determine how many columns are needed in all and insert that many columns to the right of your list. This way Excel won't overwrite any information when it moves the existing data to its own column. Note: Only insert the columns if there is data in the adjacent columns.
2. Select all cells of data in the single column.
3. Choose the Data menu in Excel 2003, or the Data Ribbon in Excel 2007.
4. Choose Text to Columns to start the “Convert Text to Columns Wizard.”
5. The data will often use the comma as a delimiter, so you would choose “Delimited,” and click Next.
6. Be sure only the comma is selected in the Delimiters list.
7. Click Finish

There you go. Now each field of information is nicely placed into its own cell. To take the process a step further, though, go back to Step 1 of the Wizard. An example of “Fixed width” would be telephone numbers. Here, you would actually click within the wizard after three (and perhaps the next three) digits. When you click Finish, the telephone prefixes and phone numbers will be placed into their own cells.

Automatically Change the Letter Case

Now that the names and countries have been parsed, it may help to have the proper casing for the data ' particularly if you'll be using this list for ongoing reports and projects. Excel offers three functions that provide a quick change: =Upper, =Lower, and =Proper.

Let's use the country as an example. We'll convert usa and uk to upper case.

1. Click in the blank cell next to usa (cell F1 in this example).
2. Type =Upper(E1), assuming usa is in cell E1.
3. Press Enter. Notice the change to USA?
4. Copy the function in E1 all the way down your list in column F.

Practice this with the Upper, Lower, and Proper functions to determine which best meets your needs.

Convert Cells to a Value

Once you have copied the function, your new cells are still dependent on the data from the cell referenced in the function. If you delete the cell with the original information, your new cell will “blank out” because it is referencing an empty cell. Here's how to address that:

1. Select all of the cells that contain the function displaying the correct case (cells F1:F3 in this example).
2. Choose Copy. Do not move your mouse off of the selected cells (do not de-select the selected cells).
3. In Excel 2007 choose the Home Ribbon; or in Excel 2003 choose the Edit menu.
4. In Excel 2007 choose the Paste arrow and click Paste Values; or in Excel 2003 choose the Paste Special menu item and click Values.
5. Click OK.

You've now got simple text in the cells. The underlying function has been stripped out. Go ahead and delete the column with the improper casing that you had used in the function.

Concatenation ' Joining Cells

We've discussed parsing data into separate cells. How about “joining” or concatenating cells that truly do belong together?

Using the column example from earlier, perhaps after you've used the “Text to columns” feature you would like to join the client and matter number to have a slash or dash between those two numbers. For example: 1/3 or 1-3.

Our first example will be a simple concatenation the joins the two cells, but doesn't place any other characters within the cell. Doing this will illustrate how starting with the very basics will help you move to more complex functions.

Simple Concatenation

1. Click in cell G1.
2. Type =Concatenate(C1,D1).
3. Press Enter.

The cells are indeed joined, but not quite what we're looking for. The results look like a whole number (13).

Concatenate and Add Characters

The few simple rules will be listed below the example. For now, let's just type the function to add a slash character between the client and matter numbers.

1. Delete what you've typed in G1
2. Type=Concatenate(C1&”/”&D1)
3. Press Enter.

That's better; now you've got a slash between the client and matter numbers (1/3).

Rules for Concatenation

• The “&” symbol simply means Join. It needs to be placed before and after any special characters or text you'll be adding to the function.
• Quotes need to be wrapped around any special characters or text you'll be adding to the function.

I like to create complex functions as though I'm speaking a sentence. For example, as I'm typing the following function =Concatenate(C1&”/”&D1) I'm thinking: =Concatenate(Take C1 and Join it to a slash and Join that to D1), “&” represents thinking the word Join, and slash I know needs to be in quotes to make it work properly

You can get as complex as you want when it comes to concatenating cells. For example, after parsing a 10-digit phone number, you may decide you want the following format: (206) 5551234.

The function would be as follows: =Concatenate(“(“&A1&”) “&B1), and I'm thinking: =Concatenate (Take an open parentheses and Join it to A1, and Join A1 to a closing parenthesis and a space, and Join that to B1). When you've finished, follow the same steps in “Convert Cells to a Value” for the cells that contain the concatenation function.

Don't waste time typing and reformatting your lists in Excel. So much of this can be done quickly. I've trained people who have literally saved hours upon hours from retyping and formatting, simply by knowing how use these tasks we've just covered. Our samples had only three records to change. Imagine how much time you'll save when you've got tens, hundreds, even thousands of records to be changed.