MS EXCEL COURSE OUTLINE

Table of Contents
1.0 SESSION ........................................................................................................................................................... 2
1.1 INTRODUCTION TO EXCEL ...................................................................................................................................2
1.2 ENTERING AND EDITING TEXT AND FORMULAS..................................................................................................2
2.0 SESSION ........................................................................................................................................................... 2
2.1 MODIFIYING EXCEL SHEET ...................................................................................................................................2
2.2 WORKING WITH BASIC EXCEL FUNCTION............................................................................................................2
3.0 SESSION ........................................................................................................................................................... 3
3.1 FORMATING DATA ON EXCEL ..............................................................................................................................3
3.2 INSERTING IMAGES AND SHAPES ........................................................................................................................3
4.0 SESSION ........................................................................................................................................................... 3
4.1 CREATING BASIC CHARTS IN EXCEL .....................................................................................................................3
4.2 PRINTING AN EXCEL WORKSHEET .......................................................................................................................3
5.0 SESSION ........................................................................................................................................................... 4
5.1 WORKING WITH AN EXCEL LIST ...........................................................................................................................4
6.0 SESSION ........................................................................................................................................................... 4
6.1 DATA VALIDATION ...............................................................................................................................................4
6.2 IMPORTING AND EXPORTING DATA ....................................................................................................................4
7.0 SESSION ........................................................................................................................................................... 5
7.1 EXCEL PIVOT TABLES ...........................................................................................................................................5
8.0 SESSION 8;........................................................................................................................................................ 5
8.1 POWERPIVOT.......................................................................................................................................................5
8.2 WORKING WITH LARGE SETS OF DATA ...............................................................................................................5
9.0 SESSION 9:........................................................................................................................................................ 5
9.1 EXCEL CONDITIONAL FUNCTION .........................................................................................................................5
10.0 SESSION 10 ..................................................................................................................................................... 6
10.1 LOOK UP FUNCTION ..........................................................................................................................................6
11.0 SESSION 11 .................................................................................................................................................... 6
11.1 TEXT BASED FUNCTIONS ...................................................................................................................................6
11.2 AUDITING AN EXCEL WORK SHEET ....................................................................................................................6
 BREAKDOWN

TUTOR: JOFREY RUTTA CPB(T)

0654 563 343

1.0 SESSION
1.1 INTRODUCTION TO EXCEL
▪ Launching Excel
▪ Microsoft Excel Startup Screen
▪ Introduction to the Excel Interface
▪ Customizing the Excel Quick Access Toolbar
▪ More on the Excel Interface
▪ Understanding the Structure of an Excel Workbook
▪ Saving an Excel Document
▪ Opening an Existing Excel Document
▪ Common Excel shortcuts
▪ Entering Text to Create Spreadsheet Titles

1.2 ENTERING AND EDITING TEXT AND FORMULAS

▪ Entering Text to Create Spreadsheet Titles
▪ Working with Numeric Data in Excel
▪ Entering Date Values in Excel
▪ Working with Cell References
▪ Creating Basic Formulas in Excel

2.0 SESSION
2.1 MODIFIYING EXCEL SHEET
▪ Moving and Copying Data in an Excel Worksheet
▪ Inserting and Deleting Rows and Columns
▪ Changing the Width and Height of Cells
▪ Hiding and Unhiding Excel Rows and Columns
▪ Renaming an Excel Worksheet
▪ Deleting an Excel Worksheet
▪ Moving and copying an Excel Worksheet

2.2 WORKING WITH BASIC EXCEL FUNCTION

▪ Working with Numeric Data in Excel
▪ Working with the SUM() Function
▪ Working with the MIN() and MAX() Functions
▪ Working with the AVERAGE() Function
▪ Working with the COUNT() Function
 ▪ Adjacent Cells Error in Excel Calculations
▪ Using the AutoSum Command
▪ Excel's AutoSum Shortcut Key

3.0 SESSION
3.1 FORMATING DATA ON EXCEL
▪ Font formatting
▪ Changing the Background Color of a Cell
▪ Adding Borders to Cells
▪ Excel Cell Borders Continued
▪ Formatting Data as Currency Values
▪ Formatting Percentages
▪ Using Excel's Format Painter
▪ Creating Styles to Format Data
▪ Merging and Centering Cells
▪ Using Conditional Formatting
▪ Editing Excel Conditional Formatting

3.2 INSERTING IMAGES AND SHAPES

▪ Inserting Images
▪ Inserting Excel Shapes
▪ Formatting Excel Shapes
▪ Working with Excel SmartArt

4.0 SESSION
4.1 CREATING BASIC CHARTS IN EXCEL
▪ Working with the Excel Chart Ribbon
▪ Adding and Modifying Data on an Excel Chart
▪ Formatting an Excel Chart
▪ Moving a Chart to another Worksheet
▪ Working with Excel Pie Charts

4.2 PRINTING AN EXCEL WORKSHEET

▪ Viewing your Document in Print Preview
▪ Changing the Margins, Scaling and Orientation
▪ Excel Worksheet Margins
▪ Working with Page Layout View
▪ Adding Header and Footer Content
▪ Printing a Specific Range of Cells
5.0 SESSION
5.1 WORKING WITH AN EXCEL LIST
▪ Understanding Excel List Structure
▪ Sorting a List Using Single Level Sort
▪ Sorting a List Using Multi-Level Sorts
▪ Using Custom Sorts in an Excel List
▪ Filter an Excel List Using the AutoFilter Tool
▪ Creating Subtotals in a List
▪ Format a List as a Table
▪ Using Conditional Formatting to Find Duplicates
▪ Removing Duplicates

EXCEL LIST FUNCTION

▪ Excel DSUM Function Single Criteria Continued
▪ Excel DSUM Function with OR Criteria
▪ Excel DSUM Function with AND Criteria
▪ Excel Function: DAVERAGE()
▪ Excel Function: DCOUNT()
▪ Excel Function: SUBTOTAL()

6.0 SESSION
6.1 DATA VALIDATION
▪ Understanding the Need for Excel Data Validation
▪ Creating an Excel Data Validation List
▪ Excel Decimal Data Validation
▪ Adding a Custom Excel Data Validation Error
▪ Dynamic Formulas by Using Excel Data Validation Techniques

6.2 IMPORTING AND EXPORTING DATA

▪ Importing Data Into Microsoft Excel
▪ Importing Data from Text Files
▪ Importing Data from Microsoft Access
▪ NEW VERSION -- Import Data From Text Files into Excel
▪ NEW VERSION -- Import Data From a Database into Excel
▪ Microsoft Excel Legacy Import Options for New Excel Versions
▪ Exporting Data to a Text File
7.0 SESSION
7.1 EXCEL PIVOT TABLES
▪ Understanding Excel PivotTables
▪ Creating an Excel PivotTable
▪ Modifying Excel PivotTable Calculations
▪ Grouping PivotTable Data
▪ Formatting PivotTable Data
▪ Modifying PivotTable Calculations
▪ Drilling Down into PivotTable Data
▪ Creating PivotCharts
▪ Filtering PivotTable Data
▪ Filtering with the Slicer Tool

8.0 SESSION
8.1 POWERPIVOT
▪ Introduction to Excel Power Pivot
▪ Why PowerPivot?
▪ Activating the Excel PowerPivot AddIn
▪ Creating Data Models with PowerPivot
▪ Excel Power Pivot Data Model Relationships
▪ Creating PivotTables based on Data Models
▪ Excel Power Pivot KPIs

8.2 WORKING WITH LARGE SETS OF DATA

▪ Using the Freeze Panes Tool
▪ Grouping Data (Columns and/or Rows)
▪ Print Options for Large Sets of Data
▪ Linking Worksheets (3D Formulas)
▪ Consolidating Data from Multiple Worksheets

9.0 SESSION
9.1 EXCEL CONDITIONAL FUNCTION
▪ Working with Excel Name Ranges
▪ Advantages and Disadvantages of Excel Name Ranges
▪ Editing an Excel Name Range
▪ Using Excel's IF() Function
▪ Excel's IF() Function with a Name Range
▪ Nesting Functions with Excel
▪ Nesting Excels AND() Function within the IF() Function
▪ Using Excel's COUNTIF() Function
 ▪ Using Excel's SUMIF() Function
▪ Using Excel's IFERROR() Function

10.0 SESSION
10.1 LOOK UP FUNCTION
▪ Microsoft Excel VLOOKUP() Function
▪ Microsoft Excel HLOOKUP() Function
▪ Microsoft Excel INDEX() Function
▪ Microsoft Excel MATCH() Function
▪ Microsoft Excel INDEX() and MATCH() Function Combined
▪ Microsoft Excel INDEX() and MATCH() Function Combined Continued
▪ Creating a Dynamic HLOOKUP() with the MATCH() Function

11.0 SESSION
11.1 TEXT BASED FUNCTIONS
▪ Using Excel's LEFT(), RIGHT() and MID() Functions
▪ Using Excel's LEN() Function
▪ Using Excel's SEARCH() Function
▪ Using Excel's CONCATENATE() Function

11.2 AUDITING AN EXCEL WORK SHEET

▪ Tracing Precedents in Excel Formulas
▪ Tracing Dependents in Excel Formulas
▪ Working with the Watch Window
▪ Showing Formulas
▪ Protecting Specific Cells in a Worksheet
▪ Protecting the Structure of a Workbook
▪ Adding a Workbook Password
▪ Working with Excel's Goal Seek Tool
▪ Working with Excel's Solver Tool
▪ Building Effective Data Tables in Excel
▪ Creating Scenarios in Excel