When taking data for simple scientific experiments, typically you have built in replication so that you can calculate the means, standard deviations and the 95% confidence intervals at each setting of interest.
But it’s always such a pain to massage the format and order of the replicated data so that you can perform the statistical magic.
Here are some Excel tricks and tips that help me when I perform data analysis. You can find more information in Help or on the Web:
- Sort: The sort function in Excel is your friend. Use it to sort the data in numeric order from smallest to largest.
- Consolidate: This magical Excel function takes a list of data with replication and outputs lists of averages and standard deviations for each unique variable in the left-most selection column of the Reference area. From these, you can calculate the 95% CI with ease and plot the data with error bars.
- SHIFT + END + RIGHT and SHIFT + END + DOWN: These two key sequences select cells from your cursor to the right-most-end and bottom-most-end of your data block respectively. It is so much more efficient than click-dragging down through pages and pages of data.
- DOUBLE CLICK the Fill Handle: This auto-fills a formula down to the bottom of the list, referencing the size of the column to the left of the formula – no need to click-drag this either. The Fill Handle is the black dot at the bottom right of the cell.
- F4 toggle: This toggles the cell reference between a relative reference that moves with the cell and an absolute one that stays fixed (the cell reference has the dollar signs in the address).
- VLOOKUP: This command allows you to find values using a lookup table. Use this to code/decode your data or to calculate the value of a function using a lookup table approximation.
- Error Bars Add-In: This free third-party add-in from Jon Peltier allows you to set error bars in both x and y directions easily from a single dialog box. You can set this to the 95% CI column to add meaningful error bars on your graphs.
Excel is a wonderful tool for analyzing simple experimental designs. For more more complicated experiments including DOEs, Response Surface Analyses, I would recommend using a statistical package like Minitab, SigmaPlot or the Statistics Toolbox in Matlab.