intro to cleaning data

By Len De Groot   ·   Updated Aug 25, 2011   ·   Print Print

Handy formulas

Function Description Formula
  Get data or apply function to data from a different sheet =Sheet1!A1
  Apply function to a column (shift-select cells, function must be in top cell) Control-d
AVERAGE Calculates the average =AVERAGE(A2:A10)
MEDIAN  Finds the value (number) in the middle of a range. =MEDIAN(A2:A10)
  Calculate percent (A2 is the smaller number) =A2/B2*100
  Calculate percent change (B2 is the new number, A2 is the old number) =((B2/A2)-1)*100
EXACT
 Check for duplicates. =EXACT(A2:A10)
COUNTIF Counts the number of cells where a given value appears. (range, criteria) Criteria can be a number or text that appears in cells. This exmple uses a ZIP Code. =COUNTIF(A2:A10, 94705 )
TRIM
 Removes spaces from beginning and end of text. =TRIM(A2:A10)
CLEAN
 Removes all non-printable characters from text. =CLEAN(A2:A10)
CONCATENATE
 Joins text items into one text item — (&” “] adds a space between cells
 =CONCATENATE(A2&" ", B2)
LOWER
 Converts text to lowercase.
 =LOWER(A2:A10)
UPPER Converts text to uppercase.
 =UPPER(A2:A10)
PROPER
 Capitalizes the first letter in each word of a text value.
 =PROPER(A2:A10)
FIXED
 Formats a number as text with a fixed number of decimals.
 =FIXED(E2,1)
MID Returns a specific number of characters from a text string starting at the position that you specify. Useful for converting nine-diget ZIP codes. (Cell, Start position, number of chars)
 =MID(A2, 1,5)
RIGHT
 Returns the rightmost characters from a text value.
 =RIGHT(A2,5)
LEFT Returns the leftmost characters from a text value.
 =LEFT(A2,4)
REPLACE
 Replaces or add characters (For example, adding digits back to ZIP code).
 =REPLACE(A2,6,5,+"-3428")
SUBSTITUTE
 Substitutes old text with new text.
 =SUBSTITUTE(A2:A10, ”Oregon”, “OR”)

Filed under: Reporting, Data Visualization