Excel Advanced Table of Contents

Breaking News & Updates

Xero’s financial report reveals BIG loss but share price pop. - eepurl.com/isNmKQ

About a year ago from Steve Slisar's Twitter via Mailchimp



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