Before you import a spread sheet, carefully review it to make sure the data makes sense. Importing garbage will cause you pain!
Data -> Text to Columns…
You can select a column of values and click the "Text to Columns" button, which is very useful for separating data from one column into two. The easiest example is having Last, First. You can use Text to Columns by identifying the comma as the Delimiter, causing the column to be broken into two columns without the comma.
Splitting full name into first and last
The Text to Columns example above works great for this task. Another approach is to use this function for the first name LEFT(cell, SEARCH(" ", cell) - 1) (replace "cell" with a reference to the cell containing the full name. For the last name: RIGHT(cell, LEN(cell) - SEARCH(" ", cell)).
Here's a link to a blog with the full description of how to use either Text to Columns or the formula.
Copying values, not functions
After you use a function in a spreadsheet, you will need to copy the data and paste special just the values in each cell. This is how you get rid of your formula and just keep the data.
Function: PROPER
Formula: =PROPER(A1)
This is what you use if you have all UPPERCASE and want to change it to capitalize the first letter of every word.
= PROPER() capitalises the first letter of each word (like Title Case)
= UPPER() - forces all letters to be UPPER CASE
= LOWER() - forces all letters to be lower case
If you want to capitalize only the First letter of a sentence use this formula
=UPPER(LEFT(A2,1))&LOWER(RIGHT(A2,LEN(A2)-1))
Function: VLOOKUP
Formula: =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
By far my most used formula. The official description of what it does: “Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify…”.
Basically, you define a value (the lookup_value) for the formula to look for. It looks for this value in the leftmost column of a table (the table_array).
Function: TRIM
Formula: =TRIM(A1)
Gets rid of any space in a cell, except for single spaces between words. I’ve found this formula to be extremely useful because I’ve often run into situations where you pull data from a database and for some reason extra spaces are put in behind or in front of legitimate data. This can wreak havoc if you are trying to compare using IF statements or VLOOKUP’s.
Function: RIGHT, LEFT, MID
Formulas: = RIGHT(text, number of characters), =LEFT(text, number of characters), =MID(text, start number, number of characters).
(Note: In all of these formulas, wherever it says “text” you can use a cell reference as well)
These formulas return the specified number of characters from a text string. RIGHT gives you the number of characters from the right of the text string, LEFT gives you the number of characters from the left, and MID gives you the specified number of characters from the middle of the word. You tell the MID formula where to start with the start_number and then it grabs the specified number of characters to the right of the start_number.
=UPPER(LEFT(A2,1))&LOWER(RIGHT(A2,LEN(A2)-1))
will capitalize only the First letter of a sentence.
Function: IF
Formula: =IF(logical_statement, return this if logical statement is true, return this if logical statement is false)
When you’re doing an analysis of a lot of data in Excel there are a lot of scenarios you could be trying to discover and the data has to react differently based on a different situation.
Function: CONCATENATE
A fancy word for combining data in 2 (or more) different cells into one cell. This can be done with the Concatenate excel formula or it can be done by simply putting the & symbol in between the two cells. If I have “Juan” in cell A1 and “Hernan” in cell B1 I could put this formula: =A1&” “&B1 and it would give me “Juan Hernan”. (The “ “ puts a space in between what you are combining with the &). I can use =concatenate(A1, “ “, B1) and it will give me the same thing: “Juan Hernan”
Formula: SUBSTITUTE
You can use this formula to clean up data such as phone numbers. Check out this tip sheet.
Handling Leading Zeroes in Excel spreadsheets
If you want to open a .csv (comma separated value) text file in Excel where there are fields that contain leading zeroes, for example Zip codes "02840" or ID numbers where you need preserve the leading zeroes "000484817", you need to do a special import into excel so that it preserves the leading zeroes. Here's instructions for how to handle the leading zeroes in excel