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 Field Settings
• 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