Information Technology 281

ITEC 281: Data Management and Analysis with Spreadsheets.

Credit Hours: (3)

A study of basic and advanced techniques for data analysis and management using electronic spreadsheets.   Coverage includes the study of features for gathering, managing, manipulating, and filtering data sets including multidimensional data.  Techniques for collaborative use of a spreadsheet are covered including multi-user access, formula protection, and security.  Advanced analysis coverage includes built-in and user defined functions, goal seeking, sensitivity analysis, scenario management, and data visualization.

Detailed Description of Content of Course

Topics include:
1. Spreadsheet Fundamentals
    a. Workbook and sheet management
    b. Data entry and formatting
    c. Formulas
    d. Debugging formulas, circular references, formula auditing
    e. Relative formulas, formula replication
2. Built in Functions
    a. Basic functions
        i. numeric
        ii. string
        iii. logic
        iv. date
    b. Searching for functions
    c. Advanced functions
3. Data Management Techniques
    a. Importing and parsing data
    b. Linking to external data
    c. Splitting and freezing panes
    d. Sorting, querying, grouping, and filtering rows
    e. Forms generation
    f. Data validation
4. Advanced Analysis
    a. Goal seeking
    b. Scenario management and sensitivity analysis
    c. Data visualization with Charts and Graphs
    d. Pivot tables
5. Saving Instructions
    a. Spreadsheet Macros
    b. User Defined Functions
6. Collaboration Techniques
    a. Simultaneous worksheet access and revision tracking
    b. Cell and formula protection
    c. Worksheet security (macro security, encryption, and passwords)

Detailed Description of Conduct of Course

Course delivery methods will include classroom lectures, discussion, examples, and student exercises.  The class may meet occasionally in a computer lab.  Exercises will focus on problem solving using specific spreadsheet techniques.

Goals and Objectives of the Course
Students who complete the course will be able to:
1. Demonstrate the ability to efficiently solve a problem using a spreadsheet, employing where necessary the advanced features learned in the course including construction and/or using appropriate formulas and functions.
2. Describe data management, collaboration, and analysis tools available within spreadsheet software.
3. Import and manipulate data within a spreadsheet.
4. Demonstrate the ability to organize, document, and protect spreadsheet solutions.
5. Identify when a particular problem requires a solution involving macros or user defined functions.
6. Demonstrate the ability to automate spreadsheet tasks using macros and command buttons.
7. Demonstrate the ability to create and use custom functions.
8. Demonstrate the ability to provide appropriate charts and graphs of data given a particular decision requirement.

Assessment Measures

At least 50 % of the course assessment will be based on actual spreadsheets produced, formatted, corrected, or extended by the students.  This may include problems solved for homework or during in-class testing.   The remainder of the course assessment can consist of more traditional tests, quizzes, papers, or presentations.

Other Course Information


Review and Approval

October 22, 2008    Initial Course Approval    Art Carter, Chair

Revised: June 1, 2012