Information Technology 281

ITEC 281
Data Management and Analysis with Spreadsheets

1. Catalog Entry

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.

2. Detailed Description 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. 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
    6) Collaboration Techniques
        a. Cell and formula protection
        b. Worksheet security (macro security, and passwords)

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

4. 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) Demonstrate the ability to format data using built in features such as tables, colors, conditional formatting, borders, alignment and
       print layout functions.
    3) Import and manipulate data within a spreadsheet.
    4) Demonstrate the ability to organize, document, and protect spreadsheet solutions.  
    5) Demonstrate the ability to automate spreadsheet tasks using macros and command buttons.
    6) Demonstrate the ability to create and use custom formulas.
    7) Demonstrate the ability to provide appropriate charts and graphs of data given a particular decision requirement.

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

6. Other Course Information


Review and Approval

October 22, 2008

June 1, 2012

June 20, 2015