Certificate Course in MS Excel
Module Description
In today’s fast-paced, technical business environment, numbers are everything. Much of our success hinges on how well we can manage the data – statistics, planning, reporting – that we encounter on a daily basis. An incredibly powerful tool for doing this is Microsoft Excel. It is the standard spreadsheet application for both the business world and personal use.
Learning Outcomes
After the completion of Advanced MS Excel 2007 course students will be able to:
- Understand Excel Abilities and its Functions.
- Explain Data Security, Formatting and Analyzing concepts.
- Understand the specialized functions like DSUM, DMAX etc.
- Explain the advanced options of Pivot Table.
- Summarize data by using Sparklines.
- Record and play a macro.
- Edit a macro using VBA Editor.
- Explain how to manage scenarios.
- Describe the restrictions and manipulating techniques.
Delivery
Each theory module introduces aspects of MS EXCEL 2007 and is followed by a practical exercise. Every participant will have dedicated use of a workstation for these practical exercises.
Notional Student Workload
| Lectures/case studies sessions | 24 hrs |
| Guided Internship and practical training sessions | 20 hrs |
| Total | 44 hrs |
Assessment
Formal assessment of this module will be conducted through theory examinations, presentation, Assignments.
Assessment Components
| Sl. No. | Internal Components | Weightage | Remarks |
|---|---|---|---|
| 1 | Internal Assessment | 50 % | 50 Marks |
| 2 | End-Term Examination (Presentations/Assignment/Practicals/Projects etc..) | 50% | 50 Marks |
Course Material
The course material will be provided by the Department of Computer Science.
Course Outline and Breakup – MSExcel – 2007
- Basics in MSExcel.
- Excel 2007 interface
- The ribbon and the levels of command organization – Tabs, Groups and Dialogue box launcher.
- Office button and Excel options.
- Customizing the quick access tool bar.
- Working with formatting
- Formatting different data types.
- Built-in and custom formats.
- Conditional formatting of data using highlighting rules, top-bottom rules, data bars, color scales, icon sets and formulas.
- Data Validation
- Creating drop down lists using data sources in different work sheets and work books.
- Restricting data types in cells.
- Using formulas for validation.
- Sorting and Filtering
- Multilevel sorting, sorting with and without headers.
- Sorting using the custom list.
- Using auto filter.
- Text and number filters.
- Advanced data filtering based on criteria.
- Formulas and Functions
- Syntax, parameters and values.
- Different types of cell referencing styles – Absolute, Relative and mixed references.
- Creating and changing cell references.
- Linking data within worksheets and workbooks.
- Excel functions: Text, Logical, Lookup, Statistical, Financial including LOOKUP, VLOOKUP, HLOOKUP, IF, AND, OR, COUNTIF, COUNTIFS, SUMIF, SUMIFS and more.
- Nested functions and array functions.
- Formula auditing.
- Tracking formulas.
- Formula calculation options.
- Checking errors in formulas, error types, circular reference.
- Creating named ranges, using named ranges in the formulas and using the name manager.
- Pivot tables and Reports
- Introduction to pivot tables, creating pivot tables and analyzing data.
- Summarizing pivot table using calculations.
- Inserted calculated field to perform calculations on elements of pivot table.
- Refreshing the pivot table with data, automatic or manual updating of the report layout.
- Grouping the data within a pivot table.
- Conditional formatting in pivot tables.
- Creating pivot charts.
- What-if analysis
- Scenarios
- Creating Scenarios and setting variables for different Scenarios.
- Merging different Scenarios.
- Creating a summary of different Scenarios.
- Using pivot table in Scenarios
- Data tables
- Creating one-variable Data table.
- Creating two-variable Data table.
- Arranging data for creating Data table.
- Scenarios
- Charts
- Introduction to different chart types.
- Plotting data series from work sheet rows and columns.
- Changing the chart type.
- Working with chart area, plot area and various chart elements.
- Adding a secondary series in the chart.
- Creating combo charts.
- Macros
- Introduction to Macros.
- Preparing a workbook for Macros.
- Recording Macros using absolute and relative reference.
- Running Macros.
- Visual basic interface and editor.
- Grouping and Ungrouping Data
- Grouping data in rows and columns.
- Using subtotal.
- Creating Hyperlinks
- Creating hyperlinks with in a workbook.
- Creating hyperlinks to word and power point.
- Using hyperlinks to create an index or menu in an excel file.
- Data Management
- Removing duplicates in data.
- Consolidating data from different worksheets and workbooks.
- Creating custom lists for autofill.
- Paste special- Transposing columns to rows, rows to columns, performing mathematical operations while pasting.
- Workbooks and Worksheets
- Grouping worksheets and working with grouped sheets.
- Viewing and editing workbook properities.
- Arranging multiple windows, multiple views.
- Protecting workbooks, worksheets and cells.
- Sharing workbooks, protecting shared workbooks.
- Merging workbooks.
- Printing
- Print options – Setting print area, printing headers on all pages.
- Working with headers and footers.
