Excel

UNC Libraries logo

Working with Data in Excel

Bookmark this page to return to it with ease.

Download Companion Excel Sheet

Position your windows so the spreadsheet takes up half your screen and this guide takes up the other half.

Table of Contents:

Instructional videos are linked at the beginning of each of the five sections. Please list questions on this page for the discussion meeting.

Getting Started

Module 1 Video: Getting Started (14:12 minutes)

(View the first sheet of the Excel_Workshop spreadsheet.)

Shortcuts

To… Windows Mac
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.

Best Practices and Tidy Data

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.

not-so-Tidy Data example

Try saving your data as a .csv (common separate values) file. This saves just the active sheet without formatting.

Functions

Module 2 Video: Functions (31:58 minutes)

Using Functions in Excel

(View the Functions sheet of the spreadsheet.)

Example of using SUM function in Excel

Working with Text

If you find you need to reformat observations, here are some functions that can help.

Pasting

Common Problems

Module 3 Video: Common Problems (25:51 minutes)

Splitting on Delimiters

(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).

Text to columns example in Excel

Filling Blanks

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.)

  1. 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.

    • (PC) Home Tab, Editing > Find & Select > Go to Special > Blanks > OK * (Mac) Edit Menu > Find > Go To…> Special… > Blanks > OK * Type =, then hit the up directional arrow. Hit CTRL+Enter (PC) or ⌘+Enter (Mac) Filling blanks example in Excel

Cell References

In each of our formulas so far, we’ve referred to cells like this:

=A1

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:

=$A$1

The reference above will stay the same no matter where we move it.

VLOOKUP

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.

VLOOKUP example in Excel

So here’s an example of a VLOOKUP function:

**`=VLOOKUP(A3,$F$3:$G$9,2,FALSE)`**

and here’s what each element of the function means:

Value Parameter Description
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.)

Introduction to PivotTables

Module 5 Video: PivotTables (34:52 minutes)

(View the second Excel spreadsheet, Pivot_Tables_IPEDS.)

Pivot_Tables_IPEDS.xlsx

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.)

PivotTable example in Excel

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.

Aggregation of Values

Sorting and Filtering Columns and Rows

Filters

PivotTable Exercises

  1. What are the “Enrolled Totals” in public and private schools (see “Control of Institutions”)?
  2. Which “Geographic Region” has the highest average “ACT Composite 75th Percentile Score”? How many regions have average scores below the national (Grand Total) average?
  3. Which category of “Degree of Urbanization” contains the most public (“Control of Institution”) universities?
    1. What percent of the “Applications Total” go to each of the top two “Degrees of Urbanization”? (Hint: Use the Show Values As tab in Values Field Settings)

Next Steps:

Other Useful Tools to Explore in Excel:

Getting Help:

Data Sources:

SOLUTIONS

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.

Completed Companion Excel Workbook

Companion Excel Workbook with all exercises completed with formulas and pasted values.

Pivot Tables Exercises

  1. What are the “Enrolled Totals” in public and private schools (see “Control of Institution”)? Solution to PivotTable exercise
    • Drag “Control of Institutions” to Row Labels to layout the table
    • Drag “Enrolled Total” to Values
    • Click “Enrolled Total” in Values and select Value Field Settings…
    • Change Summarize Values to Sum instead of the default Count
  2. Which “Geographic Region” has the highest average “ACT Composite 75th Percentile Score”? How many regions have average scores below the national (Grand Total) average? Solution to PivotTable exercise
    • Drag “Geographic Region” to Row Labels
    • Drag “ACT Composite 75th Percentile Score” to Values
    • Click “ACT Composite 75th Percentile Score” to Values and select Value Field Settings…
    • Change Summarize Values by to Average. * Sort the table by clicking the arrow icon in the corner of Row Labels on the table itself (see arrow)
    • Click More Sort Options, then select Descending (Z to A) and set to “Average of ACT Composite 75th Percentile Score”

Example of sorting a PivotTable

  1. Which category of “Degree of Urbanization” contains the most public (“Control of Institution”) universities? Solution to PivotTable exercise
    • Drag “Degree of Urbanization” to Row Labels. Drag “ID number” to Values.
    • Click “ID Number” in Values, then Value Field Settings > Summarize Values by to set aggregation to Count.
    • Note: ANY variable that doesn’t have missing values will work here. The Count aggregation is essentially counting the number of filled cells in the given categories. So some values will have slightly different counts due to empty cells. * Drag “Control of Institution” to Report Filter. Use the Filter appearing at the top of the screen to select “Public”. * Use the Row Labels arrow, then More Sort Options to sort by “Count of ID Number”
      1. What percent of the “Applications Total” go to each of the top two “Degrees of Urbanization”? (Hint: Use the Show Values As tab in Value Field Settings) Solution to PivotTable exercise 3
    • Assume that we keep the Public filter active. Leave the earlier work in place.
    • Drag “Applicants Total” to Values and place it under “Count of ID number”.
    • Click “Applicants Total” and select Value Field Settings
      • Under Summarize Values by, choose sum
      • Under Show Values As, choose % of Column Total