Upgrading Your Inside Sales Customer Service Teams Part 4: MS Excel Tips
Continuing our discussion on how to improve upon our employee skillset, let’s review Microsoft Excel. This is quite possibly the most important tool in the Microsoft line of products, because it allows anyone with a basic understanding of some Excel tools to analyze,
manipulate and comprehend data. Because knowledge is power, Excel can empower your workforce to be more analytical, and thus more capable of making decisions on actionable data.
The following example data will be used throughout this blog.
You’ll notice that all the names are in lower case. To change this without hand keying everything over again is simple―by using the Excel function called PROPER.
Step 1: Add a new column called, ‘First Name Proper’ or anything that makes sense to you
Step 2: Place your cursor in the cell where you want your corrected name to go; in this case, C2. Then, using the function field in Excel, enter the function as follows: =PROPER(A2)
This tells Excel to look at cell A2 and to capitalize the first letter of each word and use lower case for the rest of each word. Had the cell contained multiple words, each word would have the first letter capitalized.
Repeat the steps above to add a column for the Last Name Field.
Now that you’ve created the formula for one cell, you can apply the same formula to other cells using the crosshairs feature in Excel. To do this, hover your cursor in the lower left corner of the cell that contains the formula you want to extend to other cells.
You will note that the white cross becomes a black crosshairs. When you see the crosshairs, double click and the formula will be applied to every cell below it until it comes to a blank field in the column on the left of it.
Now that you’ve made the data look better, you may be bothered by the fact that you have two columns of first names and two columns of last names. This too can be easily fixed, though not as simply as an inexperienced Excel user may
Because the fields you’ve used the PROPER function on are tied directly to the cells they’re referenced to (i.e., Column C cells are referencing data in Column A cells), any change you make to column A will immediately affect the data in column C. So if you delete the
column in lower case, your PROPER column will “error out,” because it will no longer have anything to reference.
You can fix this by pasting values. By highlighting Column C and copying (using Ctrl + C or using the contextual right click menu), you can then highlight Column A and right click. From there, you’ll click on the clipboard with the numbers on it. This is the PASTE VALUES
function in Excel. This literally instructs Excel, regardless of the formula of the copied cell, to paste the value itself and not the formula that created it.
You can repeat this for the Last Name column, leaving you with duplicate First Name and Last Name columns.
From there, you can now delete Columns C & D, leaving only two columns.
In our next blog, we’ll show how to easily turn these two columns into one, and how to use the VLOOKUP function to drill into data.
Posted by Brad Vance, Epicor Senior Business Process Consultant