Basic Excel Tutorial

Because the project 3 data sets are so huge, you're probably going to need a few more tricks up your sleeve than just counting things manually. You can accomplish most data analysis you need by using pivot tables. They compute fairly fast and allow you to mix and match any columns together for comparison. Click this link to read about how to use them.

If you really want to zoom in and analyze specific parts of the data, then you might need to use either Excel functions or VBA macros. I recommend using Excel functions over VBA, which is slightly more complicated and require some serious programming. Here are some simple Excel functions to start you off.

Counting unique values in a column

  • paste this formula into a cell
    • =SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""), IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""))>0,1))
  • change all occurrences of "A2:A10" into whatever range you want. You can also use "A:A" if you want to calculate for the entire column.
  • select the cell containing the formula, press f2, then press ctrl+shift+enter
  • the value might show up as 0 initially, but will show the right value once it finishes the calculation.

Showing all unique values in a column

  • On the top bar of the excel window, click the Data tab
  • under the Sort & Filter section, click the advanced button and an "advanced filter" window will pop up
    • for action, select "copy to another location"
    • for List and Criteria range, select the column you want filtered
    • for copy to, select an empty column you want the results displayed
    • check "unique records only"
    • click okay

Counting frequency of a certain value in a column

To count word frequency in a single column

  • past this formula into a cell
    • = COUNTIF(A2:A10, "word")
  • replace "A2:A10" with your own cell range, or just "A:A" for the entire A column
  • replace "word" with your own word. use quotation marks!
  • or if you're dealing with values, replace "word" with formulas like "< 2000"

To count word frequency across multiple columns (e.g. given a name and home state column, how many John's are from NY?)

  • past this formula into a cell
    • = COUNTIF (A2:A10, "John", B2:B10, "NY")
  • replace the cell ranges with your own cell range
  • replace "John" and "NY" with your own words or values
  • you can string along as many criteria's as you want as such: = COUNTIF(range, text/value, range, text/value, range, text/value...)

Check out this guide and this guide for more info.

Note: Excel doesn't care about cares when matching words, so "John" and "john" will both be counted. Excel only tries to find exact matches, so "Johnathan" will not match with "John". If you want to match all words that contain the words "John" in some fashion, you are going to want to use regular expressions. This site has some simple tutorials on how to use them.

Other Tutorials

Simple tutorial on excel formulas and functions if you have no experience at all

List of all Excel functions