MS Excel Advanced

Course Objectives:

• Understand the intermediate functions of Microsoft® Excel 2010, and how it is different from the basic version
• Understand how to navigate efficiently to the relevant tools within the Microsoft® Excel Chart Basics
• Prepare and format Microsoft® Excel sheets and working with multi worksheets
• Be able to format various chart styles
• Proficiently use Advanced Charting
• Be able to use different viewing options
• Be able to use all advanced formatting and display options
• Be able to consolidate data and formatting charts
• Inserting pivot tables and charts into your Excel sheets
• Be able to secure, protect and customise your workbook
• Understand how to use macros and be able to use all logical and statistical functions
• Understand advanced financial functions including all Lookup functions
• Understand and utilise advanced data management

Course Outlines:

• Creating and Working with Tables
• Working With Multiple Excel Worksheets
  – Renaming Your Sheet
  – Inserting & Deleting Sheets
  – Changing The Order Of Your Sheets
  – Creating A Copy Of A Sheet
• Creating Charts Within Microsoft® Excel
• Chart Basics
  – Viewing Various Preset Chart Options Available Within Microsoft® Excel
  – Resizing A Chart
  – Formatting The Text, Colour, Etc., Within A Chart
  – Copying & Moving Charts Within Worksheets
• Advanced Charting
  – Chart Formatting Options
  – Combination Charts
  – Graphical Elements
• Using The Different View Options Available & Understanding Their Benefits
• Setting The Print Area For Your Sheet
• Inserting Headers & Footers
• Repeating Rows & Columns On Printed Sheets For Easy Viewing
• Using The Paste Special Options
• Importing & Exporting Text
• Naming Cell Ranges For Easy Viewing
• Advanced Formatting
• Working With Subtotals
  – Creating & Removing Subtotals
• Formatting & Display
  – Formatting Tables Using Preset Styles
  – Customising Preset Styles
  – Using Conditional Formatting Options For Easy Formatting As You Type
  – Hiding & Unhiding Rows, Columns & Worksheets
• Use The Sort & Query Functions
  – Customising The Sort Function
  – Applying Single / Multiple Filters To Data
  – Customising The Filter Queries
  – Removing Filters
• Consolidating Data
  – Combining Data From Various Cells Into A Single Cell
  – Linking Cells, Charts, Etc., – In Same & Different Sheets
• Formatting The Following In Your Charts:
  – Pie Chart Angles
  – Formatting & Positioning The Chart Axis, Chart Title & Chart Legend
  – Changing The Chart Design After A Chart Has Been Created
  – Adding & Deleting A Data Series Within A Chart
• Inserting Pivot Tables & Charts Into Your Excel Sheets
• Protecting Your Workbook
  – Adding A Password Protection For Your Workbook
  – Finalising Your Workbook As ‘Read Only’
  – Defining Data Entry Rules To Prevent Incorrect Information Being Entered
  – Customised Protection For Your Workbook To Allow Selective Editing
• Macros
  – Recording Macros To Speed Up Frequently Performed Actions
  – Assigning Shortcut Keys To Recorded Macros
• Customising The Quick Access Toolbar
• Logical & Statistical Functions
• Financial Functions
  – Using PMT Functions
• Lookup Functions
• VLOOKUP AND HLOOKUP
• Advanced Data Management
  – Observing Data Validation
  – Creating Data Validation Rule