Microsoft Excel Advanced Course Contents
Microsoft Excel (Course 307)
MICROSOFT EXCEL OPTIONS
- AUTOMATIC CALCULATION, AUTOCOMPLETE, ERROR CHECKING RULES
- CUSTOMISING RIBBONS & QUICK ACCESS TOOLBAR
DATABASES
- HOW ARE
RECORDS STORED
- DELIMITERS
- THE EXCEL STRUCTURE
- HEADER ROW
- ENTERING THE INFORMATION
- FLAT FILE DATABASE
- RELATIONAL DATABASE
USING FORMS FINDING INFORMATION LABELS AND NAMES
- THINGS TO KNOW
- THE NAMING MANAGER
- NAMING CONSTANTS
DATA FILES OPEN DATA FILES IN EXCEL SORTING AND FILTERING
- SORTING
- FILTERING
- DATABASE STORAGE FORMATS
ADVANCED FILTER DATA VALIDATION, DROP DOWN LISTS AND NAMED RANGES COMBINE THE CONTENTS OF CELLS
- FIND AND REPLACE
KEYBOARD SHORTCUTS
- SELECTING WORKSHEET AREAS
- EDITING DATA
Microsoft Excel (Course 308)
ADVANCED OPTIONS
- Task: Show the Developer Ribbon
3D FORMULAS BETWEEN SHEETS
- CREATING A VARIABLES SHEET
- Task: Re-create these worksheets using 3D formulas
FILTERING YOUR DATA
- Exercise: Using AutoFilter
CLEANING UP A DATA FILE USING AUTOFILTER
- Exercise: Cleaning up a database using AutoFilter
ADVANCED FILTER
- Exercise: Perform an Advanced filter
- Exercise: Advanced filter using Wildcards (search text within cells)
- Exercise: Advanced Filter for multiple search criteria (OR)
- Exercise: Narrow the results using Advanced Filter (AND)
CREATING A PIVOTTABLE
- Task: Insert a Pivot Table
- Task: Filter the data in a Pivot Table
- Task: Sort Dates by Month
- Test: Pivot Table
- Task: Change the Value
- Project: Using the PivotTable
- Project: Adding information using a function and getting total sales value
PIVOTTABLE CHARTS THE CHOOSE FUNCTION
- Task: Get month values and Choose
- Task: Automatically produce ratings
- Task: Choose the calculation you want to occur
VLOOKUP
- Task: Insert a VLOOKUP which tells us the Isle and price of a product
- Task: Perform vlookup using Named Ranges
HLOOKUP
- Task: Paste Special for Formulas, values, comments and formatting
MATCH AND INDEX
- Task: Find the match
- Incorporate cell information in text
- Task: Use Index to find the value in a matched row
NESTED MATCH AND INDEX FUNCTIONS
- Task: Performed a nested MATCH & INDEX function
- Task: Combine MATCH with IFERROR functions
PROTECTION
- Task: Open a protected workbook
- Task: Understanding cell protection
- Task: Finding Cells which contain calculations
Microsoft Excel (Course 309)
ADVANCED OPTIONS
- Task: Show the Developer Ribbon
GOAL SEEK
- 2.1.1 Task: How much can we afford to pay for a house
DATA CONSOLIDATION
- 3.1.1 Task: Consolidate contents from several sheets
- 3.1.2 Task: Data consolidation with Named Ranges
- 3.1.3 Task: Format Painter between sheets and Groups
- 3.1.4 Task: Understand Grouping
MANUALLY GROUPING SOLVER
- 5.1.1 Task: Solve a problem
INSERTING FORM COMPONENTS
Watch a Sample Training Video Student Testimonials Enrol Now