Excel Intermediate (Level 2) Course Outline With Examples

Have Fun Learning Microsoft Excel Level 2

Excel is the most powerful tool to manage and analyze various types of Data. This tutorial covers in-depth lessons on how to use various Excel formulas, Tables and Charts for managing small to large scale business process.

*EXCEL SETUP & PRINTING ISSUES

• Worksheet margins
• Worksheet orientation
• Worksheet page size
• Headers and footers
• Header and footer fields
• Scaling your worksheet to fit a page(s)
• Visually checking your calculations
• Displaying gridlines when printing
• Printing titles on every page when printing
• Printing the Excel row and column headings
• Spell checking
• Previewing a worksheet
• Viewing workbooks side by side
• Zooming the view
• Printing options
• Setting the number of copies to print
• Selecting a printer
• Selecting individual worksheets or the entire workbook
• Selecting which pages to print
• Print Page Numbers in Excel
• Single or double sided printing
• Collation options
• Clear print area in Excel Worksheet
• Page orientation
• Paper size
• Margins
• Scaling
• Printing

*FUNCTIONS AND FORMULAS

• Getting help with functions
• Nested functions
  • Excel Functions Practical Examples
  • Excel If, Nested If, And/Or Criteria Examples
  • Invoice status with nested if in Excel
  • Nested IF function example in Excel
• Consolidating data using a 3-D reference sum function
  • 3D-reference example in Excel
  • 3D sum multiple worksheets in Excel
  • 3D SUMIF for multiple worksheets in Excel
• Mixed references within formulas
  • Relative, Absolute and Mixed Referencing Examples

*TIME & DATE FUNCTIONS

• Inserting the current time and date
• Today function
• Now function
• Day function
• Month function
• Year function

*MATHEMATICAL FUNCTIONS

• Round function
• Rounddown function
• Roundup function

*LOGICAL FUNCTIONS

• If function
• And function
• Or function

*MATHEMATICAL FUNCTIONS

• Sumif function

*STATISTICAL FUNCTIONS

9 Mathematical Computations Example using Excel Statistical Function

• Count function
• Counta function
• Countif function
• Countblank function
• Rank function

*TEXT FUNCTIONS

• Left function
• Right function
• Mid function
• Trim function
• Concatenate function

*FINANCIAL FUNCTIONS

• Fv function
• Pv function
• Npv function
• Rate function
• Pmt function

*LOOKUP FUNCTIONS

• Vlookup function
  • Lookup with variable sheet name in Excel
• Hlookup function

*DATABASE FUNCTIONS

• Dsum function
• Dmin function
• Dmax function
• Dcount function
• Daverage function

*EXCEL NAMED RANGES

• Naming cell ranges
  • Understanding Named Range, Named Constant, Name Manager in Excel Formulas
  • How to get address of named range in Excel
  • How to create dynamic named range with OFFSET in Excel
  • How to reference named range different sheet in Excel
• Removing a named range
• Named cell ranges and functions

*EXCEL CELL FORMATTING

• Applying styles to a range
• Conditional formatting
  • Conditional Formatting Rules in Excel
  • Conditional Formatting New Rule with Formulas in Excel
  • Managing Conditional Formatting Rules in Excel
  • Conflicting Multiple Conditional Formatting Rules in Excel
  • Conditional Formatting Data bars Examples in Excel
  • Conditional Formatting Icon Sets Examples in Excel
  • Highlight rows with blank cells in Excel
  • Highlight values between in Excel
• Custom number formats
  • Excel Custom Number Format with Leading Zeros, Add Text, Colors Example
  • Advanced Number Formats in Excel

MANIPULATING WORKSHEETS WITHIN EXCEL

• • How to Break Worksheets into Pages in Excel
  • Split Worksheet Window in Excel
  • Hide and Unhide Columns or Rows in Excel
• Copying or moving worksheets between workbooks
• Splitting a window
• Hiding rows
• Hiding columns
• Hiding worksheets
• Un-hiding rows
• Un-hiding columns
• Un-hiding worksheets

EXCEL TEMPLATES

• Using templates
• Creating Excel templates
• Editing Excel templates
  • Excel Default Templates
  • Using Existing Templates in Excel
  • Creating and Opening an existing file in Excel Workbook

PASTE SPECIAL OPTIONS WITHIN EXCEL

• Using paste special to add, subtract, multiply & divide
• Using paste special ‘values’
• Using paste special transpose option
  • Understanding Various Paste Options in Excel
  • How To Skip Blanks Using Paste in Excel
  • Paste Special Operations in Excel