Upgrading Your Inside Sales & Customer Service Teams Part 5: More MS Excel Tips
In our last post regarding Microsoft Excel (on April 1), we demonstrated how to manipulate data in a list of customer names.
Now let’s say you want one column that has both the first name and the last name in it. Most inexperienced Excel users will rekey the whole thing, or even resize the fields to make them appear close enough to each other to be the same cell. But there’s an easier way to accomplish this. It’s called CONCATENATE.
In Excel, CONCATENATE allows a user to take data from multiple cells and connect them. To create one cell with both first and last name, let’s first create a new column called “First & Last Name.” Then, let’s insert the function ‘=CONCATENATE(A2," ",B2)’. This is telling Excel to take whatever’s in cell A2 and B2 and put them together. You’ll notice there are quotation marks with a space in between them. This is telling Excel to insert a space between the data, preventing ‘John Doe’ from coming across as ‘JohnDoe’. The commas in the equation are places between cell references.
Once you’ve applied the CONCATENATE function to the first cell, you can then use the crosshairs feature described previously to apply the formula to the entire column. Your results should look something like this:
VLOOKUP is, without a doubt, the most valuable tool in Excel. With it, you are able to quickly and effectively drill into data and extract the data you want.
Let’s assume you have another spreadsheet with hundreds of customers listed, with their street address, city, state and ZIP code. Let’s also assume that the customers listed on the Excel sheet you created earlier are also on this long list. Finally, let’s assume you now want to take the list you’ve created and add the customer’s ZIP code. The novice Excel user would likely jump between the lists and copy/paste all the data. This is inefficient. VLOOKUP solves this very quickly.
Step 1: Add a column where you want the ZIP code to go and name the column ZIP CODE and select the cell where you want the first ZIP code to appear.
Step 2: Click the Function symbol above the column headers.
Step 3: Scroll down the list of available functions and select VLOOKUP, which will open up the VLOOKUP Function Arguments. You will notice the Lookup_value field is the active field.
Step 4: Select the field on the spreadsheet that you want Excel to go search for. In this case, it’s cell C2 because you want Excel to go find “John Doe” in the long list of customers.
Step 5: Click the Table_Array field to make it active, then go to the spreadsheet that contains the long list of customers you want it to search. Highlight the first column that you want Excel to search for the name, and drag over until your highlighted selection includes the ZIP CODE column, which is the result you want Excel to bring back.
Step 6: Including the first column, count over to the right how many columns over is the column containing the result (in this case, ZIP code) that you want to bring back to the other spreadsheet. Click the Col_index_num and type in that number. In this example, the number to enter is 5.
Step 7: In the Range_lookup field, type the word “false” and hit Enter or click OK. You will always type “false,” which tells Excel to look for an exact match.
You will now see that the cell has now populated with the appropriate ZIP code from the long list.
To apply the same VLOOKUP formula to the rest of the rows, simply use the crosshairs feature explained previously to apply the formula.
When looking at data, you’ll often find that there is more data than you really need. Let’s take the ZIP Code column above as an example. It has the ZIP+4 format, which you may not need. Perhaps you only need the 5-digit ZIP code. You can use the LEFT function in Excel to accomplish this.
Step 1: Create a column and label it ZIP.
Step 2: With the cell selected where you want to pull only the 5-digit ZIP, click the “FX” function symbol above the column row.
Step 3: In the ‘Insert Function’ window that appears, scroll down and select the function labeled ‘LEFT’.
Step 4: In the LEFT Function Arguments window, you will notice the TEXT field is already active. Select the field you want Excel to use for the LEFT function. In this case, it’s D2 “75204-1423.”
Step 5: Select “Num_chars”―which is where you tell Excel the number of characters from the left you want it to bring back. In this case, you want to tell Excel to bring back the first five characters from the left. So you enter the number 5.
Step 6: Hit Enter or click OK. You will now see the 5-digit ZIP populated in the new column you created.
To apply the same LEFT formula to the rest of the rows, simply use the crosshairs feature as before.
If you no longer want the ZIP Code column showing the ZIP+4 format, follow the instructions discussed earlier on Pasting Values.
Note: There is also a function called RIGHT, which works the exact same way as the LEFT function, except you’re telling Excel to pull data starting at the right side of a cell.
As you can see, Excel has a great number of features that can offer significant efficiency when creating, manipulating and managing your data in worksheets. More features and functions will be covered in future blog posts. Until then, keep creating efficiency!
Posted by Brad Vance, Epicor Senior Business Process Consultant