Skip to content

Basic Excel Tutorial

March 15, 2016

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

 

No comments yet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: