Bookmark this page to return to it with ease.
Position your windows so the spreadsheet takes up half your screen and this guide takes up the other half.
Instructional videos are linked at the beginning of each of the five sections. Please list questions on this page for the discussion meeting.
Module 1 Video: Getting Started (14:12 minutes)
(View the first sheet of the Excel_Workshop spreadsheet.)
|Find and replace||CTRL+F||⌘+F|
|Move to the edge of the data region||CTRL+Arrow key||⌘+Arrow key|
|Select to the edge of the data region||CTRL+SHIFT+Arrow key||⌘+SHIFT+Arrow key|
|Select entire column||CTRL+SPACEBAR||CTRL+SPACEBAR|
|Select entire row||SHIFT+SPACEBAR||SHIFT+SPACEBAR|
|Enter value into all selected cells*||CTRL+ENTER||⌘+RETURN|
*Highlight empty cells; type the value you want to enter in all of them; press the shortcut keys.
We’ll come back to this last shortcut—it’s useful and we’ll cover it in more detail, but it’ll make more sense when you see the example in the spreadsheet.
Consider this example of data collected about observed small mammals in desert research plots (from Data Carpentry’s Lesson for Ecologists, Formatting Data Tables in Spreadsheets section). How could things go wrong with the data presented this way? Try to think of at least two ways.
Try saving your data as a .csv (common separate values) file. This saves just the active sheet without formatting.
Module 2 Video: Functions (31:58 minutes)
(View the Functions sheet of the spreadsheet.)
If you find you need to reformat observations, here are some functions that can help.
=LEFT()Extracts a specified number of characters from a variable, counting from the left
=RIGHT()same as above, but counting from the right
=TRIM()Removes all whitespace aside from single spaces between words
=CONCAT()combines multiple cells of text into a single text cell
Paste Transpose (View the Transpose sheet of the spreadsheet.)
Module 3 Video: Common Problems (25:51 minutes)
(View the Splitting sheet.)
The “Text to Columns” tool (Data Tab>) lets you split a cell into multiple cells based on width or a special character (delimiter).
When dealing with human-readable text, we often have categories listed once with the implication that all lines before the next category fall into this group. For example, in the Blanks sheet we might assume that Bertie Rudolph is a Freshman. While this is human readable, the relationship won’t be clear to the computer!
There are a couple of ways to fill such blanks. (View the Blanks 1 sheet.)
You may already be familiar with this method. Highlight the cell whose value you want to repeat. Click on the small square at the lower right corner of the highlighted cell and drag it downwards over the cells you want to fill. The values will fill in when you release the mouse button.
You can also use this technique to extend numeric or date series: if you have consecutive cells with 1, 2, 3, highlight all three cells and drag the dot to extend the numeric sequence to whatever end value you wish.
You cannot use it to extend letter series, but you can repeat letter patterns: if you extend A-B-C over six new cells, you won’t get A-B-C-D-E-F-G-H-I, but instead A-B-C-A-B-C-A-B-C.
You can even select all three of the examples in Blank 1 and drag them at once. Excel interprets each series appropriately.
Note: You can change some of your options with the Auto-fill Options icon that appears after you finish the fill: choose to copy instead of extending a series; choose to fill without formatting; choose to fill the formatting only; etc. Click the Auto-fill icon to browse its options.
This works with both Windows machines and Macs, but only for cells continuous with the one you want to copy. Also, for the Blanks 2 exercise in the next sheet, you would have to repeat this for each category you need to fill in.
Proceed to the Blanks 2 sheet. Think about scale: imagine having to repeat this action if you had thousands of cells in different categories like this to fill.
In each of our formulas so far, we’ve referred to cells like this:
to indicate the first row of column A. When we copy or drag this reference one cell to the right it becomes B1, or if we drag it one cell down, it automatically becomes A2.
Sometimes we don’t want our references to change as we drag our formulas, though. Absolute references provide unchanging references by placing a $ before the column letter and row number:
The reference above will stay the same no matter where we move it.
Module 4 Video: VLOOKUP (29:14 minutes)
(View the VLOOKUP sheet.)
The VLOOKUP function provides a way to merge or join additional data into a dataset, using a common code or value.
So here’s an example of a VLOOKUP function:
and here’s what each element of the function means:
|A3||lookup_value||Value in our main table that we’re looking to match in the other table|
|$F$3:$G$9||table_array||The other table we need information from (lock references with $)|
|2||col_index_num||The column from the other table we’re looking for|
|FALSE||[range_lookup]||Whether you want approximate matches [TRUE] or exact matches [FALSE]|
VLOOKUP can refer to a value in a different sheet or even a different workbook on your computer. If you click into a cell on the other table while filling out your VLOOKUP formula, it will automatically supply the reference necessary to link to the other sheet or workbook.
(View the Ex_Main sheet for a second, more complex VLOOKUP exercise which will look up a second table located in the Ex_Lookup sheet.)
Module 5 Video: PivotTables (34:52 minutes)
(View the second Excel spreadsheet, Pivot_Tables_IPEDS.)
PivotTables create cross-tabulations displaying values split out across categories displayed as row and/or column headings. Make sure you have only one cell or the entire table selected to ensure Excel auto-detects your data correctly.
Windows: Insert Tab>PivotTable
Mac: Go to Data Tab>PivotTable>Create Manual PivotTable…
Adding data: Click and drag to areas at the bottom of “PivotTable Fields”. Remove by dragging back to list.
Go ahead and drag the category State to the Rows area, and Control of Institution to the Columns box. Finally, click to select the very last variable in the list, Endowment (per FTE enrollment). It should appear in the Values box as “Sum of Endowment (p…,” but you can change the value that appears there. Click on it to see the menu, Value Field Settings. You could choose Average to make your pivot table look like the example below. We’ll talk about this more in a minute.
Columns and Rows: The categories on the edges of the PivotTable
Values: The numbers shown in the cells of the PivotTable (each cell summarizes one variable for the group defined by the combination of its column and row categories.)
In most cases, there will be many rows in your dataset represented by one cell in your PivotTable, so we need to summarize or aggregate the data. In the example above, there are many public or private universities in each state.
Disclaimer: For many of these questions, there are multiple ways to get to the correct values. These are merely one way you could get the desired results.
Companion Excel Workbook with all exercises completed with formulas and pasted values.