Excel Review

 Basic Rules of Thumb:

  •  Avoid putting ‘spaces’ within Folder names, File names and Field/Column names
    • Use an underscore instead (“_”)
       
  • NEVER begin a Folder, File or Field name with a number
     
  • Avoid Field names that use reserved words or function names (e.g.:  DATE, SELECT, FROM, TO, HAVING, WHERE, TIME, DATETIME, COUNT, AVG, STDEV, SUM, MIN, MAX, FIRST, LAST, DISTINCT, VAR)
 

Excel Functions for Massaging and Manipulating Data (Remember that you can easily look up these functions as well as many others within Excel. Explore and have fun with the software)

 

Keyboard shortcuts (http://office.microsoft.com/en-us/excel-help/keyboard-shortcuts-HP005203781.aspx)

  • ctrl + arrow key’ – move to end of region
     
  • ctrl + shift + arrow key’ –move and select to end of region
     
  • ctrl + single quote’ – copy above cell value
     
  • ctrl +’ – current date
     
  • ctrl + : ’ – current time
     
  • ctrl + c ’ – copy
     
  • ctrl + ’ – paste
     
  • ctrl + x ’ – cut
     
  • ctrl + z’ – undo
     
  • F4 – cycle through Absolute and Relative cell references for a formula (i.e. $C$3, $C3, C$3, C3)
     
  • F9 – recalculate all formulas on a worksheet. Very useful for recalculating random numbers.
 

Fill Blanks – to replace blank cells with a similar value:

  • Highlight the range of cells to search for blanks
     
  • On the Home Ribbon-Tab, click ‘Find & Select’ -> select ‘Go To Special’ -> select ‘Blanks’
     
  • Now all of the blank cells within your range of cells are selected. In the cell entry bar (above all of the cells), type in your value that you want to replace all blanks with
     
  • While holding the ctrl button, hit enter. Now all of the blank cells should be filled with your new value

 

String/Text functions

  • LEFT – grab a defined number of characters starting at the left/begin of the string
     
  • RIGHT – grab a defined number of characters starting at the right/end of the string
     
  • MID – grab strings from the middle of longer strings
     
  • FIND – grab the position within a string given a specific character you are searching for (i.e. a comma)
     
  • CONCATENATE – splice together multiple strings/values
    • Recall that using the & works the same as the concatenate function (A3 & “ “ & B3 will put a space between the values in A3 and B3)
       

Numeric functions

  • AVERAGE – The arithmetic mean of an array/range of values
     
  • STDEV.S – The standard deviation around the mean of an array/range of values for the sample. Use this instead of STDEV.P for most of your calculations
     
  • COUNT – Counts the number of non-blank numeric cells within the array/range
    • Investigate the other variations of COUNT when you get a chance
       
  • CONFIDENCE.NORM – The confidence interval around a mean given an alpha (0.5 = 95% CI), a stdev and the sample size (N)
     
  • LN – calculate the natural log of a number (good for transforming some non-linear data)
     
  • EXP – back-transform a value from the natural log (needed to convert log-odds from Logistic regression to a probability)
     
  • RANDBETWEEN – calculate a random value from a Uniform distribution between the min and max criteria values
 
 

Pivot Table

  • Go to Insert tab -> Pivot Table
    • This will take the vertical data (‘NumberStuff’ tab from example) and summarize the information into a horizontal/easier to interpret table.
    • Must choose which column to use as your rows
    • Must choose which column to summarize as ‘Values’ (many choices for summary: sum, count, average, stdev etc.)
    • You will just have to follow the instructions and don’t forget to include the column headings.

 

Date functions

  • DAY – grab the day from a date (1/20/2011 = 20)
     
  • MONTH – grab the month from a date (1/20/2011 = 1)
     
  • YEAR – grab the year from a date (1/20/2011 = 2011)
     
  • HOUR – grab the hour from a time (13:25:03 = 13)
     
  • MINUTE – grab the minutes from a time (13:25:03 = 25)
     
  • SECOND – grab the seconds from a time (13:25:03 = 03)
     
  • I strongly suggest storing date and time in the same column
    • If dates are stored in separate columns, Microsoft assumes the time to be 12:00AM (0:00:00)
    • If date and time are in separate columns, you can merge them with simple math
       
      • [Date] + [Time] = DateTime

 

Opening non-Excel files (i.e. a textfile)

  • You will need to know the delimiter of the information within the textfile (i.e. comma, tab, semicolon, quotes etc.)
     
  • Within Excel, browse to the directory of the textfile and make sure that you change the ‘File Type’ to ‘All Files’ so that you can see your textfile
     
  • Choose the delimiter, make sure the data look correct in the preview screen and voila! You can now view your text document in a more structured format. This same ‘wizard’ is used within Access if you want to import a textfile into a table.
     

 

Using Excel with ArcGIS

  • If you receive a spreadsheet in which the coordinates of a location(s) are provided, you can quickly view this info within ArcGIS.
    • Open ArcMap, click  AddData 
    • Browse to Excel file and choose the correct worksheet
       
    • Once added, go to the Source Tab and Right-click on the spreadsheet/table and choose ‘Display XY Data’
       
    • Choose your X and Y fields and choose the correct projection
      • You will need to know the projection (e.g. in class the data were UTM WGS84 Zone 6N)
         
    • Shazaam, you can now see the points projected on the map. If you feel the points are in the correct location and you want to make them a permanent feature class, you can ‘Export’ the events to a point shapefile/feature class
       
  • Degrees Minutes Seconds: if you are given data that looks something like this, 60°26"34.58' N, you cannot directly display xy events in ArcGIS. The DMS needs to be converted to Decimal Degrees (DD)
    • To convert DMS to DD, the formula is DD = (Seconds/3600) + (Minutes/60) + Degrees
       
    • You must use your string manipulating skills to parse out the required information for the equation (LEFT and RIGHT nested functions; see the Review spreadsheet for assistance)