Excel templates for creating reports
From skylightwiki
This page applies to Version 1.3 (August 2009)
Contents |
Background
Here is a simple, manual approach for creating reports for student course evaluations. The approach can also be applied to other studies where a) each of a number of groups of respondents gets the same survey and b) an identical report is needed for each respondent pool. With Silhouette (aka Flashlight Online 1.0), such parallel surveys can be created using the "Categorical Variables" command.
The strategy requires a spreadsheet (Washington State University uses Microsoft Excel) that supports multiple worksheets in the same workbook.
For users of Silhouette (aka Flashlight Online 1.0) this technique can be employed now (CTLT has been doing it for 4-5 semesters). For users of Skylight (aka Flashlight Online 2.0) this technique is supported (and enhanced) as a custom reporting method.
This approach was developed by Tom Henderson and refined by Andrew Morozov while each was coordinating end of term course evaluations for CTLT.
Concept
It is possible in Excel to write formulas on one worksheet of a workbook to address data on another worksheet. For purposes of this example, there are three worksheets in one Excel workbook. The sheets are called: "Data," "Analysis," and "Report." The Data sheet is populated with structured data coming directly from the survey; the structure of the data will be the same for each respondent pool. Specifically, a download to this page from either Silhouette or Skylight has tabular data with each row representing a single respondent and with columns that contain the various questions (aka "R-numbers"). Equations and graphs on the Report worksheet are written to get their data from the Analysis worksheet, which gets its data from the Data worksheet. The Data sheet is hidden before giving the report to the end user.
On the Analysis worksheet, formula are written to count or otherwise analyze columns of the Data sheet. The formulas are written with large ranges, {e.g., =SUM(DATA!B2..B2000) or COUNTIF(DATA!CA2..CA2000,1) } because the actual number of rows in the data are unknown in advance. The result is that the Analysis page contains summary results such as totals, frequency distributions, and other summaries of the raw data.
On the Report worksheet, graphs and tabular representations are made using the data from the Analysis page.
Example in Silhouette
In the case of end-of-term course evaluations, CTLT administers a survey to multiple course sections using the "Categorical Variable" techniques of Silhouette (aka Flashlight Online 1.0). The resulting data download contains an "X1" variable which is the name of the course which was passed into Silhouette on the URL given to the respondent.
The downloaded data are sorted by the X1 variable, so all the respondents from a single course are grouped together. The rows of data with the same X1 variable are [MANUALLY?] copied from the downloaded data sheet and pasted into the Data worksheet of the report workbook. The report workbook is "Saved as..." with the name of the course and the process repeated [A DIFFERENT WORKBOOK FOR EACH RESPONDENT POOL?]. Each resulting workbook has the same report format with the course-specific data
Step-by-step Manual Procedure
The approach requires several steps for each respondent pool:
- a) download data to a scratch Excel workbook (this is your data archive);
- b) sort the data by respondent pool and do any other data cleaning necessary,
- c) copy all the rows of data for a SINGLE respondent pool to a second Excel workbook on a worksheet named ("Data");
- d) "save as" the workbook with the name of the respondent pool,
- e) create the report on another worksheet (named "Report") (using formulae that tap data from cells on the "Data" worksheet),
- f) save this master,
- g) do a "Save as" to create a new workbook named for the a respondent pool,
- h) replace the data from the previous respondent pool on the "Data" worksheet with data from the next respondent pool (using copy/paste commands) (see step "c" above), thereby automatically creating a new report on the "Report" worksheet. Note: best way to accomplish this is to select all the data on the sheet and do a "Clear cells" command,
- i) "Save" the new report,
- j) repeat steps g-i for each successive pool of respondents until a workbook and report have been created for all the respondent pools.
Example in Skylight
In Skylight (aka Flashlight Online 2.0) the Categorical Variable concept is replaced with the idea of a Respondent Pool, but the concept is similar. While it is possible to do the manual procedure required in Silhouette (above), the Excel Report Custom Download makes the manual process unnecessary.
Categories: Help | Respondent Pool | Excel | Reports | Version 1.3