Make Excel a Little Smarter: Teach Your Spreadsheets Some Useful Tricks
Lois Schafer Mahoney, Charles F. Kelliher · Journal of accountancy online/Journal of accountancy · 2003
Key to Instructions To help readers follow the instructions in this article, we use two different typefaces. Boldface type is used to identify the names of icons, agendas, URLs and application commands. Sans serif type indicates instructions and commands that users should type into the computer. Excel 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: [ILLUSTRATION OMITTED] Rather than what you would have preferred: [ILLUSTRATION OMITTED] 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: [ILLUSTRATION OMITTED] But what you want is [ILLUSTRATION OMITTED] 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. [ILLUSTRATION OMITTED] 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. 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, above. [ILLUSTRATION OMITTED] 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, below) will appear with the data you highlighted already lined up in columns, as shown under the Data preview panel. [ILLUSTRATION OMITTED] 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, at right. [ILLUSTRATION OMITTED] 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. …