Course Description

This is a practical, hands-on course for Excel middleweights looking to gain more confidence in using the program. It provides step-by-step practice in building formulas with useful text functions like LEFT, RIGHT, MID, PROPER and CONCATENATE; conditional functions like IF, IFERROR, SUMIF AND COUNTIF; lookup functions like VLOOKUP, INDEX and MATCH; and date functions like NETWORKDAYS. The course also covers charts, sparklines, conditional formating, and graphics. It then moves on to look at all of Excel's key facilities for working with tabular data: customized sorting and filtering, subtotalling and the use of Excel tables and pivot tables. All of the worksheets used by the trainer are available to students, enabling them to follow along and practice all of the techniques being demonstrated.

Grant Gamble

Grant Gamble is an experienced IT trainer, developer, consultant and author able to deliver a wide range of training courses. He has a vast experience of delivering public and on-site IT training content at different skill levels, to groups of varying sizes. His UK company G Com Solutions Limited provide IT training courses and consultancy to a wide range of UK and international clients. His speciality is running week-long, intensive training workshops on topics like Microsoft VBA, web development and Adobe Creative Suite automation.

Course curriculum

  • 1

    Getting Started

    • 1. Welcome

    • 2. Downloading the Course Files

    • Download the Course Files Here
  • 2

    1. Text Functions

    • 1. Flash Fill Revision

    • 2. Using the TRIM Function

    • 3. Changing the Case of Text

    • 4. The LEFT and RIGHT Functions

    • 5. The MID Function

    • 6. The CONCATENATE Function
  • 3

    2. Conditional Functions

    • 1. The IF Function

    • 2. The IFERROR Function

    • 3. Nested IF statements

    • 4. The OR Function

    • 5. The AND Function
  • 4

    3. Conditional Number Crunching Functions

    • 1. The COUNTIF Function

    • 2. The SUMIF Function
  • 5

    4. Lookup Functions

    • 1. VLOOKUP Exact Match

    • 2. VLOOKUP Approximate Match

    • 3. INDEX and MATCH
  • 6

    5. Date and Time Functions

    • 1. The TODAY and NOW Functions

    • 2. Inserting Dates Using AutoFill

    • 3. The NETWORKDAYS Function
  • 7

    6. Advanced Formatting

    • 1. Text Alignment

    • 2. Text Wrap

    • 3. Using the Format Painter

    • 4. Conditional Formatting

    • 5. Creating Cell Styles
  • 8

    7. Managing Multiple Workbooks and Worksheets

    • 1. Setting the Number of Sheets in New Workbooks

    • 2. Working in Group Mode

    • 3. Viewing Multiple Workbooks

    • 4. Moving and Duplicating Sheets
  • 9

    8. Working with Charts

    • 1. Anatomy of an Excel Chart

    • 2. Creating a Column Chart

    • 3. Creating a Line Chart

    • 4. Creating a Pie Chart

    • 5. Creating a Pie of Pie Chart

    • 6. Creating a Doughnut Chart

    • 7. Creating a Bar Chart
  • 10

    9. Sparklines and Graphics

    • 1. Creating Sparklines

    • 2. Using Formulas with Graphics
  • 11

    10. Working with Excel Tables

    • 1. Benefits of Using Excel Tables

    • 2. Creating an Excel Table

    • 3. Entering Data into a Table

    • 4. Using a Total Row

    • 5. Entering Formulas into a Table

    • 6. Creating Dynamic Charts from a Table
  • 12

    11. Sorting Data

    • 1. Sorting by a Single Column

    • 2. Sorting by Multiple Columns

    • 3. Using a Custom List for Sorting

    • 4. Sorting Columns instead of Rows

    • 5. Sorting Data in a Random Order
  • 13

    12. Filtering Data

    • 1. Filtering Specific Values

    • 2. Using the Search Box

    • 3. Using Text Filters

    • 4. Using Number Filters

    • 5. Using Date Filters
  • 14

    13. Outlining and SubTotalling

    • 1. Manually Outlining Data

    • 2. Creating an Automatic Outline

    • 3. Showing and Hiding Outline Symbols

    • 4. Creating Single Level Subtotals

    • 5. Creating Multi-level Subtotals
  • 15

    14. Introduction to Pivot Tables

    • 1. Pivot Tables and Subtotalling Compared

    • 2. Creating a Pivot Table Part 1

    • 2. Creating a Pivot Table Part 2

    • 3. Grouping Pivot Table Data