Seven Excel 2007 tricks and tips for data analysis.

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.