BCA 152 - Comprehensive Spreadsheets

1. Manage multiple worksheets and workbooks.
   1. Create worksheet groups, format and edit multiple workbooks simultaneously
   2. Consolidate information from multiple worksheets using 3-D referencing
   3. Construct and edit links to data in other workbooks
   4. Demonstrate use of templates, both existing and custom
2. Produce and manipulate worksheets and workbooks.
   1. Create and navigate worksheets and workbooks.
   2. Format worksheets and workbooks, and customize options and views.
3. Evaluate formulas, formatting, page set-up, emphasizing best-of-practice methods.
   1. Create formulas emphasizing importance of cell referencing to avoid “hard coding”
   2. Rotate between results view and formula view
   3. Manage columns and rows
   4. Use the TRANSPOSE function
   5. Select page setup and printing options
4. Perform quantitative analysis using formulas and functions.
   1. Distinguish use of relative, absolute, and mixed cell referencing
   2. Correct circular reference
   3. Capitalize on features and information within the Functions Argument dialog box
   4. Use financial functions such as PMT, FV, PV, COUNT
   5. Troubleshoot errors using formula auditing tools
5. Assess data visually using charts.
   1. Interpret assortment of published charts for meaning and design
   2. Understand role of column and row labels in planning chart design
   3. Explain how chart type, chart layout and chart design can impact data representation/interpretation
   4. Modify chart data source
   5. Modify chart options including use of trend line
   6. Create two-axis charts
6. Organize large volumes of data with tables and data-manipulation features.
   1. Explain table design features including intended purpose, strategic field names, impact of empty columns/rows/fields, data consistency, and other design concepts
   2. Design tables using industry-proven design principles
   3. Add data validation rules to cells
   4. Use the Text to Columns feature to separate data into separate fields
   5. Explain use of TRANSPOSE feature in a database environment
   6. Implement effectively table formatting using features of the Table Tools Design tab
   7. Use screen manipulation features such as Split and Freeze
   8. Manipulate data using sort, nested sorts, filtering and conditional formatting rules
   9. Convert table to a range as well as a range to a table
   10. Produce data subsets using a criteria range, database range, and extract range within the Advanced Filter feature
   11. Create and run macros
7. Evaluate data with pivot tables and pivot charts.
   1. Generate aggregate data using structured references and a total row
   2. Gain proficiency in using the Subtotal feature including grouping and multiple levels of subtotals
   3. Create and modify pivot tables
   4. Interpret data generated by a pivot table by creating a written synopsis.
   5. Use pivot table features including filtering, slicing, and calculated fields
   6. Explore use of the PowerPivot add-in
   7. Create a pivot chart
8. Execute what-if analysis functions.
   1. Use database functions such as DSUM, DAVERAGE, DCOUNT and DCOUNTA, COUNTBLANK
   2. Analyze applicability and benefits of such functions as SUMIF, COUNTIF, SUMIFS, AVERAGE IFS
   3. Summarize data using 3D formulas
9. Critique specialized functions including logical and lookup functions.
   1. Generate results using logical functions such as IF, AND, NOT, OR
   2. Create nested logical functions
   3. Explain purpose of the MATCH and INDEX lookup functions
   4. Demonstrate use of such text functions as PROPER, CONCATENATE, TRIM, CHAR, CLEAN