Developing Multipurpose Grade Reports Using Excel PivotTables

Developing

Multipurpose

Grade

Reports

Using

Excel

PivotTables

NEAIR – Boston December 5, 2011

Jim Fergerson

Dir. of Institutional Research and Assessment, Carleton College Northfield, Minnesota

Overview/Objectives



_Why?

_Ongoing

_concerns

_about

_grade

_inflation

and

grading

patterns



Why

Excel?



Developing

reports—Examples

of

several

different

reporting

techniques

using

PivotTables

(paper,

interactive)



Report

Gallery:

Examples

of

static

and

interactive

multidimensional

grade

reports

3.00 3.05 3.10 3.15 3.20 3.25 3.30 3.35 3.40 3.45 3.50 3.55 3.60 1978 ‐ 79 1979 ‐ 80 1980 ‐ 81 1981 ‐ 82 1982 ‐ 83 1983 ‐ 84 1984 ‐ 85 1985 ‐ 86 1986 ‐ 87 1987 ‐ 88 1988 ‐ 89 1989 ‐ 90 1990 ‐ 91 1991 ‐ 92 1992 ‐ 93 1993 ‐ 94 1994 ‐ 95 1995 ‐ 96 1996 ‐ 97 1997 ‐ 98 1998 ‐ 99 1999 ‐ 00 2000 ‐ 01 2001 ‐ 02 2002 ‐ 03 2003 ‐ 04 2004 ‐ 05 2005 ‐ 06 2006 ‐ 07 2007 ‐ 08 2008 ‐ 09 2009 ‐ 10 Mean   GPA

Mean

End

of

Year

Cumulative

GPA

Trends

All Students Graduating Seniors First Year Students

0% 10% 20% 30% 40% 50% 60% 70% 80% 90% 100% 2009 ‐ 10 2008 ‐ 09 2007 ‐ 08 2006 ‐ 07 2005 ‐ 06 2004 ‐ 05 2003 ‐ 04 2002 ‐ 03 2001 ‐ 02 2000 ‐ 01 1999 ‐ 00 1998 ‐ 99 1997 ‐ 98 1996 ‐ 97 1995 ‐ 96 1994 ‐ 95 1993 ‐ 04 1992 ‐ 93 1991 ‐ 92 1990 ‐ 91 1989 ‐ 90 1988 ‐ 89 1987 ‐ 88 1986 ‐ 87 1985 ‐ 86 1984 ‐ 85 1983 ‐ 84 1982 ‐ 83 1981 ‐ 82

Academic Year Letter Grade Trends

A B C D F P/F + Other

Source:  Annual Grade Surveys.  Carleton has a generous “pass/fail” option to encourage students to try courses in subjects outside of their comfort 

zone.  Some courses have a pass/fail option by student choice; others are designated by the instructor.

Satisfactory / 

Unsatisfactory or 

Credit / No Credit 

Stuart Rojstaczer. “Grade Inflation at American Colleges and Universities”. GradeInflation.com. March 2009.  (Includes Carleton data, 1978‐2004.)

We

are

NOT

Stuart Rojstaczer. “Grade Inflation at American Colleges and Universities”. GradeInflation.com. March 

2009. (Includes Carleton data, 1978‐2004.)

We

are

not

Existing Report‐

One Dimensional, Paper

Names 

Multidimensional Grade

Analysis

is

Vital!

College

‐

wide

snapshots

tell

us

little;

to

understand

the

data

and

to

make

any

difference,

departments

and

instructors

need

much

broader

context

We

needed

to

replace

one

program that

presented

only

one

report

of

the

data

‐

with

‐

One

data

model

that

is

flexible

enough

to

provide

N

‐

dimensional

views

to

explore

the

Institutional/Department

‐

Program/Faculty

Dimensions

•

What is the grade point average and letter grade 

distribution?

–

All

college

–

By

department,

program,

or

division

–

By

instructor

–

By

course

and

course

level

–

Class

rosters,

distribution

of

grades

within

a

section

–

Background

material

for

faculty

reviews

–

Compare

outcomes

in

a

course

over

time

(i.e.

Intro

Science)

–

By

other

factors:

gender,

tenure

status,

rank,

seniority,

method

of

instruction,

etc.

Student

‐

Level

Dimensions

•

_What

_is

_the

_grade

_point

_average

_and

_letter

_grade

distribution?

–

By

gender,

race/ethnicity,

socioeconomic

status,

legacy/1

generation

status,

entering

or

graduating

cohort,

class

level,

course

level,

major

vs.

non

‐

major

–

How

many

students

in

category

X

took

a

course

in

Y?

–

How

many

and

what

type

of

students

continued

past

the

entry

level

course?

–

Identification

of

at

‐

risk

(or

outstanding)

students

–

Evaluation

of

admissions

selection

–

Individual

course

patterns

– pseudo

‐

transcripts

–

Complicating

factors:

Multiple

majors,

privacy

issues

Time Dimensions – What Changes?

–

Calendar

– Academic

Year/Term

–

Trends

for

institution,

department,

faculty

member

–

Categorical

changes

over

time

(Are

1

generation

students

improving?

etc.)

–

Longitudinal

tracking

(Are

individuals,

specific

cohorts

improving

during

their

four

years?)

–

Pre

‐

test/Post

‐

test

evaluations

(Was

there

improvement

in

Chem 101

after

adoption

of

Why

Use

Excel?

•

_Old

_program

_became

_{obsolete with}

_database

_upgrade.

•

_New

_reports

_are

_needed

_now,

_as

_Faculty

_review

_grading

practices

and

want

better

information.

•

_A

_Business

_Intelligence

_(BI)

_tool

_{could make}

_reporting

and

distribution

more

efficient,

but

we

don’t

have

a

campus

‐

wide

common

package.

Excel

is

the

most

widely

available

and

understood

tool

for

those

likely

to

use

reports.

•

_Ease

_of

_exploratory

_data

_analysis

_and

_Flexibility:

_New

reports

can

be

easily

updated

directly

from

our

Alternative

Data

Warehouse

Connection

Properties

SQL code to query raw data (user‐modifiable) _{OLAP Cube Connections}  (pre‐defined by IT)

Working

with

OLAP

Cubes

Pros:

• Speed: Large amounts of data 

pre‐calculated by remote server

• More fields (link cubes to cubes)

• Uses vastly less memory: the raw 

data isn’t brought to your 

desktop  (2.5 MB file with OLAP vs. 43 MB 

file to query 10 years of raw data)

Cons

:

• IT will probably need to set OLAP 

cubes up and grant access

• Limited to pre‐defined and pre‐

calculated value fields

• Less flexibility for custom 

calculations and some Excel 

options

• With cubes, some calculations, 

such as multiple views of the 

same value field (ex. Grades N/%) 

require Excel 2010

OLAP

=

O

n

L

ine

A

nalytical

P

rocessing

Cubes/Value Dimensions

Use

PivotTable

Filters

to

Select

Valid

Grades,

Course

Levels,

and

Terms

(i.e.,

Parameters

that

should

remain

CONSTANT

across

all

reports

)

Select only the 

items that are 

currently used, 

Experiment

with

Some

Tabular

Reports

by

Dragging

in

Some

Changing

Dimensions

that

You

Wish

to

Explore

Trends by Level by 

Department

Course Detail for 

Instructor One Year by 

Term

Course Roster and 

Student Grades for a 

When

You’ve

Built/Tested

One Master

PivotTable:

Then

Copy,

Modify,

Adjust,

and

Recycle It

to

Other

Tabs

•

Memory:

Copies of

a

master

PivotTable

(linked

to

the

same

data

source)

don’t

duplicate

the

data;

it’s

easy

to

“refresh

all”

•

Parameters:

Your

key

parameters

carry

over

from

report

to

report;

only

minor

adjustments

needed.

(By

copying

the

master

spreadsheet

tab—

you’re

also

copying

default

headers,

Choose the Appropriate PivotTable Options

Click in PivotTable, then 

“right‐click” to select 

“PivotTable Options” Turn “autofit” off,  or 

you’ll need to re‐size your 

Choose the Appropriate PivotTable Options

Letter

grades

or

other

labels

will

sort

alphabetically,

and

not

necessarily

in

the

desired

order.

You

can

either

arrange

them

manually

from

within

the

PivotTable,

or

pre

‐

define

a

custom

sort

order

in

“Excel

Options/Advanced/Custom

List”.

Need for  totals varies  by report DO show all relevant  items, so  you’ll have a  consistent  data matrix! Check only if you 

need to “drill 

Choose

the

Appropriate

Field

Setting

Options

Click in PivotTable,  then “right‐click” the  field label to select  “Field Settings”  options

Varies by context of your 

report(s)

Experiment!—Drag to “row or column labels” or 

drag up/down within fields box to change report 

layout!

Duplicate fields, 

Hint:

Become

Familiar

with

the

“PivotTable

Tools”

Ribbon

Options

Options:

Design:

Experiment

with

the

“Layout”

tools

to

help

structure

the

report

format—this

can

often

be

easier

than

setting

every

Report

Option

1

:

Grade

Average

Trend

Reports:

Computation

Area

A:

All

Carleton

GPA

Trend

PivotTable

Here, no filters are applied except the parameters 

Computation

Area

B:

Department

GPA

Trend

PivotTable

Change filter to create master PivotTable for next 

Computation Areas

Clean Printed 

Reports and Graphs

Key

Linkages

Dept. Label

Same data matrix: One page, 

Link

PivotTables

from

the

Computation

Area

to

the

Clean

Report

Using

Formulas;

Control

for

Errors

• Link formulas by hand, using cell references.  (Excel will, by default, try to use the 

more complicated “GETPIVOTDATA” 

references=GETPIVOTDATA("[Measures].[Grade ‐ Avg]",$B$116,"[Term].[Year ‐

Term]","[Term].[Year ‐ Term].[Academic Year].&[2010‐11]")

• Set up error‐trapping formulas to correct for cells with null values; replace with “”.

• Link the name of the selected department (from the PivotTable Report Filter cell) 

Finalizing

the

Grade

Average

Trend

Reports

1. Copy the tab of the final working Trend Report Master PivotTable (report and all print 

set‐up formats!) to new tabs (one tab for each department). 

2. Update the Report Field Department filter for each Department, and rename the tabs 

with the department name.

3. Check for errors, inconsistencies! 

4. Printing:  (In this case, all reports have the same data matrix and a 1‐page print area) a. SELECT ALL tabs to be printed in the desired print order.

b. Print paper copies to printer, and a storage copy to Adobe Acrobat file. c. IMPORTANT!  UNSELECT ALL printed tabs immediately after printing!  (Any 

changes made to current spreadsheet will apply to the  corresponding cells in any 

selected tabs unless this is done!)

5. Next Year:  Just update the filtered date ranges in each department tab. (But I’m looking 

Compare:

Carleton

vs.

Department

‐

Level

GPA

Carleton Grade Average

3.00 3.10 3.20 3.30 3.40 3.50 3.60 3.70 3.80 3.90 4.00 20 01 ‐ 02 20 02 ‐ 03 20 03 ‐ 04 20 04 ‐ 05 20 05 ‐ 06 20 06 ‐ 07 20 07 ‐ 08 20 08 ‐ 09 20 09 ‐ 10 Economics 3.00 3.10 3.20 3.30 3.40 3.50 3.60 3.70 3.80 3.90 4.00 20 01 ‐ 02 20 02 ‐ 03 20 03 ‐ 04 20 04 ‐ 05 20 05 ‐ 06 20 06 ‐ 07 20 07 ‐ 08 20 08 ‐ 09 20 09 ‐ 10 Music 3.00 3.10 3.20 3.30 3.40 3.50 3.60 3.70 3.80 3.90 4.00 20 01 ‐ 02 20 02 ‐ 03 20 03 ‐ 04 20 04 ‐ 05 20 05 ‐ 06 20 06 ‐ 07 20 07 ‐ 08 20 08 ‐ 09 20 09 ‐ 10 Psychology

Report

Option

2:

Academic

Year

Detail

Reports

by

Department

or

Instructor

1.

Compare

all

Carleton

letter

grades

and

grade

point

averages

to

detail

for

courses

completed

within

each

department,

by

course

level

2.

Provide

each

instructor

with

report

with

details

for

grades

they

gave

in

all

sections,

by

term

(including

those

offered

outside

their

primary

department)

3.

Output:

One

tab,

one

report,

print

many

pages,

but

with

PivotTable

Field

Selections

Instructor Sections  Detail

Department  by Level Detail

Hint:  You can assign 

“meaningful labels” to field 

names and values to make 

Set

Up

a

Multipage

PivotTable

Report

of

Unknown

Length

• Force a page break 

after the primary 

report item (instructor, 

department, etc.) by 

clicking on key data 

field label and “Insert 

page break after each 

item.  

• Caution:  Page breaks 

aren’t 100% perfect, 

but they work better 

when you also “Insert a 

blank line after each 

[page break] item 

label. (Watch for cases 

where only the 

subtotal rows cross a 

Printing

PivotTable

Reports

When

Print

Range

is

Unknown

Important: Leave “pages 

tall” blank!  Excel will 

calculate # of pages for a 

PivotTable print.   (Print 

job may stop before finish if 

you “force” a page limit.)

Do

a

“Select

All”

of

the

PivotTable

data

matrix,

set

any

headers,

footers,

margins,

repeating

rows,

etc.

in

the

Page

Setup

menus.

Recommendation:

Since

reports

can

be

large,

first

print

to

Adobe

Acrobat,

and

Report

Option

3:

“Show

Report

Filter

Pages”

In PivotTable Tools “Options” Menu 

(beneath “File”):

Quickly

let

Excel

generate

a

separate

tab

with

a

sub

‐

report

“Show

Report

Filter

Pages”

•

Many sub

‐

reports

are

possible

(i.e.

one

per

250+

faculty

members)

•

_{Unfortunately:}

–

Most

custom

formats

in

the

parent

report

are

lost

(print

set

‐

up,

column

size,

graphs,

etc.)

–

This

method

only

works

for

raw

data

reports,

not

Report

Gallery:

Examples

Derived

by

Modifying

the

Compare

One

Academic

Year’s

Grades

for

All

Instructors

in

a

Compare

Selected

Instructor

to

Carleton

Lookup

Instructor’s

Class

Roster

and

Student

Dean’s

Level

Interactive

Report

– Trends

by

Subject

Area

• Conditional formatting applies a “heat map” to color code grade point differentials.

• Embedded macro allows Dean to compare trends in subject to all Carleton trends

• Excel 2010 adds Edward Tufte’s “sparkline” graphs to show thumbnail trends for each 

Interactive

“Dashboard”

with

“Slicers”

“Slicers” became 

available in Excel 2010 

to allow more user‐

friendly and 

interactive data 

exploration than 

standard PivotTable 

Student

‐

Level

Cumulative

GPAs

by

Test

Score

Range

SAT Math and Critical Reading 

Range 1150‐1199 1200‐1249 1250‐1299 1300‐1349 1350‐1399 1400‐1449 1450‐1499 1500‐1549 1550‐1600 2.50 2.60 2.70 2.80 2.90 3.00 3.10 3.20 3.30 3.40 3.50 3.60 3.70 3.80 3.90 4.00 2005 2006 2007 2008 2009 2010 2011

ACT Score Range

 1‐25 26‐30 31‐36

Student

‐

Level

Cumulative

GPAs

by

Legacy

Status

and

Student’s

Home

Region

3.00 3.10 3.20 3.30 3.40 3.50 3.60 3.70 3.80 3.90 4.00 20 00 ‐ 01 20 01 ‐ 02 20 02 ‐ 03 20 03 ‐ 04 20 04 ‐ 05 20 05 ‐ 06 20 06 ‐ 07 20 07 ‐ 08 20 08 ‐ 09 20 09 ‐ 10 20 10 ‐ 11 1st Generation No Legacy Parent Legacy 3.00 3.10 3.20 3.30 3.40 3.50 3.60 3.70 3.80 3.90 4.00 20 00 ‐ 01 20 01 ‐ 02 20 02 ‐ 03 20 03 ‐ 04 20 04 ‐ 05 20 05 ‐ 06 20 06 ‐ 07 20 07 ‐ 08 20 08 ‐ 09 20 09 ‐ 10 20 10 ‐ 11 New England West Middle States South Midwest

Issues Yet to Be Resolved ‐

Political

Feedback? – too early; first reports have 

not yet been distributed broadly

Access!  Who gets to see what, and for 

what purpose?  

• Deans and IR can see and do analysis 

all

• Faculty can see most recent year and 

trends for own grades

To be discussed by Deans and Faculty 

Enrollment and Curriculum Committee:

• New data will generate new 

questions!

• Can chairs see department detail?

• How much detail can be available for 

program review and assessment?

• Privacy issues (esp. with “drill down”)

Technical – Pushing Excel to Its Limits

• Lots of dead trees!  Manual process‐‐

Need to find better ways to automate 

report updates and distribution.

• Data Policy:  Faculty can’t access data 

behind administrative firewall

• Improve design and appearance of 

reports; better automation, labels, etc.

• Discovering the limits and capabilities of 

Excel and the data warehouse

• Train Dean(s) to use 

analytical/interactive queries

• Opportunity:  The grade models are 

easily re‐purposed for many other 

Questions?

Discussion?

Special thanks for their technical support:

• Richard Goerwitz and Julie Latham, Information Technology Services, who 

designed the data warehouse and OLAP cubes

• Jody Friedow, Assoc. Dir. of Institutional Research and Assessment, who taught me