Prepare Data for Excel
Make information spreadsheet-readable.
Key to Instructions
To help readers follow the instructions in this article, we used two different typefaces: 
f you feed correctly formatted data to Excel, it can handle a multitude of calculations flawlessly. But feed it a piece of “indigestible” information—that is, data not formatted to its liking—and the calculations will generate error messages that looks like this:
Fortunately, Excel contains a group of functions designed to quickly and easily transform or retrieve many different kinds of otherwise incompatible information so the spreadsheet can perform its calculations flawlessly.
In a typical situation a CPA is preparing a fixed-asset report and the only data available are a code that contains all the necessary information for the report—the location of the asset, its description and the year of purchase. This information can be extracted manually, or an Excel tool can reach into the code and nimbly extract and reshape just what it needs. The functions we’ll be demonstrating are CONCATENATE, RIGHT, LEFT, MID, ROUND, TRIM and LEN. Once you see them in operation, you’ll surely include them in your cache of useful Excel tools.
STRING THEM TOGETHER
We’ll begin with CONCATENATE . The word means to connect or link in a series.
Say you have a spreadsheet listing a company’s employees, with first names in one cell, last names in an adjacent cell and titles in a third, as in:
But your report needs all that information tucked into a single cell, with a hyphen inserted between each person’s title and name, such as CEO-Rose Smith . To create it, place the following formula in the cell where you want the answer displayed (E2): =CONCATENATE(D2,”-“,B2,” “,C2).
The formula tells Excel to place the information in this order: D2 first, then a hyphen, then B2, a space and finally C2. Added characters (hyphen, space) are enclosed in double quotes and elements are separated by commas. The result resembles exhibit 1 below.
KEEP IT RIGHT
The RIGHT function is used to extract selected characters from a cell. For our example we’ll use a CPA preparing a fixed-asset report for a batch of laptops. All the necessary data are embedded in codes, a variation of which looks like this: NY LAPTOP 2003.
The first two letters ( NY ) show the location of the asset, the next six (LAPTOP ) describe the asset and the final four ( 2003 ) show its year of purchase. Exhibit 2 , below, shows what one code in a spreadsheet looks like.
We’ll begin by extracting the year of purchase. Since the year is embedded in the right end of the code, we’ll use the RIGHT function, and write a formula in the cell where we want the answer to appear (C2). The formula =RIGHT(B2,4) tells Excel to extract four characters (2003) from the right side of cell B2, creating a spreadsheet that resembles exhibit 3 , below.
To extract the computer location ( NY ) from the leftmost side of the code, we’ll use the LEFT function and write this formula into cell E2:
=LEFT(B2,2).
Again, the formula tells Excel to look into cell B2 and extract the first two characters ( NY ). The result will look like exhibit 4 , below.
To extract the identity of the asset ( LAPTOP ) that’s embedded in the middle of the code, we’ll use the MID function. But sometimes just saying it’s in the middle of the code is not enough information for Excel. So we add further hints, such as this: The target code contains six characters and begins with the fourth character in the code. Putting all those hints together, we get this formula:
The target begins with the fourth character (L) and is six characters long.
The result will resemble exhibit 5 , below.
ROUND IT OFF
Sometimes it’s more important to present information in ways that make data easy to grasp and retain than to give all the exacting detail. If a report is crowded with large numbers, it’s often wise to round them off. For example, our raw data show CEO Smith earns $453,525.70 a year, but we want our report to show all salaries in thousands. So we’ll create a single formula that divides the number by 1,000 and rounds it off ( ROUND ) to the next highest digit.
We write the ROUND formula in the cell where we want the answer to appear (C2), tell Excel that the source data are in cell B2, divide B2 by 1,000 and then round it to the 10th place (one place to the right of the decimal point):
=ROUND(B2/1000,1).
The spreadsheet should look like exhibit 6 , below.
If you wanted to round to the nearest whole number (that is, make cell B2 round to 454), use this formula:
=ROUND(B2/1000,0).
The spreadsheet now resembles exhibit 7 , below.
Excel will apply standard rounding rules: Numbers 0 to 4 will round down; numbers 5 to 9 will round up. The format of the ROUND function is:
=ROUND(number to be rounded, number of places to round the number to).
The data in the cell to be rounded can be a number or a formula. The number of places to round to can be positive, negative or zero. A positive number rounds to the right of the decimal; a negative number rounds to the left; and zero rounds to the nearest whole number.
Caveat: Calculations performed with ROUND may produce slightly different results from calculations performed with numbers formatted to a specified number of digits (see “Maintain Accuracy in Excel,” below).
TRIM IT TO SIZE
When you type text into a cell, inappropriate spaces sometimes are left between words. You can go into each cell and eliminate them or recruit the TRIM function with this formula, where the original text is in A2:
=TRIM(A2)
The TRIM function commands Excel to leave only one character space between words (see exhibit 8 , below).
COUNT THE CHARACTERS
Some cells limit the number of characters you can put in them, so sometimes you need to know the number of characters in a long data string. This handy Excel formula does that for you: =LEN(B2) (as shown in exhibit 9 , below).
With these few simple formulas, Excel can extract information or reformat data so the spreadsheet can read them.
JOHN DeCRISTOFARO, CPA, is finance director of a global advertising agency based in New York. His e-mail address is john_decristofaro@hotmail.com .
Maintain Accuracy in Excel
When you use ROUND, Excel performs its calculations with that rounded, less precise, number. The results, therefore, may vary slightly from calculations performed without rounding.
Exception: Excel gives users the option to choose a default that causes all calculations to be based on numbers as they are displayed. But the default carries a danger: Once you turn it on, the values of all the numbers displayed in the entire worksheet are held to those displayed values. To reset the default, go to Tools , Options , Calculations and select Precision as displayed (see screenshot below).
