Advanced Excel 

Excel is one of the most common software used in many workplaces around the world. Learning to confidently operate excel means adding a highly valuable asset to your portfolio. At a time when digital skills jobs are growing much faster than non-digital jobs, completing this course will position you ahead of others. The course is aimed at those familiar with Excel but looking to learn how to get the most out of the in-built features.

Course Objectives

By the end of this course the participants will be able to:

  • Modify Excel options
  • Understand and use formula cell referencing
  • Use the fill operations available to fill a data series
  • Create and use defined names in a workbook
  • Use common worksheet functions
  • Create more complex formulas and functions
  • Apply a range of number formatting techniques to worksheet cells
  • Apply conditional formatting to ranges in a worksheet
  • Use a variety of data validation techniques
  • Use data linking to create more efficient workbooks
  • Use goal seeking to determine the values required to reach a desired result
  • Work with tables in Microsoft Excel
  • Use a range of elements and features to enhance charts

Course Outline

Each session will include a lecture followed by practicals. Participants will also get a chance to see how to apply Excel in a real-life scenario.

1. Setting Excel Options: 

Understanding Excel Options, Personalising Excel, Setting the Default Font, Setting Formula Options, Understanding Save Options, Setting Save Options, Setting the Default File Location, Setting Advanced Options.

2. Formula/Cell Referencing in Excel

Absolute Versus Relative, Referencing Relative Formulas, Problems With Relative Formulas, Creating Absolute References, Creating Mixed References.

3. Filling Data

Understanding Filling, Extracting With Flash Fill, More Complex Flash Fill Extractions, Extracting Dates and Numbers

4. Defined Names

Understanding Defined Names, Defining Names from Worksheet Labels, Using Names in Typed Formulas, Applying Names to Existing Formulas, Creating Names Using the Name Box, Using Names to Select Ranges, Pasting Defined Names into Formulas, Defining Names for Constant Values, Creating Names from a Selection, Scoping Names to a Worksheet, Using the Name Manager.

5. Essential Functions

Worksheet Functions, Using IF With Text, Using IF With Numbers, Nesting IF Functions, The VLOOKUP Function, Using Counting Functions, The ROUND Function, Rounding Up and Rounding Down, Manipulative Functions, The MOD Function, The TODAY Function, The NOW Function, The DATE Function, The PMT Function

6. Complex Formulas

Creating the Base Formula, Adding More Operations, Editing a Complex Formula, Adding More Complexity, Copying Nested Functions, Switching to Manual Recalculation, Pasting Values from Formulas.

7. Number Formatting Techniques

Applying Alternate Currencies, Applying Alternate Date Formats, Formatting Clock Time, Formatting Calculated Time, Understanding Number Formatting, Creating Descriptive Custom Formats, Custom Formatting Large Numbers, Custom Formatting for Fractions, Padding Numbers Using Custom Formatting, Aligning Numbers Using Custom Formats, Customising the Display of Negative Values.

8. Conditional Formatting

Understanding Conditional Formatting, Formatting Cells Containing Values, Clearing Conditional Formatting, More Cell Formatting Options, Top Ten Items, More Top and Bottom Formatting Options, Working With Data Bars, Working With Colour Scales, Working With Icon Sets, Understanding Sparklines, Creating Sparklines, Editing Sparklines, Creating Custom Rules, The Conditional Formatting Rules Manager, Managing Rules, Clearing Rules.

9. Validating Data

Understanding Data Validation, Creating a Number Range Validation, Testing a Validation, Creating an Input Message, Creating an Error Message, Creating a Drop-Down List, Using Formulas as Validation Criteria, Circling Invalid Data.

9. Data Linking

Understanding Data Linking, Linking Between Worksheets, Linking Between Workbooks, Updating Links Between Workbooks, Using Names to Link Between Workbooks.

10. Goal Seeking

Understanding Goal Seeking, Using Goal Seek

11. Worksheet Tables

Understanding Tables, Creating a Table from Scratch, Working With Table Styles, Inserting Table Columns, Removing Table Columns, Converting a Table to a Range, Creating a Table from Data, Inserting or Deleting Table Records, Removing Duplicates, Sorting Tables, Filtering Tables, Renaming a Table, Splitting a Table, Deleting a Table.

12. Chart Elements

Understanding Chart Elements, Adding a Chart Title, Adding Axes Titles, Repositioning the Legend, Showing Data Labels, Formatting the Chart Area, Adding a Trendline, Adding Error Bars

13. GoTo Special

Finding Constants, Finding Formulas, Finding Blanks

14. Other Functions and Content

SUMIF Functions, Special Pasting, Finding and Replacing, Text Functions, Financial Functions, Grouping and outlining

 

Virtual Training Schedule

Start Date End Date Reg Deadline Location Course Fee Apply
Apr 03, 2023
Apr 07, 2023
Mar 31, 2023
Online
$ 345
Apr 24, 2023
Apr 28, 2023
Apr 21, 2023
Online
$ 345
May 01, 2023
May 05, 2023
Apr 29, 2023
Online
$ 345
May 22, 2023
May 26, 2023
May 19, 2023
Online
$ 345
Jun 19, 2023
Jun 23, 2023
Jun 16, 2023
Online
$ 345

Classroom Training Schedule

Start Date End Date Reg Deadline Location Course Fee Apply
Apr 17, 2023
Apr 21, 2023
Apr 14, 2023
Nairobi
$ 940
May 15, 2023
May 19, 2023
May 12, 2023
Nairobi
$ 940
Jun 19, 2023
Jun 23, 2023
Jun 9, 2023
Nairobi
$ 940
Jul 10, 2023
Jul 14, 2023
Jul 7, 2023
Nairobi
$ 940
Aug 14, 2023
Aug 18, 2023
Aug 11, 2023
Nairobi
$ 940
Sep 11, 2023
Sep 15, 2023
Sep 08, 2023
Nairobi
$ 940

Course Language

This Training course is offered in ENGLISH . Please indicate the language of choice during registration.

Course Delivery

Presentations are well guided, practical exercise, a plenary presentation, and group work. Participants are encouraged to bring any data relevant to their job responsibilities. This is hands-on, product-oriented training and will mostly involve practical exercises. Each participant MUST bring along their own working laptop and android phone.

Certification

Upon completion of training, the participant will be issued with a certificate of Completion.

Tailor-Made Course

3 months post-training support, consultation, and coaching is a guarantee from us and will be available after the course.We can also do this as a tailor-made course to meet organization-wide needs. Contact us to find out more: info@bluestron.co.ke