OFF 120: Microsoft Excel

Citrus College Course Outline of Record

Citrus College Course Outline of Record
Heading Value
Effective Term: Fall 2023
Credits: 3
Total Contact Hours: 72
Lecture Hours : 54
Lab Hours: 18
Hours Arranged: 0
Outside of Class Hours: 108
Total Student Learning Hours: 180
Transferable to CSU: Yes
Transferable to UC: No
Grading Method: Standard Letter, Pass/No Pass

Catalog Course Description

An Excel worksheets and workbooks course to include: formatting, calculating sums, formulas, charts, enhancing the worksheet, and making decisions using the IF function, completing financial functions, data lists, large workbooks using templates, using advanced sorting and filtering, creating charts, diagrams, and templates, creating PivotTable, PivotChart reports, and auditing worksheets, using data analysis, solver, and scenario features, using macros, and Visual Basic for applications, external data, database functions, side-by-side tables, and collaborating with others and preparing a workbook for distribution. 54 lecture hours, 18 lab hours.

Course Objectives

  • describe, plan, create, and correct a worksheet using Excel
  • merge workbooks, track changes, create a shared workbook.
  • embed a column, chart, print, and save a worksheet
  • build a worksheet that includes formulas, use recalculation features, and print different versions of a worksheet
  • use the IF functions, apply formulas that use absolute cell referencing, panes, goal seeking, and what-if analysis
  • freeze panes, zoom in and out, add, change and delete values, and format in a worksheet
  • create, format, and edit PivotTables, PivotCharts
  • create, record, and test Macros
  • write VBA procedure, import Access, Web page, and text file
  • insert second tables, screenshots and format side by side tables
  • Create, change, add calculated fields, filter, refresh and format PivotTables

Major Course Content

  1. Building A Worksheet
    1. Introduction to Excel
      1. Worksheets, chart preparation steps
      2. Cell, grid lines, active cell, mouse pointer
      3. Worksheet window
      4. Selecting a cell
    2. Entering text
      1. Entering titles
      2. Correcting mistakes while typing
      3. Entering column titles
    3. Using fill handle to copy adjacent cells and summing row totals
    4. Formatting the worksheets font, size, and style
    5. Auto format
    6. Charting a worksheet
    7. Saving and printing a worksheet
    8. Opening a worksheet
    9. Correcting errors
      1. In-cell
      2. Prior to entering data
      3. Clearing cells and worksheets
    10. Using on-line help
    11. Planning a worksheet
      1. Define the problem
      2. Design the worksheet
      3. Enter the worksheet
      4. Test the worksheet
  2. Formulas, Formatting, and Creating Charts
    1. Entering titles and numbers into the worksheet
    2. Entering formulas using the point mode
    3. Using the Average, Max, and Min functions
      1. Calculating the highest value in a range using the Max function
      2. Calculating the lowest value in a range using the Min function
      3. Copying the Max and Min functions
    4. Applying formats in Worksheets
    5. Applying number formats
    6. Changing widths of columns and rows
    7. Creating a 3-D column chart
      1. Drawing and enhancing a 3-D column chart
      2. Changing names on sheet tabs
    8. Printing the workbook
      1. Printing sections
      2. Displaying and printing formulas
  3. Enhancing a Worksheet and Chart
    1. Using the draw toolbar
    2. Creating formulas with an Assumption table
    3. Using fill handle to create series
    4. Copying to non-adjacent cells
    5. Inserting and deleting rows, columns
    6. Freezing worksheet titles
    7. Displaying system date
    8. Absolute versus relative cell referencing
    9. Making decisions—the IF function
    10. Adding drop shadows
    11. Changing values in cells that are referenced in a formula
    12. Goal seeking
  4. Creating Data Lists
    1. Creating a database in Excel
    2. Using V-Lookup
    3. Using subtotals
    4. Finding data using auto filter and advance auto filter
    5. Creating Criteria and Extract areas to find data
    6. Using D-Sum, D-Average, D-Count
    7. Define name
    8. Validate data
  5. Creating Financial Worksheets
    1. Loan calculator sheet
    2. Interest rate chart
    3. Calculating present and future values
    4. Creating Amortization Schedule
  6. Large Worksheets and Templates
    1. Creating a template
    2. Creating multiple sheets from template
    3. Creating a 3-D sheet reference formula
    4. Annotating text in Chart
    5. Using Word Art
    6. Using find/replace in a Workbook
  7. Sorted, Filtered, and Outlined Database
    1. Sorting on multiple columns
    2. Sorting by using column list
    3. Filtering by format and value using Auto Filter
    4. Filtering by Custom Criteria
    5. Inserting sheet names and page numbers in footers
    6. Filtering by using Advanced criteria
    7. Extracting filtered rows
    8. Subtotal, Outlining, and Grouping List Data
  8. Creating Charts, Diagrams, and Templates
    1. Creating and formatting Spark lines
    2. Changing display of Chart data
    3. Editing source data
    4. Inserting Trend line
    5. Creating and Editing Smart Art Graphics
    6. Creating and Formatting Templates
    7. Protecting worksheets
  9. Creating Pivot Tables, Pivot Charts, and Auditing Worksheets
    1. Creating a PivotTable report
    2. Adding fields
    3. Using slicers
    4. Clearing filters
    5. Changing calculations in report
    6. Creating Pivot Charts
    7. Modifying charts
    8. Using Trace Precedents and Trace Dependents
    9. Using Watch Window
  10. Using the Data Analysis, Solver, and Scenario Features
    1. Calculate a Moving Average
    2. Creating a custom number format
    3. Charting Break-even Point Chart
    4. Using Solver
    5. Evaluate complex formulas
    6. Creating Scenarios using Solver
  11. Using Macros and Visual Basic for Applications
    1. Adding the Developer Tab to Ribbon
    2. Changing Macro Security
    3. Recording Macro
    4. Testing Macro
    5. Changing the Visual Basic Code
    6. Inserting ActiveX Controls
    7. Writing VBA procedure for Command button
    8. Testing VBA Procedure
  12. External Data, Database Functions, and Side-by-Side Tables
    1. Importing into Excel from Access
    2. Creating Query
    3. Using DGET
    4. Inserting Table in Worksheet
    5. Applying Icons to Side by Side tables
  13. Collaborating with Others and Preparing a Workbook for Distribution
    1. Modifying worksheet properties
    2. Activating track changes
    3. Copy a shared workbook
    4. Make changes to a shared workbook
    5. Merge revisions
    6. Add signature line
    7. Ensuring Backward-Compatibility in a workbook
    8. Inspecting a document
    9. Encrypting a workbook
    10. Uploading to Sky Drive

Lab Content

  1. Lab assignments with business document processing for the Excel application software.
  2. Quizzes to test theory of the applications.
  3. Training and applying concepts through testing.
  4. Practice, homework, and testing in MYITLAB assessment program.

Suggested Reading Other Than Required Textbook

Review the Practice Quizzes and Tests on Blackboard for the course.

Examples of Required Writing Assignments

Display the Annual Expenses worksheet. Construct formulas to calculate the Total by Quarter. Use absolute cell references in constructing formulas.

Examples of Outside Assignments

Create a macro to automatically fill in the employee information (Name, Address, City, State, ZIP Code, Position, Supervisor, Employee ID, Phone, Email). Use Ctrl + t as the shortcut key and type "Fills in employee information" as the macro description. The file is protected; timecard is the password.

Instruction Type(s)

Lab, Lecture, Online Education Lab, Online Education Lecture