# Excel Intermediate Workshop

£75.00£2,250.00

SKU: GCOL-EXL-102 Category:

## Description

This Intermediate Excel online training course uses a practical approach to learning this powerful software tool: analysis of key features, combined with step-by-step tutorials which will reinforce your understanding of what Microsoft Excel is all about.

Aimed at beginner to intermediate Excel users, who may already be familiar with the basics, this course will provide the practical insights, tips and shortcuts you need to start becoming an Excel power user.

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.

Our Intermediate Excel online training course also covers creating and customizing charts and graphics: practical exercises on creating the different types of Excel charts, creating sparklines, importing and creating graphics and embedding formulas into graphic elements.

We then move on to look at working with tabular data: sorting data numerically, chronologically, by colour and by custom order, filtering data and creating multi-level subtotals. Creating and customizing pivot tables, pivot charts and dashboards, featuring slicers and timelines which provide interactive data filtering and exploration.
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.

COURSE OUTLINE

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

2. Conditional Functions

1. The IF Function
2. The IFERROR Function
3. Nested IF Statements
4. The OR Function
5. The AND Function

3. Conditional Number Crunching Functions

1. The COUNTIF Function
2. The SUMIF Function

4. Lookup Functions

1. VLOOKUP Exact Match
2. VLOOKUP Approximate Match
3. Using INDEX and MATCH

5. Date and Time Functions

1. The Today and Now Functions
2. Inserting Dates using AutoFill
3. The NETWORKDAYS Function

1. Text Alignment
2. Text Wrap
3. Using the Format Painter
4. Conditional Formatting
5. Creating Cell Styles

7. Managing Multiple Workbooks and Worksheets

1. Setting the Number of Sheets in a New Workbook
2. Working in Group Mode
3. Viewing Multiple Workbooks
4. Moving and Duplicating Sheets

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

9. Sparklines and Graphics

1. Creating Sparklines
2. Using Formulas with Graphics

10. Working with Excel Tables

1. The 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 Table Data

11. Sorting Data

1. Sorting by a Single Column
2. Sorting by Multiple Column
3. Using a Custom List for Sorting
4. Sorting Columns instead of Rows
5. Sorting Data in a Random Order

12. Filtering Data

1. Filtering Specific Values
2. Using the Search Box
3. Using Text Filters
4. Using Number Filters
5. Using Date Filters

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

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