Excel Analysis

From REALab Wiki
Jump to navigation Jump to search

This wiki tips and tricks from the Bar Lab about how to work with data using Excel. Click on links to get more detailed info on a topic. Note on videos: set the quality to 720p HD.

Keyboard Shortcuts

keyboard shortcuts moving around quickly with keyboard shortcuts: -ctrl+A within section of data, selects all the data only (not the whole worksheet) -ctrl+arrow keys, moves to beginning/end of row/column -ctrl+shift+arrow keys moves and selects to beginning/end of row/column -ctrl+shift+{plus sign} adds row -ctrl+space adds column

Autofill a pattern

e.g., given 1 2 3, you can copy and paste 1 2 3 4 5 6 etc. using the little square


Transpose -when copying and pasting, can switch the orientation such that columns become rows/rows become columns

{{#ev:youtube|IRqp09ypYtI|500|Here is a description of the video}}


Filter e.g., have a bunch of 0s or only want values of 1, can also select unique records only

{{#ev:youtube|8_wBiLnQWu4|500|Here is a description of the video}}

Pivot tables

Pivot Table -design-> subtotals off, grand totals off -can easily switch ordering -the following shows how to use a pivot table on attentional blink data to get average accuracy for T2 by the factors a) distractor type and b) lag, and only when T1 has a correct response:

{{#ev:youtube|K5EnWu4AZ8U|500|Here is a description of the video}}


If statements

If function -if statements are structured as follows: =if([condition], [value if true], [value if false]) -the values can consist if statements, and conditions can have multiple ANDs and ORs, so if statements can get quite elaborate -you can use the not empty logic to preserve empty spaces with your formulas, e.g., =if([datacell] <> "", [formula to be applied], "") - this means if the cell is not blank, apply the formula, otherwise leave it blank -isnumber is alternative to the above, as well as istext; other iswhatevers as well There's also countif and averageif commands, etc. useful to only count all the 4s or to average only certain values


-link together two values

Static referencing in formulas

-use the $ sign before row and column reference to keep it static, useful for computing z-scores, or to compute error bars (see graphs with error bars)

Freeze panes

-freeze headers in same location so you can see them even when scrolling down

{{#ev:youtube|dYW5Lnl8DLA|500|Here is a description of the video}}

Quick binning of continuous data (e.g., RTs) for histograms

-use the ceiling command -e.g., =ceiling([cell]/20,1)*20 to place the value into one of twenty bins (?) alternatively, =ceiling([cell],6) to place the value into one of 6 bins (?)


-useful for quickly looking up and pasting corresponding values from a table

graphs with error bars

-format data series -> add secondary axis -layout -> error bars -> more error bar options (don't use "error bar", it won't give meaningful error bars -to compute your own standard error bars, use the formula: (score-mean)/sqrt(count) -confidence intervals can be achived the same way by selecting confidence intervals you've selected


-command shift enter, allows formulas to behave like arrays in matlab -sum all values when column n=1 and column i=1, =sum(bla bla) then hit control+shift+enter -average(indirect(aw1&av2):indirect("ak"&aw2))

Other Notes:

-copying or duplicating a sheet won't copy over new chart references, the charts will still reference old positions -do not use the default error bars option that excel provides, use custom error bars and point to your own calculations