Guide: Excel

Excel

MS Excel is a spreadsheet tool developed by Microsoft. We use excel to document experiment data, and do basic levels of data cleaning. Excel can also be helpful for designing the structure of an experiment, or quickly visualizing results.

Where to find it/installation

Excel is installed on all computers in the RA office and in the 2nd floor running rooms. It’s very unlikely you will ever need to install Excel, but if you do, you can get it the whole Microsoft package for free here in the Campus Software Library (you need to log in with your WiscID.)

Use

Some basic Excel skills include creating/moving/deleting columns, entering data, sorting data in a column, and saving spreadsheets. If you are totally new to Excel and not familiar with these, check out the tutorials under the Resources header at the bottom of this entry.

• Create a table You can create a table in Excel by highlighting the data you want to be in the table, then clicking “Format as Table” in the right/center of the upper bar under the Home tab. (Or highlight, then “Insert” > “Table”). Choose the style of table you’d like to use, then click Yes to confirm you’d like to use the highlighted data in the table. The table format allows you to easily sort data—each column header should have a dropdown menu built into it, which allows you to sort/filter the contents of the column.
• Create a pivot table Highlight the data you want to appear in the pivot table, then click the Insert tab, then click the leftmost “PivotTable” button. Select “New worksheet” if you want the PivotTable to appear on a new tab of your spreadsheet. You will see a blank worksheet, and a bar on the right with a checklist of all your column headers. Drag these into the boxes below to form your table. Where you drop them depends on your specific objectives; the best way to figure this out is to just play around with it a little. You can remove headers from the lower boxes by unchecking them in the checklist or by dragging and dropping them onto the checklist.
• Create a chart or graph from a pivot table You can create a chart or a graph from a pivot table by clicking the “Insert” tab and then choosing “Chart” or “PivotChart”.
• Perform basic analyses You can perform some basic analyses (sum, mean, standard deviation, etc.) using the buttons under the “Formulas” tab, or by entering a function directly. Here is a list of functions provided by MS Office Support. There are even functions for some statistical analyses such as Chi tests, F-tests, etc.

Resources

• MS Office Support provides some tutorials on basic Excel functions, including PivotTables.
• At the DoIt Tech Store, you can schedule a meeting with an Excel pro.
• Maybe somewhat outdated (2010), but this is a great YouTube video for the absolute beginner.