__________

Course Overview:

Are you lookingto build yourskills in usingMicrosoft Excel?

This course gives you a deep understanding of how to use some of the most popular features and functions in Excel. We will build you from a base skill level and assume you have no prior knowledge in using Microsoft Excel or its functions.

In this course we will cover Excel Introduction, Intermediate, Advanced and Formulas and Functions. Most lectures are 7- 14minutes long, withno lecture being over 20 minutes in length. Your trainer for this course, Richard Walters, is a MicrosoftCertified Trainer who has beentraining MicrosoftExcel for the past 10 years throughout the UK.

__________

Excel Introduction Outline:

• The Excel Environment

• Select and Highlighting Cells

• Entering Information

• Formatting Information

• Modifying Information - Wrap Text and Merging Cells

• Inserting/Deleting Columns and Rows

• Save, Save As and Open

• Find Opening and Closing Excel

• Creating and Copying Formulas

• Using BODMAS in Formulas

• Absolute Cell Referencing

• Using Functions - SUM, IF, COUNT, AVERAGE

• AutoSum Features

• Difference between Excel manual and automated calculations

• Creating and Formatting Charts

• Inserting, Moving and Deleting Worksheets

• Grouping Worksheets

• AutoFill Feature

• Linking formulas between Worksheets

• Freeze Panes

• Print Area and Print Preview

__________

Excel Intermediate Outline

• Paste special features

• Charts and Formatting Charts

• Conditional Formatting and Conditional Formulas

• Security (adding protection)

• Sub totalling information

• Filtering and advanced filtering

• Named cells, ranges and using names in formulas

• Database functions

• Linking formulas & functions

• Mixed reference formulas

• Auditing Formulas

• Freeze Panes

• Printing using freeze panes and Print Area

__________

Excel Advanced Outline

• Advanced Charts and Linking Charts to Other Applications

• Scenarios

• Data Tables

• Goal Seek

• Data Validation

• Pivot Tables

• Formulas & Functions

  • Consolidated Formulas

  • Date Formulas and Functions

  • Referencing Formulas

  • Error Functions

  • Logical Functions (IF, AND, OR)

  • Nested Functions (IF, AND, OR)

  • Lookup Functions (VLookup, HLookup)

  • Mathematical Functions (Sumif, Sumifs)

  • Statistical Functions (Countif, Countifs, Averageif, Averageifs,
    Countblank)

  • Text Functions (Trim, Left, Right, Mid, Concatenate)

__________

Excel Formulas and Functions Outline

• Creating Date Formulas

• Now

• Day

• Month

• Year

• Vlookup

• Hlookup

• Match and Index

• IFError

• IF

• And

• Or

• Not

• IF, AND, OR

• IF & Vlookup

• IF & Sumif

• Sumif

• Sumifs

• Countif

• Countifs

• Averageif

• Averageifs

• Countblank

• DSum

• DAdverage

• DMax

• DMin

• Trim

• Left

• Right

• Mid

• Concatenate

__________

Excel Advanced Filtering and Pivot Tables

• Multi Filters

• Filtering by Colour

• Custom Filters

• Criteria Filtering (i.e. Greater Than, Less Than, Between, Not Equal To)

• Multi Criteria Filtering (Advanced Filter)

• Multi Criteria Filtering “In Place” and “Copy of another Location”

• Designing and Creating a Pivot Table

• Modifying a Pivot Table Layout

• Updating a Pivot Table and using Pivot Table options

• Managing Pivot Table Totals and Sub Totals

• Grouping Pivot Table Data

• Creating a Pivot Table using External Data

• Filtering Pivot Tables using Slicers and Timelines

• Pivot Table “Dashboards” using Slicers

• Creating Multiple Pivot Tables on the same sheet

• Consolidating Pivot Table Data

• Pivot Table calculations

• Pivot Table Report pages

• Linking using the GETPIVOTDATA function

• Creating Pivot Charts and Modifying Pivot Charts

• Filtering Pivot Charts

__________

Excel Data Visualised and Data Formatting

• Highlight Cell Values i.e. Find Duplicates

• Top and Bottom Rules

• Data Bars

• Colour Scales

• Icon Sets

• Sum Function

• Average Function

• And / OR Functions

• Formulas and Cell Referencing

• Conditional Formatting Date Formulas

• Now Function

• Managing and Deleting Conditional Formatting Rules

• Conditional Formatting and Data Validation

• Conditional Formatting and Paste Special

• Adding Conditional Formatting settings to empty cells

• Conditional Formatting and Linked Data

• Adding Sparklines

• Formatting and Modifying Sparklines

Whether you're looking for a quick overview on some of Excel's most popular features trying to diversify your Excel skill set, or hoping to step up your spreadsheet game in a major way, you've come to the right place. In today's increasingly data-driven world, Excel makes it easy and intuitive to keep track of data sets. Enrol today and start your journey to becoming a Excel master!

See you there!

-Richard Walters (Microsoft Excel Trainer)